Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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 ?

share|improve this question
1  
If you set up a failover cluster you should let the system handle all the dependancies there in. You should not have views that specifically link one node to the other - you should only address the cluster as a cluster. [cluster's-DNS-name].[database].[schema].[table] – MatBailie Sep 3 '12 at 8:21
Unfortunately we've got restricted budget, and except mirror databases, there are other databases on server M and P. If cluster failover switch all databases, this solution is impossible. Also I don't have impact on database naming conventions. Scripts from our databases are migrated to clients, and they have often other policies, and server names. Surely, in the future I would try to talk with our supervisors to use other solutions, but now, I have to solve this problem immediatly – Norbert Ozdoba Sep 3 '12 at 8:34

migrated from stackoverflow.com Sep 5 '12 at 15:19

1 Answer

up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.