Achieving High Availability - Part 2 1 2 3 4 5

The with standby option to the restore command is designed for maintaining standby servers.  This allows you to apply successive transaction logs to a standby server.   Normally when a tran log is applied, any committed transactions are rolled forward and uncommitted transactions are rolled back.  This is not desirable for a standby server since this can entail a significant amount of activity that will be subsequently overwritten.  The with standby option brings the database up in read only mode and allows uncommitted transactions to remain in the tran log.  This gives you the ability to verify the database before allowing it to recover.  This is especially useful for a standby as you will want to ensure that the data is consistent and no corruption has appeared since this database is your safety valve.

The other option is with recovery.  This is the final operation you would perform on a standby before it is ready for production.  When you execute a restore with recovery, any uncommitted transactions are rolled back and the database is taken out of read only mode.  At this point, the database is ready for production.

So far we have covered cold standbys and warm standbys.  That only leaves one type of standby server - hot standby.

There is no such thing as a hot standby.

I'm going to take a couple of minutes to put on all of my flame resistant clothing and duck behind a very thick brick wall while you ponder that statement.

A hot standby is a server that is immediately available in the event of a failure on the primary.  It is also in synch with the primary 100% of the time.  This rules out using any form of backup since a restore introduces a latency.  This rules out replication since there is a latency there as well.  This rules out any sort of manual intervention since no person can instantaneously respond to a situation.  It also rules out any automated monitoring and switch over since there will always be a lag time, however slight, during which the failure is detected and the failover occurs.

This is not to say that solutions do not exist that come close to this scenario.   However, all of those solutions involve some type of clustering which will not be covered in this article.  Even clustering solutions can not achieve immediate failover in the event of a primary failure.  It should be noted that exactly one solution does exist to accomplish instantaneous failover to a copy that is in synch with the primary 100% of the time.  This solution is available from Marathon Technologies.   I will be providing a product review for Marathon at a later date.

Now that I have exploded everyone's views on hot standby servers and probably started a few arguments, I'll cover one final solution that comes very close to providing a hot standby.  This is still a warm standby solution, although it has significantly less latency than a backup/restore method.

This solution is to implement replication over a dedicated, high speed link.  That having been said, I would ask that everyone not go running out and start throwing replication all over the place.  Replication is extremely heavy handed on your SQL Server.  It will create a database, change database options, change status flags on objects, add stored procedures, triggers, and tables into your SQL Server.  Your ability to alter objects will be severely impacted once that object is published.   Replication has processing overhead that takes resources away from your normal processing.  Replication can be exceedingly good at blowing up transaction logs when something goes wrong.  There is a significant management overhead and bringing the primary back on-line can pose a significant barrier.

That being said, replication offers a very powerful warm standby solution when implemented properly.  A replication solution can reduce the possible data loss from minutes to the fractions of a second range.

There are two types of replication I would consider for a standby solution.  The first would be transactional replication.  If you have a very heavy transaction processing environment (several hundred transactions per minute), transactional replication will be the best solution as transfers transactions much more quickly than any other method.  The second type is merge replication.  Obviously this is only available with SQL Server 7.0.  Merge replication gives you an extremely easy way to bring a primary back on-line and resynch the environment by using the -Validate 2 option to replmerg.

Achieving High Availability - Part 2 1 2 3 4 5