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
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, its
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.
So just what exactly is replication? Simply put, replication is nothing
more than the moving of data from one database to another. Youll
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
More SQL Insight?
This column was originally
published in our SQL Pro Insight newsletter. To subscribe,
-- 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.
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.