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 ?