Microsoft SQL Server 2005 High Availability options...

Recently I was proposing some of the high availability options for Microsoft SQL Server 2005, so thought of summarizing the same here....

SQL Server 2005 provides 4 basic options for high availability.

1. SQL Clustering
2. Database Mirroring
3. Transactional Replication
4. Log Shipping

Other than these 4 options some people also consider backup-restore and Database snapshots as other high availability options, which is really not true.....

"Database snapshots can be used as a recovery option from user/application specific errors or for reporting purposes when combined with other technologies. A snapshot is simply a read-only view of a database at a particular point in time, that’s why called - Database snapshots"

So let’s see all of them in more detail...

I will evaluate each high availability option on these parameters

1.	Failover type	Automatic or Manual
2.	Downtime period	Time to recover or get back the system in place
3.	Availability Scope	Database level or Server level
4.	Special hardware	Any special hardware required
5.	Standby Type	Hot / Warm
6.	Complexity	Configuration/maintenance complexity
7.	Accessibility	Is it possible to check the other server or can we make use of that anyway
8.	Client Access	Client redirection, Automatic or Manual
9.	Additional servers	Any additional Microsoft SQL Server required
10.	Distance limit	How far the servers can sit
11.	Automatic Role Change	Is it possible for the server to resume work automatically or need manual intervention
12.	Multiple storage location	Multiple data storage locations
13.	Hardware Setup Cost	How expensive the whole system will be
14.	SQL Server Editions support	What all options are available with each edition of Microsoft SQL Server 2005

	SQL Clustering	Database Mirroring	Transactional Replication	Log Shipping
Failover type	Automatic / Manual	Automatic / Manual	Manual	Manual
Downtime period	30 seconds	30 seconds	Manual effort required	Manual effort required
Availability Scope	Server	Database	Database / DB Objects	Database
Special hardware	Yes	No	No	No
Standby Type	Hot	Hot	Warm	Warm
Complexity	Complex	Little Complex	Little Complex	Easy
Data Accessibility (other than primary server)	No	Possible	Possible	Possible
Client Access	Automatic redirection	Automatic redirection	Manual effort required	Manual effort required
Additional servers (including base server)	At least >= 2 Servers	At least >= 3 Servers (automatic failover)	At least >= 2 Servers	At least >= 2 Servers
Distance limit	100 Miles	No Limit but depends upon network bandwidth	No Limit but depends upon network bandwidth	No Limit but depends upon network bandwidth
Automatic Role Change	Yes	Yes	No	No
Multiple storage location	No	Yes	Yes	Yes
Hardware Setup Cost	High, special certified hardware required	Medium	Low	Low
SQL Server Editions support	SQL Server Enterprise and Standard (2 node only)	SQL Server Enterprise and Standard (Restricted)	SQL Server Enterprise, Standard and Workgroup	SQL Server Enterprise, Standard and Workgroup

- Mohit