Moving to SQL Server 2005 at Microsoft

Technical White Paper

Published: December 19, 2005

Executive Summary

Business requirements at Microsoft demand enterprise solutions that provide real-time information and business intelligence. Microsoft® SQL Server™ has been instrumental in providing a core set of features that helps meet the needs of Microsoft. With each release of SQL Server, the core set of features has grown to accommodate new demands of business groups at Microsoft. Microsoft SQL Server 2005 is one of the most feature-rich releases yet, providing enhancements in enterprise information management, enterprise development productivity, and enterprise application performance.

To provide a more enterprise-scalable, reliable, and powerful platform to business groups at Microsoft, Microsoft Information Technology (Microsoft IT) has been using prerelease versions of SQL Server 2005 since early 2004. To provide a framework for early adopters and help in the coordination and deployment of SQL Server 2005 to both internal customers and external customers, Microsoft IT created the Technology Adoption Program (TAP). The program provides a foundation for engagement, development, testing, rollout, and postmortem feedback. As a result of the program, most of the internal applications that use Microsoft SQL Server 2000 have been upgraded to SQL Server 2005, or plans are in place to move them to SQL Server 2005 in the near future.

By moving to SQL Server 2005, Microsoft IT can maximize collaborative business performance by providing real-time business insights through an integrated analysis and reporting platform that enables Microsoft business units to make better decisions and respond to new opportunities. Additionally, the integrated developer toolset in SQL Server 2005 enables Microsoft IT to automate routine tasks and work more on scalable enterprise solutions to meet strategic business needs. With the added support for 64-bit hardware choices, adoption of SQL Server 2005 has dramatically increased enterprise application performance and has greatly improved the scalability of existing and new solutions.

The 2-terabyte central information repository at Microsoft that is named FeedStore was one of the first solutions to move to SQL Server 2005. This repository provides a central enterprise data integration point that supports both applications that retrieve data from FeedStore and applications that supply data to FeedStore. Another enterprise-class solution that Microsoft IT upgraded to SQL Server 2005 is MS Sales. This application consists of more than 40 servers and provides a single enterprise-wide decision support system for revenues. Additionally, Microsoft IT upgraded the enterprise resource planning (ERP) system SAP R/3 at Microsoft, and the SAP R/3 application is already using a number of new features. Microsoft IT deployed SQL Server 2005 Analysis Services and SQL Server 2005 Reporting Services to enhance the Microsoft Accounting Reporting System (MARS) and provide a more robust financial document reporting solution. Microsoft IT implemented the new SQL Server Integration Services component to enhance extract, transform, and load (ETL) operations between line-of-business (LOB) applications.

Microsoft IT experienced a large number of benefits by moving to SQL Server 2005 in each of its LOB applications. Improved availability, dramatic performance increases, and more robust and scalable solutions help Microsoft IT meet business requirements to a higher degree than with SQL Server 2000. By using the new features and toolsets in SQL Server 2005, Microsoft IT was able deliver new solutions more quickly and at a much lower cost.

The size and complexity of each deployment of SQL Server 2005 ranges from a single instance of SQL Server to a highly complex multi-tier, multi-instance business solution. With each deployment, Microsoft IT has created a common set of project planning guides, predeployment checklists, implementation scripts, and postdeployment checklists to help ensure a successful upgrade to SQL Server 2005. Additionally, Microsoft IT has formulated and documented a number of best practices on each completed deployment to help provide guidance for future implementations.

Moving to SQL Server 2005 can be challenging. Because a large number of applications have already been upgraded to SQL Server 2005 at Microsoft, Microsoft IT has accumulated a large repository of knowledge that may help other organizations move to SQL Server 2005. This knowledge covers the planning, predeployment, rollout, and postdeployment tasks of moving to SQL Server 2005.

This document is intended for enterprise business decision makers, technical decision makers, IT architects, database developers, database administrators, and deployment managers. Although this document provides recommendations based on Microsoft IT early-adopter experiences, it is not intended to serve as a procedural guide. Each enterprise environment has its own unique set of circumstances. Therefore, each organization should adapt the knowledge that this document provides to suit its specific business requirements.

Note: For security reasons, the sample names of internal resources, organizations, and internally developed security file names used in this paper do not represent real resource names used within Microsoft and are for illustration purposes only.

Introduction

Overview of the SQL Server 2005 Technology Adoption Program

"SQL Server 2005 is enterprise ready: We've had our most business-critical and mission-critical databases live in production on SQL Server 2005 since Beta 2. As Microsoft's first and best customer, it is vital that we in Microsoft IT run the business on our platforms before we ask our customers to deploy in their environments."

Rich Mullins

To help facilitate the early adoption of SQL Server 2005, Microsoft IT created the TAP to enable internal and external customers to deploy and implement pre-RTM releases of SQL Server 2005. The program structure consisted of the following phases:

• Engagement. The SQL Server 2005 development team works closely with Microsoft IT or the external customer to validate the customer's architecture, development, test, and project plans. Additionally, the development team documents the customer's expectations of SQL Server 2005 to obtain metrics that will validate the success of the deployment.

• Development and testing. Dedicated SQL Server support specialists work with the Microsoft IT team or the external customer's team to test and to deploy regular releases until RTM. The support specialists identify and resolve issues in a timely manner to help guarantee the successful deployment of new releases. The support specialists supplement specific feature implementations by using additional SQL Server developer resources who work regularly with Microsoft IT or the external customer to help ensure successful feature implementation.

• Rollout. For customers who are engaging in a production deployment of SQL Server 2005, Microsoft IT identifies specific resources to work with the business unit or the external customer to help deploy SQL Server 2005.

• Referencing. Microsoft IT and external customers share their experiences about their deployment and specific implementation to help build a knowledge base of information to help future deployments.

• Postmortem. At the end of the program, Microsoft IT collects customer feedback that is used to help build a better framework for future programs.

The TAP has been a successful instrument in providing guidance and collaboration for the deployment of SQL Server 2005 at Microsoft. Since the release of SQL Server 2005 Beta 2, the number of applications has grown to slightly more than 100 internal Microsoft Business Unit IT customers, and more than 50 external customers have also participated. The feedback has resulted in a better product, with more than 2,000 bugs filed and resolved by early-adopter participants. The principal driving factors behind the TAP are to validate products before they are released and to provide feedback to internal development teams to enhance the product or to correct issues.

Overview of the SQL Server 2005 Feature Implementations at Microsoft

A large number of Microsoft applications have been upgraded to take advantage of some of the new SQL Server 2005 features. Because of the large number of features included in SQL Server 2005, this document will cover only a limited number of feature implementations at Microsoft and then provide a general description of some of the benefits that resulted from each implementation. These features include the following:

• Database mirroring. This feature enabled Microsoft IT to increase the availability of SQL Server instances. Most of the LOB applications have used either clustering or log shipping to increase the availability of the application. However, by using database mirroring, Microsoft IT can now guarantee that transactions that are committed on the primary server are also on the secondary server.

• Improved hardware support. Moving to a 64-bit mode platform enabled Microsoft IT to scale its LOB applications to a much higher degree. Within some applications, run-time performance increased in the 40 percent range. All major services in SQL Server 2005 fully support 64-bit mode and can use up to a tested limit of 512 gigabytes (GB) of memory. This support enables a long-term future growth environment.

• Integrated toolset. By using both SQL Server Management Studio and SQL Server Business Intelligence Development Studio, Microsoft IT was able to build enterprise-class solutions by using more productive and intuitive methods. For example, Microsoft IT replaced complex Transact-SQL queue code with Service Broker Data Definition Language (DDL) statements. Microsoft also used Integration Services to write complex ETL operations.

• SQL Server Service Broker. By using Service Broker, Microsoft IT was able to eliminate complex Transact-SQL queue code and replace it with simple Service Broker DDL statements. Additionally, implementing Service Broker enabled Microsoft IT to scale the application to a much higher degree and simplify the maintenance of the code base.

• SQL Server Analysis Services. By using Analysis Services coupled with SQL Server Reporting Services to replace a Microsoft Office Excel® workbook solution, Microsoft IT was able to provide a more secure and available reporting framework that could scale out to meet business requirements.

The preceding list is by no means a complete list of the new features that Microsoft IT has implemented, and this document provides more details. However, the preceding list provides an insight into some of the features that Microsoft IT used in some of its implementations.

Upgrading to SQL Server 2005

"Upgrading to SQL Server 2005 is made simple with a suite of helpful preparation tools and backward compatibility built into the product. SQL Server 2005 is an enterprise scalable application that installs with ease and performs beyond expectations. The new design and features of SQL Server help the DBA perform more efficiently in the enterprise."

Barry Marlow

Moving to SQL Server 2005 at Microsoft started well before the RTM version was released. Therefore, Microsoft IT performed most installations by using a pre-RTM Setup program. Microsoft IT installed subsequent updated releases by using a customizable Setup program and installation scripts that performed a number of activities, such as detaching databases and removing a prior release of SQL Server 2005 and the Microsoft .NET Framework version 2.0.

The RTM SQL Server 2005 Setup program includes a number of enhancements that help provide for a more robust installation process. The Setup program is now based on Microsoft Windows® Installer. Therefore, the Setup program now provides a more consistent, reliable, secure, and customizable installation or upgrade. The Setup program integrates all the SQL Server 2005 components and subsystems into one product. This integration simplifies component selection and configuration tasks.

Installation Options

SQL Server 2005 supports upgrading from SQL Server 2000 Service Pack 4 (SP4) and Microsoft SQL Server version 7.0 SP4. Additionally, subsystems such as Analysis Services, SQL Server Agent, Notification Services, Data Transformation Services (DTS), Full-Text Search, and Reporting Services can also be migrated or upgraded during the installation process.

The SQL Server 2005 Setup program supports either an in-place upgrade or a side-by-side migration. In an in-place upgrade, the existing instance is replaced with the new instance of SQL Server 2005. However, the instance retains the existing data and metadata. In a side-by-side migration, the existing instance of SQL Server remains untouched, and a new instance of SQL Server 2005 is added to either the existing server or a new server. Subsequently, when the migration is completed, the user databases are manually copied to the new instance through detach and attach commands.

The in-place upgrade is best suited for smaller applications in which additional hardware is not available and resources are limited. Additionally, the in-place upgrade requires some down time and is an all-or-nothing option that replaces the prior installation. Therefore, adequate failure recovery plans are required in case a failure occurs. The side-by-side migration is better suited for larger applications in which additional hardware and resources are available. The side-by-side migration provides more flexibility by letting the existing instance continue to run until the new instance is ready for a complete migration. This flexibility permits a gradual migration during which testing and verifying the new instance can occur without disrupting the existing application. If the upgrade fails, the old instance still runs.

The technique that Microsoft IT used to deploy SQL Server 2005 consisted of deploying each new release in the lab and then rolling out a clean image to production. This technique enabled Microsoft IT to perform migration tasks offline and then push new releases through its various test, development, and production environments.

Microsoft IT enhanced the new integrated SQL Server 2005 Setup program to include the following new features:

• Windows Installer. The SQL Server 2005 Setup program now uses Windows Installer and integrates all the subsystems into one Setup program. Therefore, all components can be installed at the same time.

• System Configuration Checker (SCC). Before starting the Setup program, SCC examines the destination computer for any conditions that may prevent or block the installation of SQL Server 2005.

• Logging. The Setup program greatly improves logging, and it provides much more detailed information for each step of the installation process. Therefore, system administrators can quickly identify troubleshooting installation issues by examining the logs for errors.

• Clustering. The Setup program provides better support for the installation of SQL Server 2005 in a clustered environment.

Upgrade Plan

Microsoft IT, with the help of the TAP, carefully planned each application upgrade to SQL Server 2005. Microsoft IT documented current application SQL Server feature usage, and it identified key subject matter resources where necessary. Microsoft IT developed a comprehensive project plan for each upgrade, and it assigned tasks to designated resources. Within the planning process, a number of testing and validation processes occurred to make sure that the application that was being upgraded would work as expected when the deployment was completed.

Most LOB applications at Microsoft have the traditional development, test, and user acceptance testing (UAT) environments. Microsoft IT first moves all upgrades through each of these environments to identify any application issues that may result from deprecated or discontinued commands before the production environment is upgraded. SQL Server 2005 includes a number of new DDL commands that deprecate existing stored procedures that were used in earlier versions of SQL Server.

Specific tasks that occur during the testing may include the following:

• Create the test plan.

• Test the upgrade process by running both Upgrade Advisor and SCC.

• Test the application for performance changes, and record metrics to compare with previous baselines.

• Test the application for complete functionality and UAT signoff.

• Test a database recovery and a rollback to an earlier version.

Predeployment Preparation Checklist

Microsoft IT uses a predeployment preparation checklist to help make sure that the installation or the upgrade of SQL Server 2005 is successful. In the planning stages of the upgrade, the number of predeployment tasks may increase or decrease, depending on a number of factors. The following factors affect the number of tasks that are required:

• The current operation environment affects the upgrade process if the existing instance is running in a clustered environment or not running in a clustered environment.

• The use of existing SQL Server 2000 features, such as log shipping, replication, Full-Text Search services, DTS, and other features, may require specific IT resources.

• New SQL Server 2005 features, such as database mirroring, table partitioning, or Integration Services, will require dedicating specific resources to implement.

For a base installation or upgrade of SQL Server 2005, Microsoft IT uses the following predeployment checklist:

• Record a baseline data set. A baseline data set provides valuable information to the Microsoft Product Support Services team in Microsoft IT to help diagnose problems that may occur if the installation or upgrade fails. To obtain a baseline data set before it installs or upgrades SQL Server 2005, Microsoft IT uses the following tools to record the state of the current production environment:

  • Microsoft PSSDiag tool (PSSDiag.exe). Microsoft IT uses the PSSDiag tool to collect data that helps significantly reduce the time that is required to solve SQL Server issues. Typically, the tool captures SQL Server activity and Performance Monitor information that helps diagnose issues that are related to SQL Server deployment.

  • Microsoft Product Support (MPS) Reporting tool (MPSRPT_SQL.exe). Microsoft IT uses the MPS Reporting tool to capture information about the computer hardware and configuration that helps identify any issues that may create problems when the installation or the upgrade occurs.

  • Best Practices Analyzer Tool. Microsoft IT uses the Best Practices Analyzer Tool for Microsoft SQL Server to make sure that the server that is the target of the SQL Server 2005 installation or upgrade is being correctly managed and operated.

• Run the Microsoft SQL Server 2005 Upgrade Advisor tool. Microsoft IT uses Upgrade Advisor to analyze the existing instance of SQL Server that is being upgraded. By using Upgrade Advisor, Microsoft IT can analyze existing databases and determine whether any database is using any deprecated functionality. Then, Microsoft IT can take corrective action before the upgrade. Additionally, Upgrade Advisor can identify and report any configuration changes that will occur as part of the upgrade process. Upgrade Advisor analyzes a computer that is running SQL Server and provides a detailed issue report on the following components:

  • Database Engine

  • Analysis Services

  • DTS

  • SQL Server Agent

  • Full-Text Search

  • Notification Services

  • Replication

  • Reporting Services

  Upgrade Advisor reports issues warnings, severe issues, or critical issues, and it provides a corresponding link to documentation to help correct and resolve each problem.

• Check server health. Microsoft IT analyzes each server's hardware, configuration, and software to make sure that the server is operating correctly and no anomalies exist.

• Clean the databases. By running the Database Console Command (DBCC) CHECKDB command on each database on the server before an upgrade, Microsoft IT can check the allocation and the structural integrity of all the database objects in the database. Microsoft IT can ensure that the database objects are consistent or are repaired before the upgrade.

• Back up the databases. Microsoft IT makes a current backup of all the databases, including a backup of any full-text catalogs that exist.

• Prepare recovery media. In case of a rollback to an existing version, Microsoft IT makes sure that all the media is available to restore the server to its former state.

• Check for the database read-only attribute. To make sure that existing databases can be upgraded, Microsoft IT checks each database to make sure that the read-only attribute is removed.

• Check for SQL Server Agent job conflicts. Microsoft IT checks all SQL Server Agent jobs to make sure that their execution time does not conflict with the upgrade time window.

Production Deployment Checklist

Microsoft IT uses a production deployment checklist to make sure that each step in the deployment is followed and a successful installation of SQL Server 2005 occurs.

Stand-Alone Server

Microsoft IT performs the following tasks to perform a successful stand-alone installation of SQL Server 2005:

• Verify that the current backup is current and recoverable.

• Verify that all media that is required for a reinstallation of the existing image is available.

• Verify that the DBCC command has been performed on all databases.

• Verify that the predeployment baseline data set has been collected.

• Stop all scheduled SQL Server Agent jobs, and verify no conflicts.

• Stop all third-party software that is running on the server.

• Verify that no users are logged on to the instance of SQL Server and that no users can subsequently log on to the instance of SQL Server when the installation or the upgrade is running.

• Verify that the SQL Server, SQL Server Agent, and Setup user accounts have all the correct user rights to perform a successful installation or upgrade.

• Stop all nonessential services, and set all other servers to Manual except the essential services that are listed in the following table.

Table 1. Essential Services

Note: When SQL Server is running in a clustered environment, additional essential services are required.

• Close all applications that may try to use a SQL Server connection. For example, Internet Information Services (IIS) or Microsoft Systems Management Server (SMS) may try to connect to SQL Server. Some antivirus programs that use SQL Server may also block an installation.

• Verify that no databases are read-only.

• Verify that the system databases are set to auto-grow.

• Remove any previous SQL Server 2005 installations or .NET Framework 2.0 installations.

• Restart the server before the deployment begins to make sure that no prior install or uninstall steps require a restart.

• Review the SCC output, and address any error messages that occur before continuing the installation or the upgrade.

• Complete the installation or the upgrade, and then restart the server.

• Perform regular testing.

• Back up new databases, and make sure that backup media is stored in a safe location.

Clustered Server

Microsoft IT performs additional tasks when deploying SQL Server 2005 on a cluster:

• Verify that no clustered resources failed.

• Verify the cluster service accounts.

• Verify the DNS service accounts.

• Verify the cluster dependencies.

• Verify that the local policies for the SQL Server startup account have the following policies in place:

  • Lock pages in memory.

  • Act as part of the operating system.

  • Create a token object.

  • Log on as a service.

Postdeployment Checklist

Microsoft IT uses the following postdeployment checklist to verify the success of the SQL Server 2005 installation and to capture a baseline data set:

• Verify a successful installation and data integrity.

• Back up the upgraded databases immediately but do not overwrite the old database backups that occurred prior to the deployment.

• Run DBCC on all databases to verify that no corruption has occurred.

• Collect a postdeployment baseline data set.

• Implement the SQL Server Health and History (SQLH2) tool for data collection.

• Reverse all the necessary steps in predeployment:

  • Reset database attributes to their values before deployment.

  • Enable SQL Server Agent jobs that were disabled.

  • Re-enable all services that where shut down before deployment.

• Implement and install any application changes.

• Provide security for the environment by using the following security policy checklist:

  • Use Windows authentication mode.

  • Ensure strong passwords.

  • Enable password policy for strength and for expiration.

  • Review expired SQL Server account passwords to help secure all new instances.

  • Set auditing to a minimum of the Failed logins only option.

  • Make sure that SQL Server supporting services are off by default and are started only if the application requires them.

  • Make sure that the SQL Server common language runtime (CLR) feature is disabled by default and is enabled only if the application requires it.

  • Make sure that SQL Server roles are used, and that object permissions are never granted to SQL Server logon accounts.

  • Make sure that domain security groups are used, and that object permissions are never granted to SQL Server logon accounts and Microsoft Windows NT® groups.

  • Make sure that administrator access to SQL Server is granted through Microsoft Windows Server™ 2003 domain security groups, and that the BUILTIN\Administrators logon is not a member of the SQL Server sysadmin server role.

  • Make sure that application logon accounts that the application uses are not a member of any one of the built-in database roles. Applications must run under the least privileged account as a best practice.

  • Make sure that guest accounts are removed from or denied access to each database on the server.

  • Modify the default access control lists (ACLs) on SQL Server folders, files, and registry keys to help protect against modifications by unauthorized users. The SQL Server service accounts require read access to each area and write access to SQL Server data and log files.

  • Restrict access to the sysadmin server role for the following extended procedures: xp_regread, xp_regwrite, xp_regaddmultistring, xp_regdeletekey, xp_regdeletevalue, xp_regmovemultistring, xp_regenumvalues, xp_regenumkeys, xp_cmdshell, and all sp_OA* stored procedures.

  • Do not enable SqlMail, and use Database Mail only if required.

Troubleshooting

Installation of SQL Server 2005 creates a number of log files that document the installation steps and log any errors that may occur. The Summary.txt log file is typically located in the following folder: Drive:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG.

Detailed information that may help troubleshoot installation problems is located in the following folder: Drive:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files.

The detailed log file names increment with each installation and have the following format: SQLSetup[#####]_[ComputerName]_[Setup Task].log.

Taking Advantage of SQL Server 2005 Features

Moving to SQL Server 2005 at Microsoft was not only driven by the benefits provided in the base product. The move also took advantage of some of the new features in SQL Server 2005. These new features enabled Microsoft IT to create, enhance, and better support solutions for the Microsoft business units as new dynamic business opportunities arose.

FeedStore Implementation

"SQL Server 2005 is the most significant relational database release I've seen in many, many years and is without a doubt enterprise scalable and ready. We have been running Microsoft's most significant core enterprise applications and services that operate our global business on SQL Server 2005 for over a year to ensure it is ready for our enterprise customers."

David Fahey

The FeedStore application is one of the largest databases at Microsoft. FeedStore consolidates more than 2 terabytes of data and grows by more than 30 percent each year. FeedStore acts as a central enterprise integration point. The application pulls data from many internal sources and then publishes the data into 39 data sets. More than 500 subscribing applications worldwide subscribe to the resulting published data sets. An example of a data source includes the company's SAP R/3 ERP system. The distribution servers are geographically located in Redmond (Washington), Dublin, and Singapore to maximize bandwidth usage to subscribing applications.

Figure 1 is a simplified diagram of the FeedStore application at Microsoft.

Figure 1. Diagram of FeedStore application

SQL Server 2005 Feature Implementation

Microsoft IT wanted to enhance the FeedStore application to take advantage of some of the new features in SQL Server 2005. To do this, Microsoft IT moved to the beta edition of SQL Server 2005 in the middle of 2004. The first component of the application that Microsoft IT upgraded was the data warehouse. Future plans include updating the publishing and distribution servers after the RTM release of SQL Server 2005.

The most significant enhancement for the FeedStore application was the hardware upgrade from eight-way 32-bit computers to four-way 64-bit computers. By moving to a 64-bit platform, the FeedStore application can scale to a higher degree by using the increased processing power of the 64-bit CPU and the increased addressable memory that is available to cache pages. The native 64-bit implementation in SQL Server 2005 eliminates the memory constraint that the FeedStore application experienced when it ran in 32-bit mode. Within the FeedStore application, a large number of existing Transact-SQL scripts that are used for ETL activities are experiencing run-time performance increases in the 40 percent range.

Note: Since going live by means of SQL Server 2005, the FeedStore application has processed more than 13.5 billion transactions.

Strategic Future Planning

Plans for the FeedStore application include taking advantage of the following new SQL Server 2005 features:

• Integration Services. FeedStore uses thousands of lines of Transact-SQL code in stored procedures and batch scripts to implement its ETL processes. Supporting and maintaining this code is very costly. By moving to Integration Services, formerly known as DTS, Microsoft IT can retire thousands of lines of custom code. Additionally, Integration Services will provide Microsoft IT opportunities to change its integration architectures. These changes will enhance ETL performance and integration with existing and new data sources.

• CLR. Within the thousands of lines of Transact-SQL code in FeedStore, a number of complex procedures, triggers, and functions have been written. This complex code is much better suited to the .NET Framework 2.0 and its feature-rich library of classes and methods. SQL Server 2005, through the integration of the CLR, enables Microsoft IT to extend the functionality of FeedStore without needing custom extended procedures or third-party applications.

• Database encryption. The FeedStore application already helps protect personally identifiable information in its databases by using a strong schema security and by using the new features in SQL Server 2005. These features include built-in key management, asymmetric encryption, symmetric encryption, and certifications. Microsoft IT is considering plans to isolate personally identifiable information data even more. This data would be isolated in a central digital asset store from which Microsoft IT would use SQL Server 2005 encryption features to help protect the data.

• SQL Server Service Broker. By using the new Service Broker technology, Microsoft IT wants to create a solution that further extends extranet activity in accordance with security guidelines. The Service Broker enables Microsoft IT to provide a solution that creates endpoints in which a business conversation can occur. In this business conversation, the data is encrypted and has permission-based access for security.

MS Sales Implementation

"Scalable platform with great performance and developer productivity out of the box."

Ketan Patel

The MS Sales application is a single-solution tool that consolidates worldwide sales, inventory, distributor, reseller, and customer data at Microsoft. The relational database has been optimized for ad hoc query performance and to provide business units with an up-to-date, accurate, and consistent picture of the state of Microsoft business. The MS Sales application has the following features:

• A multi-tiered application architecture

• A datamart that is larger than 500 GB

• More than 9,000 internal Microsoft users

• More than 900 attributes on which users can query

• Load balancing across six query servers

• More than 25,000 ad hoc reports that run each week

• More than 10 million transactions per month stored in the warehouse

• More than 40 servers in the development, test, and production environment

• A user interface that consists of an Excel add-in that is supplemented with a Web-based tool that presents summarized static reports

Figure 2 provides a simplified process overview of the MS Sales application.

Figure 2. Simplified MS Sales process diagram

Data is added to the data warehouse by means of a server that is dedicated to loading and validating data imports. The server does these activities by using Windows NT batch jobs and Microsoft Visual C++® code. The factory server then de-normalizes the data to create the MS Sales datamart tables and indexes that the query servers use. A number of processes run daily, weekly, monthly, and annually on the factory server that maintains the MS Sales datamarts with up-to-date business values.

A user submits a request to the MS Sales application by using an Excel add-in that sends the request to the application tier. The application tier load balances the request and then submits the request to one of the query servers for execution.

SQL Server 2005 Feature Implementation

Table Partitioning

The new table-partitioning feature in SQL Server 2005 enabled Microsoft IT to implement a simplified table-partitioning architecture. In the previous implementation, sales data was broken down in monthly tables. When users selected multiple periods, a number of join clauses were required to return the results that users wanted. The Transact-SQL code that was required to logically create the query and submit the request to the query server was complex. The new simplified table-partitioning feature has dramatically reduced the total query size and has increased query performance when compared to running queries on separate monthly tables.

For example, when a user requests 96 months of data, the total query size has been reduced from 180 kilobytes (KB) to 5 KB. Therefore, table partitioning has dramatically improved the scalability and the manageability of the large sales tables within the MS Sales application, and query performance has improved by over 70 percent most of the time.

Database Mirroring

To increase the availability of the load balancing servers, Microsoft IT implemented database mirroring. The prior implementation on the load balancing servers was log shipping. Even though log shipping provided availability and failover support for the MS Sales application, some client requests were still sometimes lost. With database mirroring in SQL Server 2005, transactions are guaranteed on the mirrored server, and automatic failover occurs when the principal server is no longer available.

Database Mail

The new Database Mail feature in SQL Server 2005 is used for all operations and service monitoring in the MS Sales application. The MS Sales application calls Database Mail from both SQL Server Agent and stored procedures. Database Mail has the following features:

• MS Sales database servers no longer need to have an Extended MAPI client on the same computer as the computer on which Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail.

• Database Mail runs outside SQL Server in a separate process, and SQL Server will continue to queue e-mail messages even if the external process stops or fails.

• Database Mail supports failover accounts and enables users to specify more than one SMTP server.

Data Type varchar(max)

The MS Sales application uses the new varchar(max) data type to send Transact-SQL statements to execute. The new data type allows for storage sizes up to 2^21-1 bytes instead of the 8,000-byte size of the standard varchar data type.

SQL Server Service Broker

By using Service Broker for the MS Sales application tier, Microsoft IT changed the architecture to a much more scalable and reliable solution. The queue application design of Service Broker fit perfectly as a replacement of the custom-coded queuing application tier that existed before SQL Server was upgraded. The asynchronous queuing in Service Broker enables all submitted requests to be captured in a queue no matter what the load is on the server. Additionally, responding to queue submission is automatic through specifying a target service endpoint to service queue events for each request that is created. The number of query requests ranges from 200 to 4,000 a day, and the number of Service Broker messages is in the tens of thousands.

Figure 3 is a diagram of the new MS Sales application tier.

Figure 3. Diagram of the new MS Sales application tier

In the prior implementation, Microsoft IT created a SQL Server Agent job for each query that was submitted to the query servers. With five servers and 30 queries running for each server, this implementation meant 150 SQL Server Agent jobs were continuously running and consuming resources on both the application tier and the database tier. The new Microsoft IT application tier has corrected the following problems with the old implementation:

• Problems restarting failed jobs.

• Resource utilization on both the application tier and the database tier.

• Custom code queue maintenance and support.

• The fact that all jobs must be restarted if the application tier fails. This requirement severely affects application performance.

Microsoft IT has achieved the following benefits since moving to SQL Server Service Broker:

• The required code in the application has been reduced by 50 percent. This change has reduced maintenance and support costs.

• Configuration to scale load is now implemented through one statement to adjust the thread pool.

• Service Broker is integrated. Therefore, no queries are lost because the database is mirrored to a hot standby.

• The required code was simple to write and took less than 10 days to complete.

Strategic Future Planning

With the vast number of new scalability features implemented in SQL Server 2005 Analysis Services, Microsoft IT is now exploring the opportunity of providing data mining features to the MS Sales application. The following Analysis Services features will be beneficial in the MS Sales application:

• Scalability of dimensions

• Role playing dimensions

• Multiple hierarchies per dimension

• Removal of the 64,000-dimension limit

• Member-level dynamic security

• Many-to-many dimensions for dynamic currency conversion

• Measured expressions for dynamic currency conversion

• Backup and restore improvements to support having multiple query servers

• Multiple fact tables per cube to eliminate virtual cubes

• Perspectives to eliminate multiple cubes

• Data Source Views (DSVs) to help manage large data sources

• Unbalanced and ragged hierarchies

• Parameterized Multidimensional Expression (MDX) queries

• Key performance indicators

• Linked measure groups

SAP R/3 Implementation

The SAP R/3 ERP system that Microsoft uses contains more than 1.6 terabytes of data. The application services more than 2,700 named users and runs with about 600 concurrent users. The system processes more than 40,000 electronic data interchange (EDI) transactions daily and executes more than 78,000 batch jobs monthly. Response time is less than a second, and system availability is 99.9 percent.

SQL Server 2005 Feature Implementation

Database Mirroring

Microsoft IT currently mirrors the SQL Server 2005 database by using synchronous mirroring locally to provide high availability with a hot standby and by using asynchronous mirroring to a remote location for failure recovery. SQL Server 2005 mirroring capabilities help guarantee that all commit transactions are on the mirror. If the principal becomes unavailable, the mirror database can be brought online without the need to restore transaction logs.

Figure 4 shows a diagram of the SAP R/3 database-mirroring implementation.

Figure 4. Diagram of SAP R/3 database-mirroring implementation

Future plans for a database-mirroring solution include implementing the witness server to support automatic failover to the secondary database server.

Multiple Active Result Sets

SQL Server 2000 permitted only one pending request on a particular session. With the implementation of multiple active result sets in SQL Server 2005, more than one request is permitted per session. When the SAP R/3 system is running on SQL Server 2005, the system takes advantage of this feature. Therefore, the application can have more than one result set outstanding while other operations can execute with the same session.

Online Indexing

By moving to SQL Server 2005, Microsoft IT can now perform index operations without affecting the user's ability to access table data and to use other indexes. Therefore, Microsoft IT can provide higher availability of the application when indexes must be rebuilt.

Microsoft Accounting Reporting System Implementation

The MARS application is the basis for all internal management and external financial reporting at Microsoft. The MARS application provides a complete set of financial documents that are filed with the U.S. Securities and Exchange Commission (SEC). Additionally, the financial documents that the MARS application produces are included in the Annual Report that Microsoft provides to the SEC and inventors.

At Microsoft, more than 2,000 users use the MARS application to provide financial information for each business group. Externally, millions of investors around the world use the publication of Microsoft financial statements. Therefore, the accuracy and the timeliness of reporting the financial results are critical and are a key tenet of the Sarbanes-Oxley Act of 2002.

SQL Server 2005 Feature Implementation

Before SQL Server 2005, the MARS application used a custom application to load hundreds of Excel workbooks with data from the MARS SQL Server 2000 database. This process ran after the MARS database was refreshed from a number of internal data sources. For example, one internal source is the SAP R/3 ERP system at Microsoft. Refreshing the Excel workbooks took more than two hours. Each Excel workbook then used complex formulas or Microsoft PivotTable® dynamic views to present the results to the end user. The Excel workbooks were saved to geographically located file shares, and security was implemented at the file share level. Microsoft experienced the following problems in the Excel implementation:

• Excel workbooks were vary large and sometimes took several minutes to load.

• Security was implemented at the file share level. Therefore, the security did not provide for a more granular permission policy.

• During monthly and quarterly reporting cycles, the business groups required two refreshes per day. Therefore, the time that was required to refresh the Excel workbooks limited how many times a refresh could occur. This restriction caused problems during the planning cycle at Microsoft when four refreshes were required.

• Operational staff had to process a number of the Excel workbook refreshes manually.

• Development time that was required to add or change a report was complex and required specific knowledge of the MARS database structure.

To provide a more robust, secure, and scalable solution, Microsoft IT deployed Microsoft SQL Server 2005 Analysis Services and SQL Server 2005 Reporting Services. Figure 5 shows a diagram of new components added to the MARS application.

Figure 5. Diagram of new components added to the MARS application

Analysis Services and Reporting Services replaced the static Excel workbook reporting solution of the MARS application.

By deploying the new SQL Server 2005 solution, Microsoft IT obtained some of the following benefits of Analysis Services:

• Granular permissions. Granular permissions can now be defined in Analysis Services. This change has greatly simplified report creation and distribution. Because one report could not be created for all users, the prior implementation required the creation of multiple Excel workbooks to implement data requirements and security content. Now, the security that Analysis Services defines controls what data each user can access. Additionally, access to all reports is now logged, and usage can now be reported for auditing purposes.

• Calculated measures. Calculated measures that Analysis Services defines can be used on the various reports without the creation of a new formula for each report, as occurred in Excel. For example, Microsoft IT created calculated measures for the prior period, or for the same period last year, to greatly simplify the report-creation process.

• Availability and scalability. The time that is required to create the Online Analytical Processing (OLAP) cubes from the MARS database takes only 30 minutes compared to the two hours that were required to create the Excel workbooks. Therefore, a report of the results in Reporting Services was available immediately after Analysis Services was updated.

By deploying the new SQL Server 2005 solution, Microsoft IT obtained some of the following benefits of Reporting Services:

• Report development. By using Report Designer from Business Intelligence Development Studio, the report writing team has a much more feature-rich environment to create and maintain reports. Exposing the OLAP cube metadata within the report environment has reduced the skills required to create or maintain reports and has reduced the time that is required to deploy a report.

• Embedded and integrated reporting. The Reporting Services open API enabled Microsoft IT to integrate the new reports into its report catalog application to provide a seamless and integrated solution for the end user.

• Subscriptions. By using Reporting Services subscriptions, Microsoft IT can distribute static Excel workbooks to emulate some of characteristics of the prior implementation and maintain business requirements for some of the standard static reporting requirements.

• Reduced training. The training that was required to both create and use reports was drastically reduced. End users now just point and click to access, drill down, export, send, and print reports.

Centralized Reporting System Implementation

The Centralized Report System (CRS) application is a new, flexible business intelligence solution that uses SQL Server 2005 Integration Services to provide ETL operations from the Siebel Call Center database at Microsoft. The new solution replaced an existing solution that did not scale well for the now-larger reporting requirements. In the existing solution, the time to perform the ETL processes on the source data was between 16 and 20 hours. The existing solution was reaching its limits and needed to be replaced with a more robust and scalable solution.

The source data for the CRS application required many custom metric calculations before it was loaded in the CRS application, and Integration Services provided a robust toolset and environment that scaled well for this task.

Figure 6 shows the position of the CRS application in the Integration Services process flow.

Figure 6. Diagram of Integration Services process flow

The ETL operation consists of extracting data from nine different sources. Then, the operation performs calculations on more than 50 facts and performs measurable items on more than 20 dimensions and on hundreds of columns and fields. The benefits that Microsoft IT received by using Integration Services include the following:

• Development savings. Integration Services packages enabled developers to easily implement complex metrics calculations by using the .NET Framework 2.0.

• Processing savings. The time that developers required to perform ETL operations was reduced by 80 percent because of multithreaded in-memory processing.

• Date range reprocessing. Microsoft IT enhanced the ETL operations to accommodate date range reprocessing. This change enabled Microsoft IT to reprocess data within a date range only when source data was updated or corrected.

Best Practices and Benefits

During the course of planning, implementing, and performing post-installation tasks, Microsoft IT developed the following list of best practices that can help other organizations install or upgrade to SQL Server 2005:

• Planning. Careful planning has played a pivotal role in each successful upgrade of an instance of SQL Server. Microsoft IT carefully documented the existing system and usage of application features. By using Upgrade Advisor, Microsoft IT identified issues with each LOB application and resolved the issues before deployment. To help guarantee a successful upgrade, Microsoft IT identified specific resources that were required for each implementation task in the upgrade.

• Pre-implementation. Developing a pre-implementation checklist helped ensure consistency in each deployment and provided documented feedback for post-implementation tasks. By performing each task in the pre-implementation checklist, each deployment typically ran without incident. As a result, a number of tools such as Upgrade Advisor and SCC were enhanced to help provide guidance and a knowledge base for a successful implementation.

• Deployment. By performing test deployments in the planning stages, Microsoft IT made sure that the deployment window was large enough to perform the upgrade in the allotted time frame and to deploy the LOB applications in production on time.

• Post-implementation. Post-implementation tasks serve two requirements. First, the tasks bring the state of the environment to an online status. Second, the tasks provide feedback to make future deployments successful. By documenting the postdeployment issues and resolutions, Microsoft IT enhanced planning and the pre-implementation task lists even more.

• New feature implementations. By clearly identifying, at the beginning of the upgrade, any new features that enhanced the application's functionality or performance, Microsoft IT provided a much cleaner implementation on SQL Server 2005 than previously existed. For example, by carefully designing a table-partitioning strategy before implementation, Microsoft IT could increase application performance and therefore gain better user acceptance.

• Product familiarity. Before an organization upgrades to SQL Server 2005, it should develop a knowledge base to help better understand the new architecture, the new features, and any deprecated or discontinued commands. Clearly understanding SQL Server 2005 will result in a more robust and reliable implementation and an enhanced user experience.

• Built-in new features. By implementing SQL Server 2005, Microsoft IT can provide a more secure and feature-rich environment for various business groups. For example, SQL Server 2005 provided the following benefits:

  • By using the new off-by-default, enhanced security, and data encryption features in SQL Server 2005, Microsoft IT is in a much better position to help protect and deliver business-sensitive information to the intended recipients.

  • By using enhanced support for 64-bit mode in SQL Server 2005, Microsoft IT maintains a much more scalable environment in which server consolidation may occur, and in which performance has dramatically increased.

  • By using developer enhancements in SQL Server 2005, Microsoft IT has delivered more robust and scalable solutions to meet new requirements from the various business groups within Microsoft at a reduced total cost of ownership.

  • By using enhanced analysis and reporting abilities in SQL Server 2005, Microsoft IT can provide real-time insights into business values to help business groups make better-informed decisions.

Conclusion

With the help of the TAP, Microsoft IT installed SQL Server 2005 to take advantage of the built-in benefits gained by just performing the upgrade. SQL Server 2005 also provided enhancements to Microsoft LOB applications. Microsoft IT implemented a number of new features in SQL Server 2005 to help provide a more robust, reliable, secure, and available application platform.

Developer productivity enhancements in SQL Server have enabled Microsoft to develop and to maintain applications at a much lower cost than with SQL Server 2000. New applications and enhancements are delivered faster, and business requirements are now better matched to scalable enterprise solutions. These enterprise solutions give users more flexibility in performing complex analysis and discovering insights into the business relationship. Therefore, users can make more informed and timely decisions to take advantage of new opportunities as they occur within the various Microsoft business areas.

With the more robust installation process in SQL Server 2005, moving to SQL Server has never been easier. By coupling SQL Server 2005 with Upgrade Advisor and SCC, organizations can better prepare for, plan, and execute the delivery of a more robust, reliable, informative, and scalable database solution.

For More Information

Microsoft PSSDiag tool
For more information about the PSSDiag tool, visit the following Microsoft Web site:
http://support.microsoft.com/kb/830232

Microsoft Product Support (MPS) Reporting tool (MPSRPT_SQL.exe)
For more information about the MPS Reporting tool, visit the following Microsoft Web site:
http://support.microsoft.com/kb/883724

Microsoft Best Practices Analyzer tool
For more information about the Best Practices Analyzer tool, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

Microsoft SQL Server 2005 Upgrade Advisor tool
For more information about the Upgrade Advisor tool, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=6107C27A-662C-4319-AFE7-B52D1568C30A&displaylang=en

Microsoft SQL Server Health and History (SQLH2) tool
For more information about the SQLH2 tool, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=eedd10d6-75f7-4763-86de-d2347b8b5f89&DisplayLang=en

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:

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