Initializing Transactional Replication from Backup RRS feed

  • Question

  • Our reporting database is a subset (400GB) of our production 1TB+ OLTP database.   

    Even with the gentlest of settings (locking, threading, etc.) , the minimal locks taken during the Snapshot process STILL cause some pretty heavy blocking for about an hour.

    Q: Any chance the Log Reader Agent can read from an availability Group Secondary (I doubt it but must ask)?

    Which leads me once again to consider the From Backup option.  

    Q: What cleanup is required to bring the DB to the same state it would have been if built from a clean Snapshot?

    I have to remove all Triggers, many FKs, 600GB of data over 400+ table and so on.  How is this not a huge problem for people creating Reporting databases?

    Q: Is there a better way?

    Thanks all



    Tuesday, May 5, 2015 9:30 PM


  • You can use initialize from backup option but from what I understand - you have 1TB database and you only need 400gb worth data/tables. that means you have to do lot of deletes to only get the data you need and besides, if replication has issues and need to be re initialized, you need do all this again.

    what might help is if you have all the required objects in one file group- then you can just restore from the file group and use that to initialize. so, you will have less objects to clean up.

    one solution - i could think - is using log shipping as solution. the log shipping - you can put the database in -standby mode and you can query your secondary database. the reason i like this solution is because it works on simple - backup-copy-restore mechanism.

    replication, in my opinion works greats but is very hard to troubleshoot whereas log shipping you should not generally run into issues and even if you do, the error would relatively easy.

    unless you really need to have the ability to update the data on reporting database, this is a workable solution.

    if one other point is , if you want to restrict access on tables, create a different use account on the main database - with read only access on the tables, and that user will be created on the secondary as well and your users can use that to connect to the secondary using that user. also, disable the other logins on the secondary server that may have user associated with it, so the users cannot get more permissions.

    Hope it Helps!!

    Wednesday, May 6, 2015 12:59 AM