Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SQL Server Merge Replication

Creating Database Merge Replication between 2 SQL Servers

This article provides information regarding some details related to creating Merge Replication between to SQL Server Databases and what change are made that might affect the normal operations as well as issues with removal of the Publisher and Subscriber. Following some Information, each step to create Merge Replication is also presented starting with a Publisher on a SQL Server 2014 database server and a Subscriber on a SQL Server 2012 database server. Finally, the Synchronization process using SQL Server Agent is shown as well as Deleting a Publication and Subscription from the databases.

General Merge Replication Information

Addition of the rowguid column to tables - The publisher (or source database) and the subscriber (or destination database) will both have an additional column added to all tables used in replication. This may impact any UDFs or Stored Procs that have implicit Inserts (a manner of coding without using explicit column names receiving the data being inserted). An example of this is shown below - while this code works without issue in the Source DB prior to replication, it will throw an error after the rowguid column has been added.

Rowguid Addition to Table impacts Implicit INSERT statement

Addition of Credentials - Note that new credentials are created for the replication process. If and when the the publisher and subscriber need to be deleted, the credentials are deleted as well. If the Credentials for the replication are Mapped to a SQL Login, the process of Deleting the Publisher and Subscriber will throw an Error and not allow the Deletion of either one. Going to the Login and checking the Mapped Credentials and deleting it from the Login will allow the Deletion of the Publisher & Subscriber without issue.

Publisher and Subscriber Credentials

Setting up the Publication in SQL Merge Replication

To begin, right click on the Local Publications folder under the Replication folder on the SQL Server (this one is using SQL Server 2014 as the Publisher)

Start a Local Publication for replication

This will start the New Publication Wizard where you will select the Database to be replicated.

New Publication Wizard

Select the Merge Publication Type and click Next.

Select Publication Type

The Subscriber is a SQL Server 2012 server, so the Subscriber Type to check is SQL Server 2008 or later. Click Next.

SQL Server Subscriber Type

In this instance of replication, we are only replicating table data to the Subscriber. Click the Tables check box in Articles and click Next.

Replication Articles

The Article Issues warning will appear letting you know that the Uniqueidentifier column (named rowguidid) will be added to all tables selected for Replication. Click Next.

SQL Replicatin Article Issues

Filter Table Rows Option - if any of the data requires filtering, clicking Add will start the wizard. Otherwise, click Next.

Filter Table Rows option

The Create Snapshot immediately and schedule Snapshot Agent boxes are checked by default. You may change the schedule for replication or uncheck it and change it later. Click Next.

SQL Snapshot Agent

Set up the Snapshot Agent Security step requires a Domain Login to set up the account to run the process agent account.

Agent Security Step

Enter a Domain User account with Admin rights and leave the default of By Impersonating the process account option button on the Connect to Publisher section of the form. Click OK.

Snapshot Agent Security form

Leave the Create the Publication box selected and select the Generate a Script File with Steps box, if you would like to capture the TSQL in a .SQL file. Click Next.

New Publication Wizard Step

Name the Publication in the Publication Name and click Finish.

Complete Publication Wizard

SQL Server will continue to create the publication and the Stop button will change to Finish once complete.

Creating Publication SQL Server window

Setting up the Subscription

Next, right click on the newly created publication and click on New Subscriptions to start the New Subscription Wizard.

New Subscription Wizard

Select the newly created publication and click Next.

Subscription Wizard Publication Selection

In this instance, the SQL Server Agent uses a Push Subscription to send the changes to the subscriber rather than Pull from the Subscriber. Click Next.

Merge Agent Push Pull Subscription Type

Select the Subscriber. The default subscriber will be the same SQL Server name as the Publisher. In this case, we are using a separate SQL Server 2012 instance as the subscriber. To Add the other DB Server, click the Add SQL Server Subscriber button and select the Existing Database or leave the default of New Database. Click Next.

SQL Server Subscriber Selection

In the Merge Agent Security Window, click on the button on the right of the Row next to Connection to Subscriber to open the window to add the Domain User Login.

Merge Agent Security

Add the Domain Login with Admin privileges and leave the other Default options and click Next.

Merge Agent Security

In this instance, the Run on Demand Only Agent Schedule has been selected, but you may select to Run Continuously. Click Next.

Synchronization Schedule Window

In the Initialize Subscription Window, you can initialize Immediately or At First Synchronization.

Initialize Subscription Window

In the Subscription Type Wizard, the Subsciption Type is selected as Server but may also be Client. Click Next.

Subsciption Type

Click Next to accept the Create the Subscription checked box or check Generate a Script box and then click Next.

Wizard Actions Window

The Creating Subscripion Wizard will run until it has completed and then click Close.

Creating Subscription Window

Replication and Synchronizing Data

Next right click on the Publication and click Launch Replication Monitor.

Launch Replication Monitor

Click on the Agents Tab on the right and then Right Click on the Publisher and select Generate Snapshot .

Generate Snapshot

Click on All Subscriptions. If you need to synchronize the data from here, just right click and select Start Synchronizing.

Start Synchronizing in Replication Monitor

To test the replication of the data, I add a row to the Customers table for an Acct YUM and Company Name of Yummy Goods.

Addition of Data in Publisher to Synchronize with Subsciber

In SQL Server on the Publisher, go to the SQL Server Agent and create a Replication Agent job, if the option was not selected when creating the publication. Click OK and wait for the job to run.

SQL Server Agent Job

After the SQL Server Agent job runs, running a query on the Subscriber Database whows that the row was replicated.

SSMS query with replicated Data

Deleting The Publication

By looking at a table in SSMS, you can see the addition of the rowguid column to each table.

rowguid Addition to all SQL Tables

Right click on the Publication and select delete to remove it.

Delete the Publication

Click Yes to complete the Deletion of the Publication and subscriber.

Confirmation Dialog Box for Publication Deletion

After the delete operation is complete, you can verify that the rowguid column has been removed from the tables.

rowguid Columns Have Been Deleted