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
  • 81
  • 1
  • 2
  • 4

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
  • 17,274
  • 53
  • 180
  • 320
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
  • 711
  • 3
  • 10