Filename: SQL2008_ProductOverview_jstrauss_091207.doc1

Microsoft SQL Server 2008

Product Overview

Author: Michelle Dumler

Updated by: Geoff Allix (Content Master)

Reviewers: Anthony Carrabino, Eugene Belashchenko, Francois Ajenstat, Joanne Hodgins, Julie Strauss, Michael Raheem, Niraj Nagrani, Ram Ramanathan, Roni Karassik

Published: July 2007

Updated: February 2008

Summary: This paper provides an overview of the new features, benefits, and functionality in Microsoft® SQL Server™ 2008.

Copyright

This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

2008 Microsoft Corporation. All rights reserved.

Microsoft, Excel, Visual C#, Visual Basic, Visual Studio, SharePoint and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Filename: SQL2008_ProductOverview_jstrauss_091207.doc1

Table of Contents

Microsoft Data Platform Vision

What’s New in SQL Server 2008

Trusted

Protect Your Information

Ensure Business Continuity

Optimized and Predictable System Performance

Productive

Policy-based Management

Streamlined Installation

Accelerated Development

Occasionally Connected Systems

Beyond Relational Data

Intelligent

Integrate Any Data

Deliver Relevant Reports

Drive Actionable Insight

Conclusion

SQL Server 2008 Product Overview

Microsoft Data Platform Vision

A variety of factors are converging to create an information storage explosion. Enabled by new types of information, such as digitization of images and video, and sensor information from RFID tags, the amount of digital information within an organization is mushrooming. Growing regulatory compliance and globalization require that information be stored securely and available at all times. At the same time, the cost of disk storage has dramatically decreased, enabling organizations to store more data per dollar invested. Users must quickly sift through mountains of data to find relevant information. Furthermore, they want to use this information on any device and with theprograms that they use every day, such as Microsoft Office System applications. Managing this data explosionand the increase in user expectations creates numerous challenges for the enterprise.

The Microsoft® Data Platform vision meets these needs by providing a solution that organizations can use to store and manage many types of data, including XML, e-mail, time/calendar, file, document, geospatial, and so on, while providing a rich set of services to interact with the data: search, query, data analysis, reporting, data integration, and robust synchronization. Users can access information from creation to archiving on any device, from the desktop to a mobile device.

Microsoft SQL Server™2008 delivers on this vision.

Figure 1Microsoft Data Platform Vision

What’s New in SQL Server 2008

SQL Server 2008 delivers on the Microsoft data platform vision by enabling organizations to run their most mission-critical applications while lowering the cost of managing the data infrastructure and delivering insights and information to all users. This platform has the following qualities:

  • Trusted—Enables organizations to run their most critical applications with very highlevels of security, reliability, and scalability.
  • Productive—Enables organizations to reduce the time and cost required to develop and manage their data infrastructure.
  • Intelligent—Provides a comprehensive platform that deliversinsights and information where your users want it.
Trusted

In today’s data-driven world, organizations need continuous access to their data. SQL Server2008 providesrobust security features, reliability, and scalability for mission-critical applications.

Protect Your Information

Building on the proven strengths of SQL Server2005, SQL Server2008 extends its security capabilities with the following enhancements:

Transparent Data Encryption

SQL Server2008 enables encryption of entire databases, data files, and log files, without the need for application changes. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. Some of the benefits of transparent data encryption include searching encrypted data using either rangeor fuzzy searches, more secure data from unauthorized users, and data encryption. These can be enabled without changing existing applications.

External Key Management

SQL Server2008 provides a comprehensive solution for encryption and key management. To meet the growing need for greater security of information within data centers, organizations have invested in vendors to manage security keys within the enterprise. SQL Server2008 provides excellent support for this need by supporting third-party key management and hardware security module (HSM) products.

Enhanced Auditing

SQL Server 2008 improves compliance and security by allowing you to audit activity on your data. Auditing can include information about when data has been read, in addition to any data modifications. SQL Server 2008 has features such as enhanced configuration and management of audits in the server, which enable organizations to meet varied compliance needs. SQL Server 2008 can also define audit specifications in each database, so audit configuration can be ported with databases. Filtering of audits to specific objects allows better performance in audit generation and flexibility in configuration.

Ensure Business Continuity

With SQL Server 2008, Microsoft continues to give organizations the ability to provide highly reliable applications with simplified management.

Enhanced Database Mirroring

SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring. New features include:

  • Automatic page repair. SQL Server 2008 enables the principal and mirror computers to transparently recover from 823 and 824 errors on data pages by requesting a fresh copy of the corrupted page from the mirroring partner.
  • Improved performance. SQL Server 2008 compresses the outgoing log stream in order to minimize the network bandwidth required by database mirroring.
  • Enhanced supportability
  • SQL Server 2008 includes additional performance counters to enable more granular accounting of the time spent across the different stages of Database Management System (DBMS) log processing.
  • SQL Server 2008 includes new Dynamic Management Views and extensions of existing views to expose additional information about mirroring sessions.

Hot Add CPU

Extending existing support in SQL Server for adding memory resources online, Hot Add CPU allows a database to be scaled on demand. In fact, CPU resources can be added to SQL Server2008 on supported hardware platforms without requiring application downtime.

Optimized and Predictable System Performance

Organizations are faced with growing pressure to provide predictable response and to manage increasing volumes of data for growing numbers of users. SQL Server2008 provides a comprehensive set of features to provide scalable and predictable performance for any workload on your data platform.

Performance data collection

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server2008 delivers more extensive performance data collection, a new centralized data repository for storing performance data, and new reporting and monitoring tools.

Extended Events

SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure is a lightweight mechanism that supports capturing, filtering, and acting upon events generated by the server process. This ability to act upon events allows users to quickly diagnose run time problems by adding contextual data, such as Transact SQL call stacks or query plan handles, to any event. Events can be captured into several different output types, including Event Tracing for Windows (ETW). When Extended Events are output to ETW, correlation with operating system and database applications is possible, allowing for more holistic system tracing.

Backup compression

Keeping disk-based backups online is expensive and time consuming. With SQL Server2008 backup compression, less disk I/O is required, less storage is required to keep backups online, and backups run significantly faster.

Data compression

Improved data compression enables data to be stored more effectively and reduces the storage requirements for your data. Data compression also provides significant performance improvements for large input/output-bound workloads such as data warehousing.

Resource Governor

SQL Server2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor enablesdatabase administrators to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users.

Plan Freezing

SQL Server2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Productive

SQL Server 2008 reduces the time and cost of managing systems and, along with the .NETFramework and Visual Studio® Team System, enables developers to build powerful, next-generation database applications.

Policy-based Management

As part of an ongoing effort by Microsoft to reduce the total cost of ownership, SQL Server2008 introduces the Policy-based Framework, which is a new management framework for the SQLServer Database Engine. Policy-based Framework delivers the following benefits:

  • 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

The Policy-based Framework is a system for managing one or more instances of SQL Server2008. To use this framework, SQLServer policy administrators use SQLServer Management Studio to create policies that manage entities on the server, such as the instance of SQLServer, databases, and other SQLServer objects. The Policy-based Framework consists of three components: policy management, policy administrators who create policies, and explicit administration. Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly force the targets to comply with a policy.

Automated administration
Policy administrators enable automated policy execution by using one of the following execution modes:

  • Enforce – Uses DDL triggers to prevent policy violations
  • Check on Changes – Uses event notification to evaluate a policy when a relevant change occurs
  • Check on Schedule – Uses a SQLServer Agent job to periodically evaluate a policy

Figure 2Policy-based Framework

Streamlined Installation

SQL Server2008 introduces significant improvements to the service life cycle for SQLServer through its re-engineered installation, setup, and configuration architecture. These improvements separate the installation of the bits on the computer from the configuration of the SQLServer software, which enables organizations and software partners to provide recommended installation configurations.

AcceleratedDevelopment

SQL Server enables developers to create the next generation of data applications with an integrated development environment and a higher level of data abstraction, while simplifying access to data.

ADO.NET Entity Framework

A trend among database developers is to define high-level business objects, or entities, that they then map to the tables and columns stored in a database. Rather than programming against tables and columns in a database, developers use high-level entities such as ‘Customer’ or ‘Order’ to represent the underlying data. The ADO.NET Entity Framework enables developers to program against relational data in terms of such entities. Programming at this level of abstraction is highly productive and allows developers to take full advantage of entity-relationship modeling.

Language Integrated Query

Microsoft Language Integrated Query (LINQ) enables developers to issue queries against data by using a managed programming language such as C# or Visual Basic.NET, instead of SQL statements. LINQ enables seamless, strongly typed, set-oriented queries written in .NET Framework languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping Provider. SQL Server2008 features a new LINQ to SQL Provider that enables developers to use LINQ directly on SQL Server2008 tables and columns.

Figure 3LINQ to Entities

CLR integration and ADO.NET object services

The object services layer of ADO.NET enables the materialization, change tracking, and persistence of data as Common Language Runtime (CLR) objects. Developers using the ADO.NET Entity Framework can program against a database by using CLR objects that are managed by ADO.NET. SQL Server2008 introduces more efficient, optimized support that improves performance and simplifies development.

Service Broker Scalability

SQL Server 2008 continues to enhance the capabilities of Service Broker.

  • Conversation Priority – Enables you to configure priorities so that the most important data is sent and processed first.
  • Diagnostic tool – The diagnostic tool improves your ability to develop, configure, and manage solutions that use Service Broker, such as diagnosing missing route or incorrectly configured security issues prior to application deployment.

Transact-SQL Improvements

SQL Server 2008 enhances the development experience for Transact-SQL programmers with several key improvements.

  • Table Value Parameters - In many customer scenarios, it is necessary to pass a set of table-structured values (rows) to a stored procedure or function on the server. These values may be used for directly populating or updating a table or for more complex manipulation of data. Table valued parameters provide an easier way to define a table type and to allow applications to create, populate, and pass table-structured parameters to stored procedures and functions.
  • Object Dependencies- The object dependencies improvement provides reliable discovery of dependencies among objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema collections, and more.
  • DATE/TIME Data Types - SQL Server 2008 introduces new date and time data types:
  • DATE – a date only type
  • TIME – a time only type
  • DATETIMEOFFSET – a time zone-aware date/time type
  • DATETIME2 – a date/time type with larger fractional seconds and year range than the existing DATETIME type

The new data types enable applications to have separate date and time types, while providing large date ranges or user-defined precision for time values.

Occasionally Connected Systems

With mobile devices and on-the-go workers, occasionally connected has become a way of life. SQL Server2008 delivers a unified synchronization platform that enables consistent synchronization across applications, data stores, and data types. In a joint effort with Visual Studio, SQL Server2008 enables the rapid creation of occasionally connected applications by way of new synchronization services in ADO.NET and offline designers in Visual Studio. SQL Server2008 provides support for change tracking, enabling customers to develop caching-based, synchronization-based, and notification-based applications using a robust implementation with minimal performance overhead.

Beyond Relational Data

Increasingly, applications are incorporating a much wider variety of data types than has been traditionally supported by a database. SQL Server2008 builds on a strong legacy of supporting non-relational data by providing new data types that enable developers and administrators to efficiently store and manage unstructured data such as documents and images. Support for managing advanced geospatial data has also been added. In addition to new data types, SQL Server2008 provides a rich set of services on the different data types while providing the reliability, security, and manageability of the data platform. The next section of this white paper covers some of the advances in non-relational data storage.

HIERARCHY ID

SQL Server 2008enables database applications to model tree structures in a more efficient way than previously possible. HierarchyId is a new system type that can store values that represent nodes in a hierarchy tree. This new type features a flexible programming model. It is implemented as a CLR user-defined type (UDT) that exposes several efficient and useful built-in methods for creating and operating on hierarchy nodes.

FILESTREAM Data

The new SQL Server2008 FILESTREAM data type allows large binary data like documents and images to be stored directly in an NTFS file system; the documentor image remains an integral part of the database and maintains transactional consistency. FILESTREAM enables the storage of large binary data, traditionally managed by the database, to be stored outside the database as individual files that can be accessed using an NTFS streaming API. Using NTFS streaming APIs allows efficient performance of common file operations while providing all of the rich database services, including security and backup.