Database Mirroring – determine witness server/instance name programmatically?

Question

Post reply

Database Mirroring – determine witness server/instance name programmatically?

I300I

SSC Eights!

Points: 828
More actions
August 7, 2008 at 10:31 am

#122842

I am a DBA working with a .NET developer to design an application using SMO to handle database mirroring tasks, including mirroring setup and teardown.
We have a use case where mirroring teardown can be initiated against either the principal server, or the mirror server. In either case, we need to identify the witness server/instance name programmatically. We cannot find these properties for the witness server in SMO classes, or in any SQL catalog or dm views.
sys.database_mirroring
sys.database_mirroring_endpoints
sys.database_mirroring_witnesses
sys.dm_db_mirroring_connections
The closest we can find is sys.database_mirroring.mirroring_witness_name from the principal or mirror servers, but this is in the form “TCP://servername:portnumber”. I don’t think we can determine a SQL instance name from this endpoint information. We could attempt to iterate through catalog views on each SQL instance on the server name reported above, but this seems like a slow and error-prone option.
TIA,
Dan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Here's the code I use. Be sure to read the comments.

--Query for mirror status. This is how you check that the mirror is operational.

--This can be run from the primary and secondary servers, but when run from the witness

--all results will be null.

select DB_Name(database_id) as DBName,

mirroring_state_desc as MirrorState,

mirroring_role_desc as Role,

mirroring_safety_level_desc as SafetyLevel,

mirroring_role_sequence as RoleSequence,

mirroring_partner_name as Partner,

Mirroring_partner_instance as PartnerInstance,

mirroring_witness_name as Witness,

Mirroring_witness_state_desc as WitnessState

from sys.database_mirroring

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

I300I SSC Eights! Points: 828 More actions · Answer 2

crever,

You've shown me how to get the mirroring information that I already have (albeit, with new column aliases). That query returns the witness endpoint address. I'm trying to find the SQL instance name of the witness.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 3

You should just be able to parse the instance name from the server name ... it will be anything after the backslash (if it exists) and before the colon. If there is no backslash, it is running as the default instance.

I300I SSC Eights! Points: 828 More actions · Answer 4

Yeah, I wish parsing was the challenge...

Our dev witness instance is a named one and like I said above, the only reference to it from the principal or mirror catalog/dm views are TCP://servername:portnumber (no instance name).

I'm starting to think I'm going to have to store this info in a table when mirroring is setup. 🙁

matt stockham (8/7/2008)
You should just be able to parse the instance name from the server name ... it will be anything after the backslash (if it exists) and before the colon. If there is no backslash, it is running as the default instance.

joe_goeke 54489 SSC Enthusiast Points: 126 More actions · Answer 5

did you ever figure out how to do this? I have the same need 🙂