Sql Server 2005 Presentation

Microsoft SQL Server 2005 Presented to the New Mexico .NET Users Group by:

Overview The differences between SQL 2000 and 2005  What does the conversion mean to developers?  How to convert older databases to SQL 2005 

Differences between 2005 and 2000 SQL

…reduced application downtime, increased scalability and performance, and tight yet flexible security controls”

Differences between 2005 and 2000 SQL  Surface 



Limit services offered to smallest footprint OLE automation off by default

 Major 

area configuration

changes to tuning

Instance level tuning rather than DB level tuning

 How

multiple instances are handled 

SQL browser service

Differences between 2005 and 2000 SQL  Snapshots 

Transact SQL

Differences between 2005 and 2000 SQL  Database Mirroring  Continuously streams transaction log from a source server to a single target  Monitors primary and if fails target accepts connections automatically  Works on standard hardware  IP requests auto redirected  Redundant data  Failover Clustering  Certified hardware  Adds analysis, notification and replication services to clustering

Differences between 2005 and 2000 SQL  No

query analyzer  Dedicated admin connection sqlcmd -S URAN123 -U sa -P xxx –A  Online

index rebuilds don’t lock tables  Object script changes Much more granular control  Harder to script and compare – apexSQLDiff 

 SQL 

Server 2005 configuration is dynamic Very few exceptions, boost and lightweight pooling

What the conversion means to developers  Can

use CLR





Must be enabled in surface area configuration Doesn’t work with a variety of performance enhancement configuration options such as light weight pooling

What the conversion means to developers •

TSQL Try/Catch

What the conversion means to developers  TSQL 

Pivot and Unpivot DDL Triggers

What the conversion means to developers  OUTPUT

keyword

What the conversion means to developers  Programmable  

 Can

management

SQL Management Objects Analysis Management Objects

host web services without

IIS 

Any Web services application can access an instance of SQL Server

What the conversion means to developers  ADO

.Net 2.0  XML Data Type    

New data type not a string. Allows you to query XML nodes stored in the data Stored as BLOBs Can be uses in table column or stored procedure Can be parameter or variable

 Schemas   

Schemas exist independently of the database user that creates them Ownership of schemas can be transferred without changing their names Multiple users can own a single schema

What the conversion means to developers  Synonyms  Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.  Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.  Partitioning  Horizontal – split many row tables into separate smaller tables by rows.  Vertical – split many column tables into separate smaller tables by column 

Both types of partitioning can target file groups for performance boost

What the conversion means to developers  Reporting 

Report builder built into web services 



 

services improved

ClickOnce application deployed through the browser Targeted to the end user

SharePoint integration Web service as data source

What the conversion means to developers  SSIS

replace DTS

“Business Intelligence Development Studio makes building and debugging packages positively fun”  Complete rewrite for SQL 2005

What the conversion means to developers  SQL 

   

Mail (2000) versus Database Mail (2005) Multiple profiles and accounts to specify multiple SMTP servers or different email infrastructure situations SQL Server queues messages even when the external mailing process fails High security - users and roles have to be granted permission to send mail Logging and auditing HTML messages, attachment size regulations, file extension requirements, etc.

What the conversion means to developers

How to convert older databases • • • •

Upgrade wizard Restore Attach Compatibility settings – back to SQL 7

Gotchas  Oracle

server linking and SQL 64  SP OA’s fail:

Gotchas  Oracle

server linking and SQL 64  SP OA’s fail:

Gotchas  Multiple

instances not addressable without configuration  Local user accounts use new security policy settings – hard to change once account created  Creates

schemas for user objects in legacy databases

Where to go

 http://www.microsoft.com/sql/prod

 http://www.solutionwerx.com/SQL2

/  [email protected]  [email protected]