SQL Server Replication DEMO: Generate Snapshot Of New Article Only

Schenerio1: I have transaction replication configured and my publisher database is of huge size

Schenerio2: I have transaction replication and application have added some intermittent data to subscriber tables only

Issue: I have to add a table in replication without affecting existing subscriber data in minimal time.

Solution: Sql server allows you to generate limited snapshot in replication by playing with 2 properties

•     immediate_sync =0 (Whether the synchronization files are created or re-created each time the Snapshot Agent runs.)
•     allow_anonymous =0 (Whether anonymous subscriptions are allowed on the publication.)

By default these properties are set to 1 (True). If we mark them 0 (False) SQL Server will generate snapshot for article added only in its immediate snapshot after adding objects, afterwards its full snapshot.

Note: Don’t ever mark reinitialize to subscriber, once you mark reinitialize you have to apply complete snapshot only

Demo for generating snapshot of new article only is shared below:

Step1: Setting up replication quickly

/***********************************/

Create Database Repl1

Go

Use Repl1

GO

Create Table a (i int primary key)

Create Table b (j int primary key)

Create Table c (k int primary key)

GO

Insert into a values (1)

Insert into b values (1)

Insert into c values (1)

GO

use Repl1

GO

Select * from a

-- Now setup replication from Database Repl1 table “a”

-- Publisher name Repl1_PUB

-- Subscriber Database Repl1_SUB

/***********************************/

Replication is in synch now

Step2: Now we will check replication property

use REPL1 -- (publisher DB)

GO

sp_helppublication

immediate_sync =1

allow_anonymous =1

Step3: Now we have to change same to 0 using below query (order of query should be maintained)

--Run on your publisher database

EXEC sp_changepublication

@publication = 'Repl1_PUB', -- your publication name

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'Repl1_PUB', -- your publication name

@property = 'immediate_sync' ,

@value = 'false'

GO

STEP4: verify immediate_synch and allow_anonymous should be 0

sp_helppublication

STEP5: Now Add new article (table =b) in publisher Repl1_PUB using GUI

STEP6: After adding article we need to verify status of articles in subscription

Use Repl1_sub -- Subscriber database

GO

sp_helpsubscription

--Subscription status:

0 = Inactive

1 = Subscribed

2 = Active

So table “b” Subscription status is 1 .i.e. subscribed but not active

STEP7: Now we will generate snapshot by right click on publisher --: view snapshot agent status --: start

snapshot of 1 article is generated, verify snapshot folder also.

STEP8: Now run sp_helpsubscription and verify subscriptions status should be 2 for article “b” if not re-run distribution agen(restart)

STEP9: verify data in subscriber

use Repl1_SUB

GO

Select * from B

Now data is present

STEP10: Check replication latency before updating user via trace token