Click here to Skip to main content
15,867,874 members
Articles / Database Development / SQL Server

SQL Server 2005 - Merge Replication Step by Step Procedure

Rate me:
Please Sign up or sign in to vote.
4.54/5 (23 votes)
30 Aug 2008CPOL6 min read 286.2K   76   18
Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users so that they can make changes to their local copy of database and later synchronize the changes to the source database.

Introduction

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users so that they can make changes to their local copy of the database and later synchronize the changes to the source database.

Terminologies before getting started

Microsoft SQL Server 2000 supports the following types of replication:

  • Publisher is a server that makes the data available for subscription to other servers. In addition to that, publisher also identifies what data has changed at the subscriber during the synchronizing process. The publisher contains publication(s).
  • Subscriber is a server that receives and maintains the published data. Modifications to the data at the subscriber can be propagated back to the publisher.
  • Distributor is the server that manages the flow of data through the replication system. Two types of distributors are present, one is the remote distributor and the other the local distributor. The remote distributor is separate from the publisher and is configured as the distributor for replication. The local distributor is a server that is configured as the publisher and distributor.
  • Agents are the processes that are responsible for copying and distributing data between the publisher and subscriber. There are different types of agents supporting different types of replication.
  • Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database.
  • An article can be any database object, like Tables (Column filtered or Row filtered), Views, Indexed Views, Stored Procedures, and User Defined Functions.
  • Publication is a collection of articles.
  • Subscription is a request for a copy of data or database objects to be replicated.

img01.png

Replication types

Microsoft SQL Server 2005 supports the following types of replication:

  • Snapshot replication
  • Transactional replication
  • Merge replication
Snapshot replication
  • Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time.
  • Subscribers are updated with the complete modified data and not by individual transactions, and are not continuous in nature.
  • This type is mostly used when the amount of data to be replicated is small and data/DB objects are static or does not change frequently.
Transactional replication
  • Transactional replication is also known as dynamic replication. In transactional replication, modifications to the publication at the publisher are propagated to the subscriber incrementally.
  • Publisher and the subscriber are always in synchronization and should always be connected.
  • This type is mostly used when subscribers always need the latest data for processing.
Merge replication

It allows making autonomous changes to replicated data on the Publisher and on the Subscriber. With merge replication, SQL Server captures all incremental data changes in the source and in the target databases, and reconciles conflicts according to rules you configure or using a custom resolver you create. Merge replication is best used when you want to support autonomous changes on the replicated data on the Publisher and on the Subscriber.

Replication agents involved in merge replication are snapshot agent and merge agent.

Implement merge replication if changes are made constantly at the publisher and subscribing servers, and must be merged in the end.

By default, the publisher wins all conflicts that it has with subscribers because it has the highest priority. The conflict resolver can be customized.

Before starting the replication process

Assume that we have two servers:

  • EGYPT-AEID: is the publisher server (contains HRatPublisher)
  • SPS: is the subscriber server (contains HRatSubscriber) use SQL Server Authentication mode for login

On the publisher database, I created a table Employees with the fields ID, Name, Salary, to replicate its data to the subscriber server. I will use the publisher as the subscriber also.

Note: Check that SQL Server Agent is running on the publisher and the subscriber.

Steps

  1. Open SQL Server Management Studio and login with SQL Server Authentication to configure Publishing, Subscribers, and Distribution.
  2. img02.png

    1. Configure the appropriate server as the publisher or distributor.
    2. Image 3

    3. Enable the appropriate database for merge replication.
    4. Image 4

  3. Create a new local publication from DB-Server --> Replication --> Local Publications --> Right click --> New Pub.
  4. Image 5

    Then choose the database that contains the data or objects you want to replicate.

    img06.png

    Choose the replication type and then specify the SQL Server versions that will be used by subscribers to that publication, like SQL Server 2005, SQL Mobile Edition, SQL for WinCE, etc.

    Image 7

    After that, manage the replication articles, data, and database objects by choosing the objects to be replicated.

    Note: you can manage the replication properties for the selected objects.

    Image 8

    Then add filters to the published tables to optimize performance and then configure the snapshot agent.

    img09.JPG

    img10.png

    and configure the security for the snapshot agent.

    Image 11

    Finally, rename the publication and click Finish.

    img12.png

  5. Create a new subscription for the created "MyPublication01" publication by right clicking on MyPublication01 --> New Subscription.
  6. Configure the "Merge Agent" for the replication on the subscriber database.

    Image 13

    Image 14

    Choose one or more subscriber databases. You can add new SQL Server subscribers.

    img15.png

    Then specify the Merge Agent security as mentioned above on "Agent Snapshot". And specify the synchronization schedule for each agent.

Schedules:

  • Run continuously: add schedule times to be auto run continuously
  • Run on demand only: manually run the synchronization

img16.png

and then next up to the final step, and click Finish.

You can check errors from the "Replication Monitor" by right clicking on Local Replication --> Launch Replication Monitor.

Advantages of Replication

Users can avail the following advantages by using a replication process:

  • Users working in different geographic locations can work with their local copy of data, thus allowing greater autonomy.
  • Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations.
  • You can automatically back up a database by keeping a replica on a different computer. Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online.
  • You can replicate a database on additional network servers and reassign users to balance the loads across those servers. You can also give users who need constant access to a database their own replica, thereby reducing the total network traffic.
  • Database-replication logs the selected database transactions to a set of internal replication-management tables, which can then be synchronized to the source database. Database replication is different from file replication, which essentially copies files.
Replication performance tuning tips
  • By distributing partitions of data to different subscribers.
  • When running SQL Server replication on a dedicated server, consider setting the minimum memory amount for SQL Server to use from the default value of 0 to a value closer to what SQL Server normally uses.
  • Don’t publish more data than you need. Try to use row filter and column filter options wherever possible as explained above.
  • Avoid creating triggers on tables that contain subscribed data.
  • Applications that are updated frequently are not good candidates for database replication.
  • For best performance, avoid replicating columns in your publications that include TEXT, NTEXT, or IMAGE data types.

Thanks to "D J Nagendra", I used his article about SQL Server 2000 replication from CodeProject.com view to build the SQL Server 2005 replication version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Egypt Egypt
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 974679818-Feb-15 10:01
Member 974679818-Feb-15 10:01 
QuestionReplication in SQL SERVER Pin
hemantwithu6-Mar-13 23:00
hemantwithu6-Mar-13 23:00 
Questionhelp Pin
Mohamad Shahmardan31-Jul-12 23:00
Mohamad Shahmardan31-Jul-12 23:00 
GeneralMy vote of 5 Pin
UrvishSuthar10-Apr-12 1:27
UrvishSuthar10-Apr-12 1:27 
QuestionAdvise needed for selecting replication type Pin
nik92882-Sep-11 0:11
nik92882-Sep-11 0:11 
GeneralMy vote of 4 Pin
Riyasktr27-Aug-11 22:12
Riyasktr27-Aug-11 22:12 
Questionreplication Pin
mishgan201022-Jun-11 0:07
mishgan201022-Jun-11 0:07 
GeneralMerge Replication - Two Servers Pin
Mike DeLitta21-Jan-11 1:41
Mike DeLitta21-Jan-11 1:41 
GeneralMerge Replication SQL 2005 Pin
Janice Fredericks30-Sep-10 6:10
Janice Fredericks30-Sep-10 6:10 
Generalusing replication under condition Pin
mano_meee12-Sep-10 8:27
mano_meee12-Sep-10 8:27 
GeneralSQL Server 2008 Merge Replication Pin
slcuster6-May-10 6:31
slcuster6-May-10 6:31 
QuestionConfigurataion of my web synchronization in sql server 2005 ? Pin
Tunisien8616-Mar-10 23:49
Tunisien8616-Mar-10 23:49 
GeneralMy vote of 1 Pin
inspace 200917-Jan-10 22:45
inspace 200917-Jan-10 22:45 
GeneralI have problem setting up replication remotely Pin
belzer30-Jun-09 6:58
belzer30-Jun-09 6:58 
QuestionColumn Filter Pin
Burak Donbay3-Dec-08 22:40
Burak Donbay3-Dec-08 22:40 
QuestionSQL Server 2005 Replication implementation will change the publisher database? Pin
Williams Wei7-Oct-08 18:45
Williams Wei7-Oct-08 18:45 
QuestionNew Publication? Pin
Ian Uy2-Sep-08 16:18
Ian Uy2-Sep-08 16:18 
AnswerRe: New Publication? Pin
Petros Amiridis2-Sep-08 22:56
Petros Amiridis2-Sep-08 22:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.