Programmatically detect which SQL Server is principal,which is the mirror?

Question

I am looking for a way to programmatically query which SQL server is serving as the principal SQL server and which one is the mirror in a pair. Does anyone know if this is possible via PowerShell (SQLPS) or CMD (SQLCMD)? If so, how can I go about querying this info?

Thanks in advance!

Answer 1

I don't have an instance handy to test with, but I think you should be able to query sys.database_mirroring_witnesses (SQL Server 2005 or later) using SQLCMD to get at the information you are looking for.

EDIT
Thanks to @Remus for the great comment. The appropriate place to look is in sys.database_mirroring.