3

I'm kind of a the administrator of a zero-administration OODBMS, but mostly I do programming.

I remember reading years ago about some SQL database that had a concept of something like a permanent transaction. It might have been Sybase. Anyway, what that "thing" was, was like a transaction that you start, and that persist between connections, and withing which you can run "normal" transactions. It basically gave you a "view" of that database that could diverge from the main database, but running on the same server, and therefore sharing most of the resource instead of having to have it's "own" server. I hope you understand what I mean now.

Anyway, what is that kind of thing called, and is it still available in modern databases. In particular, is there any of the fancy distributed NoSQL databases that can do that?

The reason I am asking, is because I could use something like that to run the staging of the next version of my software on the main database, saving me the need to have a staging system that is as powerful as the main one, and the trouble/bandwidth of synchronizing the changes from the main to the test. I would just start the "permanent transaction", test the new software for a few days, possibly letting some users get a preview, and throw away the changes when I'm done. Then I make the real deployment, and start a new "permanent transaction" for the next version...

NB: I couldn't put meaningful tags because it's my first post and I haven't got the right to invent tags, and there was nothing like "persistent", or "permanent" or even "long running" ...

Sebastien Diot
  • 181
  • 8
  • 2
    Sounds like a database snapshot. SQL Server supports this, not sure if any of the NoSQL options support this. – mrdenny Apr 23 '11 at 04:23

2 Answers2

3

I know sort-of what you mean, we do it on the storage using NetApp FlexClones. A FlexClone "looks" just like a copy of a filesystem, but the underlying blocks on the disks are copy-on-write, so the only space it actually occupies is that of the changes you make.

However, the clone is not a stream of changes, like a redo log. The way NetApp works is that each block you see at the filesystem level is really a just pointer to a block on the "real" storage. Creating a FlexClone is merely a matter of copying the list of pointers, so it is a very lightweight operation. When you change a block on a FlexClone the pointer is moved to a fresh block, and the original is copied, then it proceeds as normal. This has two interesting implications:

  1. Once you have changed a block once, you can go on changing it as many times as you like - it will occupy no more storage
  2. Once the initial COW is done, speed of access to the FlexClone is identical to that of a real volume.

We would typically put the primary into hotbackup mode (for Oracle, but since it's on the storage this will work with any database that has an equivalent mode), FlexClone the storage, mount the clone with NFS onto the (much smaller) test server, then create a new controlfile, recover the database from archived redo logs as necessary and open it up with resetlogs. We allow a "snap reserve" of 10% by default (i.e. you can change up to 10% of the clone) but this is fully configurable - you can make it 100% and do a "split" which creates an independent copy too. This technology (with some clever shell scripting) has brought creating test environments down from 3+ days to a matter of minutes at my site, I am a huge fan.

Gaius
  • 11,200
  • 3
  • 31
  • 64
  • While copy-on-write isn't exactly the term I was looking for, it is the technique that would be used implement such a function. And best of all, it can be done at the filesystem level, so it works for most DB. Great answer! Thanks! – Sebastien Diot Apr 22 '11 at 07:45
  • 1
    Remember to click the accept button ;-) – Gaius Apr 22 '11 at 09:55
1

I think you are looking for a Disconnected Dataset. It does complicate transaction management as you can't maintain database locks.

JDBC drivers allow a dataset to be disconnected from the database.

BillThor
  • 4,468
  • 19
  • 12