Microsoft IT's Top 10 SQL Server 2008 Features
Published: June 2010
Today, more than 4,700 Microsoft® SQL Server® 2008 instances with about 3500 databases on dedicated hosts support critical applications across Microsoft. Because it has deployed SQL Server 2008 since early beta, and with each database administrator supporting roughly 400 databases, Microsoft IT has had time to assess and report the top 10 features of the product in its environment. Read a short description of each feature and the benefits Microsoft IT is reaping.
Article, 116 KB, Microsoft Word file
Microsoft IT manages one of the largest network infrastructures in the world in addition to being the first and best customer of Microsoft. Being an early adopter of SQL Server 2008 has provided Microsoft IT the opportunity to learn firsthand how some of the new and enhanced features have made significant impacts in its day-to-day operations. Following are 10 key reasons why Microsoft IT is excited about the enhancements in SQL Server 2008.
1. Backup Compression
Backup Compression, introduced in SQL Server 2008 Enterprise, is Microsoft IT’s standard method for backing up databases. It has contributed to significant disk space savings – ranging from 50%-70% – at a time when storage constraints and data growth conflicts were approaching supportability limits.
2. Row & Page-Level Data Compression
Data compression reduces the amount of storage space needed to store tables and indexes, which enables more-efficient data storage. Enabling data compression does not require changes to applications. The Business Intelligence (BI) team in Microsoft IT found that SQL Server 2008 page-level and row data compression reduces data footprint by as much as a 9:1 ratio, though a range of 20 percent to 50 percent is more typical. Row compression stores fixed-length rows as variable length, saving 20–30 percent space at the cost of a 10 percent increase in CPU utilization. For example, a 10 percent-utilized CPU would increase to 11 percent utilization.
Microsoft IT observed an 80 percent reduction of its upgraded SAP NetWeaver Business Warehouse (BW) storage. SAP BW has frequent large-volume data input/output (I/O). Note that a cleanly installed BW would not realize the same savings as an upgraded BW.
3. SQL Server Integration Services
SQL Server 2008 provides a scalable enterprise data-integration platform with exceptional Extract, Transform, and Load (ETL) and integration capabilities. It enables organizations to more easily manage data from a wide array of data sources. Microsoft IT experienced SQL Server Integration Services data-flow engine improvements for loading, filtering, and transforming one terabyte of new data daily. The security team, which culls large amounts of network data each day, reported significant improvements over Microsoft SQL Server 2005.
4. Multidimensional Expressions
Multidimensional Expressions (MDX) is a syntax that supports the definition and manipulation of multidimensional objects and data. SQL Server 2008 developers made additional emphasis to improve performance in the execution of MDX calculations. Using MDX block computations improved query performance by more than 30 percent for the BI team in Microsoft IT.
5. Resource Governor
Resource Governor enables administrators to control and allocate CPU and memory resources to high-priority applications. This enables the maintenance of predictable performance and helps prevent resource-intensive applications or processes from negatively affecting performance. The Licensing and Pricing Operations in Microsoft IT supports 400+ users of a 1.5TB Volume Licensing Application that process orders and contracts for Microsoft. They are using Resource Governor to control the impact of ad hoc queries, which allows critical batch synchronization jobs to finish more predictably.
The ad-hoc queries go from custom SSIS packages to look-up queries to excel reports. Because the readers can consume the resources of the servers, blocking the batch jobs running to update the data, the Resource Governor is critical in reducing the CPU load by reserving cycles for the batch jobs, while not blocking the customer queries. User satisfaction when up as a result due to the leveling out of data availability.
6. Star Join
Data warehouses are often implemented as star schemas. A star schema has a fact table at its center; this table typically contains a very large number of rows. Star-join query optimization can improve performance for queries that select a subset of those rows. When SQL Server processes queries by using star-join query optimization, bitmap filters eliminate rows that do not qualify for inclusion in the result set very early on. The rest of the query is then processed more efficiently. The Financial IT team in Microsoft IT experienced a 230 percent performance improvement when using star joins with SQL Server 2008 over previous versions.
7. Change Data Capture
Use Change Data Capture (CDC) to track changes to the data in your tables. CDC uses a SQL Server Agent job to capture insert, update, and delete activity. This information is stored in a relational table, where data consumers such as SQL Server 2008 Integration Services can access it. Use CDC in conjunction with Integration Services to incrementally populate data warehouses so that you can produce more-frequent reports that contain up-to-date information. CDC also allows sync-enabled mobile and desktop applications to perform efficient data synchronization between client and server, without requiring changes to the database. MERGE and CDC implementation reduced code size and complexity for the BI team in Microsoft IT. The team uses CDC to allow the Enterprise Data Warehouse to do delta processing for integration where delta capabilities do not exist at all source systems.
8. MERGE Operator
The new MERGE operator streamlines the process of populating a data warehouse from a source database. For example, rows that are updated in the source database will probably already exist in the data warehouse, but rows that are inserted into the source database will not already exist in the data warehouse. The MERGE statement distinguishes between the new and updated rows from the source database so that the appropriate action (insert or update) can be performed against the data warehouse in a single call. The BI team found that MERGE and CDC implementation reduces code size and complexity, thereby reducing operating costs.
9. Transparent Data Encryption
Transparent Data Encryption (TDE) enables security-enhanced storage of data by encrypting the database files. If the disks that contain database files become compromised, only an authorized agent can decrypt the data in those files. SQL Server performs the encryption and decryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted. Microsoft IT implemented Transparent Data Encryption to meet security and compliance requirements. It required no code changes to Microsoft IT’s applications. With SQL Server 2008 TDE functionality, servers that support services for the finance department resolved a deficiency with a production banking system to keep data encrypted and meet security requirements for handling data. SQL Server 2008 TDE functionality required no code changes, saving valuable Microsoft IT resources.
10. Database Mirroring
Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. Microsoft IT experienced two immediate benefits of mirroring. First, if any database page on a principal server becomes corrupted, SQL Server 2008 can automatically recover the page from its mirror partner. Second, SQL Server 2008 supports compression; log data streamed from a principal mirror is compressed and performance improves.
For More Information
For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada information Centre at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information through the World Wide Web, go to:
© 2010 Microsoft Corporation. All rights reserved.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft and SQL Server 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.