Database Replication Basics

This chapter assumes you have at least a basic understanding of replication functionality. However, if you do not, I offer a very brief overview to get you started.

A Brief Overview of Replication

Today's data increasingly needs to be in multiple locations at the same time and the data must be kept synchronized. Database developers must consider scale-out requirements for performance and growth and rollup requirements for reporting and data warehousing when planning and building applications. Disconnected users, such as sales or service personnel, need to take data with them when they enter the field. Keeping this disparate data synchronized on an ongoing basis is a difficult task.

Fortunately, SQL Server offers powerful technology for replicating and synchronizing data. Database replication was introduced in SQL Server version 6.5, but SQL Server 2005 brings a new level of power, performance, and ease of use to this complex functionality.

Replication in SQL Server uses a "publish and subscribe" metaphor in its configuration. The Publisher is the server instance that originally has the data and objects to replicate; a publication is a logical grouping of objects in a database that you want to replicatetables, stored procedures, views, indexed views, and user-defined functions. Properties of the publication control how the objects are replicated. Depending on your needs, you can choose between three types of publications: snapshot, transactional, or merge. After the publication is created, you can create subscriptions in the locations where you want a copy of the published objects. Replication relies on SQL Server Agent jobs to create the objects at the Subscribers and keep them synchronized.

You can configure replication by using wizards and property sheets in SQL Server Management Studio, by programming to the Replication Management Objects (RMO) managed code assembly, or by calling system stored procedures from T-SQL scripts. Many customers use Management Studio for initial configuration and then generate a script for recovery and future automation. After the publications and subscriptions are configured, you can use the Replication Monitor to monitor ongoing replication activity and troubleshoot problems.

For a complete description of replication functionality and how to prepare your data and application to be replicated, see the topic "SQL Server Replication" in Books Online.

Initializing and Synchronizing Subscriptions

Although the replication wizards make it easy to create publications and subscriptions, the real work of replication (copying the database objects to the Subscribers and keeping the subscriptions synchronized on an ongoing basis) is performed by replication agents, which are usually called from SQL Server Agent job steps.

• Snapshot Agents are used for all publication types and run infrequently. They generate scripts that can create the published objects at a Subscriber, and they also take snapshots of the published data. The scripts and snapshots are typically used to initially synchronize or reinitialize subscriptions, although you can initialize the objects and data manually.
  
  
• Log Reader Agents are used for transactional publications and run continuously. They monitor the transaction log for changes to published objects.
  
  
• Distribution Agents are used for snapshot and transactional publications and typically run continuously. There is usually one agent per subscription. For snapshot publications, the agent keeps its subscription synchronized by applying snapshots intermittently. For transactional publications, the agent keeps its subscription synchronized by applying snapshots for initialization and by replicating commands processed by the Log Reader Agent.
  
  
• Merge Agents are used for merge publications only and typically run on a schedule or on demand. There is one agent per subscription, and the agent keeps its subscription synchronized by applying snapshots and merging any data that has been changed since the last synchronization.
  
  

After creating publications and subscriptions in the wizards, ensure that the agents are running and working together. Transactional publication agents generally work without intervention because they run continuously. Making sure that new merge subscriptions get their initial synchronization can be challenging because the Snapshot Agent must generate the snapshot files before the Merge Agent can apply the snapshot. If the Merge Agent runs before the Snapshot Agent finishes, the Merge Agent reports that the snapshot is not available and stops. You have to run the Merge Agent again after the Snapshot Agent finishes. If the Merge Agent is scheduled to run once per day, that will not happen until the following day, unless you start it manually. In the meantime, you may wonder why the subscription database has no data.

Management Studio provides tools for configuring replication, but provides minimal help for monitoring the activity of the agents. To track the agent activity, you need to use Replication Monitor. Replication Monitor is a separate application from Management Studio, but you can launch it from Management Studio by using the Launch Replication Monitor command on the context menus of nodes under the Replication folder in Object Explorer.

Tip

Management Studio includes a report that gives the current state of the Snapshot Agent and, for transactional publications, the current state of the Log Reader Agent. To see the report, select the publication in Object Explorer. In the Summary document window, click Reports and select General.

To see the status of the Snapshot Agent and start and stop it inside Management Studio, right-click on a publication in Object Explorer and click View Snapshot Agent Status. Management Studio displays a dialog box that shows whether the agent is running, the last message from the agent, and buttons to start or stop the agent.

To see the status of a Merge or Distribution Agent and to start and stop it inside Management Studio, right-click on a subscription in Object Explorer and click View Synchronization Status. Management Studio displays a similar dialog box to that for the Snapshot Agent.