I have 20 users that each keep a copy of a database on their local computer (technology other than SQL Server).
Their copy (we call it a sandbox) is updated each morning and their previous changes are throw away.
We are migrating this data to SQL Server and want to maintain the sandbox feature, but we don't want the overhead of installing SQL Server on each of their computers or making 20 separate databases.
Is there a "copy on write" feature that is easy to turn on that will essentially allow each person to only see their changes to the database while still using a common database? I don't want to make big changes to the code to achieve this. If big changes to the code are necessary I will probably go with SQL Server Express and have the database on their local machine.
The users are not technical.
The application that reads the data is an
.exe that runs on the users' machine.
We can run what ever version of SQL Server that would support this.
There are enough nuances to the database interaction with the app that I am only going to use SQL Server as trying to switch to e.g. SQLite would find all kinds of problems.
The users need to update the data in sandbox so a read-only solution is not an option.