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...

• This reply was modified 5 years, 1 month ago by  bugg.

Its actually about 200 tables i just gave that as an example 🙂

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...

• This reply was modified 5 years, 1 month ago by  bugg.
• This reply was modified 5 years, 1 month ago by  bugg.

I'm not using this for DR, i have logshipping in place for that already. The replication is for reporting

I tested and it works you can delete the objects not used in replicated DB

Now having issues with trying to test this in production just gets suspended! I wonder if...

• This reply was modified 5 years, 1 month ago by  bugg.

When you initiate sub from backup it keeps all other tables in publisher DB even ones that are not replicated. e.g 10 tables replicated 100 tables in DB all 100...

Thanks for the comments, will try set this up with 2 subscribers and intialise one from backup before attempting in production. Will let you know how i get on.

Just edited my post, seems we can we have 2012 distributor on that...

• This reply was modified 5 years, 1 month ago by  bugg.

Is it possible to have the distributor on separate server from the publisher? Seems you can but more truicky to set up.

Looking at this replication matrix its seems okay to...

• This reply was modified 5 years, 1 month ago by  bugg.

Yep will test some scripts out now against a copy of the DB make sure its all good regarding accounts and access etc.

Will probably keep the old DB up and...

Not what I wanted to hear but appreciate the response 🙂

Thanks for that article! That was one of my concerns if we fail over for patching or DR then replication will have to be re-setup. It looks like with this...

On further inspection it doesn't look like one query that goes via PDO has a prepared plan in cache everything is adhoc!