Sql-server – Copy on Write for SQL Server

sql server

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.

Best Answer

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:

Drop the existing DBs if present
Drop the LVM snapshots if present from last time
Backup the DB and restore it with the data and log files on the reference LVM volume
Detach (sp_detach_db) the restored copy
Make enough read-write LVM snapshot volumes of the volume now containing the updated DB copy
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.

Related Solutions

Sql-server – Best way to copy a SQL Server database from a remote server to local computer

You should talk to your support team at appharbor. Presumably there is a way to take a backup of your database, store it on the file system somewhere, and then download the .BAK file (through http or, more likely, ftp). This should be a lot less work than your current process, but it depends on your host providing the facility to do so. This also assumes that your backup file would be < 10 GB (since this is the maximum supported by Express) and that it doesn't use any features not supported in Express Edition (such as Data Compression, SQL Server Agent, etc.).

You can do this using just Management Studio, e.g. you can right-click your database, Tasks > Generate Scripts and select all objects and on the Scripting Options tab make sure to go into advanced and select the option to script both schema and data. This will generate a humongous script that you can run on your local machine after you've created an empty database. Personally I find a backup / restore much more reliable and a lot less hassle.

But asking here isn't going to be much help - unless you happen to come across someone who has done this specific thing at that specific host using the specific hosting plan you have, there is little chance any of us knows what options you have - appharbor is going to be able to help you answer that question a lot more accurately than any of us.

Sql-server – SQL Server Sandbox

Another idea would be to simply set up a nightly job that does a copy_only backup and restores it on the dev server (or on the same server, if you only have one, but that might not be a great idea). The nice thing about this is that the restore can go to any server (or multiple servers), and can be completely decoupled from any activity on the primary database.

On server 1:

BACKUP DATABASE db TO DISK = '\\someshare\file.bak' 
  WITH COPY_ONLY, INIT, COMPRESSION;

On server 2:

RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
  WITH REPLACE, RECOVERY;

You may need to also add MOVE commands if the disk layout between the servers is different (or if you're putting the copy on the same server).

RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\somepath\somefile.mdf',
  MOVE 'log_file_name'  TO 'E:\somepath\somefile.ldf';

If you're restoring on the same server, you shouldn't have any issues with users. If you restore to a different server, your users will exist but the server-level logins may not. There are scripts to fix that, and a new feature in SQL Server 2012 (Contained Databases) which eliminates the problem altogether.

Best Answer

Related Solutions

Sql-server – Best way to copy a SQL Server database from a remote server to local computer

Sql-server – SQL Server Sandbox

Related Question