Introduction to Database Replication

• By Baya Dewald
• Mar 5, 2004

Like this article? We recommend
Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

As it relates to SQL Server, replication is a way of keeping data synchronized in multiple databases. Implementing and maintaining replication might not be a simple proposition: If you have numerous database servers that need to be involved in various types of replication, a simple task can quickly become complex. Implementing replication can also be complicated by the application architecture.

Microsoft SQL Server has supported replication since version 6.0, and setting up replication has become significantly easier over the years (in fact, 99 percent of replication setup can be accomplished by clicking through replication wizards). However, replication involves much more than setup, and unfortunately there aren't many sources of information for implementing and troubleshooting it. The only way to learn replication is to dig through the knowledge base articles and figure things out on your own.

In this series of articles, I hope to fill in some of these gaps in information. This first article introduces you to replication types and concepts. Future articles examine setup, maintenance, and troubleshooting steps for transactional replication.

Replication Terminology

SQL Server replication is commonly described by using the publisher/subscriber metaphor. A database server that makes data available for replication (source server) is referred to as the publisher; a collection of one or more database objects that are enabled for replication is called a publication. SQL Server supports replicating tables, views, stored procedures, and user-defined functions.

One or more servers that get data and/or transactions from the publisher are called subscribers. Replication is managed by the system database, which by default is called distribution. A distribution database—which can reside on the publisher, subscriber, or on a separate server—is created when you configure replication.

The server that hosts the distribution database is referred to as the distribution server or distributor.

Each database server can act as a publisher and subscriber at the same time. Each publisher can have multiple subscribers, and each subscriber can receive transactions from multiple publishers.

You should also become familiar with replication agents, which are implemented as SQL Server jobs that perform a particular task according to their schedule.