Slide 1: Microsoft Financial Services Developer Conference 2004
Slide 2: What’s New in SQL Server 2005? Roger Wolter Program Manager SQL Server Engine Microsoft Corporation
Slide 3: SQL Server “Yukon”  The scalable, secure, enterprise-class data management platform Scalability, availability, security advancements Storage of all data types, formats Focus on ease-of-use Enterprise Data Management    Leverage existing development skills Share data across any platform Provide end-to-end development tools Programmability   Business Intelligence    Scalable, comprehensive solution Real time decision making tools Extend information to employees, partners
Slide 4: Manageability - Tools  New: SQL Server Workbench    New Rich GUI Visual Studio Based UI Support all SQL Server components Managed code, new design patterns High Performance CMDLine SMTP Support, Cluster Support, Asynchronous Scale, Perf, WMI Eventing     New: Management Objects (SMO)  New: SQLCMD  New: SQLMail Replacement  Improved: SQLAgent 
Slide 5: Manageability  Dynamic Server Configuration   Goal: No Server restarts E.g. CPU Affinity doesn’t require re-start Async events on DDL   Events   Can be surfaced as WMI events Triggers on DDL DBA access to Server regardless of load No restart to kill runaway session  Dedicated Admin Connection  
Slide 6: Manageability  New Metadata Catalogue   More complete Information Read Only Views of Internal State >25 New Views in Yukon  Dynamic Management Views  
Slide 7: Availability         Improving repair Backup improvements All datatypes (full text indices, cubes ...) Filegroup granularity for simple recovery Data backups don’t block Log backups Fine-grained Online Repairs Media reliability (CheckSums) Backup Media Mirroring Online Restore  Restore/recovery improvements    Online: Page, file, filegroup granularity Much shorter outage at software upgrade - Resource Recovery database Redo committed Undo incomplete xactions FASTER: Just redo
Slide 8: 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
Slide 9: Availability: Mirrored Systems  How do you deal with catastrophe    Fire, flood, storm, earthquake, power/net breakdown? Data center move Sabotage, Gremlins?    Cluster failover can take minutes  What if I want 5 -9s? (5 minutes/year) Primary Witness Mirrored Systems Two independent systems Replicas of one another    Mirror Continuous SQL log shipping Mirror tracks primary Witness breaks ties  Easy to configure and admin
Slide 10: 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
Slide 11: 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
Slide 12: Security  Catalog and Metadata security  See only what you have access to   User / Schema separation Password Policy enforcement  Password strength, expiration   Certificate Based Authentication All permissions grantable
Slide 13: Full-Text Search  Performance improvements in Yukon  Indexing 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  Query Performance:    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
Slide 14: TSQL Enhancements  Snapshot Isolation   Readers don’t wait for writers Conflict detection     Recursive Queries Pivot and Unpivot Included Index columns Synonyms
Slide 15: 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
Slide 16: 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
Slide 17: 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
Slide 18: SQL Web Services  Native SOAP access    Standards based access to SQL Server No client dependency Improved Interop HTTP.SYS  New “HTTP ENDPOINT” object      Configure connection info Configure authentication Expose Functions & SPs Expose TSQL Batches Configure WSDL http://myserver/sql Port: 80 Integrated Auth WebMethod = myDb.dbo.mySP HTTP Endpoint  Leverages kernel-mode listener
Slide 19: XML Support  XML Datatype   Columns, Variables, Parameters Typed or Untyped   XML Schema Collections XML Indexes   Indexes on tags, values & paths Understood by Optimizer Query Instance or Column Insert, Update, Delete Extensions Nodes() Method  XQuery   
Slide 20: SQL Service Broker  Platform for building Asynchronous, loosely coupled database applications   Queues are now database objects  Queue locking reduces conflicts and deadlocks Dialogs give unprecedented message ordering assurance  Ordered across transactions, input applications and output applications Use the same API’s and tools as SQL    New DDL and DML for messaging  Activation - the right number of readers running Reliable messaging for scale out
Slide 21: 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. Backup, space management, security  Shared management platform with database 
Slide 22: Data Types  Varchar(1-8000), Nvarchar(1-4000), Varbinary(1-8000)   Maximum column length still 8,000 bytes Rows can now exceed page size  Varchar(max), Nvarchar(max), Varbinary(max)   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
Slide 23: Oracle Publishing Transactional Replication    Designed specifically for Oracle Publishers v8+ on any operating system No Oracle side software install necessary Requires minimal knowledge of Oracle Standard Transactional and Snapshot Publications Administered like SQL Server, from SQL Server    Leverages existing SQL Server skills  Yukon Distributor Subscribers
Slide 24: HTTPS Synchronization Merge Replication Subscriber IIS Publisher/Distributor -InternetUrl https://myserver.mydomain.com Replisapi.dll Firewall
Slide 25: 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
Slide 26: Reporting Services Architecture Browser Office Custom App Report Control Report Processing Rendering Data Proc. Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom) Web UI XML Web Service Interface Report Server Report Processing Data Processing Rendering Security Scheduling and Delivery Security Services (NT, Passport, Custom) Output Formats (HTML, Excel, PDF, Custom) SQL Server Catalog Delivery Targets (E-mail, SharePoint, Custom)
Slide 27: Notification Services Architecture Subscription Management Application Data Changes Events Subscribers Subscriber Devices Subscriptions Notifications External Delivery Event Provider File System Watcher SQL Server provider Custom EP Event Provider Host Generator Distributor XSLT CF XSLT SQL Match Rule Email File HttpExt .NET Alerts * Custom CF Custom DP
Slide 28: Data Transformation Svcs   Major investment New ETL pipeline   Complex data routing Scale and performance Rich transformations Integrations with Analysis Services  DW & BI Savvy    Enhanced designer and control flow
Slide 29: DTS Yukon Architecture Standard transforms Data Source Adapters Sequences Loops Tasks XML Package Custom transforms Data Destination Adapters XML Package Event Handlers Wizards DTS Designer Command Line
Slide 30: Analysis Services  Unified Dimensional Model  Integrating relational and OLAP views Bringing the best of MOLAP to ROLAP KPIs, MDX scripts, translations, currency… Native XML/A  Pro-active caching   Advanced Business Intelligence   Web services 
Slide 31: Data Mining   Significant investment Important, new algorithms  Association rules, time series, regression trees, sequence clustering, neural nets, Naïve Bayes  Embedded, integrated, complete
Slide 32: Get what you need With an MSDN Universal Subscription Visit the MSDN Table Learn more & Enter a drawing!
Slide 33: © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.