Demystifying Replication

SQL Server replication questions abound. So, let's take a closer look at what it is and how to do it properly.

• By Eric Johnson
• 12/12/2007

One of the biggest mysteries of SQL Server seems to be replication. I receive more calls from clients asking for help with setting up or fixing replication than I do for just about any other SQL Server component. The good news is that while replication has a lot of moving parts, it’s really not all that complicated.

In this short, two-part series, we'll take a quick look at replication in SQL Server to give you a better understanding of how it works, first with an examination of what it is and next time, how to set up a simple transactional replication schema.

Replication Defined
So just what exactly is replication? Simply put, replication is nothing more than the moving of data from one database to another. You’ll notice that I did not say one server to another -- that's because data can be moved within the confines of a single SQL Server. Before we get into the details, you will need to understand the roles that a server can play in a replication scheme.

When replicating data, each SQL Server will have a role in the replication topology. As we talk about these, keep in mind that when I say server, I mean instance of SQL Server and that each instance can be performing one or all of these roles simultaneously.

Publisher - A publisher is a server that contains one or databases that have been set up to share their data via replication. The data being shared is known as a publication, which is made up of a series of articles. Articles are simply database objects, such as tables or views, which have had some portion of their rows and columns shared.

Subscriber - Subscribers refers to the server that subscribes to the publications on the publishers -- that is, servers which received copies of the data being shared by the publisher. It helps to think of replication in terms of a newspaper when you think of a subscriber and publisher here.

Want More SQL Insight? This column was originally published in our SQL Pro Insight newsletter. To subscribe, click here.

Distributor -- The distributor is the server whose responsibility it is to receive data from the publishers and provide it to the subscribers. Remember, servers can perform multiple roles, so your distributor can be on the same physical machine as your publisher.

Now you should have an understanding of what replication is and how the servers interact in a replication scheme. Next time, we'll explore the different types of replication that are available in SQL Server.