Comprehensive Overview of SQL Server 2005: A Unified Data Management and Reporting Platform

1. SQL Server 2005 Overview

2. SQL ServerComprehensive, Integrated Data Platform Reporting Services Enterprise Reporting Management Tools Development Tools Analysis Services OLAP & Data Mining Notification Services Notifications & Alerts Replication Services Data Replication Data Transformation Services ETL SQL Server Engine Relational Database Engine

3. High availability for enterprise applications • Security enhancements • Focus on manageability; self optimization Enterprise Data Management • Integration with Visual Studio and .NET • Native XML technology • Interoperability: open standards, Web services Developer Productivity • Comprehensive ETL solution • Real time decision making: reporting, data mining • Scalability and availability enhancements Business Intelligence Secure, reliable, and productive database platform for line of business and analytical applications

4. Enterprise Data Management • Availability and security advancements • Database mirroring, online operations for increased availability • Secure by default, by design, by deployment • Focus on manageability • Integrated management toolset for relational and OLAP • Expanded self tuning • Performance and scalability • Scales as the business grows: partitioning, snapshots • From mobile devices to 64-bit data centers

5. Yukon Security: Feature Enhancements • Authentication Enhancements • Password policy enforcements for SQL logins • Password complexity, expiration and lockout • Strengthening SQL Authentication • Login credentials are always transferred in a secure channel, by default • Privacy and Encryption • Built-ins for Encryption and Decryption of data • Infrastructure for Key management

6. 2 3 1 5 4 3-6 2 3-6 Application SQL Server SQL Server Database Mirroring Witness Principal Mirror Log Data Log Data

7. Database Mirroring • Database Failover • Very Fast … less than 3 seconds • Automatic or manual failover • Configurable: Availability Vs Performance • Automatic, transparent client redirect • Database Snapshots for Reporting • Hardware • Works with standard computers and storage • No shared storage components

8. Database Snapshots • Snapshot of a database at a point in time • Created instantly • Read only • Does not require a complete copy of the data • Shares unchanged pages of the database • Requires extra storage only for changed pages • Uses a “copy-on-write” mechanism • Recover from User, Application or DBA error • Rewind database to Viewpoint

9. Manageability - Tools • New: SQL Server Workbench • New Rich GUI • Integrated Authoring, Management • Support all SQL Server components • New: Management Objects (SMO) • Managed code, new design patterns • Improved Scalability, Scripting • New: SQLCMD • High Performance CMDLine • New: SQLMail Replacement • SMTP Support, Cluster Support, No Outlook required

10. Demos Management Studio

11. 64 bit Virtual Address Space (currently tested up to 512 GB) SQL Server 2000 – 64 bit running on an Itanium 2 Server SQL Server 2000 – 32bit running on a Xeon Server • Virtual address space available to all structures • Direct access – no map cost • Access to large amounts of physical memory in virtual address space • Current and future hardware • Buffer pool pages can be mapped / unmapped • As pages are needed, they can be mapped into the virtual address space • Some structure stay in virtual address memory including plan cache, cursor structures and user connection context • Cost associated with mapping / unmapping AWE Memory (up to 32GB) 3GB Virtual Address space available to SQL Server

12. Developer Productivity • Leverage existing development skills • Integration with Visual Studio • Multiple languages: T-SQL, VB.NET, C#… • Share data across any platform • Native support for XML, Web services • Interoperability with any platform, application • Extensibility • Native XML data type, user defined data types • New distributed application framework: Server Broker

13. .Net IntegrationBusiness Logic • User Defined Functions • Scalar • Table Valued • User Defined Procedures • User Defined Triggers • Assemblies • Unit of encapsulation, deployment Create Function or Procedure or Trigger AS External Name assembly:class::method

14. .Net IntegrationExtensibility • User Defined Types • Complex structure and behavior • Custom Attributes, Contract • Supported in client libraries • User Defined Aggregates • Extensibility hook into the Query Processor using a well-defined Contract • Initialize a group, Accumulate a value, Merge Groups, Finish computation, return result

15. CLR IntegrationCustomer Usage, Benefits • Rich Languages, APIs in the Database + Tools  Developer Productivity • In-Proc Data Access via ADO.NET  Allows portability of code from/to middle-tier • Managed Functions - Mixture of data access & business logic • Demand Forecasting application being developed as a scenario • Managed Procedures - XP replacement • Robust alternative to access external resources (file/registry) • UDTs - Small, scalar types to augment SQL’s native typespace • E.g. Date, time

16. Demos Stored Procedures, User Defined Types

17. Native XML Store • Native XML Datatype • Columns, Variables, Parameters • XML Schema (optional) • Schema Collections • XML Indexes • XML Data access, updates • XQuery • DML Extensions • Integrates with Relational processing • Complements SQLXML

18. SQL XQuery XML SupportFlexible Storage, Query and DML OpenXML XML Data Type

19. Demos XQuery

20. SQL Web Services HTTP.SYS • Native SOAP access • Standards based access to SQL Server • No client dependency • Improved Interoperability • New “HTTP ENDPOINT” object • Configure connection info • Configure authentication • Expose Functions & SPs • Expose TSQL Batches • Leverages kernel-mode listener http://myserver/sql Port: 80 Integrated Auth WebMethod = myDb.dbo.mySP HTTP Endpoint

21. T-SQL Enhancements • Varchar(max), Varbinary(max) • Alternative to Text, Image • Uniform Programming model • Exception Handling • Try/Catch • Recursive Queries • MARS • Other • Pivot, UnPivot • Top (expression) • Customer usage, benefit : Better database applications, addresses existing pain points.

22. Demos CTE, Try/Catch, Rank()

23. Full-Text • Integration with Server • DDL • Backup, Restore of Catalogs • Multi-Instance • Thesaurus • Multi-column • Support for additional languages • Huge strides in Scalability, Performance • Customer usage, benefit : Scalability and Performance, Integrated management

24. Service Broker SQL Engine SQL Engine • Framework for building Reliable, asynchronous, distributed applications • Communication infrastructure • Two-way “Dialogs” • Reliable delivery to local, remote queues • Exactly-once, In-order semantics • Large Message Fragmentation APP 1 APP 2

25. Service Broker • Fully integrated into the database engine • Programming Model - DDL and DML • Integrated Management and Deployment • Dependent Yukon features – Events, Query Notifications, SQLiMail • Customer Usage, benefits: Enables new scenarios - async SPs, Scalable distributed multi-db applications

26. Scalable, comprehensive solution • Extensive data modeling capabilities • New data mining algorithms for advanced analytics • Integrated with Visual Studio for rapid BI development • Real-time decision making tools • New extensible reporting platform • Scalable and reliable for 24*7 support • Integration with operational systems • Extend information to employees, partners • Supports heterogeneous data sources • Enterprise-class ETL (DTS) • Native XML Web Services

27. Development Tools Management Tools Reporting Services Analysis Services OLAP & Data Mining Data Transformation Services ETL SQL Server Relational Engine Business Intelligence Scalable, comprehensive solution • End-to-end business intelligence • Integrated with Visual Studio for rapid BI development Real-time decision making tools • New extensible reporting platform • Scalable and reliable for 24*7 support BI for the entire enterprise • Supports heterogeneous data sources • Enterprise-class ETL (DTS)

28. Analysis Services • Unified Dimensional Model • Integrating relational and OLAP views • Pro-active caching • Bringing the best of MOLAP to ROLAP • Advanced Business Intelligence • KPIs, MDX scripts, translations, currency… • Web services • Native XML/A

29. Data Mining Focus in Yukon • Complete set of analytics • Most popular data mining algorithms (5 new) • Enhanced creating, editing, and viewing environment • Embedded data mining • Embed data mining in LOB applications • Complete SQL language based API • Native XML/A support • Integrated solution • Tight integration with Relational, OLAP, DTS, Reporting technologies • Comprehensive SQL Server BI platform • Alliance with ISVs • Focus on broadening the market • Agreed on finalizing DM industry standard based on OLE DB for DM and XML/A

30. Decision Trees Clustering Time Series Naïve Bayes Sequence Clustering Association Neural Net Complete Set of Algorithms Introduced in SQL Server 2000

31. Reporting Services • Introduced with SQL Server 2000 • Open, extensible enterprise reporting solution • Report authoring, management, delivery • Office System integration • VS.NET development environment • SQL Server 2005 enhancements • Integration with AS, DTS, management tools • Developer enhancements • Improved report interactivity

32. Flash Ad Hoc Query Tool

33. Data Transformation Services • A true Enterprise ETL platform • High performance and scale – distribute transformations across processors • Trustworthy and reliable – built-in data cleansing, error data handling • Complex data routing • Best in class usability • Full developer IDE • Source control • Visual debugging of control flow and data • Great range of transforms out-of-the-box: little or no scripting required! • Highly extensible – with native or managed code • Custom tasks, enumerations, transformations, data sources

34. BI Development Studio • A complete, integrated tool for the development of BI applications • One tool, multiple technologies: • Relational, OLAP, DM, DTS, Reporting, Code, Web pages… • Enterprise software development environment: • Integrated into Visual Studio.NET • Team development, source control, versioning, developer isolation, resource independent coding • Development cycle lifetime support • Develop, Test, Deploy, Modify, Test… • Breakthrough ease of use

35. © 2003-2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.