How to determine mirroring role?

Once I have identitied that mirroring is enabled on database via SMO Database.IsMirroringEnabled, I need to determine the mirroring role. I noticed an enumerated type - MirroringRole, but no SMO method/property to access it.

I have tried to query the sys.database_mirroring table directly, but this fails with an exception on the mirror database - as it is being mirrorred :-(

What is the recommended way to determine the mirroring role?

Thanks, Nick

You might try in calling from a C# embedded in SMO, like converting the value from c# to smo.|||

Not sure what you mean, I have tried querying the database ...

String query = String.Format("SELECT mirroring_role FROM sys.database_mirroring WHERE mirroring_guid = '{0}'", db.MirroringID.ToString());

DataSet dsResultSet = db.ExecuteWithResults(query);

.. but this causes an exception as the database is being mirrored.

+ base {"Execute with results failed for Database 'Mms'. "} Microsoft.SqlServer.Management.Smo.SmoException {Microsoft.SqlServer.Management.Smo.FailedOperationException}
+ InnerException {"The database \"Mms\" cannot be opened. It is acting as a mirror database."} System.Exception {System.Data.SqlClient.SqlException}

Thanks, Nick

|||

You don't need to connect to the mirror database (you can't, as you have experienced). You can connect to the master database on the mirror server and execute the following query:

SELECT m.mirroring_role_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE d.name = '<your_database_name_here>'