+972 52-548-6969
SQL Server 2005 and database snapshots
I was playing with SQL Server snapshots today, and they are very cool. The idea is that you can freaze your database at a point in time, and always refer back to it. The problem is that it costs you in terms of performance, and it may cost dearly.
The way that database snapshots are implemented is copy-on-write to sparse files, so when you create a snapshot, a sparse file (a file that is filled with zero, which the OS knows not to save to the disk, so you don't waste space). Any time a page has to be written, it's copied to the sparse file first, and then written to disk.
This means that you get double the I/O if you've a snapshot, but also that you get to see your database at the point you created the snapshot. It's very cool, and restoring to the snapshot is very fast, compared to backups.
One problem that I run into is that I didn't consider the cost of multiply snapshots, at one point I had seven snapshots of the same database, and I was wondering why operations was taking so long (I multiplied the I/O cost {which was significant to start with} by X8!).
Either way, this is a cool thing just technologically speaking. I've researched a bit of implementing this as a stream in .Net, but is seems that there isn't a bulitin way to set a region in the file to zero data, and I don't want to use P/Invoke to call DeviceIoControl().
Comments
Comment preview