I have an existing subscriber and want to setup another subscriber but on different machine.So 2 subscribers, 1 publisher
I want to initiate it from a backup rather than snapshot but when i try set the following i get major blocking in production and have to kill of the process.
DECLARE @publication AS sysname SET @publication = N'Publication' EXEC sp_changepublication @publication = @publication, @property = N'allow_initialize_from_backup', @value = 'true' GO
Anyone else experience this ? And know a way round it!
So this stored procedure gets called which in turn blocks a load of normal queries
Blocked SQL fragment: -- -- Name: sp_MSenableautonosync -- -- Description: This is a helper procedure for activating all articles in a -- publication when the allow_initialize_from_backup property is -- changed from 0 to 1 in sp_changepublication. This procedure -- can also be used to perform "just-in-time" article activation -- when setting up a 'replication support only' subscription. -- -- Steps that will be performed by this procedure: -- 1) Update all null or empty destination object/owner name -- to be their source object's equivalent -- 2) Update all article status to active -- 3) Set the replicated bits of all article objects except those -- for schema-only articles -- 3.1) Set NFR identity columns -- 4) Flush the article cache -- 5) Set the allow_initialize_from_backup bit of the publication -- to 1 -- 6) Increment the publication's min autonosync lsn -- -- Parameters: @publication sysname (mandatory) -- @activate_articles_only (optional, default 0) -- -- Notes: @publication is assumed to be verified as a valid publication name -- in the current database by the caller. No attempt will be made in -- this procedure to check for the validity of the @publication -- parameter. -- -- The @activate_articles_only parameter was subsequently added to -- bypass all but steps 2) & 3) when it is set to 1. This new parameter -- is used when setting up a 'replication support only' subscription -- to perform "just-in-time" article object activation. -- -- Security: This is an internal system procedure.