Tip: Understand the Replication Models in SQL Server 2008

The architecture for the replication process is extensive. This ensures that the architecture is versatile enough to meet the needs of almost any replication situation. Unfortunately, this versatility also makes replication tricky to configure.

To make the replication go smoothly, you should do a bit of planning, which involves selecting a specific replication model and performing any necessary preliminary tasks before you start configuring replication.

The main decision to make when you select a replication model involves the physical layout of the publisher, distributor, and subscriber databases. Common replication models that you might want to use include:
Peer-to-peer model Allows replication between identical participants in the topology. The advantage of this model is that it permits roles to move between replicated nodes dynamically for maintenance or failure management. The disadvantage is the additional administration overhead involved with moving roles.
Central publisher model Maintains the publisher and distributor databases on the same server, with one or more subscribers configured on other servers. The advantages of this model are manageability and ease of maintenance. The disadvantages include the extra workload and resource usage on the publication server.
Central publisher with remote distributor model Maintains the publisher and distributor databases on different servers, with one or more subscribers configured on other servers. The advantage of this model is that the workload is more evenly distributed. The disadvantage is that you have to maintain an additional server.
Central subscriber model A single subscriber database that collects data from several publishers. For example, if you have ServerA, ServerB, and ServerC, ServerA and ServerB act as central publishers and ServerC acts as the central subscriber. In this configuration, when updates are distributed from ServerA and ServerB, they are collected on ServerC. A central subscriber can then republish the combined data to other servers. To use this model, all tables used in replication must have a unique primary key; otherwise, the replication model will not work properly.
Publishing subscriber model Relays the distribution of data to other subscribers; you can use this with any of the other models. For example, if you have two geographically separated sites, a publisher can replicate data to servers at site A and then have a publishing subscriber at site B that distributes the data to servers at site B.

The central publisher model is the most commonly used replication model. Unfortunately, you will often find that the extra load on the publication server will slow down server performance. To reduce the server load, you should put the distributor on its own server. Be aware, however, that doing this will not entirely eliminate the workload on the publication server. The publisher and distributor still need to communicate, and they still need to pass data back and forth.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.