How to check if database is in mirrored mode?

Question

I have two database servers A and B where I have databases set up in mirroring namely X. There is another database on server A named Y which is only present on database server A. Y database has a stored procedure SP_Z which refers one table from database X to compute some value.

When database X on server A is in principle mode the SP_Z stored procedure in database Y works perfectly but when database X from server A trips and goes down in mirrored mode and database X on server B becomes principle database the SP_Z in Y database fails as it can not find database X.

How do I solve this problem? I understand the question is presented in complicated manner. Feel free to edit.

Answer 1

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
END IF

Answer 2

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?

EDIT:

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

Answer 3

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
     BEGIN
          -- Database is in principal mode
     END
Else If @offline = 1 
     BEGIN
          -- Database is in mirrored mode
     END