While providing a replication solution to one of my client in SQL Server 2016, I came across a scenario where the client had retail stores at various locations and all data from those stores had to be synchronized at a central location in real time. The aim of this synchronization was to make it easy for senior management to analyze all stores performance from a single location. After giving considerations to different options, I finally came up with a solution that worked for my client.

Let’s assume we have servers,  RBTSERV1 and RBTSERV2, which will act as a publisher for 2 stores. RBTSERV3 will act as a subscriber and data from both servers RBTSERV1 and RBTSERV2 will be synchronized to RBTSERV3. There were several tables that needed to be synchronized but to keep it simple, I will take 1 table in our example.

Notice that All stores have a unique StoreId assigned to them.

Let’s create some sample table and data on all servers. On RBTSERV1:PUBLISHER 1, here is the first code to run:

CREATE DATABASE DBPub1;
GO
USE DBPub1;
GO
CREATE TABLE CustomerOrders
(
    StoreID INT ,
    OrderId INT ,
    ItemName VARCHAR(100) ,
    Qty INT ,
    OrderDate DATETIME
        DEFAULT GETDATE() ,
    CONSTRAINT PK_CustomerOrders
        PRIMARY KEY
        (
            StoreID ,
            OrderId
        )
);
INSERT INTO CustomerOrders
VALUES
(1, 1, 'Books', 5, GETDATE());
INSERT INTO CustomerOrders
VALUES
(1, 2, 'Toys', 3, GETDATE());
---------------------------------

On RBTSERV2: PUBLISHER2, run this code:

CREATE DATABASE DBPub2;
GO
USE DBPub2;
GO
CREATE TABLE CustomerOrders
(
    StoreID INT ,
    OrderId INT ,
    ItemName VARCHAR(100) ,
    Qty INT ,
    OrderDate DATETIME
        DEFAULT GETDATE() ,
    CONSTRAINT PK_CustomerOrders
        PRIMARY KEY
        (
            StoreID ,
            OrderId
        )
);
INSERT INTO CustomerOrders
VALUES
(2, 1, 'fans', 15, GETDATE());
INSERT INTO CustomerOrders
VALUES
(2, 2, 'pens', 13, GETDATE());

On RBTSERV3:SUBSCRIBER, run this:

CREATE TABLE CustomerOrders
(
    StoreID INT ,
    OrderId INT ,
    ItemName VARCHAR(100) ,
    Qty INT ,
    OrderDate DATETIME
        DEFAULT GETDATE() ,
    CONSTRAINT PK_CustomerOrders
        PRIMARY KEY
        (
            StoreID ,
            OrderId
        )
);

Publisher RBTSERV1 has 2 records in the table and publisher RBTSERV2 also has 2 records in the table. The subscriber doesn’t have any records. Now let’s start with our replication.

Configuring Publication on RBTSERV1

To setup a publication on RBTSERV1, Follow the below steps. First, right Click on folder “Local Publication” and select “New Publication...”

In the publication wizard, select the database to be used for Publication. In our case, we are using DBPub1. 

Since we have to make the sync almost real time, so we choose Transaction replication.

Next we pick the table we wish to use for our replication. Here the important point is to choose the “Properties for all Table Articles” under “Article Properties” as shown below. Set the Action if name is in use: Keep Existing Object Unchaged

Next we leave the “Create a Snapshot Immediately...” option unchecked.

Under Agent security, choose the options below. Note that on my server, SQL and Agent services are running under administrator account.

Give your publication a name and click Finish.

The publication wizard should finish without any error.

Next repeat the same steps for RBTSERV2 and give your publication a name SERV2_Publication.

Once this is complete, right click on Publication SERV1_Publication and choose “New Subscriptions...”.  In the Subscription wizard, choose your publication as SERV1_Publication

Then you select if you wish to have a push or pull subscription. Here I have used Push Subscriptions.

Now you select your subscriber server name and the subscription database. Here I have used RBTSERV3 as subscriber and DBSubs as subscriber database.

Now we can configure the Distributor Agent security.

Choose to run the distributor Agent continously. 

It is important not to initialize the subscription as we will be manually synchronizing the old data.

Finally, finish the subscription wizard. 

Now if you check the log reader agent status of RBTSERV1 or RBTSERV2, you should see it running fine. 

Now if you check data in subscriber table using below query, there should be no data. You can check with a simple query:

SELECT *   FROM [DBSubs].[dbo].[CustomerOrders]

Now let’s add some new records to both servers.  On RBTSERV1, run this:

INSERT INTO CustomerOrders VALUES (1,5,'notebooks',15,Getdate())

On RBTSERV2, run this:

INSERT INTO CustomerOrders VALUES (2,6,'erasers',150,Getdate())

Now check that the new records from both stores have been replicated to the subscriber table.

Now you can sync the old records from both stores either using a SQL query or export import wizard. Now in future if you wish to reinitialize just one store, you may delete the data of that store from subscriber and manually sync the data from that store using queries or export import wizard.