Real World DBA Episode 9 – SQL Server Features – SQL Server Replication  


This week:

In the news, there’s a new way to read SQL Server Documentation. In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about SQL Server Replication. The web link shows you how Microsoft used Service Broker to put together MSDN and this week’s tip is on SQL Server Services Accounts.


News this week:

Microsoft’s MSDN Network now includes “tags” for the MSDN site, which allows you to apply your own meta-data to the content. Since SQL Server Books Online is hosted on the MSDN site, you can, in effect, now create your own “table of contents”. Find out more here: 



In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first.

This week we’ll take a look at SQL Server Replication.


SQL Server Replication is a feature that, similar to SQL Server Clustering and Database Mirroring, copies data from one place to another. You could use this feature to make your systems more available, but with Replication you can copy a subset of data from one system to multiple other systems, on different platforms (such as Oracle and Microsoft Access), over longer distances, and on various schedules. So most of the time when you take advantage of this feature you’ll use it to copy data, not to make your systems more available, although that it certainly an option.


Replication uses a “Publisher” and “Subscriber” methodology. What that means is that one system “Publishes” a set of data, and one or more systems “Subscribe”, or ask for that data. This process can be pushed from the Publisher to the Subscribers, or the Subscribers can ask for the data, either on demand or on a schedule. Let’s take a look at the entire process, and along the way I’ll mention the systems and features that are involved in transferring the data. The process I’ll describe is the logical flow – as the wizard guides you through the setup, you’ll actually create certain features in a different order than I’ll describe here. Don’t let that confuse you – this is just an overview, so you still have a little study to do before you set up your replication.


To begin, you run a Wizard that prepares your installation and the databases that you want to replicate. In essence, this process creates the jobs, files and database objects needed to perform the data transfer. Three server types are involved: The Publisher, which contains the data to send, the Distributor which is responsible to hold and transfer the data, and the Subscriber, which receives the data. The hardware and software systems I’m describing can exist on one physical server, although normally these functions are broken out multiple systems.


At the lowest level of the process is the Article. An article is a table, a view or a stored procedure with Transact-SQL in it that represents the data you want to transfer. A wizard will guide you through the creation of your Articles after the system is prepared for replication.


The next level in replication is the Publication. This is the collection of Articles that you want to send to a Subscriber. Subscribers always ask for Publications, not for Articles, although you could set up a Publication with one Article in it.


The next level is the Subscription. The Subscription is set up to allow another SQL Server to receive the Publication. The Subscription can be “pushed”, meaning that it is the responsibility of the Publishing system to move the data, or “pulled”, meaning it is up to the Subscriber system to ask for the data. Push Subscriptions are most often used in constantly-connected systems, such as server to server, and Pull Subscriptions are often used when the systems are not always connected to each other, such as a salespersons laptop. Pull Subscriptions can be on-demand or at a scheduled time, and Push Subscriptions can work from a schedule, manually, or whenever data changes.


When you transfer data, you can move only the changes in the Publication, called Transactional Replication. You can also transfer the entire Publication, called Snapshot Replication. You can also transfer changes from the Publisher to the Subscriber, and changes from the Subscriber back to the Publisher, which is called Merge Replication.


So that you understand the power you have with this feature, you  can Publish from one system to multiple Subscribers, or one Subscriber can get data from multiple Publishers, and many mixes in between.


Your Replication Topology, schedules and strategies are highly dependent on what your connection methods are and what you need to accomplish with it. You can read more at:



Web Link:

You may have noticed that the content on the Microsoft Technical and Developer Network sites have changed. to aggregate the hundreds of thousands of documents they present, the team at Microsoft responsible for these sites used the Service Broker in SQL Server 2005.  It’s a fascinating study, and you can read it at


Tip of the Week:

SQL Server setup automatically configures the service account or accounts with the specific permissions required by SQL Server. When modifying or configuring the Windows services used by SQL Server 2005, you should grant only the permissions they require. For more information, see Setting Up Windows Service Accounts.

Skip to main content