Thursday, April 7, 2011

High Availability Options for SQL Server 2008

Each SQL Server 2008 High Availability option addresses different risks. As we know, no "one" solution fits all. We need to review carefully the business requirements. Basically, we need to consider a few questions like:
  • Is Automatic Failover required?
  • What is the data granular level for the High Availability?
  • How much data loss or downtime is accepted?
  • Is the standby server needed to provide read access?
  • What are the budget constraints?
The High Availability requirement may differ from client to client, some clients may need the database to be up and running 24X7 and others may require only during office hours. So based on the client criticality need we have to choose appropriate the High Availability option. Otherwise there will be unnecessary expenses in terms of money, performance and resource utilization.
It is also very important to know what options are available in the different SQL Server editions before planning for the actual implementation. The below table shows differences between Enterprise and Standard High Availability features.
HA OptionsEnterpriseStandard
Clustering16-node failover clustering2-node failover clustering
Database MirroringFull SupportPartial Support (Single Threaded, Safety Full Only)
Log ShippingFull SupportFull Support
ReplicationFull SupportOracle Publishing not supported.
To summarize:
  • Failover Clustering is an ideal selection if there is no budget constraint.
  • Database Mirroring is alternative choice with minimal down time, because of automatic failover.
  • Log Shipping is a good selection if we want to have multiple secondary servers.
  • Replication is a good selection when there is the need to duplicate some of the data, send the data to multiple servers or for disconnected architecture support.
  • Multiple options can be used, if there is a need to further protect from failures.
  • This is not a complete list of all pros and cons for each option, but I hope this gives you a starting point on which technology to consider for your environment.

No comments:

Post a Comment