Switching Over to a Warm Standby (Subscriber)
If done properly, failing over to a warm standby (subscriber) in a replication configuration can be done in less than two minutes if the failure is detected early. There will be a few main areas to consider:
Knowing what has failed. Has the publisher failed only? Have the most recent transactions been replicated to the subscriber?
Define the process that must be run to make the subscriber the primary server.
Define the process that must be followed to point the client to the new primary server (the standby).
Define the process to switch this all back to the way it was before the failure occurred (if desired).
Just as a precaution, you should always keep all the SQL Server instances that are a part of your replication topology as up to date as possible from a SQL Server service packs point of view. Executing the sp_vupgrade_replication system stored procedure before you are forced to use a subscriber as a warm standby for high availability is highly advised. In particular, the metadata and stored procedures for replication should be kept at the same service pack level across your replication topology.
SP_vupgrade_replication will upgrade schema and system data as needed to support replication at the current product level. This stored procedure creates new replication system objects in system and user databases (such as the distribution and msdb databases). This stored procedure should be executed at the server instance where the replication upgrade is to occur. You can also use your warm standby (subscriber) as a temporary place to switch your client activity to while you do upgrades on the publisher. Once these are completed, you can switch back to the publisher (that has been upgraded) and then repeat the upgrade for the subscriber (warm standby server), thus increasing overall availability of your system.
Scenarios That Will Dictate Switching to the Warm Standby
The main reason why we are building a warm standby configuration with replication is so that we can fail-over to it when the primary server fails. In other words, it does not come back up after automatic SQL Server recovery has attempted to bring it back online. Your database is basically completely unavailable and unusable for any number of reasons (failed disk, failed memory, and so on). When using replication configurations the basic failure scenarios to deal with are
Publisher fails, distributor alive, subscriber alive— You can use the subscriber for your client connections once the distributor has distributed all published transactions to the subscriber.
Publisher fails, distributor fails, subscriber alive— You can use the subscriber for your client connections once you have renamed the SQL Server instance (to the publisher's name). Some data loss will potentially have occurred.
Switching Over to a Warm Standby (Subscription)
Assuming that the primary server (publisher) is not available for any number of reasons and won't be available for the foreseeable future, you must now make your warm standby the primary server.
1. | Verify that the last set of transactions that made it to the distributor have been replicated to the subscriber by reviewing the history of the distribution agent. This should be up to the minute (if not to the second). |
2. | Remove replication from the subscriber by executing the system stored procedure sp_removedbreplication at the subscriber. |
3. | Disable all replication agents (Log Reader Agent, Distribution Agent, and so on). Don't delete them, just disable them. We will clean up later. |
4. | If you are not using Network Load Balancing (NLB) to insulate your client connection from having to change, you may need to rename the subscription server to the name of the old primary server (the one that failed). This is pretty severe and if it is easier to change the client connection string to point to the new server location, pursue that approach. If there are too many clients to do this with, go ahead and rename the server (sp_dropserver, followed by sp_addserver). In addition, this will require that you start and stop SQL Server so that it will come up under the new server name and be known to the network under this new name. |
Before the standby database is made available to the clients for use, be sure you have set the recovery mode to “full” if it was previously set to “simple” or “bulkcopy.” Then, you will want to run your backup/recovery scripts to initiate database backups and transaction log backups immediately.
Turning the Subscriber into a Publisher (if Needed)
Most importantly, if you have to use a subscriber as your fail-over SQL Server instance, you need to be prepared to turn this subscriber into a publisher (if needed). This would be the scenario of the subscriber taking over the publisher's job permanently (until it fails and a switch back needs to occur). We strongly advise that you keep all replication configurations in script form. This would include a version of replication scripts that can enable the subscriber as a publisher and start publishing. As a reminder, make sure you have kept your SQL logins/users synchronized and up to date in both the publisher and the subscriber SQL Server instances.
Insulate the Client Using an NLB Cluster Configuration
You can also use the Network Load Balancing cluster configuration to help insulate your clients from having to change their connection information when reconnecting after a failure or from having to wait to have the subscriber renamed to the publisher instance name. The client application only has to worry about the NLB cluster name for its connection. Using NLB within a high availability topology was described in Chapter 5, “Microsoft SQL Server Clustering.”