Post reply

Snapshot replication distrubtion database question

  • January 30, 2020 at 2:21 pm

    Go to Answer

    #3721610

    Hello

    I have a couple of questions in regards to snapshot replication, we are going to have 2 publications and 1 subscriber server (both pubs will feed to the 1 subscriber), but wondering:

    1. we are planning on doing the snapshot nightly, if there is a lot of transactions that happened during the day, will that fill up the distribution database, which we will have to calculate how much to make sure there is sufficient space until the snapshots happen on the subscriber?
    2.  If snapshot replication would cause distribution database grow more than maybe we can anticipate, would any recommend to use log shipping since the databases on the primary servers recovery model is full?

    thanks in advance

  • January 30, 2020 at 2:54 pm

    Answer

    #3721622

    Snapshot doesn't right much to the distribution databases.  Your main concern is enough disk space to store the sch and bcp files the snapshot agent generates.

    What is written to the distribution database is the location of these sch and bcp files for each of the snapshots as a command to pass to the subscribers to do the bulk load.

     

    Log Shipping is an option, you would have to put the database into standby mode and prevent restoring the logs at certain times.  You can set it up so that you delay the log restores for 24 hours, but then it has to play the whole transactions, so maybe slower than the pieces you need for replication (assuming your not replicating the whole DB)

     

    Have you looked at availability groups and readable secondary's?  Database snapshots? Rolling your own BCP jobs?  SSIS data copies etc?  As always more than 1 way to skin a cat, just depends which way is the best way for your needs.

  • January 30, 2020 at 4:07 pm

    #3721673

    Hey Anthony,

    thank you for the reply, and thank you for the information about snapshot replication, we are looking at HA AG however for the sake of time and storage (we don't want to replicate all the tables from the source database), but I would agree, having HA AG would kill 2 birds with 1 stone (hence HA and DR) but orders are orders, I just wanted to find out what is best since I was only given 2 options.

    I was also thinking SSIS since it would be easy to manage and custom made, but at the same time since SQL already has something out of the box, it would save time.

    thanks again for your help 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply