Solution for syncing a database between two server and secondary can be used for all DML operation

All we need is an easy explanation of the problem, so here it is.

I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting synchronized, and would be able to run all operations on it like INSERT, UPDATE, SELECT etc. Can anyone please suggest a solution on this?

AlwaysOn Availability Group Secondary Server cannot be used since it is only read-only.
Log Shipping also cannot be used because it is also read-only.

Will it work with transactional replication? Can we update on the subscriber without issues?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

With transactional replication you can INSERT, UPDATE, DELETE, create indexes, etc. on subscriber

However, some of above statements can break replication

For example, if you insert manually column on a subscriber, and then something else inserts row on a publisher to the same table, and publisher tries to push inserted row to subscriber, it breaks. Or if you delete a column or otherwise change table schema on subscriber, it can break

Method 2

There is no "update anywhere" functionality with SQL Server, if that is what you mean.

By "update anywhere" I mean

  • you have two (or more) nodes
  • you can do modifications on both
  • you will never end up in any types of conflicts.

You can pick two of above, however. Or build your own trying to handle the conflict part yourself.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply