Thursday, January 11, 2007

SQL 2005 Database Snapshots

I read this article on TechRepublic about SQL 2005 snapshots this morning.

It's a pretty simple "how to" post showing how to create a snapshot of a database which is touted to be a great solution for reporting and even disaster recovery.

In my new role, I haven't even had a need to install SQL 2005 yet - so I reached for the DVD and thought I'd at least take the steps of creating a snapshot before commenting. But the process of running a few SQL commands to create a snapshot is not really the point here - the interesting part of this post (as is often the case) was in the comments. A couple of guys "go at it" - briefly - on the relative merits of snapshots in general. The main gist of it being - are they really worthwhile? Is a handy solution for a read-only, no locks database worth the hassle of creating a snapshot and maintaining a snapshot maintenance plan - which must be on the same machine and perhaps doesn't even solve anything with regards to lightening the load on that server. I don't purport to know the answer but frankly, in the small environments I'm used to working in, I don't think these snapshots will have a place. Too much planning, work and understanding for too little reward. I'm not the target market though, that's for sure! It will be interesting to watch this evolve though. Will it just disappear into obscurity like so many "major new features" or will it actually be the greatest thing since sliced bread? I expect the former but that's just me? I'd love to hear what real DBAs think of it - I'll have to go find one and ask him?