Programatically Determine Master Server in SQL Failover Scenario

Question

Part 1: In a Microsoft SQL Server 2005 failover scenario, how can the databases be queried to determine which server is the master and which server is the slave? I'm looking for something more elegant than attempting to query on the server and see which one throws an exception.

Part 2: Is there a way to monitor and determine when the master SQL Server fails over to the slave?

The end goal of this is to have a script, or another method, that will keep track of the servers and send notifications when a failover occurs.

Answer 1

From SQL 2005 BOL, there is this article Monitoring Mirroring Status which looks useful.

Otherwise, if you query SELECT @@SERVERNAME from a stored proc in the mirrored DB, you would be able to determin which SQL Server is master.