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 server RBTSERV1 and RBTSERV2, which will act as
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.
--RBTSERV1:PUBLISHER 1
create database
DBPub1
use DBPub1
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())
---------------------------------
--RBTSERV2: PUBLISHER2
create database
DBPub2
use DBPub2
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())
---------------------------------
--RBTSERV3:SUBSCRIBER
create table
CustomerOrders(StoreID int ,OrderId int,
ItemName varchar(100), Qty int, OrderDate datetime default getdate()
, CONSTRAINT
PK_CustomerOrders PRIMARY KEY (StoreID,OrderId))
So
publisher RBTSERV1 has 2 records in the table and publisher RBTSERV2 also has 2
records in the table. 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:
1.
Right Click on folder “Local
Publication” and select “New Publication...”
2.
In the publication wizard,
select the database to be used for Publication. In our case, we are using DBPub1.
3.
Since we have to make the sync
almost real time, so we choose Transaction replication.
4.
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 below:
Action if name is in use: Keep Existing
Object Unchaged
5.
Next we leave the “Create a
Snapshot Immediately...” option unchecked.
6.
Under Agent security, choose
the options below. Note that on my server, SQL and Agent services are running
under administrator account.
7.
Give your publication a name
and click Finish.
8.
The publication wizard should
finish without any error.
9.
Next repeat the same steps for
RBTSERV2 and give your publication a name SERV2_Publication.
10.
Now right click on Publication
SERV1_Publication and choose “New Subscriptions...”. In the Subscription wizard, choose your
publication as SERV1_Publication
11.
Then you select if you wish to have a push or
pull subscription. Here I have used Push Subscriptions
12.
Now you select your subscriber
name and the subscription database. Here I have used RBTSERV3 as subscriber and
DBSubs as subscriber database.
13.
Configure the distributor Agent
security.
14.
Choose to run the distributor
Agent Continuously.
15.
It is important not to
initialize the subscription as we will be manually synchronizing the old data.
16.
Finally finish the subscription
wizard.
17.
Now if you check the log reader
agent status of RBTSERV1 or RBTSERV2, you should see it running fine.
18.
And now if you check data in
subscriber table using below query, there should be no data. SELECT * FROM [DBSubs].[dbo].[CustomerOrders]
Now
let’s add some new records to both servers.
--RBTSERV1
INSERT INTO
CustomerOrders VALUES (1,5,'notebooks',15,Getdate())
--RBTSERV2
INSERT INTO
CustomerOrders VALUES (2,6,'erasers',150,Getdate())
19. Now finally check that the new records from both stores have come to
subscriber table.
20.
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.
So that's that. Hope it helped. If you have any issue in understanding the article, feel free to contact us and we will surely help you. Call or whatsapp - +91 997148322
email- support@redbushtechnologies.
Author- Suresh Kumar - A seasoned SQL DBA with more than 15 years of experience in working with fortune 500 MNCs.
Thank you
Team RedBush
