Setting up Replication in SQL Server 2008 R2

Replication is one of the accessibility features available in SQL Server. And it allows database administrators to distribute data to various servers throughout an organization.

In this article, I will demonstrate the step by step approach to configuring replication in SQL Server 2008R2 Before we start with the configuration, we need to understand three important terms:

• Publisher- Publishers have data to offer to other servers. Any given replication scheme may have one or more publishers.

• Subscriber- Subscribers are database servers that wish to receive updates from the Publisher when data is modified. The subscriber database normally resides on a different server in another location.

• Distribution Database- A database that contains all the Replication commands database can reside on the same server as the publisher, but it is always recommended to keep them on a separate server for better performance. Normally, when you hold the distribution database on the same machine as the publisher database if there are many publishers and publishers are always an impact on the performance of the system. This is because, for each publisher, one distrib.exe file is created.

Microsoft SQL Server supports the following three types of database replication

Merge replication: 

The Merge replication is used when both Publisher and Subscriber need to make changes to their personal databases. In this case both databases might have been changed between runs of the Merge SQL replication, and the replication merges the changes in both locations. Of course when using merge replication you should be aware that there might be conflicts, for example duplicated primary keys. If there is a conflict, then the merge replication follows predetermined conflict resolution plan to correct the issue. 

Transactional replication:

The Transactional replication is usually used with databases where data changes frequently and there’s need for constant refreshing of the data. The replication process watches the publisher’s database for any changes, if there are changes; it distributes them over to the replication subscribers.

Snapshot replication:

Snapshot replication acts in the manner its name implies. The publisher simply takes a snapshot of the entire replicated database and shares it with the subscribers. Of course, this is a very time and resource-intensive process. For this reason, most administrators don’t use snapshot replication on a recurring basis for databases that change frequently. There are two scenarios where snapshot replication is commonly used. First, it is used for databases that rarely change. Second, it is used to set a baseline to establish replication between systems while future updates are propagated using transactional or merge replication.