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





1 comment: