Post reply

Transactional Replication Problem for a Large Database

  • November 17, 2018 at 12:11 pm

    #378634

    All,
    I inherited an OLTP SQL server with a large 2.5 TB database that is replicated to another server.  Not all but about 2/3 of the data is replicated.  The replication had been running well until last week we ran a massive meta data change script that seemed to break the replication.  The latency (obtained from EXEC sp_replcounters) which was always below 10 seconds began to increase steadily close to 95,000 seconds within 1 day so I decided to drop the subscription, publication and created a new replication.  The subscriber is still the same database.  The snapshot took 4 hours then it has been more than 7 hours but not all data are replicated because I compared and found less row counts at the subscriber.  I see new progresses are being written to table MSsnapshotdeliveryprogress at subscriber.

    Is it normal when we replicate a large database?

    Is there anyway to speed up the replication so all required data are shown up at subscriber sooner?

    Many thanks.

  • November 26, 2018 at 1:16 pm

    #2014569

    Probably if you can restore the database from the Publisher to the subscriber and then recreate the replication ( just a suggestion) .
    Other than this, option would be to replicate only the table that is most urgently needed and then you can add the other tables. 
    Since you database is quite huge, the time would generally take a some hours to replicate the data. 
    I remember one database which was ~1 TB  that I used for replication took almost 7 to 8 hours(that was SQL 2012).

  • November 26, 2018 at 1:29 pm

    #2014571

    Hi Rinu, thanks for your feedback.  I already looked at the option to initialize from a backup but there was not enough disk space to restore the whole database.  I had to start a new snapshot but configured it to run SQL scripts to send email to me before and after applying the snapshot.  It took 27 hours to complete.  Not sure why it was that slow but last week was holidays we I had a chance to let it finished.  

    Regards,

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

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