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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two database servers Server1 and Server2, configured with mirroring. A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on Server1 and is not mirrored. OtherDB has a stored procedure named SP_Z which refers to a table in MirrorDB to compute some value.

When Server1 is the principal for MirrorDB the SP_Z stored procedure in OtherDB works perfectly, however when MirrorDB fails over to Server2 the SP_Z in OtherDB fails as it can not open MirrorDB.

How do I solve this problem?

share|improve this question

migrated from Mar 15 '13 at 12:06

This question came from our site for professional and enthusiast programmers.

At first I was expecting your error to be located at the point of retrieving SP_Z procedure. Sorry for being hasty.

This link gives an explanation on how to query database mirroring status of your database. So, you do the following:

DECLARE @MirroringRole int;
SET @MirroringRole = (SELECT mirroring_role
    FROM sys.database_mirroring
    WHERE DB_NAME(database_id) = N'DB_X');   -- your database name here
IF @MirroringRole = 2 -- Mirror
    -- connect to the failover partner server, using your database
ELSE IF @MirroringRole = 1 -- Principal
    -- connect to this server
share|improve this answer
Good solution .... just to add few cents ....To connect to the principal (when mirroring trips down), you need to configure Linked server and make sure that you set RPC and RPC out properties to "TRUE". then you can use 4 part convention to execute the SP e.g. exec [linkedserver].[dbname].[schema].[sp_name]. – Kin Apr 18 '13 at 16:29

Generally when connecting to mirrored databases, you wouldn't just connect directly to the principal instance, you would use a connection made by the native client or odbc driver as pacified in the link below which would know about the failover partner and retry the connection on that if it couldn't access the principal:

Connecting Clients to A Mirrored Database

As it seems you are connecting directly (via a linked server I presume???) you may need to incorporate some try catch logic into your procedure perhaps?


I did a bit more research on this and if you are using a linked server connection to your mirrored database, you can set up your linked server so that it supports database mirroring by following the instructions in the article below:

SQL Server linked server that supports database mirroring failover

share|improve this answer

This is what I use in my SQL jobs. Hope it helps.

DECLARE @offline int

SET @offline = (
select state from sys.databases where name = [DATABASE_NAME]

If @offline = 0
          -- Database is in principal mode
Else If @offline = 1 
          -- Database is in mirrored mode
share|improve this answer
why would you use sys.databases when mirroring has its own DMV's that will give accurate information ? – Kin Apr 18 '13 at 16:35
What difference does it make if I query sys.databases or sys.database_mirroring? – avakharia May 10 '13 at 2:42

I have this script I generally use before I need to DETACH a database. it tests for mirror too.


-- before detaching the database
-- see what files it has and where they are located

-- checks mirror and replication
-- database must not be part of mirror (neither as principal nor mirror) in order to be detached
      ,DB_in_Mirror=COALESCE(m.mirroring_role_desc,'Not Part in Mirror')  --database must not be part of mirror (neither as principal nor mirror) in order to be detached
      ,[Size (Gb)]=CAST(f.size/1024.00/1024.00 AS NUMERIC(18,2))
 FROM sys.databases d
      INNER JOIN SYS.master_files f 
                 ON d.database_id = f.database_id

      LEFT OUTER JOIN sys.database_mirroring m
                      ON d.database_id = m.database_id            
where 1 = 1
  AND d.state = 0 -- online 
  AND D.is_published = 0 -- not on replication
  AND d.is_subscribed = 0 -- not on replication
  AND d.is_merge_published = 0 -- not on replication
  AND d.is_distributor = 0 -- not on replication
  AND like '%OrderArchive' -- the dbs that i need to detach have OrderArchive at the end of their names
share|improve this answer

Your Answer


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