Microsoft SQL Server 2000 High Availability

There are three words that should apply to any replication architecture: keep it simple. The more complex it is, the more difficult it is to maintain over the long term . In the event of a disaster (see the later section, Disaster Recovery with a Replicated Environment ), the simpler the topology, the easier it should be to recover. You should also test the normal operation of replication and simulate an actual switch to the replicated secondary, and, if applicable , a switch back to the original Publisher, prior to rolling out your replication architecture in a production environment.

The three obvious components that you are looking to protect are the Publisher, Distributor, and Subscriber. Of these three, the first two ”the Publisher and Distributor ”are the most important to protect. Without the Publisher, you have no source. Without the Distributor, you do not have anything to push the data. It is as simple as that.

Replication Agents

Before you can plan for your architecture you must understand which replication agents, the underlying components that initiate different actions of replication, are used by the different methods of replication (see Table 8-1).

Snapshot Agent

The Snapshot Agent is used with all models of replication. It prepares schema and initial data files of published tables and stored procedures, stores the snapshot files, and inserts information about initial synchronization in the distribution database. The Snapshot Agent typically runs at the Distributor.

There is one Snapshot Agent per publication. When you create a snapshot of data, there are physical files, or objects, associated with it, and it differs with the model of replication, as shown in Table 8-2. These objects vary in number depending on your replication configuration s publication and articles; there is no exact formula to calculate it. You can only get the exact count by testing. All forms of replication imply that a snapshot initiates them.

Once you create the initial snapshot, you should consider backing up the snapshot files if you want to use them to initiate replication again. You can either back up the snapshot folder itself or allow another execution of the snapshot agent to generate another snapshot. As part of a disaster recovery plan, you can use the old or the new snapshot files (depending on your strategy) for the publication prior to creating a new subscription or reinitializing an existing one.

Distribution Agent

The Distribution Agent is used with snapshot replication and transactional replication. It moves snapshot files and incremental changes held in the distribution database to Subscribers. The Distribution Agent typically runs at the Distributor for push subscriptions and at the Subscriber for pull subscriptions.

Log Reader Agent

The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database marked for transactional replication has one Log Reader Agent that runs on the Distributor and connects to the Publisher.

Merge Agent

The Merge Agent is used only with merge replication. It applies the initial snapshot at the Subscriber. It then moves and reconciles incremental data changes that occurred after the initial snapshot was created. Each merge subscription has its own Merge Agent that connects to and updates both Publisher and Subscriber. The Merge Agent typically runs at the Distributor for push subscriptions and at the Subscriber for pull subscriptions.

Queue Reader Agent

The Queue Reader Agent is used with snapshot or transactional replication when you select the Queued Updating Subscribers option. It moves transactions and reconciles those incremental changes from a Subscriber with those at the Publisher as needed. There is one Queue Reader Agent per published database and the Queue Reader Agent typically runs at the Distributor regardless of whether push or pull subscriptions are employed.

Scenario 1: Separate Publisher and Distributor

When you use transactional replication, you should not put the Publisher and Distributor on the same server, which is otherwise known as a Local Distributor (see Figure 8-1).

Figure 8-1: Local Distributor in a replication topology.

It is better to separate the Publisher and Distributor and create what is known as a Remote Distributor (see Figure 8-2). In fact, you can have more than one Distributor, but a Publisher can only talk to one Distributor. Multiple Publishers can share the same Distributor.

Figure 8-2: Remote Distributor in a replication topology.

Using a Remote Distributor has the following benefits:

• It can improve performance because it offloads work performed by the Replication Agents from the primary computer.

• It allows for the smoothest possible upgrade paths. SQL Server replication is designed to support replication among servers of differing versions. This can be an advantage because you can upgrade one computer at a time, redirecting client connections to a replica during the upgrade process. Users achieve an aggregate data-access availability that might not be possible in a nonreplicated scenario. You can also do this with merge and transactional replication with queued updating even if the Publisher and Distributor are on the same machine, but the best scenario is if they are separated.

• One SQL Server with both Publisher and Distributor roles is removed as a single point of failure. Although the Publisher and Distributors can still potentially be single points of failure, you are not creating a bigger problem with them on the same server.

• In a disaster recovery scenario, you might only be dealing with one server and one piece of functionality, not multiple problems. This approach helps to minimize single points of failure, and is a compelling reason to use a Remote Distributor.

Scenario 2: Using a Republisher

This scenario is not dissimilar to using a Remote Distributor. You still have a Publisher and a Distributor, but you then take a Subscriber and have it publish the data out to other servers. That Subscriber is then known as a Republisher, or a Publishing Subscriber (see Figure 8-3). In this case, the Republisher would act as its own Distributor, or you could even set up another one. This scenario would benefit you if your company has sites around the world. Because of network issues, it is easier and more cost-effective to have more local resources generating data to Subscribers. This also increases the availability of your solution so that even if the original Publisher or Distributor goes down, if there are changes to be propagated out to other Subscribers that are still in the Republisher, they will still get to the other Subscribers.

Figure 8-3: Using a Republisher.

SQL Server Service Packs and Replication

SQL Server service packs will definitely make an impact on your replication implementation. If you apply them wrongly, you might need to reinitialize your replicated environment or use the disaster recovery procedures detailed here. See the section Applying a SQL Server 2000 Service Pack in Chapter 13, Highly Available Upgrades, for more information on replication and SQL Server service packs. The order in which you apply the service pack to your servers participating in replication matters. First, you must update the Distributor; then comes the Publisher; and finally the Subscriber.

You can also use replication to help keep your database available during a service pack install, much like you can use log shipping. Switch to the standby, and allow updates to happen on the standby assuming that it can function as the primary in every way it needs to. Once the service pack is done on the Publisher, switch back and upgrade the Subscriber. This is only applicable after the Distributor upgrade, which might even be the same server as the Publisher.

Planning Disk Capacity for Replication

Replication makes an impact on your planning for your disk capacity (in addition to anything outlined in Chapter 4 and the rest of the book), which is an obvious concern for any highly available environment. You do not want to run out of disk space before you even implement your solution. Table 8-3 walks through the requirements for each model of replication.