I was asked this question in this interview:
How to Synch two database data? There will be time delays etc. How do we handle?
The background: I mentioned about Microservice architecture and also using CQRS for performance (Separate Read/get query database) and separate write command database.
Now, if the customer enters or modifies data, how it will be replicated/synched in to the read database?
I was talking about stuffs like cosmos db options etc which prevents dirty read etc. I also mentioned about cache. But I am not certain what are all variousoptions to do synch. Interviewer specifically asked me in SQL DB level how do I synch between two DBs.
There are a couple of options for database syncing in SQL server.
1. SQL Server Always on Feature (SQL 2012 Onwards) - By using this feature, You need to make a primary and secondary replica (could be multiple secondry replica), Once Always On feature is configured, the Second replicas automatically updated based on Primary replica updates. This also provides HADR feature, if the primary replica goes down, the secondary replica will be active and play primary replica role. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-2017
2. SQL Server Replication - Merge replication, Transaction replication etc. https://learn.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-2017