SQL Server 2005
Running the Microsoft SAP Infrastructure on SQL Server 2005
Mike Hatch
At a Glance:
- How Microsoft uses SAP
- How SAP runs on SQL Server
- Avoiding single points of failure with mirroring
- Synchronous and asynchronous mirroring
Database Mirroring
SAP
SQL Server
With 60,000 employees and operations in 89 countries, Microsoft has plenty of financial and operational data to track. The company’s SAP R/3 system handles the Microsoft
treasury, material management, payroll, worldwide sales, finance, human resources, operations, as well as other mission-critical functions. In fact, the company’s business depends on SAP R/3 being available.
For enterprises that depend on SAP, SQL Server™ 2005 provides three features of immediate benefit: online indexing to allow index maintenance while remaining online, SQL Server Dynamic Management Views to simplify administration, and database mirroring to enable hot standby failover with zero transaction loss. Microsoft began running its SAP R/3 environment on the beta edition of SQL Server 2005 in August 2004.
Figure 1** Three-Tier Architecture **
The SAP deployment has a three-tier architecture that includes a presentation tier, an application tier, and a database tier, as you can see illustrated in Figure 1. Figure 2 shows the architecture based on Microsoft products and SAP.
Figure 2** Product Architecture **
Presentation Tier
The presentation tier includes a full client application, the SAP graphical user interface (GUI), which is used by some 2,000 heavy users of the SAP environment. The rest of the employees at Microsoft access SAP through a Web browser, with access handled by 20 load-balanced servers running Windows Server™ 2003 Enterprise Edition and Internet Information Services (IIS) 6.0. Authentication is handled through the Active Directory® service. The presentation tier hosts a number of custom applications created using the Visual Studio® .NET 2003 development system and the ASP.NET feature of the Microsoft® .NET Framework. Applications cover a range of functions that in turn access the SAP R/3 database. Among other types of applications that access SAP using Web services from the presentation tier are smart clients, and Microsoft Office applications that integrate SAP data into Excel.
Application and Database Tiers
The application tier includes five load-balanced SAP application servers running Windows Server 2003 Enterprise Edition. The SAP apps are hosted on two HP ProLiant DL740 eight-way computers with 12GB of RAM, and on three HP ProLiant DL 585 four-way AMD Opteron-based x64 servers with 16GB RAM.
The 2.1-terabyte SAP R/3 database is hosted on SQL Server 2005, running on Windows Server 2003 Enterprise Edition. The database grows by about 40GB a month and is hosted on a pair of HP ProLiant DL740 eight-way computers with 16GB of RAM. These servers are connected using fiber to an EMC CLARiiON storage area network (SAN). The two SQL Server instances are configured as a database mirroring pair using SQL Server 2005 database mirroring.
Database Mirroring
The SQL Server 2005 data mirroring features provide the ability to keep a transactionally consistent standby system available for failover. Data mirroring works by transmitting SQL transaction log records directly to the secondary server. There are different modes of data mirroring that allow you to choose the appropriate configuration depending on the failover scenario and the available infrastructure.
The first mode is called asynchronous mirroring, in which there is a standby server when network resources or physical distance preclude the use of other mirroring modes. The transaction log records are sent asynchronously to the mirror node, and the principal database server does not wait for the mirror to acknowledge the receipt of the transactions. Because some latency between the mirror pair is allowed, it is not possible to have automatic failover, but the secondary system is available for different types of scenarios. This could be used in disaster recovery where you are mirroring across a wide area network (WAN) to a remote site. Typically, organizations use log shipping for that today, and asynchronous data mirroring is an alternative that improves transaction consistency. It’s not a problem if the WAN link or the secondary system can’t keep up—it won’t impact the performance of the system.
Asynchronous mirroring provides an improved alternative to SQL Server Log Shipping by providing a mirror that remains in a near-consistent state with the principal server. With log shipping, the system recovery interval is how frequently transaction log backups are made. If there is a catastrophic failure of the primary server, and you back up transaction logs every five minutes, for example, there is a risk of losing five minutes worth of transactions. With asynchronous mirroring, the secondary server stays as closely in sync with the primary server as network and computing resources will allow, reducing the risk of transaction loss. Transaction log backups are still effective, in tandem with data mirroring, to reduce risk.
Synchronous Mirroring
With synchronous data mirroring, you move into a high-availability scenario where the transactions are committed on both servers in the configuration—the principal and the mirror. The commit to the client is not complete until both systems have committed the transactions. This ensures that you always have two consistent copies of the database, but introduces limits on how it can be used. Because the performance of the network link, the disk subsystem, and CPU resources of the secondary server impact transaction performance, synchronous mirroring requires more efficient and reliable resources than what can be used in asynchronous scenarios. We have found that it’s better used in a LAN scenario since WAN performance introduces too much latency to the production system. The servers have to be fairly close to each other with high-speed, fault-tolerant network links.
Synchronous Mirroring with Automatic Failover
Since you have two-phase transaction consistency, SQL Server 2005 also supports automatic failover between the two nodes (see Figure 3). This is similar to a Windows clustering scenario, but instead of having a single copy of the data on shared storage, you have two separate but consistent copies. Automatic failover requires the addition of a witness instance of SQL Server, which can be SQL Server Express Edition. The witness server allows the quorum logic necessary in automatic failover clusters where two of the three servers need to agree on a failover. If the principal machine goes down, the witness and the mirror machine together make a quorum and can then arbitrate to bring the mirror online and redirect the clients to the mirror server.
Figure 3** Database Mirroring with Failover **
Since Microsoft has been using log shipping for both disaster recovery and high availability in our SAP R/3 environment, we’re enthusiastic about the ability to use data mirroring to improve our high-availability scenario. The disaster recovery scenario uses log shipping between our production site in Puget Sound and our test site in northern California. This remains effective and will not be changed with SQL Server 2005. Our high-availability scenario has been using log shipping between two co-located database servers in Puget Sound.
As of this writing, Microsoft has upgraded the high-availability log shipping secondary to a database mirror using asynchronous mirroring between the principal SAP database and the mirror system. As we complete the deployment of the final release version of SQL Server 2005, we’ll be upgrading to synchronous mirroring with automatic failover by adding the witness and switching our asynchronous mirror into synchronous mode with automatic failover. This will provide us with high availability for the database tier.
Online Indexing
Online Indexing is a new feature that allows you to make index modifications without impacting your online transaction processing (OLTP) system. In previous versions of SQL Server, an index creation or rebuild operation would cause very coarsely grained locks (usually table locks) to be held on the table being modified. This prevented online maintenance of indexes in an OLTP system like SAP with very large tables since excessive blocking would occur.
Online indexing introduces a new parameter to the index DDL commands:
CREATE index [x0] on x (a) with (online = on)
DROP index x.[x0] with (online = on)
When you specify online=on, SQL Server will perform the index operation in a shadow index. While that’s occurring, if a transaction hits the primary table, the transaction will not be blocked; SQL Server will remember the transactions made while the index operation is in process.
At the end of the index rebuild process, SQL Server will replay any transactions that have happened during the index operation and then switch out those two indexes on the fly. Once you’ve reached the completion point, SQL Server simply flips the two complete indexes and drops the old one. This allows you to rebuild an index or add indexes without causing blocking on the server. By allowing index operations during productive use, maintenance can now be performed that otherwise would have required downtime or caused blocking on the system.
For SAP systems, this feature helps on those occasions where you might have a new program in SAP that could use a new index, or when you might require index maintenance (rebuild, for example). Now you can perform those operations without taking maintenance or downtime on the system, increasing the availability and maintainability of the system.
There are a couple of specifics about how to do this in a SAP environment. SAP maintains a data dictionary and tools that allow for index and table maintenance in addition to SQL Server metadata, so that SAP knows about the indexes that exist. If you build custom indexes, you need to add them into SAP’s dictionary for change management and transport of index modification from development to test to production.
SAP’s transport landscape is a facility for migrating changes from a development environment to test and production environments. When the transport occurs, if the index doesn’t exist, SAP will create it. At that point it can be tested, and once that’s complete, the change can be moved from the test environment to the production environment. And again, SAP will create the index based on the transport request. As of the release of version 6.2 of SAP’s Web Application Server, the data dictionary and transport tools are not aware of the new SQL Server online indexing option. SAP will normally issue the index DDL statements using the classic syntax, as opposed to the new syntax for SQL Server 2005. This would often result in locking because it would do this in the classic offline mode.
The way to work around this locking problem is to do the initial change in the development environment and then simply capture index names that SAP is using. You can then manually create those indexes on the test system using the same names and the online parameters. When the transport goes through, SAP will recognize that they already exist and simply update the dictionary without trying to recreate that index. Repeat this procedure when you go into production. The database administrator can go into the system, issue the create index online statement to do that work online, and then issue the SAP transport request afterwards so that the dictionary reflects the actual indices on the system. SAP will recognize the index as already existing, and not attempt to build or rebuild it.
Dynamic Management Views
SQL Server 2005 provides a couple of very interesting dynamic management views. The first, called sys.dm_exec_query_stats, is great for looking at query performance because it stores aggregated performance information about queries that were run since SQL Server was started.
Here are some sample queries using dynamic management views:
SELECT * FROM sys.dm_exec_query_stats
SELECT * FROM sys.dm_exec_sql_text(sql_handle)
SELECT query_plan FROM sys.dm_exec_query_plan(plan_handle)
From this view, you can see all of the queries in the statement cache for SQL Server, and the actual statements and query plans are available via handles to the relevant statements. There are columns that reflect a number of different performance metrics for each statement, including physical reads and writes, logical reads and writes, even things like the common language runtime (CLR). It also displays worker time—how much time that statement has gotten from the SQL Server process, as well as the total elapsed time. It also reports the total for that particular statement in all executions as well as the last execution, the minimum and maximum.
Figure 4Query Using Index
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
This offers a nice collection of aggregated performance statistics about the queries in your system since SQL Server has been running. You can use this information from your system to provide trend analysis on how the system is performing.
Another helpful dynamic management view is sys.dm_db_index_usage_stats. This view aids in monitoring the index utilization against your system. It lists the index, categorized by user and system, and tells you how those indexes have been used. You can use this dynamic management view to show where your index utilization is happening on the aggregate, as well as look at queries or indexes that have not been used (see Figure 4).
Rolling It Out
So how does an enterprise such as the IT division at Microsoft manage its relationships with the business units, keep a handle on the amount of change we’re introducing to the system, while also facilitating technical change for things like SQL Server Beta rollouts? Each release is roughly a three-month project called a quarterly release, with five distinct phases:
Envision Gather requirements, assess workload, and prioritize.
Design Create specifications for requirements.
Build Develop code to specifications.
Stabilize Perform three-week stress and functional tests.
Production Roll the changes into the production environment.
These quarterly releases, and the phases within them, are used to manage normal business change within the SAP landscape. Without a rigorous change management process to ensure stability, we would not be able to leverage our SAP R/3 environment for running beta versions of Windows and SQL Server in production. We have beta-tested every release since Windows NT® 4.0 and SQL Server 7.0, and every product has been a reliable platform for our SAP production system, even in early beta releases.
This is relevant in a SAP landscape because over a long period of time, you might create some custom indexes on your tables for particular programs, or perhaps the business changes or the programs change, and those indexes really aren’t as relevant anymore. There’s no way to know for sure without running some kind of tracing to determine whether the index is actually being used. If you have a clustered or nonclustered index, and custom indexes against those tables, you’re consuming a lot of extra space. This view can help identify those that aren’t being used and then get rid of them, reducing database consumption and space.
It can also increase performance because every extra index incurs an extra little bit of time when transactions are hitting that particular table. As a general rule you don’t want to have more than 10 indexes on a particular table. This view allows you to look at the indexes that are there, see how much they are being used, and clean up the ones that aren’t really effective for your system.
Conclusion
SQL Server 2005 promises significant advantages for SAP systems over previous releases, and I’ve only had time to touch on a few of them here. Database mirroring provides another option for high-availability environments, and improves on Windows clustering by providing a completely independent, transactionally consistent copy of the database. SQL Server further improves availability by allowing for online index operations that simply were not possible with earlier releases. Finally, the management and administration is made even simpler through new capabilities such as the dynamic management views that automatically collect information about your system that would have previously required custom coding or third-party products.
Mike Hatch is an Application Architect in the Enterprise Application Services group at Microsoft. He designs the enterprise services software architecture around SAP, plans for disaster recovery and high availability, and implements beta versions of Microsoft products into their production SAP environment.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.