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

2 Choose the correct replication technology
Based on your RPO
andRTO,acomprehensivereviewofarray-basedandhost-basedreplication
technologies for SQL Server 2005, and the practical considerations for selecting which method to
deploy, should be completed prior to implementation.
• For heavy workl
oads, 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.
• SQL Server dat
abase mirroring performance is not affected when small bandwidths (OC-3)
are used; howe
ver, Continuous Access may not be a viable solution for low bandwidth
implementations.
• Checkpointing is key for both Continuous Access replication and SQL Server database mirroring,
especially w
ith a heavy workload.
• Both Continuous Access and SQL Server database mirroring require the administrator to monitor
the appropriate log mechanism if the ISL is out of service for a period of time.
• Continuous Access will copy all SQL Server components; SQL Server database mirroring will
copy only th
e actual production database. When using the SQL Server database mirroring
solution, the administrator must be sure to manually place the system databases, logins, and
jobs on the Mirror server.
• Continuou
s Access is application neutral and is the preferred option when the administrator needs
to replicate different applications simultaneously.
• SQL Server database mirroring can use an existing Ethernet infrastructure; Continuous Access
requires the deployment of a dedicated connection (either a dark fibre link or an FCIP network).
• SQL Serve
r database mirroring utilizes the server processor about half as much as Continuous
Access.
• Better database performance is expected when replicating in asynchronous mode rather than
in synch
ronous mode, unless there are low latencies on the ISL. At latencies below 10 ms,
synchronous replication typically achieves a higher transaction rate (TPS) than asynchronous
replication. If the network link has 6 ms or less, HP generally recommends using synchronous
replication. (Tests indicate that at ISL latencies of 20 ms and beyond synchronous replication
solutions may be non-viable.)
• Althou
gh there are benefits to using an asynchronous solution, there is the potential for data to
build up in either the send queue or the write history log on the primary site, directly impacting
theRPO.Thisdatawillbelostifthereisafailureoftheprimarystoragesystemandthearray
cannot be recovered.
• Async
hronous replication can use more of the available ISL bandwidth than synchronous
replication, at high workloads and low latencies. This is an expected behavior, as asynchronous
replication does not rely on an acknowledgement back from the destination array before sending
the next I/O. As the ISL latency increases, the asynchronous bandwidth utilization decreases
significantly due to the inability to drive additional I/O across the link. At in ISL latency of 50
ms and above, the bandwidth utilization results are typically the same for both light and heavy
workloads due to significant I/O restrictions. For light workloads ISL latency plays little part in
ban
dwidth utilization.
• Higher bandwidths (OC-12) deliver consistently better transactional performance in asynchronous
solutions, regardless of latency.
• Asynchronous replication can use more of the available ISL bandwidth than synchronous
re
plication, at high workloads and low latencies. This is an expected behavior, as asynchronous
r
eplication does not rely on an acknowledgement back from the destination array before sending
t
he next I/O. As the ISL latency increases, the asynchronous bandwidth utilization decreases
significantly due to the inability to drive additional I/O across the link. At in ISL latency of 50
ms and above, the bandwidth utilization results are typically the same for both light and heavy
workloads due to significant I/O restrictions. For light workloads ISL latency plays little part in
bandwidth utilization.
• Higher bandwidths (OC-12) deliver consistently better transactional performance in asynchronous
solutions, regardless of latency.
2