Design a solution where multiple users will update same database with their own changes

Question

Hi,

I've a typical use case as described below:

Activity 1: Using Azure Data Factory, delta changes will be fed from actual data source to MyDB(Implemented)

Activity 2: Changed data will be synced to individual copies of MyDB(TBD)

Activity 3: Users are updating their copy of MyDB in isolation(TBD)

Activity 4: Each user writes back his/ her changes to MyDB(TBD)

MyDB and its copies are Azure SQL single instances.

Please help to design a solution.

Regards,

Partha

Answer 1

Here's a high-level solution for your use case:

Activity 2: To sync the changes from MyDB to individual copies of MyDB, you can use Azure Data Factory to create a pipeline that copies the data from MyDB to the individual copies of MyDB. You can schedule this pipeline to run periodically, or trigger it to run whenever there are changes in MyDB.

Activity 3: To allow users to update their copies of MyDB in isolation, you can create a separate database user and login for each user, and grant them only the necessary permissions on their copy of the database.

Activity 4: To allow each user to write back their changes to MyDB, you can use Azure Data Factory to create a pipeline that copies the data from the individual copies of MyDB to MyDB. You can schedule this pipeline to run periodically, or trigger it to run when the user initiates the write-back process.

Additionally, you can use Azure SQL Data Sync to keep the multiple copies of MyDB in sync. It allows to synchronize data bidirectionally and also you can use it to copy data from the individual copy of MyDB to MyDB in real-time.