3

When working with virtual machines often it can be very useful to have a base read-only VM then have several smaller VM's that use that larger VM as a base and write their changes to their own writeable copy.

Is something similar available in Microsoft Sql Server?

The situation I am in is we host a demo copy of our product that our clients can connect to for about 30 days or so. When we create a demo account we have to create a new copy of the base database the program uses and have the software point at that. Each image is a little over 2GB, but on average the demo users will only change about maybe 100MB worth of data in the database.

What I would like to do is have a read-only database that acts as a base then have the demo databases create a "differencing database" and write out it's data and log information to that differencing database. Is this possible? I have been searching through the MSDN documentation but I have yet to find anything and unfortunately google searching has been fairly useless as searching for Sql server differential database gets polluted with all the pages about doing differential backups (doing Sql server differential database -backup did not return any useful results either, all I got was information about differential deployment scripts).

|improve this question
1

No, there is not really any such thing. Of course if the VMs are actively connected on a reliable pipe, a view could always coalesce the original data in the base VM with the changed data in the new VM.

|improve this answer
3

The closest thing is a database snapshot. You take a snapshot of your database, that will act as the 'base image'. Then customers go nuts on the database and hack it to pieces. When all said and done, you revert the database back to the snapshot and voila, everything is back to its original state. See Revert a Database to a Database Snapshot.

You do have space savings between the snapshot and the DB, because the copy-on-write semantics of the snapshot. But unfortunately this only works on a single customer, you cannot have one snapshot serving as origin for multiple writable customers, which is what you ask. Snapshots can server something like a demo boot, where your restore it every morning.

|improve this answer
  • Rolling back is not a issue, the goal I was after was instead of having 50 2GB databases I would have 1 2GB database and 50 200MB differential databases. From what I read about snapshots I don't think that will do what I want. – Scott Chamberlain Apr 1 '14 at 14:41
  • Snapshot has to be on the same instance (and therefore same VM), too, so doesn't look like it meets for that reason also. – Aaron Bertrand♦ Apr 1 '14 at 14:55
  • I guess what I want is not possible, oh well. I opened a feature request on connect to get it added. – Scott Chamberlain Apr 1 '14 at 15:15

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.