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.

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
add comment

migrated from stackoverflow.com Mar 15 '13 at 12:06

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

3 Answers

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
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
add comment

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

share|improve this answer
add comment

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
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
add comment

Your Answer

 
discard

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