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

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 defa
ult checkpointing settings causes dirty pages to be written to disk predominantly by the
checkpoint op
eration. This does not take advantage of the Lazy Writer’s ability to do more of this work as
abackground
task. The Lazy Writer can assist in smoothing out the I/O patterns for the trafficovertheISL.
When the defa
ult recovery interval is changed to any value above 0, the Lazy Writer instantly writes more
of the dirty p
ages to disk. What is assumed is that the SQL Server understands the impact of altering
the recover
y interval and will increase the potential for the disk buffer pool to become full; it therefore
instructs the Lazy Writer to write more dirty pages to disk to compensate for this. All of this has the effect
of removing some of the write I/O load from the checkpointing operation and distributing it more evenly
over time. This reduces the I/O contention at checkpointing times.
When using the 0 default recovery interval, the “lazy writes” are fewer than half of those observed
when using any other value. It is assumed that SQL Server already has calculated recovery time and
anticipated the checkpointing time and duration needed to write all the dirty pages to disk; therefore it
does not e
mploy the unpredictable Lazy Writer process.
8 Monitor your performance
The key to SQL Server engine performance management is to monitor the disk write response times
for the database data and log disks. Acceptable SQL Server performance can be attained with disk
write response times of up to 20 ms. As disk write response times increase, a near linear reduction in
transactions per seconds is typically observed. Keep in mind that write performance will be directly
affected by the replication technology utilized, the ISL Bandwidth, and the ISL latency.
Processor and network bandwidth utilization should also be examined. Because the network
stack is managed by the operating system, processes such as interrupts, memory allocation, and
encapsulation/de-encapsulation consume processor cycles. Therefore, as network utilization increases,
processor utilization also increases. For SQL Server database mirroring, processor utilization of the Mirror
server will be significantly less than that on the Principal server. This is because the Mirror server’s
only task in this configuration is to accept the mirrored transactions and to process them from the redo
queue to the data files.
9 Validate Failover and Recovery
There are a number of failure modes that should be simulated prior to implementation in order to validate
that the committed RPO and RTO objectives can be met. These include: server failure, storage failure, ISL
failure, and full site failure. Each replication solution will behave differently in each situation and will also
be i
mpacted differently in the case of a planned failover versus an unplanned failover.
Continuous Access replication
Recovery time is directly impacted by the length of the checkpoint period.
If you have a lengthy storage or ISL failure, the size of the Write History Log could prevent a full site copy
which could significantly impact performance during this period. When planning for this, make sure you
understand the reliability of your ISL and be aware when you set the size of the Write History Log.
Although transactional performance of SQL Server may be impacted quite heavily after certain failure
conditions, it is possible to manage recovery performance by managing user loads after the failure.
SQL Server database mirroring
After an outage, the solution has to be synchronized in order for it to be operational and able to accept
n
ew transactions. It will be in this state until all transactions have been played into the database.
Customer Focused Testing: Ten steps to improved SQL Server 2005 replication
5