2

I have two SQL Server databases that I am trying to replicate. I am able to replicate small tables without any issues.

However I have a table with over 16 Billion rows that is quite problematic.

I am currently using transactional replication to replicate the large table. From the documents I have read online, when you select transactional replication, the process uses @sync_method = N'concurrent' parameter which is not suppose to lock the table, but when I initiate the process, the table seems to lock which renders it un usable. At the same time when I initiate the subscription i.e on the destination server, it also locks the table at the destination.

I would want to know what am missing or doing wrong such that the table locks. Remember that the table is also in use as the replication happens. So it should not lock at any given time.

Read committed snapshot isolation is already enabled on the database.And this is the initial snapshot that I am trying to run.

Crispin
  • 71
  • 1
  • 1
  • 4
  • Please provide more details such as if the locking is occurring during the initial snapshot, and how you're aware that the table is locked at the publisher. (You can use Adam Mechanic's sp_whoisactive to help verify locking issues.) It's totally possible you have no locking on the publisher side but you're just facing overall resource contention. – J.D. Sep 07 '21 at 17:33
  • Thank you J.D for your response. I try to insert like 100 records into the replicating table, and it just keeps running for minutes. A process that takes seconds in a normal situation. And when I check sp_whoisactive, I see that the insert has been blocked by the replication process. However you could also be right by saying that we are just facing overall resource contention in accordance to the replication monitor which sometimes gives a status of performance critical. – Crispin Sep 08 '21 at 06:25
  • 1
    @Cris - please use the [edit] link to add details to your question. Thanks! – Hannah Vernon Sep 08 '21 at 12:15
  • 1
    Please update your original post with those details. Is this the initial snapshot you're trying to run or it was already snapshotted over to the Subscriber? – J.D. Sep 08 '21 at 18:22
  • @J.D This is the initial snapshot.I have already updated it on my question. – Crispin Sep 09 '21 at 07:06
  • Gotcha, Marcello's answer is on point then. Your best bet is to probably restore a backup at the Subscriber first or use some bulk copy methodology to transfer the data in smaller chunks that is suitable to your server's needs to alleviate locking and contention. – J.D. Sep 09 '21 at 11:59

2 Answers2

2

A table with 16 billion rows will need more attention and will take longer to replicate no matter how you do it.

I had a similar issue about locking but it was on my subscription servers not the publisher - many moons ago as you can see here.

I had good experience with READ_COMMITTED_SNAPSHOT in replication as you can see here.

The transactional replication on the publisher will not lock the replicated tables this is how it is done: Modifying Data and the Log Reader Agent

However, when initialising the subscriptions or anytime you need a new full snapshot, then yes there will be locks on the table but not exclusive locks and not for the whole duration of the snapshot:

BOL:

Concurrent snapshot processing, the default with transactional replication, does not hold the share locks in place during the entire snapshot generation, which allows users to continue working uninterrupted while replication creates initial snapshot files.

there is this question that deals with the same problem:

How to generate replication snapshot without locking tables

one of the ways to work around that is to

Initialize SQL Server replication using a database backup

Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300
  • 1
    Thank you for your response.I am currently trying Initialize SQL Server replication using a database backup and I am hoping it will workout. – Crispin Sep 09 '21 at 07:13
0

If you are talking about the table lock while the snapshot agent is running, I think the only way to avoid it would be to enable « read committed snapshot isolation » on the database, but this must be done only if the applications have been tested with this enabled.

Eric Prévost
  • 701
  • 2
  • 9
  • Thank you Eric Prévost for your response. However read committed snapshot isolation is already enabled on the database. – Crispin Sep 08 '21 at 06:39