Wednesday, November 25, 2009

Microsoft SQL Server 2008 Product Overview

Data Platform Vision

Organizations are seeing a confluence of factors meeting to create a data storage explosion. The first amazing fact is the sheer amount of data that is being created in the world. If you look at the data stored digitally on a global basis, we were at 12 exabytes by 1999. By the middle of 2002, the second dozen exabytes was created.

If you look at the types of data being generated you can see that magnetic digital plays a huge role in storage, and will continue to increase in size relative to other formats. Where the real storage explosion happens is in the storage of digital assets: pictures, movies, and more. These formats are increasing the demand for storing structured and unstructured data types.

The cost of storage is rapidly decreas

ing, enabling this storage explosion to continue. In 1980, the world's first gigabyte-capacity disk drive, the IBM 3380, had a price tag of $40,000 USD. Today, the cheapest 500‑GB (gigabyte) hard disk drive is offered for $0.38 USD.

For organizations to be successful and thrive in this world of data, they need a vision that

addresses these key data trends. The Microsoft data platform vision helps organizations meet these data explosion needs and the next generation of data-driven applications. Microsoft will continue to invest in and evolve the following key areas to support their data platform vision:

mission-critical enterprise data platform, dynamic development, relational data, and business intelligence (BI).

Read on to learn about the Microsoft data platform vision and how SQL Server 2008 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 capabilities deliver on the four key areas of the data platform vision.

· Mission-Critical Platform – SQL Server 2008 enables IT groups to be more productive by providing a more secure, scalable, and manageable platform. It includes a new policy-based management framework that shifts from

managing by scripts to managing by rules. SQL Server 2008 also protects valuable information in existing applications and disconnected devices. In addition, SQL Server 2008 delivers predictable query performance with an optimized platform.

· Dynamic Development – SQL Server 2008 along with the .NET Framework enables developers to build the next generation of applications. Developers are more productive because they work with business entities instead of tables and columns. They can build applications that enable users to take their data with them and synchronize their data with back-end servers.

· Beyond Relational Data – SQL Server 2008 enables developers to consume any type of data, from XML to documents, and build applications that incorporate location awareness.

· Pervasive Business Insight – SQL Server 2008 provides a scalable infrastructure that can manage reports and analysis of any size or complexity while at the same time empowering users because of its close integration with the Microsoft Office System. 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 be more

productive by providing a more secure, scalable, and manageable enterprise data platform with reduced application downtime.

Secure Trusted Platform for your Data

Building on the strengths of SQL Server 2005, SQL Server 2008 extends its security capabilities with the advancements covered in this section.

Transparent data encryption

SQL Server 2008 enables the encryption of an entire database, data files, or log files, without the need for application changes. One key benefit is that it provides the ability to search encrypted data, including both range and fuzzy searches.

External key management

Currently, in SQL Server 2005, encryption and key management is contained entirely within SQL Server. With the growing demand for regulatory compliance and the overall concern for data privacy, more organizations are leveraging encryption as a way to provide a comprehensive solution. SQL Server 2008 provides a mechanism for SQL Server encryption to work with third-party key management and Hardware Security Module (HSM) products.


SQL Server 2008 allows users to create and manage auditing via DDL while also simplifying compliance by providing comprehensive data auditing. This enables organizations to answer common questions such as, “What data was retrieved?”

Transparent failover

The implementation of database mirroring in SQL Server 2005 requires client configuration to allow automatic failover with client redirect. There are times when it is not possible or practical to configure clients in this way. In SQL Server 2008, clients can add database mirroring without requiring application changes. This enables clients to provide a higher level of availability to existing applications.

Automatic recovery from page corruption

Data that is stored in database files runs the risk of data page corruption due to hardware failures. With SQL Server 2008, database mirroring implementations enable automatic recovery of the corrupted pages from the mirror database, transparent to the end user or application.

Log stream compression

Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants minimizes the network bandwidth used by database mirroring. This provides optimal performance of database mirroring.

Hot Add CPU

Hot Add CPU allows users to address scalability issues on demand within a database, enabling CPU resources to be added to SQL Server 2008 on supported hardware platforms without enforcing any downtime on applications. SQL Server already supports the ability to add memory resources online.

Productive Policy-based Management

SQL Server 2008 introduces a new policy-based management framework that shifts organizations from managing by scripts to managing by rules. This shift enables organizations to reduce the time spent on daily maintenance operations by defining a common set of policies for database operations such as query optimizations, naming conventions, backup and restore operations, and index management. These policies will automatically be enforced and monitored and will enable users to publish these policies to thousands of servers and thus provide a consistent management framework across the enterprise. This section describes some of the advancements in policy-based management.

Declarative Management Framework

The Declarative Management Framework (DMF) raises the level of abstraction so that administrators can manage SQL Server via concepts that are familiar to them. In SQL Server 2008, DMF enables forced compliance to policy, instead of simply monitoring via periodic polling. It enables administrators to author and apply their own policies based on the needs of their organization.

Figure 2 Declarative Management Framework

Streamlined installation

SQL Server 2008 introduces significant improvements to the service lifecycle for SQL Server through the re-engineering of the 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.

Optimized and Predictable System Performance

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To address this, improvements have been made to the insight into performance that is delivered to administrators by way of extensive performance data collection, a centralized data warehouse for storing performance data, and tools for reporting and monitoring. Following are some of the advancements in system performance.

Data compression

Data compression enables you to store your data more effectively and reduce the storage requirements for your data. SQL Server 2008 provides significant performance improvements for large I/O bound workloads such as data warehousing. SQL Server 2008 also provides native support out-of-the box for backup


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.

Predictable query performance

SQL Server 2008 enables greater query performance stability and predictability by providing functionality to lock down query plans so that, to the maximum extent possible, query plans survive server restart, server upgrade, and production deployments.

Dynamic Development

SQL Server 2008, along with the .NET Framework, enables developers to build the next generation of applications. Developers can be more productive because they can work with business entities instead of tables and columns. They can build applications that enable users to take their data with them and synchronize their data with back-end servers.

Accelerate Your Development with Entities

One common trend among developers who work with data is that, while they may interact with data from a database, they often like to define high-level business objects that they map the data into; in SQL Server 2008 rather than writing application logic against tables and rows, developers use entities such as ‘customer’ or ‘order.’ The ADO.NET Entity Framework enables developers to program against relational data in terms of entities. Programming at this higher level is incredibly productive and allows developers to use Entity-Relationship modeling directly. Some of the advancements in developing with entities in SQL Server 2008 are covered in this


Language Integrated Query

Language Integrated Query (LINQ) enables developers to issue queries against data in terms of their programming language instead of SQL. It enables seamless, strongly typed, set-oriented queries in Microsoft Visual C#® or Microsoft Visual Basic® against the connected ADO.Net stack (SqlClient), ADO.NET DataSet, and the Entity Data Service Mapping provider.

Figure 3 LINQ to entities

ADO.Net Object services

The Object services layer of the ADO.NET stack enables materialization, change tracking, and the persistence of data as CLR objects. Developers using the ADO.NET Framework can program against a database using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development and supports entities.

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, stores, and data types. In combination with Microsoft Visual Studio®, SQL Server 2008 enables the rapid creation of occasionally connected applications through the combination 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 cache-based, synchronization-based, or 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 are traditionally supported by a database. SQL Server 2008 builds on the strong legacy of supporting nonrelational data by providing new data types that enable developers and administrators to store unstructured data such as documents and images. SQL Server provides a rich set of services on the different data types while providing the reliability, security, and manageability of the data platform. The following sections outline some of the advancements in the area of data storage.

Store Any Type of Data

SQL Server 2008 allows for seamless transition between relational and nonrelational data, enabling users to access documents as data, encode complex hierarchies within XML, and query across relational and text data.

FileStream data

The SQL Server FileStream object allows large binary data to be stored in the file system yet remain an integral part of the database with transactional consistency. This enables the scale out of large binary data to be managed by the database and yet stored on more cost-effective storage and file systems.

Integrated Full-Text Search

Integrated Full-Text Search makes the transition between text searching and relational data seamless, while enabling users to use text indexes to perform text searches on large text columns.

Sparse columns

Sparse columns allow users to store object models onto relational data without undergoing large space costs. They also allows users to build complex content management applications with the underlying database.

Large user-defined types

Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.

Location Intelligence

Geographical information is rapidly becoming main stream to many business applications. SQL Server 2008 provides new spatial data types for developers to build location-aware applications.

Spatial data

SQL Server 2008 includes a 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 integrate the data 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 customers to collect, clean, store, and prepare their business data for the decision-making process. Building on the strong momentum in the BI market, SQL Server 2008 provides a more scalable infrastructure that enables IT to drive business intelligence throughout the organization, manage reports and analysis of any size or complexity, and at the same time provide deep integration with the Microsoft Office System. SQL Server 2008 enables organizations to deliver business insights to all employees leading to better, faster, more relevant decisions.

Next-Generation Data Warehousing

Organizations continue to invest in deriving business value from their data by investing in a BI and data warehousing solution. SQL Serve 2008 provides a comprehensive and scalable data warehouse platform that enables organizations to integrate data into the data warehouse faster, 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

The key goal of data compression is to shrink data warehouse fact tables, while also improving query performance.

Backup compression

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

Partitioned table parallelism

Partitions enable organizations to more effectively manage large, growing tables by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by increasing 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 Management

Use Resource Governor to manage CPU and memory resources within an instance of the relational engine.

Grouping Sets

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping 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. This feature captures the 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 such as checking whether a row exists and then executing inserts or updates.

Scalable Integration Services

The two key advancements in scalability of Integration Services include:

· SQL Server Integration Services (SSIS) pipeline improvements

Data Integration packages can 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

Performing lookups is a common operation, especially in data warehousing, where fact records must use lookups to transform business keys to their corresponding surrogates. SQL Server Integration Services (SSIS) will increase the performance of lookups so that it scales to meet the largest tables.

Scalable Analysis Platform

With SQL Server 2008, organizations can perform highly complex analysis with a large number of dimensions and aggregations. To facilitate the process, SQL Server Analysis Services has made the following advancements in analytics.

Analysis scale and performance

Drive broader analysis with enhanced analytical capabilities, including more complex computations and aggregations. Improved dimension designers to help users avoid performance issues. Address current limitations and increase the flexibility of report layout with report engine improvements, including on-demand processing and instance based rendering.

Block computations

Block computations provide a significant improvement in analysis cube performance, enabling users to increase the depth of their hierarchies and complexity of the computations.


While they are viewing analytical data, users would like to make changes to the underlying data. SQL Server 2005 provides writeback support; with SQL Server 2008 the performance is greatly improved for writeback scenarios.

Scalable Reporting

For many organizations, the challenge is to get 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

The reporting engine provides simplified deployment and configuration, making it easier to deliver reports throughout the organization. This enables users to easily create and share reports of any size or complexity.

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.

Internet report deployment

Reach customers and suppliers by deploying reports over the Internet.

Manage the reporting infrastructure

SQL Server 2008 enables increased 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.

Rich Information Experiences

SQL Server 2005 provides end-to-end services supporting business intelligence applications: data warehousing, Integration Services, Analysis Services, and Reporting Services. Microsoft Office 2007 makes it even easier to support strategic and tactical decision makers with rich analytics in Microsoft Excel® and Excel Server and collaborate around BI through SharePoint® Report Center. Because users can access the power of Reporting Services by leveraging their existing familiarity with Office, more users can create and deploy reports. SQL Server 2008 empowers users with actionable business insight with the reporting advancements covered in this section.

Report Builder enhancements

Easily build ad-hoc reports and author reports with any structure by using 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 front-end applications.

Office integration

SQL Server 2008 enables users to easily author reports directly in Microsoft Word and Excel and publish and share them within the organization with Microsoft Office SharePoint Server.


SQL Server 2008 provides the technology and capabilities that organizations count on. 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.

Special Thanks :Michelle Dumler

No comments:

Post a Comment