Let say, that I have principal server named P, and mirror server named M. I've got 7 databases that are in server P, and 7 mirror databases on server M. Whole engine works fine when we are talking about server failover. But sometimes I've got events, when only one database is broken ( for example data consistency problem ). Then this one database is switched to mirror server, and rest stay on principal server. What's wrong with this solution ?
On databases there are procedures and views, that use full-name object calling, for example:
SELECT * FROM DATABASE1.dbo.TABLE1
LEFT JOIN DATABASE2.dbo.TABLE2
And now, when only DATABASE1 is switched, and when SP from DATABASE1 tries to select TABLE2 from DATABASE2 – it is impossible. DATABASE2 is in restoring mode.
My idea is to switch all database to MIRROR server whenever one database is broken. Is there a way to detect database failover ? (maybe trigger) Maybe You've got better ideas to solve this problem ?
Best Answer
Glen Berry has some example code on his blog here - http://sqlserverperformance.wordpress.com/2012/03/27/sql-server-database-mirroring-tips-and-tricks-part-3/ - which aims to fix this problem. It works by monitoring the tagged databases, and if it notices one of them has failed across to the mirror it will fail the others across. I had a play with it after a similar question on SE a couple of weeks ago and it does the job, though whether it's as fast/reliable as clustering is something you'll have to evaluate for yourself.
Grouping mirrored databases into a 'group' which fails over together is a feature of Availability Groups in SQL Server 2012.