Technical White Paper

Published: January 23, 2006

Deploying SQL Server 2005 as the database for SAP R/3 at Microsoft

Executive Summary

Microsoft, like many large organizations, relies on SAP AG enterprise resource planning (ERP) products to manage the day-to-day operations of its business. From the mid-1990s when Microsoft first deployed SAP products in its business environment, Microsoft has expanded its use of SAP software modules. Microsoft® SQL Server™ has always acted as the database engine for the SAP R/3 implementation at Microsoft.

SAP used together with a SQL Server back-end database has become the most popular SAP implementation; new SQL Server/SAP implementations outnumber all new implementations of SAP combined with other databases. Because of the Microsoft commitment to SQL Server customers who run SAP in their organizations, Microsoft Information Technology (Microsoft IT) implemented the Microsoft SQL Server 2005 beta edition in its own production SAP R/3 environment. The purpose of this implementation was to help make sure that SQL Server 2005 exceeds customer expectations with regard to performance, reliability, and return on investment (ROI) in their own SAP environments. Microsoft now relies on SQL Server 2005 to provide high availability, reliability, scalability, and high performance for its global SAP R/3 environment.

The SAP R/3 deployment at Microsoft—in the top 10 percent of the largest SAP R/3 deployments worldwide—handles virtually all of the business transactions at Microsoft, from the retail sales of Microsoft Xbox® video game systems through the movement of more than U.S. $39 billion in treasury funds each month. This SAP R/3 implementation, expected to double in size by the end of 2006 from 2.5 terabytes of data to more than 5 terabytes of data, relies on the performance and reliability of SQL Server 2005 as its back-end database. This database includes tables that are 50 to 90 gigabytes (GB) in size.

The Enterprise Application Services (EAS) group within Microsoft IT is responsible for the implementation and ongoing maintenance of the SAP environment at Microsoft. These responsibilities include the deployment of SAP products and technologies, the maintenance of the SAP application servers and SQL Server 2005 database servers, and the configuration and testing of the disaster recovery environment for the SAP R/3 implementation at Microsoft. This document shares the experiences of the EAS group in the deployment of SQL Server 2005 in its SAP R/3 environment. Because of the significant amount of experience that the EAS group has gained throughout the deployment of SQL Server 2005 with SAP R/3, and because the EAS group represents sign-off criteria for the release of new SQL Server products, this information should provide meaningful guidance to organizations that want to deploy SQL Server 2005 as the back-end database in their SAP environments.

This document is intended for enterprise business decision makers, technical decision makers, IT architects, database developers, 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 unique circumstances. Therefore, each organization should adapt this information to meet its specific requirements.

Note: For security reasons, the sample names of forests, domains, 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

When considering Microsoft products and technologies, corporate decision makers often request information about experiences with using these products and technologies within Microsoft. Microsoft IT not only provides IT services for Microsoft but also acts as the first customer for each new release of server and business productivity software. Because Microsoft IT requirements are among the most technically challenging in the world, the methods that Microsoft IT uses to deploy these technologies and the experience that Microsoft IT gains from these deployments often provide meaningful deployment and operational guidelines for other organizations that want to deploy Microsoft products. Additionally, because Microsoft IT works with these Microsoft products from the prerelease editions to the Release to Manufacturing (RTM) editions, Microsoft IT provides Microsoft with valuable feedback about features and functionalities. This feedback not only improves the software products throughout their development, but also helps Microsoft customers and partners successfully deploy these products and technologies.

As the largest software company in the world, with yearly revenues exceeding $39 billion, Microsoft considers the availability and the reliability of its SAP implementation as a critical part of the day-to-day operations of the company.

Overview of SAP at Microsoft

As Microsoft developed into a global organization in the early 1990's, Microsoft IT recognized the need to implement standardized business processes around the world. Inconsistencies had begun to appear where, for example, report generation was performed differently in Europe than it was in the United States. Additionally, business processes started to become less efficient than Microsoft required. For example, accountants at Microsoft sometimes took up to six weeks to close the corporate books. To consolidate line-of-business (LOB) applications and to help streamline business processes within Microsoft, in 1996, Microsoft IT implemented SAP R/3 running with Microsoft SQL Server version 6.5 as its back-end database.

Microsoft has steadily expanded its use of SAP products since that time. Microsoft has added more SAP modules, and the number of internal business operations that use SAP has steadily increased. Throughout this process, each subsequent version of SQL Server has provided a more robust and reliable back-end database to support the growing business at Microsoft. The current SAP implementation at Microsoft consists of the following SAP modules:

• SAP R/3 4.7

• SAP Advanced Planner and Optimization (APO) 3.0a

• SAP Business Warehouse (BW) 3.5

• SAP Global Trade Services (GTS) 2.0

Currently, at Microsoft, 2,400 users access SAP by using the SAP graphical user interface (GUI) tool. Additionally, 60,000 users access SAP by using Web-based tools.

The assets that Microsoft currently manages by using SAP include the following:

• The finance department processes $39 billion each year.

• The treasury department moves $39 billion through SAP each month.

• Microsoft uses SAP to manage real estate holdings for all offices.

• Microsoft uses SAP to manage employee benefits for 61,000 Microsoft employees.

• Microsoft uses SAP R/3 for supply chain management to process millions of hardware devices.

Microsoft relies on SQL Server as the back-end database for all its business data. Microsoft has worked closely with SAP AG for more than 10 years to help make sure that SQL Server provides customers with a highly scalable enterprise-level back-end database solution to use in their SAP implementations.

As with all beta software (including Microsoft beta software), Microsoft IT first carefully examined and tested the SQL Server 2005 beta product with SAP to make sure that it was robust and stable enough to deploy in a large Microsoft production environment. Additionally, Microsoft IT committed additional resources in terms of support and test staff to continually monitor all operations and applications that the beta product might affect throughout the beta program. Because of these increased support requirements, and because of Microsoft IT's added commitment to support SQL Server 2005 with SAP products, the EAS group could not immediately deploy the SQL Server 2005 beta edition throughout the whole SAP environment. Therefore, EAS chose the SAP R/3 implementation, the most critical and largest SAP module at Microsoft, as the most suitable application with which to use the SQL Server 2005 beta edition.

To further underline the Microsoft commitment to its customers who run SQL Server in their SAP environments, Microsoft IT runs beta editions of both SQL Server 2005 and SAP software in its production environment. Microsoft IT not only strives to make sure that SAP runs reliably with SQL Server 2005 as its back-end database, but also requests the inclusion of specific features and functionalities in SQL Server 2005 that SAP developers can take advantage of and use effectively in the SAP code base. In fact, Microsoft declared its SAP implementation as one of the sign-off criteria for the release of SQL Server 2005. Therefore, if any bugs or blocking issues existed between SAP and SQL Server 2005, SQL Server 2005 would not have been released.

The SAP R/3 implementation at Microsoft consists of a single global production R/3 environment, as shown in Figure 1.

Figure 1. SAP R/3 environment at Microsoft

In this environment, all transactions are processed in SAP R/3. Full-time users use the SAP GUI tool to access SAP R/3, and casual SAP users use Web-based tools. Microsoft partners access SAP R/3 by using XML, electronic data interchange (EDI), or the Internet. SAP R/3 publishes data to a central store, and then users access this data for reporting purposes by using business-unit-owned datamarts.

This SAP R/3 implementation is in production in 67 countries. It consists of 2,400 GUI users out of a total of 60,000 SAP users. Of these users, between 200 and 1,200 users access SAP concurrently. The SAP R/3 environment at Microsoft experiences the following usage:

• Approximately 300,000 SAP dialog steps are performed every day, with up to 560,000 dialog steps performed during peak periods of activity (such as during the sale of Xbox systems during the holiday season).

• Dialog-step activity corresponds to approximately 14 million transactions per month. More than 120,000 batch jobs are performed each month.

• Users experience an average response time of a half second, with peak response times not exceeding two seconds.

It costs Microsoft more than $4 million in lost sales, trade, treasury trade, and deliveries for each hour of downtime in its SAP R/3 environment. This cost corresponds to approximately $67,000 for each minute that SAP R/3 is unavailable. (These costs do not include the penalties that Microsoft might incur if data is lost or compromised.) Because of the enormous financial cost of downtime in its SAP R/3 environment, Microsoft IT considers the stability, availability, and scalability of SQL Server 2005 as the key to its SAP R/3 implementation.

Microsoft IT has experienced a 99.98 percent availability in using beta editions of SQL Server 2005 in the production SAP R/3 environment. EAS determined that SQL Server 2005 contains many new features and functionalities that greatly enhance and improve the SAP R/3 implementation at Microsoft.

High Availability and Disaster Recovery

SQL Server 2005 includes many performance enhancements and management and reporting features. However, because of the large volume of information that passes through SAP R/3 each minute, EAS considers high availability and disaster recovery to be the most critical aspects of its SQL Server 2005–based database tier.

The EAS group's production SAP R/3 system has three database servers. At the Microsoft Redmond (Washington) data center, two of these database servers are configured to synchronize data by using the SQL Server 2005 database mirroring feature. Additionally, EAS maintains a SQL Server 2005 test center in the San Francisco Bay (California) area that acts as a complete disaster recovery center for the SAP R/3 implementation at Microsoft. This SQL Server 2005 disaster recovery server uses SQL Server log shipping to maintain an up-to-date copy of all SAP R/3 data.

Figure 2 illustrates the SAP R/3 high availability and disaster recovery environment at Microsoft.

Figure 2. SAP R/3 high availability and disaster recovery environment

"Log shipping improvements in SQL Server 2005 gave us the ability to dump transaction logs while our database backup is running, greatly reducing our exposure to data loss for our log shipping database server."

Steve Bury

Log Shipping

The Microsoft disaster recovery center is designed to enable Microsoft to return to an operational state in the shortest amount of time as possible if a disaster occurs in its main Redmond operations center. The EAS group uses its testing facilities in the San Francisco Bay area to act not only as a test environment for SQL Server 2005 but also as the production disaster recovery center if the Redmond environment becomes unavailable.

The advantage of using the live test facilities as the disaster recovery center is that the current status of all servers and the network infrastructure is in a known state at all times. All computers and equipment are kept up to date according to current Microsoft corporate security standards, and less time must be spent getting equipment ready if that equipment is needed in response to a disaster.

As with previous versions of SQL Server, the EAS group uses the log shipping functionality in SQL Server 2005 to keep its SQL Server 2005 disaster recovery database up to date. Figure 3 illustrates the EAS SQL Server 2005 log shipping implementation.

Figure 3. Log shipping implementation

Previously, with Microsoft SQL Server 2000, EAS configured log shipping to use the minimum log shipping interval of two minutes. This practice meant that if a disaster occurred, Microsoft IT could lose two minutes of transaction log data.

Note: Generally, organizations set the log shipping interval to five minutes.

The loss of two minutes of data might seem like an acceptable amount when taken in the context of a disaster. However, EAS wanted to minimize the possibility of data loss to the greatest degree possible, for the following reasons:

• Each minute that SAP R/3 is unavailable costs Microsoft about $67,000.

• Microsoft might incur potential penalty assessments for data loss because of government regulatory requirements, such as the Sarbanes-Oxley Act of 2002.

• Loss of transaction logs might cause the loss of database referential integrity.

With the implementation of SQL Server 2005, EAS was able to set the log shipping interval to one minute, effectively reducing the potential loss of data by half.

Additionally, in SQL Server 2000, an administrator must disable transaction log dump operations while a database backup operation runs. For EAS, this meant that because the back-end database backup runs for three to four hours each day, the EAS log shipping environment could be out of synchronization by as much as four hours.

By using SQL Server 2005, EAS is able to dump transaction log files while the database backup runs, keeping the log shipping destination synchronized with the primary database server. The implementation of SQL Server 2005 in the SAP R/3 database tier has greatly reduced the possibility of a loss of up to four hours of transaction log data that might occur if a disaster occurs while the database is being backed up.

Database Mirroring

Microsoft SQL Server 2005 supports failover clustering to provide a fault-tolerant server solution for an organization's back-end database servers. Failover clustering connects one or more cluster nodes to a common data store. However, failover clustering does not provide an organization with a redundant data store. The SQL Server 2005 database mirroring feature provides an organization with a redundant database server to provide redundancy that is similar to server clustering. SQL Server 2005 database mirroring also provides an organization with a redundant data store, providing data redundancy similar to that of a log shipping environment.

Additionally, database mirroring offers an organization the ability to keep the redundant data store fully synchronized with the primary data store. EAS determined that SQL Server 2005 database mirroring would provide the maximum possible level of availability for the 2.5 terabytes of SAP R/3 data in the SQL Server 2005 back-end database.

High availability of the SAP R/3 SQL Server 2005 back-end database is of key importance to EAS. Although the financial cost for each minute of SAP R/3 downtime is significant, the issue that EAS considers as the most important reason for using database mirroring over other high-availability solutions (such as server clustering or log shipping) is the loss of or corruption of transaction log data. In fact, of all the new features and functionalities that SQL Server 2005 includes, EAS determined database mirroring to be one of the most important reasons to use SQL Server 2005 as the back-end database for its SAP R/3 implementation.

EAS implemented SQL Server 2005 database mirroring to circumvent a situation similar to the following:

1. A customer completes a transaction to purchase an item from Microsoft.

2. SAP R/3 processes the order and returns a delivery confirmation to the customer.

3. A catastrophic hardware failure occurs on the production database server.

4. The customer's transaction is lost because it was not transferred to the log shipping destination within the one-minute backup schedule for the transaction log.

In this situation, because the transaction log that contains the customer's purchase is lost, the customer might not receive the purchased item. Microsoft considers the loss of customer satisfaction that might occur in such a situation to far outweigh the financial cost to the company if this type of data loss occurs. Additionally, in this type of situation, EAS must stop SAP R/3 to determine which transactions have been lost. IT staff must manually review the transaction history in SAP R/3 to manually re-enter lost or corrupted transactions.

Synchronous vs. Asynchronous Database Mirroring

SQL Server 2005 database mirroring is a functionality whereby information that is submitted to the transaction log on one particular server (the primary database server) is also submitted to the transaction log of a secondary database server. An administrator can configure the primary server to require acknowledgement from the secondary server that the information has been committed to the secondary server's transaction log before the primary server acknowledges the commit operation to the sending application (known as synchronous mirroring). Or, the administrator can configure the primary server to not require acknowledgement from the secondary server before the primary server returns an acknowledgement to the submitting application (known as asynchronous mirroring).

The advantage of synchronous mirroring is that the data must be committed to the transaction log file on both the primary server and the secondary server before the submitting application receives acknowledgement of a committed transaction. This type of database mirroring ensures that both the primary and secondary servers have the same information at the same time.

The advantage of asynchronous mirroring is that network latency between the primary and secondary servers in the database mirror pair does not cause a delay in acknowledging transactions to the submitting application. The primary server sends the commit acknowledgement to the sending application without waiting for a reply from the secondary server. Asynchronous database mirroring is best suited to avoid delays in committing transactions in an environment where an organization does not have too great a distance between the primary and secondary servers.

Note: For asynchronous mirroring, the primary server still requires acknowledgement from the secondary server for each transaction that is sent to the secondary server. However, the operation of the submitting application is not delayed because of latency between the primary and secondary servers.

SQL Server 2005 database mirroring is more than a configurable real-time log shipping function. In database mirroring, the transaction itself is sent to the secondary server to be committed to that secondary server's transaction log. The primary server's transaction log files are not log shipped to the secondary server. Additionally, as soon as the secondary server commits the received transaction to its transaction log, it sends an acknowledgement to the primary server. Therefore, the primary server receives acknowledgement of the committed transaction even if the secondary server experiences a delay in committing its own transaction log file to its database.

Note: By default, database mirroring is not enabled in SQL Server 2005. As of the SQL Server 2005 RTM release, database mirroring is supported on a per-project basis in a Technology Adoption Program (TAP)—a program to provide assistance to organizations that use Microsoft beta products. However, customers are free to test and evaluate database mirroring on test and development systems. To activate database mirroring for evaluation and test purposes, an administrator must set trace flag 1400 on all related SQL Server 2005 instances.

Implementation of Database Mirroring

To provide high availability, and to test all aspects of SQL Server 2005 database mirroring with SAP R/3, the EAS group implemented SQL Server 2005 database mirroring feature in two phases. Initially, the EAS group deployed SQL Server 2005 database mirroring in asynchronous mode. Figure 4 illustrates the EAS SQL Server 2005 database mirroring implementation.

Figure 4. EAS implementation of SQL Server 2005 asynchronous database mirroring

The SQL Server 2005 database mirroring environment at Microsoft consists of two servers. These servers are located in the same data center and on the same Gigabit Ethernet subnet. Database mirroring requires a high-speed local network to facilitate the real-time transmission of database transactions between members of the mirror pair. On November 4, 2005, the EAS group switched the SQL Server 2005 back-end databases to use synchronous mirroring.

Although high availability, and not performance, was the goal of the SQL Server 2005 database mirroring implementation, the EAS group monitored performance to determine whether database mirroring would affect the SAP R/3 application. In SQL Server 2005 RC2 and later editions, EAS did not experience any performance impact from using database mirroring. Additionally, the built-in SQL Server 2005 automatic client redirection feature together with the SAP reconnection logic caused SAP R/3 to automatically recognize the secondary server in the SQL Server 2005 mirror pair.

Through performance monitoring, the EAS group saw that its SAP R/3 implementation generated peak traffic between the SQL Server 2005 servers in the mirror pair of approximately 5 to 6 megabytes (MB) per second, with the average amount of traffic running at approximately 1 MB per second. Additionally, EAS noted that the secondary server in the mirror pair had a consistent CPU usage of approximately 65 percent of the primary server's CPU usage.

The implementation of SQL Server 2005 database mirroring gave EAS the server and database redundancy it required for the SAP R/3 environment. Additionally, EAS was able to implement this functionality without experiencing any performance impact to the critical SAP R/3 application.

The default time-out value for automatic failover in SQL Server 2005 database mirroring is 10 seconds. EAS increased this value to 120 seconds to facilitate the generation of manual or automatic memory dump files to use for the monitoring of the beta editions of SQL Server 2005. EAS required 120 seconds for the database server to dump the contents of 3 GB of random access memory (RAM) to a file. When EAS later upgraded the database servers to 64-bit computers, the team noticed that the whole contents of memory are dumped to a file. Therefore, EAS may re-evaluate the failover time-out settings in the near future. EAS wants to set a failover value that strikes a balance between the generation of memory dump files and the availability of the SAP R/3 application. Generally, an administrator does not have to increase the time-out value for automatic failover in SQL Server 2005 database mirroring.

Commodity Hardware Running SQL Server 2005

Like most corporations, Microsoft strives to be fiscally responsible to its stakeholders while still supplying its IT departments with sufficiently powerful computing resources to enable the company to function with the greatest possible efficiency. With the general increase in computing power for each dollar spent, and specifically, with the use of 64-bit computers becoming more common in the mainstream business environment, this task has become much easier.

Formerly, the SQL Server back-end database for the SAP R/3 environment at Microsoft ran on 32-bit computers. These computers had the following specifications:

• Eight 2.0-gigahertz (GHz), 32-bit hyperthreaded processors (which appear as 16 processors to the operating system)

• 16 GB of RAM

• Microsoft Windows Server™ 2003 operating system, Enterprise Edition

• Connection to a network attached storage device

Note: The EAS group uses the same hardware for both the servers in the SQL Server 2005 database mirroring pair and for the disaster recover database server in the SQL Server 2005 log shipping destination. Therefore, EAS was using three servers to meet the Microsoft IT availability requirements for the SAP R/3 application.

Generally, EAS did not experience any performance-related problems with this back-end database configuration. The 32-bit servers running Windows Server 2003 provided a robust and reliable platform on which to run SQL Server 2005. Because of an expected increase in sales that generally occurs during the holiday season, the EAS group determined that it must scale the SAP R/3 database tier vertically to meet the demands of this expected increase in SAP R/3 traffic. However, the EAS group determined that because of the 32-bit memory addressing limitations, adding additional RAM to the 32-bit servers would not give EAS an appreciable ROI. EAS would have had to remove the /3GB switch from the Boot.ini files of the 32-bit computers. The reduction of memory address space that occurs after removal of the /3GB switch causes SQL Server to swap information out of its cache at an earlier time. Therefore, the EAS group decided that the best way to improve its SAP R/3 environment was by upgrading the SAP R/3 database tier to SQL Server 2005 running on 64-bit computers.

Additionally, the EAS group is currently working on a Unicode conversion for its SAP implementation. Because the memory requirements of the Unicode conversion require a 64-bit computing environment, EAS eventually has to upgrade its whole SAP R/3 environment to 64-bit computers.

To scale the SAP R/3 database tier, organizations purchase servers that have more processors, faster processors, and more RAM. However, in the current SAP R/3 environment, EAS calculated that, at the time of purchase, the cost of replacing the former 32-bit servers with similarly configured IA-64–based servers would exceed $100,000 for each computer.

The EAS group determined that it could increase the performance, scalability, and stability of the SQL Server 2005 back-end database by using x64-based servers running the x64 version of Windows Server 2003, Enterprise Edition. In a 64-bit computing environment, EAS would no longer be limited by the address space limitations of the 32-bit memory architecture. Instead, EAS could allocate all available memory to SQL Server 2005. Additionally, by running SQL Server 2005 on x64-based servers, EAS calculated that it could reduce the total cost of ownership (TCO) in the SAP R/3 database tier.

To this end, in mid-2005, the EAS group replaced the former 32-bit, eight-processor database servers with x64-based servers that have the following specifications:

• Four 2.2-GHz dual core processors (which appear as eight processors to the operating system)32 GB of RAM

• Windows Server 2003 operating system, Enterprise Edition

• Connection to the network attached storage device

Each of these x64-based servers, which are generally considered to be commodity servers (low-cost servers that are designed for general use), cost the EAS group approximately one-half that of the former 32-bit servers. Although these commodity servers have half the number of processors than do the former 32-bit servers, EAS calculated that the x64-bit servers would provide at least the same performance as the former servers while providing the scalability in the SQL Server 2005 database tier that EAS required.

Initial performance monitoring results showed performance improvements in the SQL Server 2005 database tier. However, these performance gains are not all attributable to SQL Server 2005 running on x64-based computers. Some of these gains occurred because of increased efficiency in the Gigabit Ethernet network due to the architecture of the new x64-based computers.

"SQL Server 2005 enabled us to take full advantage of 64-bit technologies, further ensuring our readiness for future business requirements."

Kevin Lin

Transaction Processing

The EAS group wanted to make sure that SQL Server 2005 provided a reliable and scalable back-end database when running on x64-based servers in its SAP R/3 environment. Therefore, in addition to using SQL Server 2005 with the 32-bit version of SAP R/3 in its production environment, EAS ran beta editions of the x64 SAP R/3 executable files in its production environment. The EAS group beta tested the x64 version of SAP R/3 on four of its six production SAP R/3 application servers. After SAP AG released the x64 version of SAP R/3, the EAS group upgraded the x64 beta edition of SAP R/3 to the x64 released version of SAP R/3. EAS currently runs the x64 version of SAP R/3 on five of its six SAP R/3 application servers.

Because the EAS group did not perform an exhaustive comparison between the former database tier and SQL Server 2005 running on x64-based computers in its SAP R/3 environment, the performance results that EAS gathered only indicate a trend. The results do not provide actual performance characteristics that an organization can expect when running SQL Server 2005 on an x64-based computer. However, the results do indicate a significant performance improvement over that of a 32-bit platform.

Table 1 illustrates the EAS group's SAP R/3 performance statistics over one a one-week period running SQL Server 2005 on the former 32-bit platform.

Table 1. SQL Server 2005 in a 32-bit environment

Table 2 illustrates the EAS group's SAP R/3 performance statistics over one a one-week period running SQL Server 2005 on a commodity x64-based server.

Table 2. SQL Server 2005 in a 64-bit environment

EAS gathered these statistics over a two-week period, so they do not reflect long-term averages. However, Table 3 illustrates the trend toward improved performance that EAS noted when using SQL Server 2005 on the x64-based server.

Table 3. Improved SQL Server 2005 performance in a 64-bit environment

CPU Usage

The EAS group considered scalability and not performance improvements as the business justification to use x64-based computers with the SQL Server 2005 back-end database in the SAP R/3 environment. EAS determined that the implementation of a 64-bit platform in the database tier would give SQL Server 2005 access to the memory it requires to run optimally. Additionally, EAS expected the 64-bit processors to handle the increased SAP R/3 load without the large CPU spikes that EAS had experienced when using a 32-bit hardware platform in the database tier.

Figure 5 illustrates the CPU usage taken over a seven-month period to show the difference in CPU usage between SQL Server 2005 running on the 32-bit platform and SQL Server 2005 running on the x64-based commodity hardware. This figure illustrates a significantly reduced processor spike level when SQL Server 2005 runs on an x64-based computer that has half the number of processors than the former 32-bit computer.

Figure 5. SQL Server 2005 CPU usage

After running SQL Server 2005 on commodity x64-based computers in the high-volume SAP R/3 implementation at Microsoft, EAS experienced the following approximate performance improvements:

• Update response time is 25 to 30 percent better throughout the SAP R/3 environment.

• Average transactions are faster throughout the SAP R/3 environment.

• Average CPU usage is 7 percent less than that of the former 32-bit platform.

• Maximum CPU spikes are 20 percent less than the spikes in the former 32-bit platform.

Because SQL Server 2005 supports the x64-based architecture, the EAS group was able to replace the former specialized 32-bit computers with commodity x64-based computers. This replacement enabled the EAS group to save a considerable amount of money in its SAP R/3 database tier without having to sacrifice performance or scalability.

Index Management

SQL Server 2005 includes many new management features to help database administrators monitor and optimize performance in their enterprise's database tier. With new functionality such as online indexing and index usage statistics, SQL Server 2005 made the EAS group's job of managing efficiency, optimizing storage space, and monitoring performance for the SAP R/3 data in the SQL Server 2005 back-end database much easier than with the former system.

SQL Server 2005 Online Indexing

The EAS group considers SQL Server 2005 online indexing to be an important feature toward meeting Microsoft IT's availability requirements for its SAP R/3 application.

At Microsoft, the SQL Server 2005 back-end database for SAP R/3 has tables of up to 90 GB in size. For EAS, an index creation operation on a table of this size can take up to 1.5 hours. Formerly, when running SQL Server 2000 as the back-end database for SAP R/3, EAS had to carefully plan and schedule index creation operations to make sure that the SAP R/3 application remained available to end users. This issue threatened to become even more of a problem because the SAP R/3 footprint is expected to increase from 2.5 terabytes to 3 terabytes by August 2006.

Note: The EAS group plans to run the SAP R/3 Unicode conversion upgrade in August 2006. Although the Unicode conversion will double the size of the text fields in the database, this conversion is also expected to compact the database, removing the extra free space in the database pages. Therefore, EAS expects the database to remain at approximately the same size after the Unicode conversion is finished. However, because much less free space will exist in the database pages, EAS expects more page splits to occur when data is later inserted into the database.

With the larger database size, indexing operations take longer, and creating a new index requires even more downtime. In SQL Server 2000, when a database administrator creates an index on a database table, SQL Server locks the table from being modified during the index creation process. Therefore, operations that use INSERT statements to insert data into the table cannot run until the index has been created.

Figure 6 illustrates the insert blocking issue that could have occurred in EAS's former back-end database implementation.

Figure 6. Insert blocking during index operations

In the preceding figure, Change Request 2, which runs an INSERT statement, is blocked from completing until Change Request 1, which creates an index, is completed.

Because of the critical nature of the SAP R/3 application at Microsoft, and because the SAP R/3 application is accessed from many locations around the world, EAS requires substantial business justification, scheduling, and notification for SAP R/3 application downtime. The EAS group, like IT departments in other large organizations, is cognizant of the need to maximize the availability of line-of-business applications such as SAP R/3. By using the online indexing feature in SQL Server 2005, the EAS group can configure indexing to execute without blocking any kinds of INSERT operations into the table being indexed. Figure 7 illustrates the behavior that occurs when EAS uses online indexing in SQL Server 2005.

Figure 7. SQL Server 2005 online indexing

In the preceding figure, the INSERT statement (shown in Change Request 2) is not blocked from running because SQL Server 2005 runs the online index creation statement (Change Request 1). SQL Server 2005 therefore maintains the underlying tables and their associated indexes available for queries and for data modification during the index operation.

When a database administrator performs online indexing, SQL Server 2005 creates a new index while leaving the original index in place. When the new index is created, SQL Server 2005 swaps the newly created index with the original index.

SQL Server 2005 online index creation takes longer than the creation of an offline index. However, the EAS group does not consider this to be a problem because the online index creation process does not affect the availability of the SAP R/3 application. SAP R/3 users can still access and modify SAP R/3 records while the index creation operation progresses.

Additionally, EAS has only a two-hour maintenance window each weekend to perform maintenance operations for the SAP R/3 application to create new indexes. (During weekends that occur during month-end financial operations, however, there is no maintenance window.) By using SQL Server 2005 online indexing, the EAS group did not have to wait until its maintenance window to run the index creation statements.

The SQL Server 2005 online indexing feature helps the EAS group minimize the impact of index creation operations for SAP R/3 end users. Between the time that EAS implemented SQL Server 2005 as the back-end database for SAP R/3 in August 2005 and November 2005, the EAS group used online indexing 10 times to optimize the functionality of its SAP R/3 application. Because EAS formerly performed index creation during the SAP R/3 maintenance window, no financial gain can be associated with the use of SQL Server 2005 online indexing feature to create new indexes in the SAP R/3 application space. However, this new SQL Server 2005 feature gave the EAS group the flexibility it required to create indexes immediately instead of having to wait until scheduled SAP R/3 downtime. Also, because the SAP R/3 database grows at approximately 90 GB each month, with new indexes taking longer to create, SQL Server 2005 online indexing helps free the tight scheduling for operations that must be performed in the two-hour maintenance window that is available for the SAP R/3 application.

In addition, the EAS group does not re-index or defragment the SAP R/3 SQL Server 2005 back-end database. EAS considers these operations unnecessary because of how efficiently SQL Server 2005 manages its data space. When data is deleted from a SQL Server 2005 database, SQL Server 2005 shrinks to reclaim the available space. Because of this behavior, EAS saves considerable time related to index maintenance tasks.

Implementation Considerations with SAP R/3

SAP R/3 is an application-layer program. When a database administrator creates or activates an index in SAP R/3, SAP R/3 sends an index creation command to the database layer of the system. This command creates the index in the SQL Server database. The version of SAP R/3 that EAS runs does not currently support the creation of indexes through the SQL Server 2005 online index creation option.

To work around this issue, EAS uses the following steps to create a new index in its SAP R/3 environment:

1. An EAS database administrator creates the new index on the SQL Server 2005 back-end database by using the ONLINE option.

2. The EAS database administrator issues the SAP transport request to create the new index in the SAP data dictionary.

For SAP to recognize new indexes, those indexes must be added to the SAP data dictionary. This action keeps the data dictionary synchronized with SQL Server 2005. When an administrator issues the SAP transport request to create the new index after manually creating the index in the SQL Server 2005 back end, SAP recognizes that the index has already been created. SAP then automatically updates the SAP data dictionary with the new index information. In this scenario, SAP does not rebuild or re-create the index.

Dynamic Management Views

SQL Server 2005 includes many new management features to enable an organization to track and maintain its SQL Server 2005 back-end databases. EAS makes extensive use of two of these features to help improve the performance of the SAP R/3 application and to help reduce the storage costs associated with SAP R/3.

Query Performance Statistics

Formerly, to view the performance of SQL Server queries that run in the SAP R/3 environment at Microsoft, the EAS group had to use the SAP query monitoring transaction (SAP transaction ST04). EAS database administrators had to log on to SAP R/3, and then run query monitoring transactions to determine the performance characteristics of SQL Server queries in the SAP R/3 system. Because of the way that SAP manages information internally, EAS database developers sometimes experienced difficulty writing custom programs to extract this query information from the SAP system.

SQL Server 2005 makes this job easier. Database developers can monitor query performance from within SQL Server 2005. Although EAS does not put a financial figure on the time savings for this feature, EAS more quickly notices performance issues that might occur with queries running in SQL Server 2005. EAS now uses the sys.dm_exec_query_stats dynamic management view to monitor query performance in the SQL Server 2005 back-end database.

Index Usage Statistics

SQL Server 2005 includes new functionality to monitor index usage statistics. The EAS group uses the dynamic management view for index usage statistics to make sure that indexes for the SQL Server 2005 SAP R/3 back-end database are used efficiently. Efficient use of indexes enables an organization to maximize application performance while minimizing storage costs.

In the SQL Server 2005 back-end database for the SAP R/3 application, some indexes are larger than the table for which they are created. For example, a 40-GB table might have a 60-GB index. By using SQL Server 2005 index usage statistics, EAS can easily view the usage history for indexes in the SAP R/3 database tier.

SQL Server 2005 records the number of times that indexes are used in the database system. SQL Server holds this information in a nonpersistent table. Database administrators can query this statistics table to determine whether indexes are being efficiently used. This feature is automatically enabled but is reset when the server is restarted. Generally, EAS restarts the server running SQL Server 2005 only after installing security updates to comply with Microsoft corporate security requirements.

EAS uses the sys.dm_db_index_usage_stats dynamic management view table to return index usage statistics in the SAP R/3 system. EAS uses the following code to query this table.

SELECT sys.indexes.name,user_seeks, user_scans, user_lookups,
    user_updates, last_user_seek, last_user_scan,
    last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats 
INNER JOIN sys.indexes on   
sys.dm_db_index_usage_stats.object_id=sys.indexes.object_id
AND   
sys.dm_db_index_usage_stats.index_id=sys.indexes.index_id
WHERE sys.dm_db_index_usage_stats.database_id=      
(SELECT dbid FROM master..sys. databases WHERE name = '<SID>')
ORDER BY sys.indexes.name

Table 4 illustrates example results from the preceding query.

Table 4. Index Usage Results

EAS also uses this dynamic management view to locate custom indexes that experience no use. By running a query that is similar to the following, EAS can quickly locate an index that has little or no use in the SAP R/3 system.

SELECT object_name(object_id), i.name 
FROM sys.indexes i 
WHERE  i.index_id NOT IN (SELECT s.index_id
   FROM sys.dm_db_index_usage_stats s
   WHERE s.object_id=i.object_id AND
         i.index_id=s.index_id )
ORDER BY object_name(object_id) asc

Table 5. Unused Index

After reviewing the index, if EAS determines that the index is not required, EAS drops it. This action saves many gigabytes of high-speed storage space in the SQL Server 2005 database and improves performance in the SAP R/3 system.

Note: The EAS group drops only unused custom indexes. EAS never drops SAP standard indexes, even if those indexes are unused.

Invoice Document Archiving

At Microsoft, SAP users require the ability to view previously printed invoices for up to 12 years prior to the current date. Formerly, to view invoices after they had been printed, SAP users had to locate a physical copy of the printed document. No functionality to view a copy of the printed invoices existed in the EAS group's SAP R/3 implementation. Therefore, in some cases, SAP users had to spend an inordinate amount of time locating copies of previously printed invoices.

To resolve this problem, the EAS group developed a system to store a copy of a printed invoice in the SAP R/3 back-end database. The concept that EAS developed was to store the individual lines that make up the invoice in a SQL Server 2005 table (known as the Detail table) and to associate these lines with a record in another table (known as the Header table). The header record represents the information about the invoice so that the invoice document can then be reconstructed at a later time. By developing this system and by archiving this invoice document information in the SQL Server 2005 SAP R/3 back-end database, EAS gave SAP users the ability to quickly re-create copies of invoices after those invoices are printed.

As Microsoft increases its use of SAP R/3 to help manage the critical day-to-day operations of the company, and as Microsoft continues to expand its business and increase its sales and services throughout the world, EAS experiences greatly increased growth in the SAP R/3 SQL Server 2005 back-end database. For example, the Detail table of the invoice document archiving database increases by approximately 11 GB every month.

To help forecast the growth of the invoice document archiving system over a 12-year period, the EAS group extracted an eight-week sampling of data from the SQL Server 2005 back-end database. EAS extrapolated this data to calculate the projected size of the invoice document data over a 12-year period, as shown in Table 6.

Table 6. SAP Invoice Document Archiving Growth

* Estimated increase based on eight weeks of actual growth

Based on the current growth of the invoice document archiving system, EAS calculated that after 12 years, the Detail table will exceed 15 billion rows and will consume approximately 1.7 terabytes of space. These calculations do not take into account additional growth that this database might experience because of increased sales and the corresponding invoice volumes that EAS expects to see at Microsoft. Therefore, these numbers more accurately reflect minimum growth values that EAS expects in the invoice document archiving database.

Formerly, the EAS group experienced growth of approximately 400 GB each year in the SAP R/3 database tier. At that time, the EAS group had a goal of archiving about 50 GB of data each year. However, because of added functionality in SAP R/3, the EAS group currently experiences a growth of about 700 GB each year in the SAP R/3 database tier. With even more functionality to be added in the immediate future, EAS expects this growth to increase further.

Because of the critical nature of the SAP R/3 application at Microsoft, EAS hosts the SQL Server 2005 back-end database on high-speed hard disk drives configured in a redundant array of independent disks (RAID) 10 array in Microsoft IT's EMC CLARiiON storage system. With the increasing storage demands of the invoice document archiving system, and with high-speed data storage costs at a premium, the EAS group looked toward lower-cost methods that it could use to not only archive 12 years of SAP R/3 invoice data, but to make that data readily available to SAP users.

Initially, the EAS group considered the purchase of a third-party data archiving solution to meet the capabilities that EAS required in the areas of reporting functionality and invoice document data archiving. EAS considered the approximate $3 million cost of this solution to be a reasonable amount because this solution would resolve the EAS group's invoice document archiving issue and would also reduce the costs to store the invoice document data.

However, with the implementation of SQL Server 2005, the EAS group determined that it could use the SQL Server 2005 table partitioning feature to meet all the invoice document data archiving requirements while keeping this data available for SAP users to reconstruct invoices as required. By implementing SQL Server 2005 table partitioning together with approximately $150,000 in additional hardware costs, EAS developers created a system to increase performance in the invoice document archiving system, to store the invoice document data on inexpensive Serial ATA (SATA) drives in the CLARiiON storage system, and to archive data according to the business requirements for the invoice document archiving system.

Overview of Table Partitioning

Table partitioning is a new feature in SQL Server 2005 that applies to indexes and tables in a database. This feature enables a developer to logically divide a single table into specific subsets of data (partitions), such as date ranges. The developer can also define how the rows of a table or index are mapped to a set of partitions based on the values of certain columns (known as partitioning columns). The developer can then reference the data in that partition as if it were contained in a separate table. Figure 8 illustrates an unpartitioned table in SQL Server 2005.

Figure 8. Unpartitioned database table

A partition scheme maps each partition that the developer specifies in the partition function to a particular file group. Developers can then spread partitioned database tables across several file groups and therefore across different physical storage devices. Figure 9 shows the same table partitioned by date ranges.

Figure 9. Partitioned database table

In the preceding figure, one partition is created to represent each year from 1994 through 1997. Additionally, the indexes apply to the particular partition instead of to the whole table.

With an unpartitioned table, operations to delete data from the table or to load data from an online transaction processing (OLTP) system to an online analytical processing (OLAP) system take much longer than with a partitioned table (up to several hours instead of only a few seconds). Additionally, maintenance tasks take much longer on an unpartitioned table because these tasks must be run against the whole table instead of only the subset of data that makes up a table partition. Figure 10 illustrates the removal of data from a large unpartitioned table.

Figure 10. Data removal from an unpartitioned table

In the preceding figure, the SQL DELETE statement to remove rows earlier than 1995 from a very large table may take several hours to complete because the query must parse the whole table, and the index entries that affect those rows must also be adjusted.

When a developer performs maintenance operations against a partitioned database table, those operations affect only the partition that the developer specified instead of the whole table. This feature increases the manageability of large tables in SQL Server 2005 because it enables developers to manage and access subsets of data quickly and efficiently while maintaining the integrity of a data collection. Figure 11 illustrates the removal of a partition from a table in SQL Server 2005.

Figure 11. Removal of a partition from a table

In the preceding example, the removal of the partition takes only a few seconds to complete because it does not delete data from the original table and therefore does not have to maintain the indexes or log the DELETE transactions. Instead, the SQL statement performs a logical delete operation by changing the metadata in the system tables of the database server. The former partition is removed from the original table (Table A) and added to the destination table (Table B). From here, a database administrator can quickly and easily drop the unwanted table (Table B).

Implementation of Table Partitioning

The EAS group developed a proposal to divide the database tables for invoice document archiving into partitions based on date ranges. By partitioning the database tables in this manner, the EAS group determined that it could meet the archival requirements of the invoice document archiving system and reduce the cost of data storage. Additionally, EAS determined that using smaller table partitions instead of one increasingly large table would make data access much quicker and would greatly improve the maintenance and manageability of the invoice document archiving system.

At the time of this writing, the EAS group has not yet implemented the invoice document archiving system in its SAP R/3 environment. EAS requires the archived invoice documents to be encoded in Unicode. However, this encoding must occur in the SAP system before the invoice documents are moved to the archive database. Therefore, EAS must wait until its SAP environment is converted to Unicode before implementing the invoice document archiving solution in the SAP R/3 production environment.

The EAS group expects to experience the following benefits from the implementation of SQL Server 2005 table partitioning in the invoice document archiving system:

• The number of rows to manage will be reduced from approximately 15 billion in a single table to approximately 316 million rows in each partition during the 12-year archiving period.

• The table size will be reduced from approximately 1.7 terabytes in a single table to 37 GB in each partition during the 12-year archiving period.

• EAS will back up the current quarter's partition on a weekly basis. However, after a quarter is complete and a new quarter begins, EAS will set the partition for the previous quarter to read-only permissions, and then take a final archival backup.

• Access times will decrease because SQL Server 2005 examines only the partition or partitions that contain the date ranges of the requested data.

• The removal of data from the table will be much quicker because after the partition that represents the obsolete data has been removed from table partitioning, the data can be truncated (an extremely fast operation).

  Note The SQL Server 2005 table partitioning feature provides functionality to remove a partition from a partition group and to place that partition into a nonpartitioned table within seconds. EAS can then quickly drop this removed table. This action is much quicker than the overhead of freeing data pages and logging the DELETE transactions; this overhead would occur if EAS had to delete 316 million rows from a table that contained more than 15 billion rows.

• The performance benefits that occur with SQL Server 2005 table partitioning will enable EAS to store invoice document data on large (320-GB), inexpensive SATA drives in the CLARiiON storage system.

To archive data from SAP R/3, the EAS group must move that data from the SQL Server 2005 back-end database to a separate SQL Server database. Formerly, because every record to be moved was in the same table, an archival solution had to select each of those records to move them to the archival database. The EAS group plans to use SQL Server 2005 table partitioning to create a sliding window approach to data archiving for the invoice document archiving system. In a sliding window approach, only the most recent data (based on date ranges) is stored on the high-speed storage device; aging data is moved to lower-speed storage drives. To create this sliding window approach, EAS will perform the following procedures in the archival database:

1. Initially, EAS will create one year worth of quarterly partitions.

2. Daily, when an invoice is printed, the invoice document archiving system will store the invoice header information and the invoice detail information in the SAP R/3 database.

3. Weekly, EAS will transfer invoice data that is more than one week old to the document archiving database that is partitioned by quarter.

4. Quarterly, EAS will set the completed quarter's partition to read-only permissions and adjust the backup schedule to no longer back up this partition on a weekly basis. EAS will then create another new quarterly partition that will include an adjustment of the constraint on the database tables.

5. After 12 years, EAS will roll the oldest partition out of the partition group, and then drop the obsolete data.

To implement this solution, the EAS group created a number of file groups that correspond to the quarterly database table partitions in the invoice document archiving database. Because this solution was developed in August 2005, EAS created file groups named FG_2005Q3 through FG_2006Q1. For each of these file groups, EAS created one data file. Figure 12 illustrates the architecture that EAS created for the invoice document archiving database.

Figure 12. Architecture for the invoice document archiving database

In this architecture, EAS created a data file for each file group, with data files for odd-numbered quarters stored on one drive and data files for even-numbered quarters stored on a different drive. The partition function shown in Figure 12 specifies the date range of each partition. The partition scheme that EAS created associates the partitions with the particular file groups. Additionally, in this architecture, EAS created the Header and Detail tables; each table has a column constraint on the date field in order to cause SQL Server 2005 to trust the data in the date field. EAS then created a cluster index (which includes the date field) on each table. For more detailed information about the steps that EAS used to configure table partitioning in the invoice document archive database, see Appendix A in this document.

Without considering the money saved by using the built-in table partitioning functionality of SQL Server 2005 instead of implementing a third-party document archiving solution, EAS expects the use of SQL Server 2005 table partitioning to save thousands of dollars in storage costs each year. The initial cost of high-speed storage for EAS is more than two times that of the less expensive SATA storage. Additionally, the annual cost for high-speed storage is several thousand dollars more than for an equal amount of storage on SATA drives. The SQL Server 2005 table partitioning feature will enable EAS to store the invoice document data on the less expensive SATA drives instead of increasing the use of high-speed storage in the SAP R/3 database tier.

Best Practices

Because the EAS group uses SQL Server 2005 as the back-end database for the SAP R/3 application, EAS generally has to use only the features that the SAP R/3 application supports. However, because EAS has spent a considerable amount of time running SQL Server 2005 in its SAP R/3 environment, EAS has gained a large amount of experience optimizing SQL Server 2005 as the back-end database for SAP R/3. This experience has led to a list of best practices that other organizations can use to help maximize the performance and manageability of SQL Server 2005.

Disaster Recovery and High Availability

Disaster preparedness is of critical importance to any business whether large or small. The disaster recovery plan at Microsoft considers the safety and protection of people as the highest priority; the secondary goal is the resumption of normal business operations in as short a time as possible. Through repeated practices and tests, the EAS group has found the following ways to minimize the time that it takes to return its SAP production environment to an online state in a disaster recovery scenario:

• Include business stakeholders in the disaster recovery team

  The operations required to bring a disaster recovery center fully online take longer than typical server operations. In a disaster recovery scenario, the EAS group generally assigns one support person to the database server to bring it online, one person to the central instance server, and two people to the five application servers. In addition, EAS includes in the disaster recovery team a representative from each functional group (finance department, sales department, and so on) in the SAP environment. The functional support personnel are responsible to test the system when it returns to an online state. This testing includes running reports to make sure that the system is stable and healthy. After the representatives of the business stakeholders report the system as functional, EAS releases the system to the SAP users.

• Have a fully budgeted disaster recovery environment

  The EAS group uses the SAP test center as its disaster recovery center. Because the SAP test center is fully budgeted, unexpected issues—such as defective hard disks or insufficient RAM—do not occur in a disaster recovery scenario.

• Use virtual server names

  By implementing virtual server names in its SAP environment, the EAS group experiences greatly reduced recovery times in its disaster recovery exercises.

• Keep mirror servers in the same local network

  For synchronous database mirroring, EAS locates both the primary and secondary database servers in the same local Gigabit Ethernet network. For asynchronous database mirroring, the EAS group has determined that in general, the primary and secondary servers should be located within 100 miles of each other.

• Perform an incremental rollout for database mirroring

  The EAS group implemented database mirroring in an incremental fashion to determine the impact that database mirroring would have on its SAP R/3 application. EAS started with asynchronous mirroring, configuring performance monitoring to determine whether the network infrastructure had sufficient bandwidth to support synchronous mirroring.

Performance and Management

Throughout the SQL Server 2005 beta program into the deployment of the RTM edition of SQL Server 2005 and during tests with beta editions of SAP R/3, the EAS group continuously monitored and optimized the performance of SQL Server 2005 in its SAP R/3 production environment. The following is a list of best practices that the EAS group implemented as a result of this optimization process:

• Use asynchronous auto-update statistics

  By default, using auto-update statistics is a synchronous operation. To improve update response time in the SAP R/3 application, the EAS group sets the SQL Server 2005 auto-update statistics to run asynchronously. By using asynchronous auto-update statistics, EAS saw a reduction in update response times from approximately 0.8–1.5 seconds to 0.4–0.77 seconds.

• Run SAP R/3 with the 1211 trace flag enabled

  By setting this trace flag, the EAS group experiences decreased lock blocking in SAP R/3.

• Increase the size of the MSDB log file and increase the file auto-growth setting

  EAS not only increased the MSDB log file size in its log shipping environment, but also increased the auto-growth setting from the default value of 256 kilobytes (KB) to more than 1 MB. This action reduces the run times for the clean-up process for the log shipping job.

• Lock pages in memory

  Even in a 64-bit computing environment, EAS assigns the service account under which SQL Server 2005 runs the Lock pages in memory Group Policy right. This action prevents memory that SQL Server 2005 uses from being paged to the hard disk.

• Increase the size of the SQL Server Agent history log file

  To meet the requirements of its disaster recovery implementation, EAS backs up transaction logs from the primary SQL Server 2005 database server and performs log shipping to its remote data center every minute. EAS must therefore increase the maximum job history log size to prevent a loss of job history data.

• Do not reindex or defragment the database

  Because of the efficiency with which SQL Server 2005 reclaims space, EAS has found no advantage in reindexing or defragmenting the SQL Server 2005 database.

Conclusion

The EAS group at Microsoft deployed SQL Server 2005 as the back-end database for its SAP R/3 application, an application that is critical for the day-to-day operations of the company. This deployment takes advantage of the new high-availability features that SQL Server 2005 includes. Because of the enormous cost to Microsoft, both financially and with regard to customer satisfaction, that may occur if the SAP R/3 application experiences unexpected downtime, the EAS group looked forward to implementing SQL Server 2005 database mirroring in its SAP R/3 database tier. Additionally, by using SQL Server 2005, the EAS group was able to decrease its log shipping interval to one minute, minimizing the risk of data loss in its disaster recovery center if a disaster occurs at its main data center.

Because of its built-in support for x64-based computers, SQL Server 2005 saved the EAS group thousands of dollars by enabling EAS to deploy commodity 64-bit hardware in its SAP R/3 database tier. The EAS group also experienced significant performance improvements by using the combination of SQL Server 2005 and x64-based computer systems.

SQL Server 2005 features such as online indexing and dynamic management views increase the overall manageability of the SAP R/3 database tier. In addition, SQL Server 2005 enabled the EAS group to add new functionality to SAP R/3. For example, SQL Server 2005 table partitioning makes possible the archiving of 12 years of invoice document data on inexpensive drives while keeping that data available for SAP users.

Appendix A

Virtual Server Names

Formerly, to bring the SQL Server 2005 disaster recovery center completely online, the EAS group had to change the names of the computers in the San Francisco Bay area test center and wait for those new server names to propagate throughout the Domain Name System (DNS) namespace. This DNS propagation took up to 72 hours. To reduce the time that it takes to bring the SQL Server 2005 disaster recovery center completely online, EAS configured SAP to use the virtual server names instead of the physical server names for all the servers that a disaster recovery scenario would affect. EAS would then only have to modify the virtual server names of the servers in the recovery center to have SAP connect to those servers. EAS would not have to wait for DNS propagation to occur.

Note: Virtual server names are typically used only with Microsoft Windows® Clustering.

EAS configured virtual server names for every application server in the SAP production and test environments. Each SAP instance is addressed through the virtual server name, and all indicators in SAP use a virtual name such as SM51 or RZ04. Therefore, EAS can easily fail over to another server without having to rename that physical computer. In this scenario, EAS only has to move the virtual server name to the destination server. This change is transparent for users, applications, and SAP. In the local data center, the virtual server name is moved to the destination server. No propagation of the server name or IP address has to occur. Only the local server's settings are modified.

To assign a virtual server name:

1. Obtain a static IP address for the virtual server.

2. Enter the virtual server name and the static IP address into Windows Internet Name Service (WINS).

3. Enter the virtual server name and the static IP address into the %SYSTEMROOT%\System32\Drivers\etc\hosts file.

4. Add the following registry entry:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Lanmanserver\Parameters

   Value type: REG_MULTI_SZ

   Value name: OptionalNames

   Value data: VirtualServerName

5. For Microsoft Windows Server 2003 Service Pack 1 (SP1), add the following registry value:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0

   Value type: REG_MULTI_SZ

   Value name: BackConnectionHostNames

   Value data: VirtualServerName

6. Assign the new IP address to the network adapter.

7. Restart the server.

8. Modify the user environment of the user under which the SAP instance starts. Typically, this environment is SAPServiceSID. Configure the following SAP startup parameters:

   SAPLOCALHOST = VirtualServerName

   SAPLOCALHOSTFULL = VirtualServerName

9. Add SAPLOCALHOST and SAPLOCALHOSTFULL to the instance and start profiles.

10. Restart the SAP service if it is running

Database Table Partitions

To create the table partitions and the partition function for the invoice document archiving database, the EAS group used the following code. Database developers can use this code as an example when implementing database partitioning in their organizations.

To create the table partitions and the partition function:

1. Create the partition function as follows.CREATE PARTITION FUNCTION PF_QRTLY(varchar(8))AS RANGE RIGHT FOR VALUES ('20050701', '20051001', '20060101', '20060401', '20060701', '20061001')

2. Create file groups for the document archiving database as follows.ALTER DATABASE Doc_Archiving ADD FILEGROUP [FG_2005Q3]

3. Create the data files as follows.ALTER DATABASE Doc_Archiving ADD FILE ( NAME = '2005Q3_1', FILENAME= 'H:\MSSQL\Data\2005Q3_1.ndf', SIZE = 20GB, FILEGROWTH = 1GB)TO FILEGROUP [FG_2005Q3]

4. Create the partition scheme as follows.CREATE PARTITION SCHEME PS_QTRLYAS PARTITION PF_QTRLY to ([PRIMARY], [FG_2005Q3], [FG_2005Q4], [FG_2006Q1], [FG_2006Q2], [FG_2006Q3], [FG_2006Q4])

5. Create the partitioned tables as follows:

   • Check the constraint on the Date column.

   • Create the table on partition scheme PS_QRTLY.

   • Configure the primary key to include the date.

Database developers can use the following information to configure a new partition in a database table partitioning environment. These steps use the partition and file names that EAS used in its table partitioning solution.

To add a new partition by using file groups:

1. Create the new file group.

2. Create the data file for the new file group.

3. Set the new file group as the next used file group. Use the following statement:ALTER PARTITION SCHEME PS_QTRLYNEXT USED [2006Q2]

4. Add the new partition. Use the following statement:ALTER PARTITION FUNCTION PF_QTRLY ()SPLIT RANGE ('20060401')

If all partitions are in one file group, use the following information to add the new partition:ALTER PARTITION FUNCTION PF_QTRLY ()SPLIT RANGE ('Default')

For More Information

For more information about best practices on architecture, configuration, and operation of SQL Server 2005 with SAP, see the technical white paper Best Practices for Running SAP on Microsoft SQL Server 2005. To obtain this white paper, go to:

http://www.microsoft-sap.com/technology.aspx

Under Technologies, click Microsoft SQL Server.

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
• http://www.microsoft-sap.com

For any questions, comments, or suggestions on this document, or to obtain additional information about Microsoft IT Showcase, please send e-mail to: showcase@microsoft.com