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 |
Now lets take a closer look at the evaluating the different options…….
|
|
SQL Clustering |
Database Mirroring |
Transactional Replication |
Log Shipping |
|
1. |
Failover type |
Automatic / Manual |
Automatic / Manual |
Manual |
Manual |
|
2. |
Downtime period |
30 seconds |
30 seconds |
Manual effort required |
Manual effort required |
|
3. |
Availability Scope |
Server |
Database |
Database / DB Objects |
Database |
|
4. |
Special hardware |
Yes |
No |
No |
No |
|
5. |
Standby Type |
Hot |
Hot |
Warm |
Warm |
|
6. |
Complexity |
Complex |
Little Complex |
Little Complex |
Easy |
|
7. |
Data Accessibility (other than primary server) |
No |
Possible |
Possible |
Possible |
|
8. |
Client Access |
Automatic redirection |
Automatic redirection |
Manual effort required |
Manual effort required |
|
9. |
Additional servers (including base server) |
At least >= 2 Servers |
At least >= 3 Servers (automatic failover) |
At least >= 2 Servers |
At least >= 2 Servers |
|
10. |
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 |
|
11. |
Automatic Role Change |
Yes |
Yes |
No |
No |
|
12. |
Multiple storage location |
No |
Yes |
Yes |
Yes |
|
13. |
Hardware Setup Cost |
High, special certified hardware required |
Medium |
Low |
Low |
|
14. |
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 Nayyar