Offsite Database Mirroring (SQL Server 2005)
Importance: Important
Difficulty: Intermediate
Used to:
-
Increase outside force HA
-
Increase hardware (including data) HA
-
Increase infrastructure HA
-
Increase power HA
-
Increase application software HA
-
Increase OS HA
-
Increase SQL Server HA
-
Increase 3rd_party HA
Database mirroring is a new feature being introduced with SQL Sever 2005. Without using any special hardware, it allows you to duplicate (real time mirroring) a production database on another SQL Server at a different location. In addition, if the first server should become unavailable, the system will failover to the mirrored database and SQL Server, helping to ensure high availability. You can also combine clustering and database mirroring if you like. Because of its lower cost than failover clustering, its easier implementation, the ability to work over distances, and the ability to protect data, SQL Server 2005 database mirroring should become a very popular high availability option.
Database Snapshots (SQL Server 2005)
Importance: Important
Difficulty: Easy
Used to:
-
Increase human error HA
-
Increase performance-related HA
Another new feature of SQL Server 2005 is the ability to create snapshots (static database copies) of your production database onto another SQL Server. This feature can be used to recover from human errors, or more likely, be used to run reports from, helping to remove the reporting load from your production server to another server, helping to boost your production server’s performance and availability.
Replication
Importance: NOT recommended for HA
Difficulty: Intermediate
Used to (in theory):
-
Increase server hardware HA
-
Increase OS HA
-
Increase application software HA
-
Increase SQL Server HA
-
Increase 3rd-party HA
-
Increase data HA
-
And others (if replicated server is off-site)
I have listed this option because a lot of people consider replication as a means to help attain high availability. I vigorously disagree. Replication does serve some specific business needs, but HA is not one of them. More often than not, replication reduces high availability.
Hot Standby Server
Importance: Depends on other choices you have made
Difficulty: Easy
Used to:
-
Increase server hardware HA
-
Increase OS HA
-
Increase application software HA
-
Increase SQL Server HA
-
Increase 3rd-party HA
-
Increase data HA
-
Increase human error HA
-
And others (if standby server is off-site)
If being down for awhile is not a problem, a hot standby server (SQL Server is installed, along with recent backups, ready to be used) is a viable option for may companies to help ensure SQL Server high availability. But again, if you have the money to purchase a hot standby server, you might as well use it for disk mirroring or log shipping (discussed later), and be ahead of the game.