Setting up Merge Replication in SQL Server 2005SQL Server 2005 - Merge Replication Step by Step Procedure

Click :Setting up Merge Replication in SQL Server 2000

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.

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 mergereplication, 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 bemerged 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.


1. Configure the appropriate server as the publisher or distributor.


2. Enable the appropriate database for merge replication.

2. Create a new local publication from DB-Server --> Replication --> Local Publications --> Right click --> New Pub.

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

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.

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.

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


and configure the security for the snapshot agent.

Finally, rename the publication and click Finish.


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


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

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

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 ReplicationMonitor.

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 filereplication, 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.

Setting up Merge Replication in SQL Server 2000
SQL Server 2000 - Merge Replication Step by Step Procedure

Click :Setting up Merge Replication in SQL Server 2005
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 containspublication(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 mergereplication, 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 bemerged 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 replicationprocess:

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.
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.
   2. Enable the appropriate database for merge replication.
   3. Enable the appropriate server as subscriber.
2. Open SQL Server Enterprise Manager and select the appropriate SQL Server Group for which replicationneeds to be done, then select Tools menu -> Replication -> Create and Manage Publications.
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.
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.
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.
   
6. Choose the database which you want to publish and Click Next.
7. Select the Publication Type as “Merge Publication”.
8. Specify the Subscriber Types. Select “Servers running SQL Server 2000”. Then click Next.
9. Select the Object Types (like Tables, Stored Procedures and Views) which you want to publish, and click Next.
10. It will show some issues which may require some changes at later stages in order to work as expected. Just clickNext.
11. Give Publication Name and click Next.
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.
13. Then, it will ask “How do you want to filter this publication?” Don’t select any thing here. Just click Next.
14. Then, it will ask “Whether you want to allow anonymous subscription to this publication?”. Select “No, allow only named subscriptions”, and click Next.
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.
16. Click Finish to create a Publication.
17. Finally, it will show “SQL Server Enterprise Manager successfully created publication ‘pub1’ from database ‘db1’. Just click Close.
    
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”.
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.
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.)
22. Choose Subscription (destination) database name by browsing and clicking Next. (Note: You can create new database if you want by clicking Create New).
23. “Set Merge Agent Schedule”. Change the Schedule as per your requirement and click Next.
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.
25. “Set Subscription Priority” as “Use the Publisher as a proxy for the Subscriber when resolving conflicts”, and clickNext.
26. It will show the status of the SQLSERVERAGENT service as running. Just click Next.
27. Click Finish to complete the Push Subscription.
28. Finally, it will show “Subscriptions were created successfully at the following Subscribers:”. Just click Close.
29. Now, in the SQL Server Enterprise Manager, go to the appropriate SQL Server Group and go to “ReplicationMonitor -> 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 filereplication, 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 updatereplication 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.

Setting up Transactional Replication in SQL Server 2008 R2.

Click Part 1: SQL Server Replication Step by Step
Replication is one of the High Availability features available in SQL Server. Transactional Replication is used when DML or DDL schema changes performed on an object of a database on one server needs to be reflected on the database residing on another server. This change happens almost in real time (i.e. within seconds). In this article, I will demonstrate the step by step approach to configuring transactional replication in SQL Server 2008 R2.

Scenario: An Address table which belongs to the Person schema in the Adventureworks Database is replicated to the Adventureworks_Replication database residing on the same server. The Adventureworks_Replication database acts as a subscriber. The subscriber is normally present on a  separate database server.

Before we start with the configuration, we need to understand three important terms:

1.       Publisher
2.       Subscriber
3.       Distributor Database

Let’s discuss each these in detail.

Publisher:

The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.

Subscriber:

The Subscriberis the  database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.

Distribution Database:

A database which contains all the Replication commands. Whenever any DML or DDL schema changes are performed on the publisher, the corresponding commands generated by  SQL Server are stored in the Distribution database. This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance. Normally, I have observed that if you keep the distributoion database on the same machine as that of the publisher database and if there are many publishers then it always has an impact on the performance of the system. This is because for each publisher, one distrib.exe file gets created. 

Let us now begin with the Configuring of the Transactional Replication.

There are 3 steps involved for Configuring the Transactional Replication:

1.       Configuring the Distribution Database.

2.       Creating the publisher.

3.       Creating the subscriber.

1.Configuring the Distribution Database.

A database which contains all the Replication commands. Whenever any DML or DDL schema changes are performed on the publisher, the corresponding commands generated by  SQL Server are stored in the Distribution database. This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance. Normally, I have observed that if you keep the distributoion database on the same machine as that of the publisher database and if there are many publishers then it always has an impact on the performance of the system. This is because for each publisher, one distrib.exe file gets created. 

2.Creating the publisher.

The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.

3.Creating the subscriber.

The Subscribers the database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.

How it works

Transactional replication is implemented by the Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent 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 on the Distributor.

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. The Distribution Agent moves the initial snapshot jobs and the transactions held in the distribution database tables to Subscribers.

Configuring the Distribution Database

1.       Connect to the Microsoft SQL Server 2008 R2 Management Studio.

2.        Right Click on the Replication node and Select Configure Distribution as shown in the screen capture below:

3.       A new window appears on the screen as shown in the screen capture below:

4.       Click  the Next> button and a new window appears on the screen as shown in the screen capture below:

5.       As you can see in the above screen capture, it gives the user two choices. The first choice says that whether the server on which the Replication will be configured will be Hosting the distribution database. The second choice asks the user whether some other server will be Hosting the distribution database. The user can select any one of the either choices are per his/her requirements. I decide to use the First option, i.e. the server on which the Replication is configured will itself be holding the distribution database. Then Click on the Next> button as shown in the screen capture above.

6.       A new window appears as shown in the screen capture below:

7.       Select the first option, i.e. Yes, configure the SQL Server Agent service to start automatically and click on the Next> button as shown in the screen capture above.

8.       A new window appears on the screen as shown in the screen capture below:

As you can see in the above screen capture, you are asked where the Snapshot folder should reside on the Server. Let us first understand what the Snapshot folder exactly is.

The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder. This folder should never be placed on the C drive of the server i.e. the drive which is hosting the Operating System.

Create a folder on any other drive to hold the Snapshot folder and Click on the Next> button as shown in the screen capture above.

9.       A new window appears as shown in the screen capture below:

As you can see in the above screen capture, it displays information such as what will be the distribution database name, the location where the data and the log file will reside. Click on the Next> button as shown in the screen capture above.

10.   A new window appears as shown in the screen capture below:

11.   Click on the Next> button.

12.   Click on the Next> button as shown in the screen capture below:

  

13.   Click on the Finish button as shown in the screen capture below:

14.   Once done, a new database named distribution gets created. In order to confirm it just expand the System Database node and you shall be able to view the distribution database, please refer the screen capture below:

         

Creating the Publisher

The following steps need to be followed while creating the publisher.

1.      Right Click on Local Publications and select New Publications, please refer the screen capture below:

2.      Click on the Next> button as shown in the screen capture below.

3.      Select the database which is going to act as a publisher. In our case, I select the AdventureWorks database. Please refer the screen capture below and Click on the Next> button.

4.      Select Transactional Replication from the available publication type and Click on the Next> button as shown in the screen capture below:

5.      Select the Objects that you want to publish. In this example, we will select a table named Person which we need to Replicate. Select the table as shown in the screen capture below and Click on the Next> button. One important point to note is that Only those tables can be replicated in Transaction Replication which has a Primary Key column in it.

6.      Since there are no filtering conditions, Click on the Next> button as shown in the screen capture below:

  

7.      Check the first radio button as shown in the screen capture below and Click on the Next> button.

8.      Click on the Security Settings tab as shown in the screen capture below.

A new window appears as shown in the screen capture below.

Select Run under the SQL Server Agent service account as the account under which the Snapshot Agent process will run and Connect to the publisher By impersonating the process account as shown in the screen capture below and then Click on the OK button.

Click on the Next> button as shown in the screen capture below.

9.      Click on the Next> button as shown in the screen capture below.

 

10.  Give a suitable name to the publisher ad Click on the Finish button as shown in the screen capture below.

Creating the Subscriber

Once the publisher is created the next step is to create the subscriber for it.

The following steps needs to be performed for creating the subscriber.

1.       Right Click on the publisher created and select New Subscriptions as shown in the screen capture below.

2.       Click on the Next> button as shown in the screen capture below.

3.       Click on the Next>  button as shown in the screen capture below.

4.       Click on the Next> button as shown in the screen capture below.

5.       As shown in the screen capture below, it asks for the Subscriber name as well as the subscription database. The subscriber database can be created by restoring the publisher database at the start itself or by creating a new database as shown in the screen capture below.

If you have already restored the backup of the database which is a publisher, then the database name will appear in the dropdown as shown in the screen capture below:

If we wan’t to now create the subscriber database then it can be done as follows:

Click on New Database as shown in the screen capture below.

A new window appears as shown below. Give a suitable database name as well as the path where the data and log file are going to reside.

Click on the OK button.

If the subscriber is some other server, then the following steps need to be performed.

Click on the down arrow available on the Add Subscriber button as shown in the screen capture below.

Click on Add SQL Server Subscriber as shown in the screen capture above.

A new window appears which asks for the SQL Server Name as well as the Authentication neeed to connect to the SQL Server, please refer the screen capture below.

6.       Click on the Next> button as shown in the screen capture below.

                      

7.       Click on the button as shown in the screen capture below. Here we need to specify              

     Process account as well as the connection options for the distribution agent.

     

8.        A new window appears as shown in the screen capture below.

   

9.       Specify the distribution agent to run under the SQL Server Agent Service Account. Also connect to the distributor as well as the subscriber by impersonating the process account. Please refer the screen capture below.

10.   Click on the OK button as shown in the screen capture above.

11.   Click on the Next> button as shown in the screen capture below.

12.   Ensure that the Agent is scheduled to Run Continuously and then click on the Next> button as shown in the screen capture below.

13.   Ensure that the Subscriber is initialized immediately and then click on the Next> button as shown in the screen capture below.

14.   Click on the Next> button as shown in the screen capture below.

15.   Click on the Finish button as shown in the screen capture below.

16.   This creates a subscriber for the corresponding publisher.

17.   Expand the publisher node and you shall be able to view the subscriber as shown in the screen capture

Thus, we have successfully set the Transactional Replication in SQL Server 2008 R2.