5-314 1 2 3 4 5 6

When looking at 24x7 availability, most people start looking at failovers, clustering, and hot standby servers.  These solutions are part of providing 24x7 availability, but you must also consider your maintenance routines, database layout, and storage planning.

SQL Server 7.0 includes clustering support.  Clustering is not standalone for SQL Server.  It requires having MCS installed and configured on the server.  This is only available with NT Server 4.0 Enterprise Edition and Windows 2000 beta 3.  MCS installs a copy of NT or Windows 2000 on each node.  These two servers each receive a distinct name and IP address.  When you configure MCS, you create a virtual server.   This server also has a name and IP address.  From that point forward, the virtual server name and IP address are what are used to manage the system.  MCS controls everything else that occurs underneath the system.

The biggest thing to keep in mind with a cluster is that the machines are clustered, but they share a common disk subsystem.  This can cause problems, because the most common source of failures occurs at the disk subsystem.  Instead of diving into a tangent on clusters, I'll leave that discussion to a future article.

Installing SQL Server on a clustered system is relatively straightforward.  You install it on first one node and then the other.  Once the installation is complete on both nodes, you run the cluster setup wizard which will configure the SQL Server to operate in a clustered mode.  Now when one server goes down, the existing connections are terminated and the other node takes over.  This causes a recovery procedure to begin.  This happens, because the SQL Server on one node is shutdown.  The other node picks up and the SQL Server is started on that node.  This causes SQL Server to run through its normal startup procedure.  You have to remember that during a failover to another node, any transactions currently being processed are lost and all connections are terminated.  This means applications will need to reconnect and any transactions in process will need to be resubmitted.  The other thing to remember with clustered SQL Servers is that you need a license for each node in the cluster.

Maintaining a standby server is done a variety of ways.

The first way can be termed as a warm standby.  This is a setup where you apply a full database backup and periodic transaction log backups to an alternate server.   This type of setup will apply transaction logs every 5 minutes - 15 minutes.   This is termed a warm standby simply because there will be latency in the system while transaction logs are applied to catch the server up.  Only at periods of no transactional volume will the primary and standby server be in synch.

The second way you can manage a standby server is via replication.  I wouldn't normally recommend using this as it requires a very heavy footprint on both systems and creates many points of failure in the system.

You can automate the standby by using the log shipping utility in the BackOffice Resource Kit.  This is built on top of the sqlmaint utility and can provide significant automation of your standby solution.  I haven't used the log shipping utility, so I won't attempt to describe it until I can run some tests.

5-314 1 2 3 4 5 6