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

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.
Surprises can be minimized by using the mirror monitor or the documented formula for estimating the
synchronization period.
After an ISL outage, monitor the transaction log size; it could fill to capacity as the log send queue grows,
and could bring the database to a halt if left unaddressed.
10 Follow the
documented HP Best Practices
SQL Server administrators
When replicating SQL Server databases to a contingency site, Continuous Access can process more TPS
than SQL Server database mirroring with the same workload. This is the expected outcome because the
dedicated hardware technology can off-load the resource from the SQL Server to accomplish this task.
Consider customizing the checkpointing operation when implementing either Continuous Access
replication or database mirroring.
Consider using a duration option when implementing the checkpointing to regulate the surge-type nature
of the traffic over the underlying architecture.
Failover of a clustered SQL Server instance between nodes will result in downtime for the database due to
the failover mechanism and the database recovery process.
The SQL Server recovery time after an outage is affected by the checkpoint process and settings.
In order to satisfy the SQL Server engine when using Continuous Access to replicate databases between
two sites, make sure disk write response time is less than 20 ms.
If the transaction delay is too high for acceptable application performance when mirroring a database
between two sites in synchronous mode, consider adding more server memory and making it available to
the SQL Server.
The time it takes to fail over a database between the Principal and Mirror depends on the database
throughput prior to the failover.
When implementing SQL Server mirroring, to ensure thatcapacitywillbeavailableshouldtheMirror
take over the Principal role, do not to install any resource-intensive application on the Mirror server.
Although there are clearly transactional performance benefits from using the asynchronous mode, RPO is
compromised if there is a nonrecoverable failure of the Principal database.
In an asynchronous SQL Server database mirroring configuration,ifthereisalossofthePrincipal
database and the Mirror database has to be transitioned to become the Principal, the likelihood for some
data loss is high if there is any buildup of transactions in the Principal’s send queue.
Server a
dministrators
For all solutions, consider adjusting the default HBA I/O buffer length (execution throttle settings) to relieve
any potential disk I/O queues that can develop on the SQL Server, especially at checkpointing times.
When implementing a combination of Microsoft Cluster Server and SQL Server database mirroring, HP
recommends that tests be conducted on the mirroring timeout value setting, including the application of a
real-life workload to the database, to determine the correct number.
Even though it has been shown that the Mirror server processor utilization is significantly less than that
of the Principal, be careful if considering placing other applications or workloads on the Mirror server
becauseitwillbecomethePrincipalifafailoveroccurs.
6