While working on a customer implementation of Microsoft SQL Server 2008 R2 and Citrix DataStream I ran into a need to quickly failover mirror partners between servers for testing reasons. This can be done easily via the ALTER DATABASE command but to save time I wrapped the command up into a dynamic Stored Procedure.

mirroring_role being equal to 1 indicates it has the PRINCIPAL role where the mirroring_state being equal to 4 indicates it is SYNCHRONIZED.

The procedure should be run on the server where the database is the PRINCIPAL. Running it on the MIRROR server will not result in any changes. Using Linked Servers and 2 parameters in the Stored Procedure such as:

sps_FailoverMirror (@databaseName nvarchar(200), @serverOne nvarchar(200), @serverTwo nvarchar(200))

would allow the Stored Procedure to query both servers, find the PRINCIPAL server, and perform the failover.