Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Deploying a 10-Terabyte Enterprise Data Warehouse using SQL Server 2008

Business Case Study

Published: April 2009

Microsoft, with more than 60,000 employees worldwide, and with 2006 revenue exceeding U.S. $50 billion, generates a lot of internal data that the company wanted to centralize to provide it with a more integrated view of its customers. To fill this need, Microsoft IT is using the Microsoft® Application Platform to create an enterprise data warehouse (EDW) hosted on Microsoft SQL Server 2008 database software, and running on the Windows Server 2008 Enterprise Edition operating system. The EDW, which is already 2.5 terabytes and is expected to exceed 10 terabytes within the first 12 months of deployment, will give the company a better view of its customers. The IT group has found it can use the Backup Compression feature of SQL Server 2008 to gain a 3-to-1 reduction in backup storage space. The company is also enjoying easier data management because of new features in SQL Server 2008.

Download

Download Business Case Study, 328 KB, Microsoft Word file

Customer Profile

Situation

Solution

Benefits

Based in Redmond, Washington, Microsoft is the worldwide leader in software, services, and Internet technologies for personal and business computing.

Microsoft IT needed to unify information from a number of databases across the enterprise to gain a more integrated view of its customers.

The IT group created an enterprise data warehouse (EDW) using Microsoft® SQL Server® 2008 Enterprise Edition. The EDW is expected to exceed 10 terabytes within its first year of operation.

  • Integrated view of the customer
  • Easier data management
  • Automated capture of data changes
  • 3 times smaller backup storage with compression
  • Faster data inserts
  • Extensibility of Integration Services

Situation

With operations in 89 countries, Microsoft is the largest software company in the world, generating 2006 revenues exceeding U.S.$50 billion. Like other large organizations, the company requires a repository of data to help it better track its business and to analyze customer needs.

The company, which has more than 60,000 employees, already had a large collection of data warehouses, data marts, and other repositories to support reporting and analytics across a spectrum of its internal organizations and functions, including its FeedStore database—so named because it pulls data from 39 internal sources and feeds some 500 subscribing internal applications. (To read a case study about the company's FeedStore database, please see: http://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=48596.)

However, the Microsoft IT group identified a need for creating a centralized data repository that would begin with FeedStore data and go even further, adding information from customer relationship management, finance applications, and a range of other sources to create an enterprise data warehouse (EDW).

"We needed an enterprise data warehouse to provide a true integration of the customer and financial data found in other data warehouses and repositories around the company," explains Jim Walch, GM of Business Intelligence in Microsoft IT. "Creating an EDW using the Microsoft Application Platform was the only way we could provide users with a consistent view of information across the whole enterprise. Microsoft IT's EDW creates the perfect platform to enable our users to analyze data using tools like SQL Server® Reporting Services, SQL Server Analysis Services, Performance Point Server, and Office Excel."

Fast Facts

Current database size

2.5 terabytes

Anticipated first year database size

10 terabytes

Backup Compression ratio

3-to-1

Reduction in log entries per gigabyte of inserted data using enhanced TABLOCK insert

Reduced from 3+ gigabytes to 3 megabytes

Application Platform Capabilities

Data Management, Business Intelligence

Solution

"Prior to SQL Server 2008 and its MERGE SQL statement we had to write custom code which we implemented as stored procedures. We can now use a single MERGE SQL statement to replace two fairly complex stored procedures."

Mike Gudyka, Senior Applications Developer, Microsoft

The organization created its EDW using Microsoft SQL Server 2008 Enterprise Edition database software, running on the Windows Server® 2008 Enterprise Edition operating system. The company needed a rock solid database that could support the 2.5 terabyte size and provide scalability for what is anticipated to be a 10-terabyte EDW within the first 12 months of deployment.

The EDW includes data from the company's FeedStore database, while adding information from a number of other sources including the company's Microsoft Accounting Reporting System, a data warehouse of information used to build Microsoft's financial statements, and the Resource & Project Management database, which stores information used for resource allocation and management, including resource skills and resource roles.

Microsoft IT wanted to build its EDW using SQL Server 2008 in order to take advantage of new features, including:

  • MERGE SQL Statement. The MERGE SQL statement, new for SQL Server 2008, enhances efficiency of executing common database administration tasks such as checking whether a row exists and then executing an insert or update.
  • Change Data Capture. New for SQL Server 2008, Change Data Capture collects the complete content of changes, and places the information in change tables. The feature maintains cross-table consistency and also works across schema changes.
  • Backup compression. With SQL Server 2008 backup compression, the compression is performed in memory before the data is transferred to disk, enabling backups to run faster since less disk I/O is required. Backup compression reduces the storage required to keep backups online, reducing the overall cost of keeping disk-based backups.
  • Enhanced TABLOCK insert. SQL Server 2008 has enhanced the efficiency of using TABLOCK inserts to gain faster data loads. The table locking of TABLOCK provides faster loading than using row locking.

The EDW solution also makes extensive use of SQL Server 2008 Integration Services. First introduced with SQL Server 2005, and enhanced for SQL Server 2008, Integration Services provides enterprise-grade tools for creating and managing extract, transform, and load (ETL) processes. Microsoft IT used SQL Server 2008 Integration Services to create an internal tool, called METaL, for automating much of the process of creating new ETL packages.

"We've used a number of new features in SQL Server 2008 as major building blocks for our solution," says Alexey Yeltsov, Lead Systems Administrator at Microsoft. "And we are looking to take advantage of several other new features, so we are impressed with the wealth of new functionality and features that have been designed into the product."

Benefits

The enterprise data warehouse created using SQL Server 2008 is giving the IT group the integrated view of the customer the company needed, as well as easier data management. SQL Server 2008 features are yielding a number of other benefits for the company, including 3 times smaller backup storage with compression, faster data inserts, and the ability to build solutions using the extensibility of SQL Server Integration Services.

Integrated View of the Customer

As with other large organizations with extensive data collections, Microsoft saw the value of creating an EDW to provide an integrated repository that yields a more complete view into the data.

"Why build an EDW?" asks Yeltsov. "We need to gather our data from different sources into one place to resolve any conflicts in definitions and to provide a single view of the truth."

The EDW, as a comprehensive, integrated, data set, also provides a rich view into customer activity across Microsoft business units and geographies.

"The EDW we are creating with SQL Server 2008 provides our business users at Microsoft with a 360-degree view of the customer," says Mike Gudyka, Senior Applications Developer at Microsoft. "We can gain a more complete view of the customer and improve the quality and velocity of decisions by integrating data that was otherwise isolated in silos across the organization."

Easier Data Management

"The ability to define the period of retention is a big help because previously you would have to code your own job … With the Change Data Capture feature data is held for 3 days by default, but you can simply configure the feature for longer or shorter periods of change data retention."

Ron Ortloff, Technology Architect, Microsoft

EDW developers benefited from using the MERGE SQL statement in SQL Server 2008, because MERGE SQL combines ability to do an insert, update, or delete off the target table in one statement.

"Prior to SQL Server 2008 and its MERGE SQL statement we had to write custom code which we implemented as stored procedures," says Gudyka. "We can now use a single MERGE SQL statement to replace two fairly complex stored procedures."

Replacing stored procedures with the MERGE SQL Statement should also increase update processing speed.

"Using MERGE SQL reduces the amount of code that would otherwise need to be written for table maintenance, and so reduces the chance of making mistakes," says Yeltsov. "Handling what used to require two or three transactions with a single statement also provides faster update processing."

Automated Capture of Data Changes

The Change Data Capture feature of SQL Server 2008 has made it easier for developers to create solutions requiring a mechanism to track change history on a table. Prior to SQL Server 2008, capturing data changes required the creation of custom code.

"Before we had the Change Data Capture feature we created our own code to handle changes," says Gudyka. "This worked, but didn't scale well. When dealing with tables that have millions of rows, it could take longer than we liked to load all of the row indexes to look for changes. Change Data Capture provides a much more efficient mechanism that captures the rows as they're changed. This greatly enhances the scalability of tracking changes."

Change Data Capture also supports a user defined limit on how long change data history is kept.

"The ability to define the period of retention is a big help because previously you would have to code your own job that would go through and selectively prune the table based on the time stamp of the data," says Ron Ortloff, Technology Architect at Microsoft. "With the Change Data Capture feature, data is held for 3 days by default, but you can simply configure the feature for longer or shorter periods of change data retention."

3 Times Smaller Backup Storage with Compression

Anticipating its database to rapidly exceed 10 terabytes, and knowing that multiple backup copies are usually kept, the IT group was eager to take advantage of the ability to compress data with SQL Server 2008.

"Our testing found that the Backup Compression feature in SQL Server 2008 provides 3-to-1 compression, which will correspondingly reduce the disk space we need to deploy to support backups," says Yeltsov. "SQL Server 2008 backup can compress data in-memory – just before it is written to the disk."

The IT group has found that backup compression can reduce the time required to complete backups. Yeltsov notes, "We're seeing faster backups because the CPU compresses data faster than it would have taken to write uncompressed data to disk."

Faster Data Inserts

"Integration Services provides an excellent execution platform that we use to perform ETL across a distributed environment."

Mike Gudyka, Senior Applications Developer, Microsoft

"Our testing found that the Backup Compression feature in SQL Server 2008 provides 3-to-1 compression, which will correspondingly reduce the disk space we need to deploy to support backups."

Alexey Yeltsov, Lead Systems Administrator, Microsoft

With table locking enabling faster loading of large data sets than using row locks, the IT group was happy to find that SQL Server 2008 has enhanced the efficiency of TABLOCK inserts.

With earlier versions of SQL Server, INSERT statements were fully logged into the transaction log, which meant that a 1 gigabyte (GB) insert resulted in 1 or more GB of information being written to the transaction log. "With the enhanced TABLOCK insert functionality of SQL Server 2008, the transaction log entries for a 1 gigabyte insert are reduced to just a few megabytes," says Yeltsov. "This reduces disk usage and enables significantly faster inserts. We're seeing large inserts processed at least three times faster compared to earlier versions of SQL Server."

Extensibility of Integration Services

The extensibility of SQL Server 2008 Integration Services helped the IT group to create an internal solution, called METaL, to automate much of the creation of SQL Server Integration Services packages for performing ETL processes on data brought into the EDW.

The METaL application includes a metadata store and business logic to use the metadata to automate creation and distribution of Integration Services packages customized for a range of ETL needs.

"Integration Services provides an excellent execution platform that we use to perform ETL across a distributed environment," says Gudyka. "We like the fact that we can use our metadata to create Integration Services packages and then send them out for execution. As the packages are running they report back on their progress, supporting fine-grained monitoring."

In summary Microsoft IT is using the Microsoft Application Platform, including SQL Server 2008 Enterprise Edition, to gain a more integrated view of its customers by gathering several terabytes of data from across its operations into a unified enterprise data warehouse. The company is taking advantage of several new features in SQL Server 2008, including the ability to reduce the size of its backup footprint by using Backup Compression.

Products & Technologies

Windows Server 2008, SQL Server 2008, and Visual Studio 2008 provide a secure and trusted foundation for creating and running your most demanding applications. Combined, the products offer advanced security technology, developer support for the latest platforms, improved management and Web tools, flexible virtualization technology to optimize your infrastructure, and access to relevant information throughout your organization.

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 via the World Wide Web, go to:

http://www.microsoft.com

http://www.microsoft.com/sql/2008

http://www.microsoft.com/technet/itshowcase

© 2009 Microsoft Corporation. All rights reserved.

Microsoft, SQL Server®, Visual Studio®, Windows, and Windows 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.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

 

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.