SQL Server 2008 Overview A Whirlwind Tour

1. SQL Server 2008 Overview  A Whirlwind Tour Val Matison Director, Business Intelligence Solutions vmatison@systemgroupinc.com

2. Session Overview November CTP Release update What�s new for SQL Server 2008 http://www.microsoft.com/sql/2008

3. VideoVideo

4. Delivering On Our Vision 

5. Transparent Data Encryption External Key Management Data Auditing Pluggable CPU Transparent Client Redirect for Database Mirroring Declarative Management Framework Server Group Management Streamlined Installation Enterprise System Management Performance Data Collection System Analysis Data Compression Query Optimization Modes Resource Governor Entity Data Model LINQ Visual Entity Designer

6. Secure, Trusted Platformfor Your Data Protect your information Encrypt your data without requiring an application rewrite Backup your data securely Consolidate security keys within the data center Answer common auditing questions, like �What data was retrieved?� Increase the reliability of your applications Add system resources without affecting your users Add automatic failover transparency to your existing applications

7. Transparent Data Encryption Enables encryption of entire databases, data files, and log files No the need for application changes Both�the data and the log files of a database are encrypted on disk Decrypted when they are read into memory Encryption of a database with TDE is done using a special key called, simply,�database encryption key (or DEK, for short). Both�the data and the log files of a database are encrypted on disk and get decrypted when they are read into memory. Both�the data and the log files of a database are encrypted on disk and get decrypted when they are read into memory.

9. Productive Policy-based Management of Your Infrastructure Spend less time on ongoing operations Manage via policies instead of scripts Define enterprise-wide data management policies Automated monitoring and enforcement of policies Simplify your installation and configuration Integrated with your enterprise system management Define Policies that are compliant with System Definition Model Manage your data and system infrastructure with Microsoft System Center

10. Declarative Management Framework Compliance with policies for system configuration Monitors and prevents changes to the system by authoring policies against the configuration Reduces total cost of ownership by simplifying administration tasks Detects compliance issues in SQL Server Management Studio Both�the data and the log files of a database are encrypted on disk and get decrypted when they are read into memory. Both�the data and the log files of a database are encrypted on disk and get decrypted when they are read into memory.

11. Enabled by: On Demand � Uses DDL triggers to prevent policy violations Changes are Attempted � Uses event notification to evaluate a policy when a relevant change occurs On Schedule � Uses a SQL�Server Agent job to periodically evaluate a policy

13. Database Mirroring Protecting data by providing complete or nearly complete redundancy. Increasing availability of a database in the event of a disaster Improving availability of production database during upgrades SQL 2008 compresses outgoing log stream to minimize bandwidthSQL 2008 compresses outgoing log stream to minimize bandwidth

14. Optimized and Predictable Performance Optimize your system performance Monitor your system by collecting and storing system events Analyze and report on your system performance Reduce your storage costs and increase query performance Predictable performance Manage concurrent workloads on single system Prevent runaway queries Ensure consistent query performance

15. SQL Server 2005 Resource Management Single resource pool Database engine doesn�t differentiate workloads Best effort resource sharing

16. Resource Governor � Workloads Ability to differentiate workloads e.g. app_name, login Per-request limits Max memory % Max CPU time Grant timeout Max Requests Resource monitoring

17. Resource Governor � Importance A workload can have an importance label Low Medium High Gives resource allocation preference to workloads based on importance

18. Resource Governor � Pools Resource pool: A virtual subset of physical database engine resources Provides controls to specify Min Memory % Max Memory % Min CPU % Max CPU % Max DOP Resource monitoring Up to 20 resource pools

19. Resource Governor Putting it all together Workloads are mapped to Resource Pools (n : 1) Online changes of groups/pools SQL Server 2005 = default group + default pool Main Benefit Prevent run-away queries

21. Delivering On Our Vision 

22. Accelerate Your Development with Entities Develop with business entities Define your business not tables Model your complex business relationships Retrieve entities instead of rows and columns Connect to SQL Server with entity aware adapters Reliable application performance Store entities locally on your clients

23. Connect to Your Data from Any Device Access your data from anywhere Store your data locally while disconnected from server Synchronize incremental changes between client and server Detect conflicts during synchronization including deletes Add disconnected scenarios without rewriting existing applications

24. Delivering On Our Vision

25. Store and Consume Any Type of Data Seamless transition between relational and non-relational Store your documents in cost effective storage platform Access documents as data Encode your complex hierarchies within XML Extend the database to meet your unique needs Find your data Query across relational and text data Build global applications Capture precise time from clients across the globe

27. Date and Time Data Types

28. Date and Time Data Types

30. Deliver Location Intelligence Within Your applications Build location-aware applications Capture location data from across your organization Integrate location intelligence to your existing applications Visualize your location information Standard Spatial applications Integrate with industry standard spatial applications Supported by third parties

31. Delivering On Our Vision 

32. Enterprise Data Warehouse Scale and Manage large number of users and data Improve Query performance on large tables Optimize Queries for data warehousing scenarios Increase I/O performance with efficient and cost effective data storage Manage concurrent workloads of ad-hoc queries, reporting and analysis Integrate growing volumes of data Optimize ETL performance by identifying data in your largest tables Reduce the data load volumes by capturing operational changes in data Simplify the insert and update data processing Profile your information to identify dirty data

33. MERGE statement New DML statement (aka �UPSERT�) Perform multiple DML operations against a single target table based on a source table INSERT / UPDATE / DELETE SQL-2006 compliant (with extensions) Uses: OLTP insert-or-update (upsert) Synchronize target with source (insert/update/delete) � and more! Fully orthogonal CTEs, TOP, OUTPUT, OPTION, �

34. MERGE MERGE Stock S USING Trades T ON S.Stock = T.Stock WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE -- delete stock if Qty reaches 0 WHEN MATCHED THEN -- delete takes precedence on update UPDATE SET Qty += Delta WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta) OUTPUT $action, T.Stock, inserted.Delta;

35. Reach All your Users withScalable BI platform Deliver insights throughout your organization Deliver reports of any size at enterprise scale Scale out through read-only Analysis Services storage Enhance analytical capabilities with more complex computations and aggregations Deploy and manage your BI infrastructure Streamline development of the analysis infrastructure Backup cubes with enhanced scalability Improve productivity with new cube design tools Optimize cube design with real time best practice alerts Deploy Reporting Services without IIS dependency

37. Empower Every UserWith Actionable Insight Deliver information via Microsoft Office Render reports to Microsoft Word Enjoy improved rendering to Microsoft Excel Enable users to create powerful reports Build powerful ad-hoc reports Create reports with any structure using Tablix Add rich text regions your reports Embed powerful graphical data visualizations into reports (Dundas) Empower users with enhanced analysis Empower users with enhanced write-back scenarios Enhanced Data Mining structures and bring data mining to a new, broader audience through Office

39. There's much more� DB Engine Partition-aligned indexed views Locking enhancements - partition Analysis Services Query Tracing � like Showplan, but for AS Write-back performance Resource monitoring � like the DMVs in the engine Integration Services Visual Studio Tools for Applications (VSTA) scripting

40. Delivering On Our Vision 

41. Resources