So, like most funny stories (involving me), this one starts with “Yesterday, I had this bright idea...” and goes downhill from there.
Database mirroring is great. Full stop. But there’s this one little catch: it can only have a single mirror copy of the principal. Awesome for HA, but not so good for scale out.
Because database mirroring with SQL Server 2005 and 2008 makes available this great way to leverage your failover partner database for reporting purposes (to get some value out of your high availability solution besides insurance and a good night’s sleep) using database snapshots... well, you just had the same idea that I did, right?
It only seemed logical that log shipping would allow for the same AND allow for multiple target servers/databases, right?
Not so much.
With 2005, the log shipping target in a warm standby mode is in a perpetually restoring state (sort of like a mirror copy, eh? *grumble*), but you can’t take a snap of a database in restoring mode. Of course, I tried it. Wouldn’t you?
And even though Katmai exposes the feature in the UI now that allows for a log shipping target database to be online and query-able in read-only mode (if memory serves, you could do it the “hard way” in Yukon), it’s not possible to take a database snap shot of it... because it’s read-only. Did you know that creating a database snapshot actually modifies the database being snapped? I’d never thought about it. And now I know the answer!
The error message when creating the snap of a read-only database isn’t intuitive, either (if you read from the bottom up – the first one’s pretty clear – don’t ask): “A database snapshot cannot be created because it failed to start.” Huh?
Read-only standby mode shows some promised BUT you’ve got to close all open connections to the database and put it in a restoring mode in order to restore future tran log backups to it as part of your log shipping. Doh!
Needless to say, it would be pretty handy [if this worked] for a scale out scenario where I need to keep, oh, say, 44 copies of the same database continuously in sync AND available 100% of the time for customers to query, but nooo... It couldn’t possibly be that simple.
But you know, I probably wouldn’t be happy (or have a fun job) if it was that simple. Heh. Besides, not I get to go Visio up a more complicated solution, right?