Export (0) Print
Expand All

SQL Server 2008 R2 Improves Performance and Flexibility of Microsoft.com Data-Analysis Solution

Business Case Study

Published: July 2010

The Microsoft.com Core team collects and provides Microsoft product teams with data about software and other downloads from Microsoft.com. The team tested and then deployed a beta version of Microsoft SQL Server 2008 R2. The team found that the upgrade process was fast and easy, and the new solution improves system performance and provides more flexible data access.

Download

Download Business Case Study, 174KB, Microsoft Word file

Customer Profile

Situation

Solution

Benefits

As the worldwide leader in software for business and personal computing, the vision of Microsoft Corporation is to enable people and businesses throughout the world to realize their full potential. In addition to developing and maintaining the Microsoft home page and Download Center, the Core team at Microsoft is responsible for supporting authoring, publishing, and web analytics for all sites on Microsoft.com.

The Core team collects and analyzes data about the approximately 250 million downloads requested on an average day from Microsoft.com. Users need information such as download rates, download-failure rates, most-common referrers, preferred browsers and operating systems, and customer locations.

The Core team migrated the entire MS-D system to SQL Server 2008 R2. The system uses the SQL Server 2008 R2 Database Engine and Microsoft SQL Server 2008 R2 Integration Services to extract and store the data, and Microsoft SQL Server 2008 R2 Analysis Services to expose cubes to users.

With SQL Server PowerPivot for Excel, analysts on Microsoft product teams can use familiar Excel tools to more easily build and report on custom data-analysis models.

  • Easy upgrade
  • Improved performance
  • More flexible data access available with SQL Server PowerPivot for Excel

Situation

The Core team at Microsoft.com collects and analyzes data about the approximately 250 million downloads requested on an average day from Microsoft.com. This data is accessed by about 1,800 users from various Microsoft product teams and other business divisions that rely on Microsoft.com to provide downloadable software packages and informational materials to customers. These users need information such as download rates, download-failure rates, most-common referrers, preferred browsers and operating systems, and customer locations.

In 2008, the Core team developed a Business Intelligence system called Microsoft Decisions (MS-D) to consolidate and model information about all Microsoft.com downloads. MS-D, which was based on Microsoft® SQL Server® 2008, stored about 2 terabytes of data and exposed multidimensional data cubes to users, who could either access preconfigured reports or pull data from MS-D into their own data platforms.

To ensure that it can continue to offer high-quality customer service, the Core team engages in an ongoing improvement process that includes evaluating new technologies for possible use in the MS-D system.

Solution

In 2009, the Core team learned of the availability of a beta version of Microsoft SQL Server 2008 R2 data management software. In September 2009, after a successful test, the team migrated the entire MS-D system to SQL Server 2008 R2. The system uses the SQL Server 2008 R2 Database Engine and Microsoft SQL Server 2008 R2 Integration Services to extract and store the data, and Microsoft SQL Server 2008 R2 Analysis Services to expose cubes to users.

With the new solution, the Core team can provide MS-D users with access to Microsoft SQL Server PowerPivot for Excel®, which integrates SQL Server 2008 R2 Analysis Services and Microsoft Excel 2010 spreadsheet software. With SQL Server PowerPivot for Excel, analysts on Microsoft product teams can use familiar Excel tools to more easily build and report on custom data-analysis models.

"With SQL Server PowerPivot for Excel, our users will be able to slice and dice data in local-machine memory, improving performance and giving them the ability to dig a lot deeper into the data."

Dany Daher
Program Manager
Microsoft Corporation

Benefits

The Core team is still in the early stages of evaluating new features for the solution, but users are already enjoying significant benefits.

Easy Upgrade

Backward compatibility was a high-priority for the Core team. The team found that the process of upgrading from SQL Server 2008 to SQL Server 2008 R2 was fast and easy. The team did not encounter compatibility problems transitioning between the two versions.

Improved Performance

The Core team will continue to improve the performance of its MS-D system by using the SQL Server 2008 R2 Resource Governor to manage CPU and memory resources. Some users pull data from MS-D into their own data warehouses. Without the ability to manage the allocation of resources across different users, a process like that could grow to monopolize as much as 90 percent of memory and processing power. It could essentially make the system unavailable to the rest of the MS-D users. With SQL Server 2008 R2 Resource Governor, the team can restrict such processes to no more than 5 percent of server overhead and reduce the performance problems that users experience.

Performance is also improved by using the solution’s data-compression capability. The Core team now has a data-compression rate of 50 percent and will benefit from better compression of multilingual data, because the MS-D system monitors downloads from centers located around the world. Previously, with the data-compression capabilities in SQL Server 2008, the team was able to double existing storage capacity, avoiding tens of thousands of dollars in hardware costs. Data compression remains a strong capability in SQL Server 2008 R2.

More Flexible Data Access

The team is looking forward to using SQL Server PowerPivot for Excel. Given the volume and structure of the data, it is essential that the team maintain high cube and browsing performance. As Dany Daher, Program Manager for the MS-D team says, "With SQL Server PowerPivot for Excel, our users will be able to slice and dice data in local-machine memory, improving performance and giving them the ability to dig a lot deeper into the data." It will also save time that the team would otherwise have to spend creating and managing new custom reports for users.

Products & Technologies

  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft Office
  • Microsoft Excel 2010
  • Microsoft SQL Server 2008 R2 Analysis Services
  • Microsoft SQL Server 2008 R2 Integration Services
  • Microsoft SQL Server 2008 R2 Resource Governor
  • Microsoft SQL Server PowerPivot for Excel

Conclusion

The Core team at Microsoft.com migrated their MS-D Business Intelligence system to Microsoft SQL Server 2008 R2. The new solution takes advantage of the SQL Server 2008 R2 Database Engine and Microsoft SQL Server 2008 R2 Integration Services to extract and store data, and Microsoft SQL Server 2008 R2 Analysis Services to expose cubes to users. The Core team found that the upgrade to SQL Server 2008 R2 was fast and easy. The new solution provides improved performance and more flexible data access. SQL Server is at the center of everything that the Core team does. SQL Server continues to be a powerful technology for everything from data platforms to producing actionable reports.

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 Order Centre at (800) 933-4750. 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/technet/itshowcase

http://www.microsoft.com/sqlserver

http://www.microsoft.com/office

© 2010 Microsoft Corporation. All rights reserved.

Microsoft, Excel, SQL Server, 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.

 

 

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft