CODEWITHSUNDEEP
sqlsql-serverazure-sql-databasedatabase-migrationtransactional-replication
Rajesh
Rajesh

Reputation: 61

How to avoid table locks and replicate large articles using transaction replication

We are planning to migrate our SQL on prem database to azure and this database has lot of tables and out of them few are very highly transaction table (contains millions of records), we want to minimize the down time of the application and decided to use the transaction replication using snapshot for the replication of data and then take some down time and do a cut-over to azure database from our application

Below are the issues which we have seen so far in pre prod

  1. Table locks during initial time and lot of requests from the application were failing due to this locks. How can we avoid this?
  2. 2 articles (millions of rows) replication failed one which was almost completed (90%) and other due to some data issue, we have created 3 separate publications one for rest of small tables and other 2 for each big tables. I know that we can reinitialize the publication and do it from start but that will again delay the cut over time and also table locks.
    So how can we handle this case 1 for where we most of the data was replicated and we do not want to start from scratch

I hope many of you have experience this issues and has some best practices to share.

Upvotes: 1

Views: 3811

Answers (2)

Emka
Emka

Reputation: 350

I would suggest using a time reference to your procedure. I can think two ways to achieve that:

  1. Make a bridge table between the live db and the target db. Once you load the bridge and check that the data are valid, pass them to the target db. But this way might be hard to implement due to data verification on bridge.
  2. At a timestamp column to your tables. When the replication process begins take those rows that have timestamp before the initial time. So you would ignore all rows after the start of the procedure. This will guaranty that you will not have any key binds (fk) that are inclomple.

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28900

This article says

[ @sync_method=] 'sync_method'

Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications. Not supported for Oracle Publishers.

You may want to try it..

References:
https://dba.stackexchange.com/questions/73629/how-to-generate-replication-snapshot-without-locking-tables
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addpublication-transact-sql

Upvotes: 1

Related Questions