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

3Configure the EV
A
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 specificdiskgroup.
• The disk mecha
nics are such that disk performance suffers when serial I/O and random I/O
patterns are mixed on the same disk. The transaction log data is always written to disk in a serial
fashion, and OLTP data usually is written in a random fashion. Thus, the separation optimizes
disk performance.
NOTE:
The recommen
dation for two disk groups applies only when sufficient numbers of spindles are available
to the configu
ration. It is not generally recommended for configurations of 28 or fewer disks.
For workloads where tempdb is not heavily used, it is acceptable to leave in the default location on the
shared storage and the associated SQL Server default databases. For application scenarios that require
significant use of tempdb, such as queries and table copies, use a database layout where tempdb is
placed on its own LUN.
The EVA 8000 array is capable of running typical SQL Server workloads with minimal tuning. Therefore,
it should be sized for capacity requirements first, then for performance. Parity-based VRAID5 is entirely
appropriate, although VRAID1 remains acceptable if increased availability is imperative. Use the one
disk group configuration for simplicity and ease of management.
With Continuous Access, it is recommended that all LUNs associated with a single database in a Data
Replication (DR) Group reside on one owning controller. As this may unbalance the EVA controller load,
consider assigning the majority of non-replicated LUNs hosted by the EVA to the controller with the least
load, or consider multiple database replication streams. Since Continuous Access uses a single controller
for each DR group, it is important to verify, prior to implementation, that there is sufficient controller
performance bandwidth to handle the workloads.
4Configure the SQL Server cluster
Implementation of the database servers for the Continuous Access EVA and SQL Server database
mirroringsolutionseachincludeMicrosoftClusterServer(MSCS)localsiteforfailovercapabilitieson
siteA.However,thesiteBconfigurations differ slightly:
• Because replication requires an independent SQL Server instance on site B, the Continuous
Access solution includes the server on site B in the cluster configuration, effectively creating a
geographically dispersed cluster. The synchronous Continuous Access configuration provides the
option of including solutions such as HP StorageWorks Cluster Extension Enterprise Virtual Array
(CLX) in the configuration to assist in case of a major component failure on site A. This enables
Microsoft Cluster Server (MSCS) to control the failover operation of the EVA LUNs associated with
the SQL Server in tandem with the other related cluster group resources.
• The SQL Server database mirroring configuration consists of a two–node, single-quorum-based
cluster on site A, with one clustered instance of SQL Server mirrored to a stand-alone, dedicated
SQL instance on site B.
5 Tune the HBA
In Performance Monitor, there is an average disk queue length performance counter. Use this counter to
study the disk queue length of the operating system. On the Continuous Access solution use the counter to
determine if there is a significant buildup of requests held at the server. In general, the queue length
should be limited to less than two times the number of physical spindles. It is important to test various (32,
64,128, and 256) HBA I/O buffer settings (also known as execution throttle settings) to determine the
effects of these settings on disk write operation performance and on transactional performance.
Customer Focused Testing: Ten steps to improved SQL Server 2005 replication
3