Take the 2-minute tour ×
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.

We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints.

If I go into the Database Properties for the test database and click the "Failover" button, will it fail over the production database as well since both databases share a mirror endpoint and their Server Network Addresses properties are the same?

enter image description here

I am concerned because when I set up mirroring for the 2nd database, I did not have to configure anything new. It just used all the existing information.

If I use the "Failover" button from the Database Properties, will it result in failing over all databases that use that endpoint, or just the specific database that I am viewing the properties for?

share|improve this question

2 Answers 2

up vote 20 down vote accepted

@mrdenny's answer is accurate that failing over one database will not result in all the other databases failing over as well.

However just to give more overview of what a database mirroring Endpoint is:

From BOL,

Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables SQL Server to communicate over the network. For database mirroring, a server instance requires its own, dedicated database mirroring endpoint. All mirroring connections on a server instance use a single database mirroring endpoint. This endpoint is a special-purpose endpoint used exclusively to receive database mirroring connections from other server instances.

To be more flexible, you can use T-sql for doing manual failover:

Turn mirroring off (break mirroring) on the mirror DB.

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Set the recover mod the DB to RECOVERY

RESTORE DATABASE <<DB NAME>> WITH RECOVERY

Fix and orphaned users if you are using SQL authentication

EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’

EDIT:

Since you are using high safety mode (as per your screenshot), you can use the following from the principal server

ALTER DATABASE <<DB NAME>> SET PARTNER FAILOVER

Doing so, only the role reversal happens and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN.

Note that per this MSDN article

Manual failover can be initiated only from the principal server.

If you want to automate the entire failover then refer Database Mirroring Automation

share|improve this answer
    
Thank you @Kin, that was quite helpful :) –  Rachel May 9 '13 at 17:46
    
If I were to use T-SQL for doing a manual failover like you have in your answer, is there a T-SQL way of setting up the old principal database as the mirror? It doesn't look like I can manually put the old principal database in NORECOVERY mode to make it take over as the mirror, although the failover button from the database properties does do that automatically. –  Rachel May 9 '13 at 19:50
    
@Rachel I have updated my answer. –  Kin May 9 '13 at 20:30
3  
Good answer! Join us on chat.stackexchange.com/rooms/179/the-heap if you want to interact and chat more –  gbn May 10 '13 at 7:42
    
Thank you very much @Kin! I hope you don't mind, but I've suggested an edit to your answer to include the actual answer to my question so that I can accept your answer :) –  Rachel May 10 '13 at 12:07

No it will not. The databases are failed over independently from each other. There's no harm is failing over the test database, the production database will stay where it is.

share|improve this answer
    
Thank you very much. I thought that might have been the case, but I wanted to confirm it before testing, and was having problems finding anything definitive using Google. –  Rachel May 9 '13 at 17:45

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.