November 19, 2019 at 7:52 am
So when trying to set allow_initialize_from_backup flag this stored procedure gets called and causes aload of blocking and long running 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.
May I can try the @activate_articles_only flag as well if only there was a way 🙂
November 19, 2019 at 8:51 am
what percentage of the tables do you need for your reporting? if it's 10 tables that are used in your reports then initialisation from backup might not be your best path
MVDBA
November 19, 2019 at 8:55 am
Its actually about 200 tables i just gave that as an example 🙂
November 19, 2019 at 9:09 am
so just to clarify - so we can get you a suggestion - your report server uses 200 tables? it needs all of these ?
I'm only asking because that sounds a tiny bit "not normalised" - to be fair, I have a legacy database with 7000 tables (dont ask)-i'm not going to bother asking whether you can redesign the db, because we all know that would get a resounding no from your board of directors.
regardless of whether you initialise from a snapshot or a backup, you are going to have to lock every table involved to get the replication running - these are done pretty much in sequence... and the time between the backup/snapshot being created and delivered will affect you.
have you considered making multiple publications (just an idea) with 20 articles per publication … more difficult to manage, but quicker to re-initialise
I'm sorry but that's the only thing I can come up with - but It is an easy thing to try
MVDBA
November 19, 2019 at 9:30 am
Yep scenario is off the shelf ERP system MS NAV dynamics so cant control the normalization unfortunately 🙂
-DB has 14K Tables ( whats worse is the amount of indexes this beast has!) sp_blitz no likey! 🙂
-Currently we replicate 180 tables to old report server
-Need to replicate same tables to new server with limited downtime
-Retire old server and subscription
Used Snapshot to for current replication but takes about 1.5 hrs to generate and 4-5 hours to sync cant afford that in live at the moment
The initialize from backup setting took seconds in test with copy of DB + repl setup from live
I think my plan is to get a downtime window of 30 mins so i can set the setting then i can to the set replication from backup.
Not a bad idea having separate smaller publications
November 19, 2019 at 9:40 am
1.5 hours to generate? there musta be a few big tables in there
pick off the big tables and isolate them into their own publications - you can always run snapshots and initialisations in parallel - stick all of the little tables in a single publication (it might make life easier)
hope that helps
be aware that there used to be a registry setting(sql 2005) that controlled how many sql agent threads could be run at any time - i'm not sure, but I think it was 20 - you can change it.. but I've forgotten. I'm sure you can google it though
MVDBA
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply