SQL Server Failover – Do All Databases Sharing the Same Mirror Endpoint Fail Over?

failovermirroringsql serversql-server-2005

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?

Best Answer

@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

Related Question