Cold 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)
The only difference between a cold standby server and a hot standby server is that you literally have to build the SQL Server when you need it. This, of course, take time, but it does save some money if you don’t mind being down for awhile.
Log Shipping
Importance: Depends on other choices you have made. Can be used with clustering instead of disk mirroring
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
Log shipping is one step above a hot standby server, but one step below disk mirroring or failover clustering. Because it is easy and inexpensive to implement, it has become very popular. But I am guessing that it will slowly fade away once SQL Server 2005 disk mirroring becomes available, as disk mirroring is much superior, and not much harder or much more expensive to implement than log shipping.
Daily Backups
Importance: Critical
Difficulty: Easy
Used to:
-
Increase outside force HA (if stored offsite)
-
Increase hardware HA
-
Increase OS HA
-
Increase application software HA
-
Increase data corruption-related HA
-
Increase human-error HA
This may seem obvious, but you might be surprised how many SQL Servers don’t get backed up. On top of this, backups are mandatory, no matter what other type of high availability options you are using, including failover clustering or database mirroring. Notice that this is the first item I have listed with a critical importance, and it also one of the easiest to implement.
Transaction Log Backups
Importance: Critical
Difficulty: Easy
Used to:
-
Increase outside force HA (if stored offsite on regular basis)
-
Increase hardware HA
-
Increase data corruption-related HA
-
Increase human-error HA
As with daily backups, hourly (or more often) transaction log backups are critical to overall high availability efforts. Ideally, transaction log backups should be stored on a server other than the production server. For example, you might have a file server designated in your organization for the sole purpose of storing SQL Server log files, just in case you should loose the production server. The shorter the transaction log backup interval, the less data you potentially lose.
Backup Verification (RESTORE command)
Importance: Critical
Difficulty: Easy
Used to:
-
Helps ensure backups and transaction logs are good.
-
Important for log shipping also.
This high availability suggestion is also obvious, but again, not everyone uses this option of the RESTORE command. By using the VERIFYONLY option, you are adding one more way to help ensure your backup data is good.
Monitor Backup Success and Failure
Importance: Critical
Difficulty: Easy
Used to:
-
Ensure backups and transaction logs are made.
Just because you have set up a job to make database and log backups, doesn’t always mean that the jobs are successful. You need to have in place a system to regularly monitor backups to ensure that they are being made. You can do this manually, using SQL Server, or use some third-party monitoring software.
Store Backups Offsite
Importance: Critical
Difficulty: Easy
Used to:
-
Ensure backups and transaction logs are safe should outside forces cause a problem.
Should the worst occur, and you loose your entire building, and if you don’t have a backup data center, then offsite backups are the only way you can recover your data. The very existence of your business may be at stake if all your data is gone. The easiest way to prevent this from ever happening is by keeping backups offsite. You need a system in place to ensure that nightly backups are stored safely offsite, and to ensure old tapes are returned back into rotation.
Test Backups to Verify Them
Importance: Critical
Difficulty: Easy
Used to:
-
Ensure backups and transaction logs are actually able to be restored.
This is not the same as using BACKUP verification. Here, I am talking about implementing a systematic method to verify that the backups you make are good. For example, you may want to perform a test restore of a different database each week. This critical and easy, albeit, boring job is very important for ensuring high SQL Server availability.