Microsoft SQL Server supports the following types of replication:

  • Snapshot replication
  • Transactional replication
  • Merge replication

Merge replication 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. Implement merge replication if changes are made constantly at the publisher and subscribing servers, and must be merged in the end.

Replication agents involved in merge replication are snapshot agent and merge agent. By default, the publisher wins all conflicts that it has with subscribers because it has the highest priority. The conflict resolver can be customized.

In this article, we will provide the instruction of how to set up merge replication.

Architecture

We have two servers:

  • WIN-ADN7QEFVG8T: is the publisher server
  • MSSQL: is the subscriber server 
 

On the publisher server, a DualShield database called "Deepnet_DAS" has already been created by the DualShield server. 

Preparation

 On the publisher server, log into SQL Server Management Studio.

  1. Connect to both the publisher & subscriber server
  2. Check that SQL Server Agent is running on the publisher server and the subscriber server.
  3. Select the publisher server, check the size of 'log' and 'log_field' tables in the DualShield database, i.e. 'Deepnet_DAS'. If these 2 tables are large in size, truncate them (so that the initial replication process will be quick)

Create Publication

  1. Select "Replication > Local Publication" on the publisher server. Right click "Local Replication"

2. Select "New Publication" from the menu to start the Publication Wizard

3. Click Next to continue

4. Select the DualShield database, i.e. Deepnet_DAS, then click Next to continue

5. Select "Merge publication", then click Next to continue

6. Select the appropriate option, e.g. "SQL Server 2008 or later", then click Next to continue

7. Select all tables, then click Next to continue

8. Click Next to continue

9. Click Next to continue

10. Select "Create a snapshot immediately", then click Next to continue

11. Click "Security Settings..." button

12. Select the appropriate options as shown above, then click OK to continue

13. Select "Create the publication". Optionally, select "Generate a script file with steps to create the publication" if you want to keep a copy of the script. Then, click Next to continue  

14. If you selected "Generate a script file with steps to create the publication", then provide the file name save the script. Then, click Next to continue  

15. Provide a name of the publication, e.g. "Deepnet DAS". Then, click Finish to complete the wizard

16. Wait until all tasks are completed successfully, as shown above.

The snapshot agent needs to create the first snapshot for replication, which might take time depending on the size of the database. We need to check the status of the snapshot agent first

17. Under "Replication > Local Publications" a new publication is added, i.e. "Deepnet DAS". Right click it to bring up its menu

18. Select "View Snapshot Agent Status"

19. Wait until it finishes

Now, we are ready to create subscriptions. 

Create Subscription

  1.  Select "Replication > Local Publication > Deepnet_DAS", then right click it to bring up its menu

2. Select "New Subscription" to start the subscription wizard

3. Click Next to continue

4. Select the publisher server, e.g. "WIN-ADN7QEFVG8T" which is the publisher server in this tutorial. Select the publication to which the subscription will subscribe, e.g "Deepnet DAS" 

5. Select "Run all agents at the Distributor", then click Next to continue

6. Click "Add SQL Server Subscriber" button

7. In the Server Name field, enter or select the subscriber server name, e.g. "MSSQL" in this example. Provide the authentication & login details, then click "Connect" to continue

8. The subscriber name, e.g. "MSSQL", is now listed. Select it and select the subscription database, e.g. "Deepnet_DAS". Click Next to continue

9. Now, click the "...." button to configure the Merge Agent security

10. Select the appropriate options as shown above. Then, click OK to continue

11. Now, click Next to continue

12. In Agent Schedule select "Run continuously", then click Next to continue 

13. In Initialize When select "Immediately",  then click Next to continue

14. In Subscription Type select "Server",  then click Next to continue

15. Select "Create the subscription(s)". Optionally, select "Generate a script file with steps to create the subscription(s)" if you want to keep a copy of the script. Then, click Next to continue 

16. If you selected "Generate a script file with steps to create the subscription(s)", then provide a file name to save the script. 

17. Review the settings, then click Finish to complete the subscription wizard

18. Wait until all tasks are completed successfully. Then, click "Close" to finish.

In the publisher server, a new subscription is now listed under "Replication > Local Replication > Deepnet DAS"

In the subscription server, a new database "Deepnett_DAS" has been created. The content of the database has also been replicated.

Replication Monitor

From the publisher server, navigate to "Replication > Local Publication > Deepnet_DAS", right click to launch the replication monitor:




References





  • No labels