Scenario 1: I have transaction replication configured and my publisher database is of huge size
Scenario 2 : 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
No comments:
Write commentsPlease do not enter spam links