Microsoft SQL Server 2008

Author: Michelle Dumler
Reviewers: Anthony Carrabino, Eugene Belashchenko, Francois Ajenstat, Joanne Hodgins, Julie Strauss, Michael Raheem, Niraj Nagrani, Ram Ramanathan, Roni Karassik
Published: July 2007
Summary: This paper provides an overview of the new benefits and functionality available in the release of 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.


 2007 Microsoft Corporation. All rights reserved.

Microsoft, Excel, Visual C#, Visual Basic, Visual Studio, 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.


Table of Contents
SQL Server 2008 Data Platform Vision 1
What’s New in SQL Server 2008 2
Mission-Critical Platform 2
Secure Trusted Platform for your Data 2
Productive Policy-based Management 3
Optimized and Predictable System Performance 4
Dynamic Development 5
Accelerate your Development 5
Occasionally Connected Systems 7
Beyond Relational Data 7
Store Any Type of Data 7
Location Intelligence 8
Pervasive Business Insight 8
Next-Generation Data Warehousing 8
Scalable Analysis Platform 10
Scalable Reporting 10
Rich Information Experiences 11
Conclusion 12


SQL Server 2008 Data Platform Vision
Today a variety of factors are converging to create an information storage explosion. The amount of digitally born information within an organization is increasing dramatically, enabled by new types of information, such as digitization of data from digital imaging and video, and sensor information from RFID tags. Growing regulatory compliance and globalization require that information be stored securely but also be available at any time. Users must shift through mountains of data to find relevant information. Furthermore, users want to use this information on any device and within applications that they use daily such as the Microsoft Office System. The cost of disk storage has dramatically decreased; FLASH storage has enabled mass data storage on new devices. Organizations can now store more data and reduce storage costs. However, numerous challenges still remain in managing this data explosion.
The Microsoft Data Platform vision meets the needs of the coming data explosion and the next generation of data-driven applications by providing Your Data, Any Place, Any Time. Organizations require a data platform that can store and manage a wide variety of data including XML, e mail, time/calendar, file, document, spatial, and so on while providing a rich set of services to interact with the data: search, query, powerful data analysis, detailed reporting, seamless data integration, and robust data synchronization. Users can access information from creation to archival and on any device, from the desktop to a mobile device.
Microsoft Data Platform is a complete end-to-end solution that meets these challenges and more, by delivering innovations in four key areas that support data platform vision: mission-critical platform, dynamic development, beyond relational, and pervasive insight.
Read on to learn how SQL Server 2008 delivers on this vision and meets the needs of the next generation of data-driven applications.

Figure 1 Microsoft Data Platform Vision
What’s New in SQL Server 2008
SQL Server 2008 delivers on the following four key areas of the Microsoft data platform vision:
• Mission-critical platform – SQL Server 2008 enables organizations to run their most complex applications on a secure, reliable, and scalable platform while enabling IT to reduce the complexity of managing their data management infrastructure. SQL Server 2008 provides a secure and trusted platform by securing valuable information in existing applications and enhancing the availability of the data. SQL Server 2008 introduces an innovative policy-based management framework that enables policies to be defined for explicit and automated administration of server entities across one or multiple servers. In addition, SQL Server 2008 delivers predictable query performance with an optimized platform.
• Dynamic development – SQL Server 2008 along with the .NET Framework reduces the complexity of developing new applications. The ADO.NET Entity Framework enables developers to be more productive by working with logical data entities that align with business requirements instead of programming directly with tables and columns. The new Language Integrated Query (LINQ) extensions in the .NET Framework revolutionizes how developers query data by extending Visual C#® and Visual Basic® .NET to support an SQL-like query syntax natively. And support for occasionally connected systems lets developers build applications that enables users to take data with them on devices and later synchronize their data with central servers.
• Beyond relational data – SQL Server 2008 enables developers to consume and manage any type of data from traditional data types to advanced new geospatial data. Developers can build next-generation database applications that feature new location awareness support and that enable document management capabilities.
• Pervasive business insight – SQL Server 2008 provides a scalable infrastructure that can manage reports and analysis of any size and complexity while making it easier for users to access information through deeper integration with Microsoft Office. This enables IT to drive business intelligence throughout the organization. SQL Server 2008 makes great strides in data warehousing, enabling users to consolidate data marts in an enterprise data warehouse.
Mission-Critical Platform
In today’s data-driven world, data and the systems that manage that data must always be secure and available. SQL Server 2008 enables IT to reduce the complexity of their infrastructure while providing a more secure, scalable, and manageable enterprise data platform with reduced application downtime.
Secure Trusted Platform for your Data
Building on the proven strengths of SQL Server 2005, SQL Server 2008 extends its security and high-availability capabilities with the following advancements.
Transparent data encryption
SQL Server 2008 enables encryption of an entire database, data files, and log files, without the need for application changes. Some of the benefits of transparent data encryption include searching encrypted data using both range and fuzzy searches, searching secure data from unauthorized users, and data encryption. These can all be enabled without changing existing applications.
Extensible key management
SQL Server 2008 provides a comprehensive solution for encryption and key management. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. SQL Server 2008 delivers an excellent solution to this growing need, by supporting third-party key management and hardware security module (HSM) products.
Hot Add CPU
Extending SQL Server current support 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 Server 2008 on supported hardware platforms without requiring application downtime.
Productive Policy-based Management
As part of an ongoing effort by Microsoft to reduce the total cost of ownership (TCO), SQL Server 2008 introduces the Declarative Management Framework, which is a new policy-based management framework for the SQL Server Database Engine. Declarative Management delivers the following benefits:
• Ensures 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
Declarative Management Framework
Declarative Management Framework (DMF) is a policy-based system for managing one or more instances of SQL Server 2008. To use the DMF, SQL Server policy administrators use SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects. Declarative Management 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 SQL Server Agent job to periodically evaluate a policy


Figure 2 Declarative Management Framework

Streamlined installation
SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineered installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, which enables organizations and software partners to provide recommended installation configurations.
Optimized and Predictable System Performance
Organizations are faced with both growing pressures to provide predictable responses and increasing volumes of data and growing numbers of users. SQL Server 2008 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 Server 2008 delivers more extensive performance data collection, a new centralized data repository for storing performance data and new tools for reporting and monitoring.
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 I/O bound workloads such as data warehousing.
Resource Governor
SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor allows organizations to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users.
Predictable query performance
SQL Server 2008 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
Dynamic Development
The Microsoft comprehensive Data Programmability platform enables developers to build data-centric solutions that target desktops, mobile devices, online Web servers, and enterprise servers. SQL Server 2008 supports building applications that use a variety of managed and native connectivity technologies, including ODBC, ADO/OLEDB, and ADO.NET. In particular, SQL Server 2008 along with the .NET Framework and Visual Studio® Team Systems enables developers to build powerful, next-generation database applications.
New support for occasionally connected systems lets developers build applications that enable users to take data with them on devices and later synchronize their data with central servers. Some of the advancements for developing applications using SQL Server 2008 are covered in the next sections.
Accelerate your Development
The new ADO.NET Entity Framework enables developers to be more productive by working with logical data entities that align with business requirements instead of programming directly with tables and columns. The new Language Integrated Query (LINQ) extensions in the .NET Framework revolutionizes how developers query data by extending C# and Visual Basic .NET to support an SQL-like query syntax natively.

Accelerate your development with entities and the ADO.NET Entity Framework
A common trend among database developers is to define high-level business objects, or entities, that get mapped 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 (E-R) modeling.
Language Integrated Query
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 Server 2008 features a new LINQ to SQL Provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.

Figure 3 LINQ 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 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 Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.
Occasionally Connected Systems
With mobile devices and workers on-the-go, occasionally connected has become a way of life. SQL Server 2008 delivers a unified synchronization platform that enables consistent synchronization across applications, data stores, and data types. In a joint effort with Visual Studio, SQL Server 2008 enables the rapid creation of occasionally connected applications by way of new synchronization services in ADO.NET and offline designers in Visual Studio. SQL Server 2008 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 Server 2008 builds on a strong legacy of supporting nonrelational 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 Server 2008 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 covers some of the advancements in beyond relational data storage.
Store Any Type of Data
SQL Server 2008 allows for a seamless transition between managing relational and nonrelational data. This enables users to easily access documents as data, encode complex hierarchies within XML, and query across both relational and text data.
DATE/TIME
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 datetime type
• DATETIME2 – a datetime type w/ larger fractional seconds and year range than the existing DATETIME type
The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.
HIERARCHY ID
SQL Server 2008 enables database applications to model tree structures in a more efficient way than currently 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 UDT that exposes several efficient and useful built-in methods for creating and operating on hierarchy nodes.
FILESTREAM Data
The new SQL Server 2008 FILESTREAM data type allows large binary data to be stored directly in an NTFS file system while letting the data remain an integral part of the database and maintaining transactional consistency. The new FILESTREAM data type enables the scale-out of large binary data, traditionally managed by the database, to be stored outside of the database on more cost-effective storage without comprising features for accessing such data.
Integrated Full-Text Search
Integrated Full-Text Search makes the transition between Full-Text Search and relational data seamless while enabling users to use the full-text indexes to perform high-speed text searches on large text columns.
Sparse columns
This feature provides a highly efficient way of managing empty data in a database by enabling NULL data to consume no physical space. For example, sparse columns allows object models that typically contain numerous null values to be stored in a SQL Server 2008 database without experiencing large space costs.
Large user-defined types
SQL Server 2008 eliminates the 8 KB limit for user-defined types (UDTs), enabling users to dramatically expand the size of their UDTs.
Location Intelligence
Geographical information is rapidly becoming main stream to many business applications. SQL Server 2008 provides new spatial data types that enable developers to build location-aware applications.
Spatial data
SQL Server 2008 includes a new vector-based spatial solution that conforms to industry spatial standards. This enables developers to build location-aware applications that capture geographical information from within the organization and allows this data to be integrated easily into applications.
Location data
SQL Server 2008 enables users to capture location data from across the organization and integrate location intelligence into existing applications creating location-aware applications.
Pervasive Business Insight
End-to-end business insight enables better decision making through technology that allows users to collect, clean, store, and prepare their business data for the decision-making process. Addressing the strong momentum in the business intelligence (BI) market, SQL Server 2008 provides a scalable infrastructure that enables IT to drive business intelligence throughout the organization by managing reports and analysis of any size and complexity while providing deep integration with the Microsoft Office System. SQL Server 2008 enables organizations to deliver business insights to all employees, resulting in better, faster, and more relevant decisions.
Next-Generation Data Warehousing
Organizations continue to invest in BI and data warehousing solutions in order to derive business value from their data. SQL Server 2008 provides a comprehensive and scalable data warehouse platform that enables organizations to integrate data into the data warehouse faster and to scale and manage growing volumes of data and users while delivering insights to all users. Following are some of the advancements in data warehousing.
Data compression
Data volumes in data warehouses continue to grow with the addition of myriad operational systems. SQL Server 2008 enables organizations to store the data more effectively with efficient data compression that also provides improved performance with reduced I/O.
Backup compression
Keeping disk-based backups online is expensive and time consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online and backups run significantly faster since less disk I/O is required.
Partitioned table parallelism
Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables
Star join query optimizations
SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star join query optimizations reduce query response time by recognizing data warehouse join patterns.
Resource Governor
SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor allows organizations to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance.
GROUPING SETS
GROUPING SETS is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. GROUPING SETS produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
Change data capture
With change data capture, changes are captured and placed in change tables. It captures complete content of changes and maintains cross table consistency and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
MERGE SQL statement
With the introduction of the MERGE SQL statement, developers can more effectively handle common data warehousing scenarios like checking whether a row exists and then executing an insert or update.
Scalable Integration Services
The two key advancements in scalability of Integration Services include:
SQL Server Integration Services (SSIS) Pipeline Improvements
Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.
SSIS Persistent Lookups
The need to perform lookups is one of the most common extraction, transformation, and loading (ETL) operations. This is especially prevalent in data warehousing where fact records must use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.
Scalable Analysis Platform
Instant access to accurate information enabling end users to answers even the most complex questions at the speed of thought is the premise of Online Analytical Processing (OLAP). SQL Server 2008 builds on the strong OLAP support in SQL Server 2005 by delivering faster query times and data refresh rates. This performance boost enables organizations to perform highly complex analysis with a large number of dimensions and aggregations. SQL Server Analysis Services provides the following analytical advancements.
Analysis scale and performance
SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure, enabling them to build solutions for optimized performance.
Block computations
Block computations provide a significant improvement in processing performance, enabling users to increase the depth of their hierarchies and complexity of the computations.
Writeback
New MOLAP-enabled writeback capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
Scalable Reporting
For many organizations the big challenge is getting the right information to the right people at the right time. SQL Server 2008 provides a high-performance reporting engine for processing and formatting reports, along with a complete set of tools for creating, managing, and viewing reports. An extensible architecture and open interfaces enable easy integration of reporting solutions in diverse IT environments.
Enterprise reporting engine
Reports can easily be delivered throughout the organization with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.
Internet report deployment
Customers and suppliers can be reached effortlessly by deploying reports over the Internet.
Manage reporting infrastructure
Increase supportability and the ability to control server behavior with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.
Better scale-out configuration
To enable better scale-out configuration, SQL Server 2008 provides the tools needed to support the management of multiple report servers.
Rich Information Experiences
SQL Server 2008 empowers users with actionable business insight with the reporting advancements covered in this section. These enhancements enable information workers to access their information from within the tools they use every day.

Report Builder enhancements
Easily build ad-hoc and author reports with any structure through Report Designer.

Figure 4 Report Designer

Built-in forms authentication
Built-in forms authentication enables users to easily switch between Windows and Forms.
Report Server application embedding
Report Server application embedding enables the URLs in reports and subscriptions to point back to frontend applications.
Office integration
SQL Server 2008 provides new Microsoft Office rendering that enables users to consume reports directly from within Word. In addition, the existing Excel® renderer has been greatly enhanced to accommodate the support of features like nested data regions, sub-reports as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.
Conclusion
SQL Server 2008 provides the technology and capabilities that organizations count on to manage the growing challenges of managing data and delivering actionable insights to users. With significant advancements in the key areas of mission-critical platform, dynamic development, beyond relational data, and pervasive insight, the benefits of SQL Server 2008 are substantial. SQL Server 2008 is an integral part of the Microsoft Data Platform vision that is designed to meet the needs of managing and working with data today and beyond.
SQL Server 2008 is a significant product release that delivers many new features and key improvements making it the most robust and comprehensive release of SQL Server to date.

This paper provides only an overview of all the new benefits and functionality in SQL Server 2008.
For more information, please visit the following:
General product information: http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
For developers:
http://msdn2.microsoft.com/sqlserver
For IT Pros and administrators:
http://technet.microsoft.com/sqlserver

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:
• Are you rating it high due to having good examples, excellent screenshots, clear writing, or another reason?
• Are you rating it low due to poor examples, fuzzy screenshots, unclear writing?
This feedback will help us improve the quality of white papers we release. Send feedback.