0

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?

1
  • Why not use one database? What problem are you solving with 2 databases? You will run into the CAP problem which is quite fundamental.
    – user189695
    Jun 14, 2021 at 15:33

2 Answers 2

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.