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

SQL Server 2000 - Merge Replication Step by Step Procedure

Rate me:
Please Sign up or sign in to vote.
4.52/5 (31 votes)
16 Dec 20049 min read 325K   98   35
Merge replication step by step procedure for SQL Server 2000.

Introduction

Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of the 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.

Microsoft SQL Server replication uses publisher, distributor and subscriber entities.

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. Publisher contains publication(s).

Subscriber is a server that receives and maintains the published data. Modifications to the data at 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 remote distributor and the other one local distributor. Remote distributor is separate from publisher and is configured as distributor for replication. Local distributor is a server that is configured as publisher and distributor.

Agents are the processes that are responsible for copying and distributing data between publisher and subscriber. There are different types of agents supporting different types of replication.

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 copy of data or database objects to be replicated.

Types of Subscription:

Changes to the subscriptions at the publisher can be replicated to subscribers via PUSH subscription or PULL subscription. With Push subscription, the publisher is responsible for synchronizing all the changes to the subscriber without subscriber asking for those changes. With Pull subscription, the subscriber initiates the replication instead of the publisher.

Replication Types

Microsoft SQL Server 2000 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 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. Conflict resolver can be customized.

Necessary steps to be taken before doing replication process:

  1. Before starting the replication process, change the log on account for the MSSQLSERVER service as “This account”. Use any SQL login account which is a member of sysadmin server role. Please see the screenshot for more information. Don’t forget to restart the MSSQLSERVER service.

    Image 1

  2. Adequate disk space should be allocated for publisher, distribution and subscriber’s databases.
  3. Use NOT FOR REPLICATION option when defining Identity columns.

Step by Step Procedure for Merge Replication setup

  1. Open SQL Server Enterprise Manager and select Tools menu -> Replication -> Configure Publishing, Subscribers, and Distribution…
    1. Configure the appropriate server as publisher or distributor.

      Image 2

    2. Enable the appropriate database for merge replication.

      Image 3

    3. Enable the appropriate server as subscriber.

      Image 4

  2. Open SQL Server Enterprise Manager and select the appropriate SQL Server Group for which replication needs to be done, then select Tools menu -> Replication -> Create and Manage Publications.

    Image 5

  3. This will open a dialog box for “Create and Manage Publications on respective server”. Select the appropriate database and then click “Create Publication”. This will open “Create Publication Wizard”. Just click Next.

    Image 6

  4. It will ask to choose a Distributor for the selected server. Select “Make Server its own Distributor; SQL Server will create a distribution database and a log”. Then click Next.

    Image 7

  5. It will ask for the Snapshot folder path. Browse and select the appropriate path for Snapshot folder and then click Next.

    Note: Create one folder in the Publisher machine and share the folder, then give full permissions for the user through which you logged in. Make sure that you are able to access this folder from the Subscriber machine also. If you are not able to access, give full permissions to that shared folder for the appropriate user in the Publisher machine. The Snapshot folder should be in the Publisher machine.

    Image 8

  6. Choose the database which you want to publish and Click Next.

    Image 9

  7. Select the Publication Type as “Merge Publication”.

    Image 10

  8. Specify the Subscriber Types. Select “Servers running SQL Server 2000”. Then click Next.

    Image 11

  9. Select the Object Types (like Tables, Stored Procedures and Views) which you want to publish, and click Next.

    Image 12

  10. It will show some issues which may require some changes at later stages in order to work as expected. Just click Next.

    Image 13

  11. Give Publication Name and click Next.

    Image 14

  12. It will ask to customize the properties of the Publication. Select “Yes, I will define data filters, enable anonymous subscriptions, or customize other properties”. Then click Next.

    Image 15

  13. Then, it will ask “How do you want to filter this publication?” Don’t select any thing here. Just click Next.

    Image 16

  14. Then, it will ask “Whether you want to allow anonymous subscription to this publication?”. Select “No, allow only named subscriptions”, and click Next.

    Image 17

  15. It will show “Set Snapshot Agent Schedule” dialog box. Change the Snapshot Agent Schedule as per your requirement, then select “Create the first snapshot immediately”. And click Next.

    Image 18

  16. Click Finish to create a Publication.

    Image 19

  17. Finally, it will show “SQL Server Enterprise Manager successfully created publication ‘pub1’ from database ‘db1’. Just click Close.

    Image 20

    Image 21

  18. It will show the dialog box “Create and Manage Publications on respective Server”. Now go to the respective created Publication and click “Push New Subscription”.

    Image 22

  19. Before doing “Push New Subscription”, create new SQL Server Registration for Subscriber machine in the Publisher machine’s SQL Server Enterprise manager with SQL Authentication mode. For this, there should be one common SQL login name in both Publisher and Subscriber machines. Set server roles for this user as System Administrator, Process Administrator and Bulk Insert Administrators, and give database access to the respective database for which you want to perform replication.
  20. Go to “Push New Subscription” wizard. This will open “Push Subscription Wizard”. Just Click Next.

    Image 23

  21. Choose one or more subscribers from Enabled Subscribers and click Next. (Note: It will show the Subscriber’s SQL Server name under Enable Subscribers only if you do step 19.)

    Image 24

  22. Choose Subscription (destination) database name by browsing and clicking Next. (Note: You can create new database if you want by clicking Create New).

    Image 25

  23. “Set Merge Agent Schedule”. Change the Schedule as per your requirement and click Next.

    Image 26

  24. Specify whether the Subscription(s) needs to be initialized or not. Select “Yes, initialize the schema and data” as well as select “Start the Snapshot Agent to begin the initialization process immediately”, and click Next.

    Image 27

  25. “Set Subscription Priority” as “Use the Publisher as a proxy for the Subscriber when resolving conflicts”, and click Next.

    Image 28

  26. It will show the status of the SQLSERVERAGENT service as running. Just click Next.

    Image 29

  27. Click Finish to complete the Push Subscription.

    Image 30

  28. Finally, it will show “Subscriptions were created successfully at the following Subscribers:”. Just click Close.

    Image 31

  29. Now, in the SQL Server Enterprise Manager, go to the appropriate SQL Server Group and go to “Replication Monitor -> Publishers -> Respective Server -> Publication Name”. In the right pane, you will see the snapshot agent. Just right click and select “Start Agent”. Refresh it once. Then right click on the respective publication name and select “Start Synchronizing”. It will merge the necessary data. Refresh it once.

Important Note:

SQL Server 2000 replication will not support full-text indexing. But, enable full-text indexing at the subscriber machine manually. This can be done by Full-text indexing wizard. Select the appropriate table and enable the required fields in that table as full-text indexed. Then, create a new catalog or else use the existing catalog and schedule it, if needed. Once this is done, go to that particular catalog and right click and select “Start full population”. The status will be displayed as “population in progress”.

Advantages in Replication:

Users can avail the following advantages by using 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.

Conclusion

In a nutshell, replication is the capability to reliably duplicate data from a source database to one or more destination databases. SQL Server 2000 gives you the power for replication design, implementation, monitoring, and administration. This gives you the functionality and flexibility needed for distributing copy of data and maintaining data consistency among the distributed. You can automatically distribute data from one SQL Server to many different SQL Servers through ODBC (Open Database Connectivity) or OLE DB. SQL Server replication provides update replication capabilities such as Immediate Updating Subscribers and merges replication. With all the new enhancements to SQL Server replication, the number of possible applications and business scenarios is mind-boggling.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
United States United States
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 3 Pin
fareedulhassan19-Oct-11 0:02
professionalfareedulhassan19-Oct-11 0:02 
Generalreplication between different servers Pin
Member 779939830-Mar-11 8:19
Member 779939830-Mar-11 8:19 
Generalmerge replication Pin
ershad1210-Oct-10 23:45
ershad1210-Oct-10 23:45 
GeneralReplication Pin
Nazrul22-Oct-09 4:37
Nazrul22-Oct-09 4:37 
Question[My vote of 1] pls help Pin
syriawimax10-Mar-09 7:16
syriawimax10-Mar-09 7:16 
QuestionSql Server replication Error URGENT HELP! Pin
baridi21-Jan-09 0:27
baridi21-Jan-09 0:27 
GeneralThank you + "An Introduction to Troubleshooting SQL Server 2000 Transactional Replication" Pin
LageB13-Oct-08 3:31
LageB13-Oct-08 3:31 
GeneralSQL Server 2005 - Merge Replication Step by Step Procedure Pin
Ahmad Eid Salim20-Aug-08 1:30
Ahmad Eid Salim20-Aug-08 1:30 
GeneralThanks Pin
Ahmad Eid Salim18-Aug-08 23:08
Ahmad Eid Salim18-Aug-08 23:08 
QuestionError when replication on WAN. Pin
NguyenDuy8412-Jun-08 18:57
NguyenDuy8412-Jun-08 18:57 
Generalchanging table structure on the publisher Pin
rfalagan22-Feb-08 3:46
rfalagan22-Feb-08 3:46 
GeneralRe: changing table structure on the publisher Pin
devriesnl21-Mar-08 0:19
devriesnl21-Mar-08 0:19 
AnswerRe: changing table structure on the publisher Pin
multisan6-May-08 22:48
multisan6-May-08 22:48 
Questionmerging to database Pin
Sankar KR24-Oct-07 18:58
Sankar KR24-Oct-07 18:58 
QuestionSQL Server 2005 Pin
woaksie2-Oct-07 7:28
woaksie2-Oct-07 7:28 
QuestionCan i apply any conditon before publishing the data? Pin
Aamir Dharani2-May-07 1:02
Aamir Dharani2-May-07 1:02 
Generalproblem with romote server Pin
sriaksharaya10-Jan-07 22:37
sriaksharaya10-Jan-07 22:37 
Questioncontinuous replication Pin
jasuja12-Dec-06 19:47
jasuja12-Dec-06 19:47 
Generalthank you my frind Pin
mostshar.com28-Oct-06 22:16
mostshar.com28-Oct-06 22:16 
GeneralThe process could not connect to Subscriber 'REMOTE-SERVER'. Pin
Kamal Hemajith18-Oct-06 20:28
Kamal Hemajith18-Oct-06 20:28 
GeneralMerger Replication Pin
kcurpen12-Oct-06 21:38
kcurpen12-Oct-06 21:38 
GeneralSetting up Merge Replication Pin
kcurpen12-Oct-06 21:35
kcurpen12-Oct-06 21:35 
GeneralAgents for Merge Replication Pin
FDBA16-Aug-06 7:30
FDBA16-Aug-06 7:30 
QuestionCan a publisher be made a subscriber? Pin
shelly kalra27-Jun-06 4:20
shelly kalra27-Jun-06 4:20 
QuestionPlagiarism? Pin
KKam17-Mar-06 7:58
KKam17-Mar-06 7:58 

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.