Understanding Replication Types

Snapshot, transactional and merge -- your replication schema will fall under one of these methods, so choose wisely.

• By Eric Johnson
• 01/09/2008

If you are not familiar with replication or the roles of replication servers, take a minute and read the first part of this series. Go on ... I'll wait. Ready? Okay, you should be done now. Let's go.

When dealing with replication in SQL Server, you will encounter one of three types of replication, snapshot, transactional or merge. There are options that change how each of these types behaves, but your schema will be some form of these three types.

Snapshot replication -- This will copy all of the data in the publication to the subscribers. Beyond the snapshot, there is no mechanism to keep the data in sync. The publisher and the subscribers will be synchronized as of the snapshot. If you want to bring a subscriber up to date, you will need to redo the entire snapshot. Each of the other replications uses a snapshot replication to do the initial synchronization of the data.

Transactional Replication -- Probably the most widely used type of replication, transactional replication allows the subscribers to be kept in sync with the publisher by sending all the new transactions. You start by syncing the publisher and the subscriber via a snapshot. Then, an agent, called the LogReader agent, will watch for and record transactions that occur on the publisher. These changes are written to the distributor. Then the Distribution Agent gets the changes from the distributor and updates the subscriber. There are also additional options for allowing the subscribers to update the publisher (known as updating subscribers) and a transactional scheme where all the servers are both publishers and subscribers of the same data (known as peer-to-peer). These are a little beyond the basics, but if you hear about these types, know that they are both subsets of transactional replication.

Merge Replication -- Using merge replication, you can have all the changes made on both the publisher and the subscriber sent back to the other server. This allows multiple copies of the data to be in use and in sync at the same time.

Which type of replication you choose will differ depending on the situation. Now that you have a general understanding of each of these types, you probably want to know how to set up replication.

Yes, I'll cover that next time and walk you through the setup and configuration of a transactional replication.