Friday, November 6, 2015

Steps to add an article to the existing Transaction Replication

Publisher Server   : MSSQLL1
Subscriber Server : MSSQLL2


Take a backup of the destination database at the subscriber MSSQLL2 before making a snapshot. 

Deployment Steps

      To replicate the article Demo Table
a.       Navigate to the replication publisher MSSQLL1
b.      Right click on the publication and select properties
c.       A window will appear with the properties for the replication, choose the articles tab
d.      A list of all the tables will appear, navigate to the table you want to add to replication (remember to uncheck the show only checked articles in the list)
e.      You will now put a check in the checkbox for the new table.
                                                              i.      Demo
f.        Click the ok button.
g.       Execute below to set publication properties (allow_anonymous and immediate_sync  is set to false)
use [publisher database]

--Run on your publisher database

EXEC sp_changepublication
@publication = 'Publication name',
@property = 'allow_anonymous' ,
@value = 'False'

GO
EXEC sp_changepublication
@publication = 'Publication name',
@property = 'immediate_sync' ,
@value = 'False'

h.        Change the above value to True and run it in reverse order after the below steps are completed.
i.         Now right click on the publisher and choose Launch Replication Monitor.
j.        The replication monitor will open in a window, right click on the replication instance for the one you added the new article to.

k.       Click on Generate Snapshot. (Snapshot will be create snapshot only for one table as publication properties (allow_anonymous and immediate_sync  is set to false)
l.         Connect to the Publisher in Management Studio, and then expand the server node.
m.    Expand the Replication folder, and then expand the Local Publications folder.
n.      Expand the publication for which you want to synchronize subscriptions.
o.      Right-click the subscription you want to synchronize, and then click View Synchronization Status.
p.      In the View Synchronization Status - <Subscriber>:<SubscriptionDatabase> dialog box, When synchronization is complete, the message Synchronization completed is displayed.
q.      Click Close.
r.        Please monitor: All Subscriptions to make sure the latency falls back to normal pretty quickly.

Hope this helps you in replication

No comments:

Post a Comment