Customer Focused Testing: Ten steps to improved SQL Server 2005 replication (5697-7434, March 2008)

ManualsBrandsHP ManualsSoftwareHP StorageWorks Continuous Access EVA V2.1 Media Kit

banner

Customer Focused Testing: Ten steps to

improved SQL Server 2005 replication

Microsoft SQL 2005 Server provides a built in, application aware, host-based feature called Database

Mirroring as an alternative to existing array-based remote replication solutions such as the HP Continuous

Access soluti

on. This document provides 10 recommended steps that should be used when planning to

deploy remot

e replication solutions for SQL Server 2005 in an enterprise environment. This document is

meant as a companion to the HP whitepaper, “High Availability for SQL Server 2005 Using Array-Based

Replication and Host-Based Mirroring”. By leveraging these recommendations and best practices,

customers can select the optimal replication technology for their environment, can optimize performance,

and can acce

lerate implementation, thereby reducing costs and personnel resources.

1Understandyourenvironment

Before implementing any replication solution, it is important to understand your recovery goals and

the limitations of the available infrastructure. For SQL Replication, the key information includes:

• Recovery Point Objective – This is a measure of how much data loss can be tolerated. For an RPO

of zero, array based replication solutions such as HP Continuous Access should be implemented

in synchronous mode, whereas synchronous replication using SQL Server database mirroring,

being a log-shipping solution, has some potential for data loss. Asynchronous replication can

provide advantages but should not be considered if an RPO of zero is required.

• Recovery Time Objective – This is a measure of the amount of time it takes a user to get their

backup site running after a complete failure at the primary site. The RTO includes the time to

recover, bring the backup database online, and redirect any applications to the backup database

server. For the Continuous Access solution, it includes the time to fail over the replicated LUNs

to the backup EVA; for the SQL Server database mirroring solution, it includes the time for the

Mirror to transition to the role of the Principal.

• Workload (IO activity) – Heavy workloads will put more of a strain on the available resources

(servers, storage, and the intersite link), whereas lighter workloads are more forgiving. Keep

in mind that for replication, it is only the amount of writes that matters. Databases with low

percentage of changes (typically <5%) will provide more ﬂexibility than those with a higher

quantity of writes.

• Intersite Link – Both the bandwidth and latency of the intersite link have a direct inﬂ uence

on replication performance. The ISL has to support the ability to transport the writes to the

remote site while still providing disk write response times of <20ms. Asynchronous results are

relatively unaffected by the ISL latency and bandwidth; however, the link can have a direct

impact on recoverability. In a heavy workload conﬁguration, response times will typically

increase sharply with greater ISL latencies. In a light workload conﬁguration, more linear and

predictable increases are typically observed.

5697-7434

The information contained herein is subject to change without notice. The only warranties

for HP products and services are set forth in the express warranty statements accompanying

such products and services. Nothing herein should be construed as constituting an additional

warranty. HP shall not be liable for technical or editorial errors or omissions contained herein.

Inc. Microsoft and Windows are U.S. registered trademarks of Microsoft Corporation. Oracle is

a registered trademark of Oracle Corporation and/or its afﬁliates.

The information in this document is subject to change without notice.

5697-7434

1st edition: March 2008

Summary of content (8 pages)

PAGE 1
banner Customer Focused Testing: Ten steps to improved SQL Server 2005 replication Microsoft SQL 2005 Server provides a built in, application aware, host-based feature called Database Mirroring as an alternative to existing array-based remote replication solutions such as the HP Continuous Access solution. This document provides 10 recommended steps that should be used when planning to deploy remote replication solutions for SQL Server 2005 in an enterprise environment.
PAGE 2
Choose the correct replication technology Based on your RPO and RTO, a comprehensive review of array-based and host-based replication technologies for SQL Server 2005, and the practical considerations for selecting which method to deploy, should be completed prior to implementation. • For heavy workloads, Continuous Access produces better transactional performance and is less sensitive and more predictable than SQL Server database mirroring, regardless of the replication mode—synchronous or asynchronous.
PAGE 3
Configure the EVA The database data disks should be separated from the transaction log disk using two separate disk groups on the EVA. The separation of disk groups is beneficial for two main reasons: • It guards against data loss due to problems with a specific disk group. • The disk mechanics are such that disk performance suffers when serial I/O and random I/O patterns are mixed on the same disk.
PAGE 4
When examining the impact of HBA queue length in the mirroring solution, it is important to gauge the performance of both the Principal and the Mirror servers. In this configuration, the Mirror server is involved in most of the strenuous disk operations; therefore it was more important to monitor the disk write response time on the Mirror than on the Principal server.
PAGE 5
the surge nature of checkpointing traffic tends to impact processes within the SQL Server mechanism, especially when the SQL Server database mirroring process is included in the configuration. 7 Allow the Lazy Writer to do some of your work Using the default checkpointing settings causes dirty pages to be written to disk predominantly by the checkpoint operation. This does not take advantage of the Lazy Writer’s ability to do more of this work as a background task.
PAGE 6
To understand the condition of the SQL Server database mirroring configuration, use the Database Mirroring Monitor. This can be accessed via the SQL Management Studio interface and estimates the time needed to recover the database when synchronizing. In an unplanned failover, there will be a period of time during which the database is not available. The duration of this unavailability depends on the actual workload entering the database prior to failure.
PAGE 7
In testing, the SQL Server behaved in a predictable fashion and used all but 500 MB of available system memory (23.5 GB). If other programs or applications are installed on the server, consider placing an upper limit on the SQL Server’s maximum server memory setting. In an asynchronous SQL Server database mirroring configuration, after a local cluster node failover, limit client access to the Principal database in order to enable the databases to synchronize once the Principal is back online.
PAGE 8
After an ISL outage, the SQL Server database mirroring solution may need to have client access restricted for a period of time to enable the solution to catch up and become synchronized; under some circumstances, with low ISL latency and a light workload, the solution will be capable of catching up while still allowing client access. We value your feedback In order to develop technical materials that address your information needs, we need your feedback. We appreciate your time and value your opinion.