Webcast: High Availability for SQL Server 2005 Using Array-Based Replication

Array-based remote replication solutions for Microsoft SQL Server have existed for years, and SQL Server 2005 provides a built-in, host-based feature called database mirroring as an alternative for disaster-recovery solutions. Although these technologies are widely available, users may lack the information needed to select the replication, mirroring, or high availability method(s) suitable for their environment; they need information on how to configure the database, storage, and software.
This paper is intended to inform users who are running or planning to deploy remote replication solutions for SQL Server 2005 in an enterprise environment. This paper provides the data needed to understand the options and the limitations for implementing remote replication solutions on a clustered HP server and storage area network (SAN)-based storage infrastructure. Both array-based replication with HP StorageWorks Continuous Access EVA and host-based copying with SQL Server database mirroring are discussed, along with strategies for synchronous and asynchronous replication of SQL databases between primary and remote HP StorageWorks Enterprise Virtual Array (EVA) disk arrays.

Key findings
Based on extensive lab results, this paper demonstrates that checkpoint duration is critical to managing the solution behavior, whether using Continuous Access EVA or SQL Server database mirroring. Implementation of the best practices for checkpoint duration results in significant performance improvements:
- For Continuous Access EVA with heavy online transaction processing (OLTP) workloads—Modification of the checkpoint intervals results in a 14% improvement in overall transaction performance (measured in transactions per second [TPS]) and a 39% improvement in transaction response times.
- For SQL Server database mirroring—Modification of the checkpoint intervals results in a 59% improvement in overall transaction performance (measured in TPS) and a 52% improvement in transaction response times.

 This paper provides best practices and recommendations for best-fit scenarios, fundamental deployment methodology, and operational guidelines, including:
- A comprehensive review of array-and host-based replication technologies for SQL Server 2005 and the practical considerations for selecting which method to deploy
- The impact to database performance of synchronous and asynchronous replication for both HP Continuous Access and SQL Server database mirroring
- The impact of the distance between sites and latencies incurred on the intersite link (ISL)
- The impact of checkpointing and checkpoint duration on performance
- Recommendations for recovery point objectives (RPO) and recovery time objectives (RTO), including the impact of clusters and HP Cluster Extension EVA
- Planning considerations for properly sizing the remote link and for understanding how the bandwidth and latency of the remote link influence replication behavior
- Test-proven best practices for configuring, implementing, and operating SQL Server 2005 with the major elements of the deployment—HP blade servers, HP StorageWorks 8000 Enterprise Virtual Array (EVA8000) storage, the interconnect, and database configuration, and includes the  appropriate configuration for the HP EVA, including the optimal number, size, and configuration of the disk groups.
By leveraging the recommendations and best practices, administrators can select the optimal replication technology for their environment and can accelerate implementation, thereby reducing cost and optimizing hardware and personnel resources.