Microsoft - Sql Server 2005

Microsoft Financial Services Developer Conference 2004

What’s New in SQL Server 2005? Roger Wolter Program Manager SQL Server Engine Microsoft Corporation

SQL Server “Yukon”

The scalable, secure, enterprise-class data management platform

Enterprise Data Management

Programmability

Business Intelligence



Scalability, availability, security advancements



Storage of all data types, formats



Focus on ease-of-use



Leverage existing development skills



Share data across any platform



Provide end-to-end development tools



Scalable, comprehensive solution



Real time decision making tools



Extend information to employees, partners

Manageability - Tools 

New: SQL Server Workbench   



New: Management Objects (SMO) 



High Performance CMDLine

New: SQLMail Replacement 



Managed code, new design patterns

New: SQLCMD 



New Rich GUI Visual Studio Based UI Support all SQL Server components

SMTP Support, Cluster Support, Asynchronous

Improved: SQLAgent 

Manageability 

Dynamic Server Configuration  



Goal: No Server restarts E.g. CPU Affinity doesn’t require re-start

Events 

Async events on DDL 





Can be surfaced as WMI events

Triggers on DDL

Dedicated Admin Connection  

DBA access to Server regardless of load No restart to kill runaway session

Manageability 

New Metadata Catalogue  



More complete Information Read Only

Dynamic Management Views  

Views of Internal State >25 New Views in Yukon

Availability

Improving repair 

Backup improvements  



Data backups don’t block Log backups Fine-grained Online Repairs



Media reliability (CheckSums)



Backup Media Mirroring Online Restore







All datatypes (full text indices, cubes ...) Filegroup granularity for simple recovery

Restore/recovery improvements  

Online: Page, file, filegroup granularity Recovery - Resource Much shorter outage at software upgrade Redo committed Undo incomplete xactions database

Partitions   

Partition Tables + Indices by range Transparent to applications Can add, drop, split, merge partitions in seconds.     

Create new filegroup F Create empty version of the table F.T (no keys) Heap load F.T Index F.T Now add F.T to online table T (takes a second) Table T

Table T

A

B

C

D

E

F

F

Availability: Mirrored Systems 

How do you deal with catastrophe   



Cluster failover can take minutes 

 

 

Witness

What if I want 5 -9s? (5 minutes/year)

Mirrored Systems Two independent systems Replicas of one another 



Fire, flood, storm, earthquake, power/net breakdown? Data center move Sabotage, Gremlins?

Continuous SQL log shipping Mirror tracks primary Witness breaks ties

Easy to configure and admin

Primary

Mirror

Database Mirroring Witness



Database failover – Instant standby  Very fast … < 3 seconds  

   

Failed Repaired Primary Primary

New Mirror Primary

Automatic or manual Automatic re-sync after failover

Automatic and transparent client redirect No single point of failure No special hardware; standard computers and storage Minimal impact to transaction throughput

Availability 

Online Index build   



Build index in background (table always up) (also works for rebuild/drop) No table lock – good for foreign keys too Not for XML Indexes

Database View: Read-only copy of DB

Security 

Catalog and Metadata security 

 

User / Schema separation Password Policy enforcement 

 

See only what you have access to

Password strength, expiration

Certificate Based Authentication All permissions grantable

Full-Text Search 

Performance improvements in Yukon 

Indexing Performance: 



Query Performance:  



Building Full-Text indexes is two orders of magnitude faster Query performance has also improved considerably (Full-Text queries are 30-50% faster) Sort by rank setting now allows extremely fast FREETEXT-based queries

Scalability 

Yukon Full-Text Search is capable of handling large amounts of data  

Tested up to 2,000,000,000 rows of data Time to index scales nearly linearly with size of data

TSQL Enhancements 

Snapshot Isolation  

   

Readers don’t wait for writers Conflict detection

Recursive Queries Pivot and Unpivot Included Index columns Synonyms

CLR 

Infrastructure, Integration  Memory, threads, SQL

type library

Functions, Procedures, Triggers  User defined Types, Aggregates 

In-Proc Data Access (perf)  Symmetric data access 

 Mid-tier/data



tier deployment decision

Tight integration with Visual Studio  Authoring, debugging, profiling

Authoring/Debugging/Deploying  

New Visual Studio project type in “Whidbey” for “Yukon” managed code Server debug integration



Set breakpoints anywhere Full debugger visibility



Single step support:



 

Between languages: T-SQL, C#, VB, & C++ Between deployment tiers: 

E.g. Mid-tier, through SQL Server stored proc call & back to mid-tier

Data Access

API Enhancements: ADO .NET V2      

Multiple active result sets (MARS) Query notifications – for cache invalidation Functional parity with ADO (ADOX, etc) Asynchronous client access XML Data type support User Defined Type (UDT) support

SQL Web Services 

Native SOAP access   



New “HTTP ENDPOINT” object     



Standards based access to SQL Server No client dependency Improved Interop

HTTP.SYS

Configure connection info Configure authentication Expose Functions & SPs Expose TSQL Batches Configure WSDL

Leverages kernel-mode listener

http://myserver/sql Port: 80 Integrated Auth WebMethod = myDb.dbo.mySP HTTP Endpoint

XML Support 

XML Datatype  

 

XML Schema Collections XML Indexes  



Columns, Variables, Parameters Typed or Untyped

Indexes on tags, values & paths Understood by Optimizer

XQuery   

Query Instance or Column Insert, Update, Delete Extensions Nodes() Method

SQL Service Broker 

Platform for building Asynchronous, loosely coupled database applications 

Queues are now database objects 



Dialogs give unprecedented message ordering assurance 





Ordered across transactions, input applications and output applications

New DDL and DML for messaging 



Queue locking reduces conflicts and deadlocks

Use the same API’s and tools as SQL

Activation - the right number of readers running Reliable messaging for scale out

SQL Service Broker 

Built into the database     

Single database connection for messaging and other database operations No two-phase commits for transactional messaging Remote Receives from Transactional Queues Stored Procedure Activation Backup and failover of messages 



Messages have the same integrity guarantees as other database data - Clusters, Database Mirroring, etc.

Shared management platform with database 

Backup, space management, security

Data Types 

Varchar(1-8000), Nvarchar(1-4000), Varbinary(1-8000)  



Varchar(max), Nvarchar(max), Varbinary(max)  

 

Maximum column length still 8,000 bytes Rows can now exceed page size

Can hold greater than 8000 characters / bytes Same programming model as limited types

XML – A full-fledged data type UDTs – User-defined Data Types using CLR

Oracle Publishing Transactional Replication 

Designed specifically for Oracle Publishers 



Administered like SQL Server, from SQL Server  



v8+ on any operating system No Oracle side software install necessary Requires minimal knowledge of Oracle

Leverages existing SQL Server skills 

Standard Transactional and Snapshot Publications

Yukon Distributor

Subscribers

HTTPS Synchronization Merge Replication

IIS

Subscriber

-InternetUrl https://myserver.mydomain.com

Firewall

Replisapi.dll

Publisher/Distributor

Other replication changes   

Stand alone replication monitor DDL on published objects supported seamlessly while replicating New data types supported 

 

XML, CLR, varchar/varbinary(max) etc.

Merge performance improved 2-4x New security architecture

Reporting Services Architecture Browser

Office

Custom App Report Control Report Processing Rendering

Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom)

Web UI XML Web Service Interface

Data Proc.

Security Services (NT, Passport, Custom)

Report Server Report Processing

Output Formats (HTML, Excel, PDF, Custom)

Data Processing

Security

Rendering

Scheduling and Delivery

SQL Server Catalog

Delivery Targets (E-mail, SharePoint, Custom)

Notification Services Architecture Subscription Management Application

Data Changes

Events

Event Provider File System Watcher SQL Server provider Custom EP

Event Provider Host

Subscribers Subscriber Devices Subscriptions

Notifications

Generator

SQL Match Rule

External Delivery

Distributor XSLT XSLT

CF

Email File HttpExt .NET Alerts *

Custom CF

Custom DP

Data Transformation Svcs  

Major investment New ETL pipeline  



DW & BI Savvy  



Complex data routing Scale and performance Rich transformations Integrations with Analysis Services

Enhanced designer and control flow

DTS Yukon Architecture Custom transforms

Standard transforms

Data Destination Adapters

Data Source Adapters

XML Package

Sequences Loops Task s

XML Package

Event Handler s Wizards

DTS Designer

Command Line

Analysis Services 

Unified Dimensional Model 



Pro-active caching 



Bringing the best of MOLAP to ROLAP

Advanced Business Intelligence 



Integrating relational and OLAP views

KPIs, MDX scripts, translations, currency…

Web services 

Native XML/A

Data Mining  

Significant investment Important, new algorithms 



Association rules, time series, regression trees, sequence clustering, neural nets, Naïve Bayes

Embedded, integrated, complete

Get what you need

With an MSDN Universal Subscription

Visit the MSDN Table Learn more & Enter a drawing!

© 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.