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.

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.

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)

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

Select the Merge Publication Type and click Next.

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

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

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.

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

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.

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

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.

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.

Name the Publication in the Publication Name and click Finish.

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

Setting up the Subscription

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

Select the newly created publication and click Next.

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.

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.

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.

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

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

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

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

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

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

Replication and Synchronizing Data

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

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

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

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.

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.

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

Deleting The Publication

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

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

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

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