0

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.

0
1

If the overhead you are concerned about with the local SQL installs is a licensing overhead, and the database is smaller than 10Gb, then you could instead install Express edition which is free (but has the 10Gb limit). If the DB is a little over 10Gb then you could restore to the server, compress enough tables & indexes to make up the difference, then backup and restore that locally.

There is no copy-on-write support in SQL Server itself. If you are running it on Linux you could perhaps try using LVM snapshots to try acheive this:

  1. Drop the existing DBs if present
  2. Drop the LVM snapshots if present from last time
  3. Backup the DB and restore it with the data and log files on the reference LVM volume
  4. Detach (sp_detach_db) the restored copy
  5. Make enough read-write LVM snapshot volumes of the volume now containing the updated DB copy
  6. Attach the DB from each of the snapshots (sp_attach_db)

If your storage subsystem is a SAN with RW-snapshot features than you could potentially use a similar method with that.

I would not recommend this, or other options with 20 copies of the DB on the same instance and drive(s), if the users might pose much load - they will all compete for IO, they will all compete for buffer pool memory, and while the on-disk data is CoW the SQL instance will not know this so will happily hold duplicate copies of unmodified data in memory.

1

Frankly, I think the easiest solution would be to have each sandbox user specify a different database to connect to. Then script a daily backup of the primary DB, and then drop and restore each of the 20 user databases. If it's just a subset of tables, you could script that instead. This gives you one server with 21 databases on it. The biggest issue here would be if you need to push data from a sandbox to the primary DB, but if you're doing that then I wouldn't call what you'd doing a "sandbox." It might be worthwhile to put the sandbox DBs on their own instance and their own hardware, especially if the primary DB is in production and these sandboxes aren't.

You could also use per-user schemas for this. Each user could have the necessary permissions to clear and re-instance the data in their schema's tables. Then to refresh it you'd do that and pull from the primary dbo tables. However, this is just a just a fancy way to say that instead of maintaining 21 databases you have to maintain 21 sets of tables in one database. And this method will require some application changes, and may require significant application changes. However, it may be the least amount of changes if you decide to go that route. It does still requires DBA tasks when adding new users, too, so it's not necessarily the best solution. This method might work best if you need to publish data from the sandboxes back to the primary DB.

0

There is no built-in copy-on-write feature for SQL Server.

Possible third parties that provide the service you are looking for, and all do it via "cloning" using virtual hard drive differencing technology:

  1. Dephix
  2. NetApp SnapCenter - doesn't work for the cloud
  3. WinDocks
  4. RedGate SQL Clone (I use this one with AWS EC2 instances)

There are some limitations with each of these products. RedGate's documentation is very good. They have a PowerShell API for automation. The major limitation, for me, is you can't directly clone an Amazon RDS Sql Server instance database (but you can host the "repository" that keeps track of all this info in RDS to save money). To clone an Amazon RDS SQL Server database, you have to take a bak file, restore it, and then make a clone "image" of the restore. But, overall, I have found it works great. I am currently using SQL Clone 3.1.9.18065 and we're finally upgrading to 5.x this month.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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