T-SQL for SQL Server 2008 to detemine who is Principal and who is Mirror

from the docs: http://msdn.microsoft.com/en-us/library/bb522476.aspx
To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement:

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

Select all Open in new window

For both servers that shows, PARTNER, STARTED.  It doesn't tell me who is currently the Principal or Mirror.

I found this:

select mirroring_state_desc,mirroring_role_desc from sys.database_mirroring where database_id =(select database_id from sys.databases where name='DBName')

That tells me who is Principal and who is Mirror but, since I can barely spell SQL, I can't figure out how to run this from one SQL 2008 SP2 server and get the info from both SQL servers.

In my case, I have two SQL servers: SQL1 and SQL2 and the database name is Data_Store.

How can I run the code from SQL1 and get the info for SQL2?

if the 2 servers have a linked server setup to point to the other, yes, you should be able to run this via linked server on the "other" server.

May I ask you how to setup the linked server?  Do you need to know anything besides the 2 server names and the database name?

it's quite easy from the GUI:
http://msdn.microsoft.com/en-us/library/ff772782.aspx
you specify the linked server's name, and eventually specify the security tab's settings if the login name/passwords are not the same...
that's it

I need to have a script I can use, so using the T-SQL examples from your link I came up with:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SQL2', @srvproduct=N'SQL Server' ;
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL2', @locallogin = NULL, @useself = N'True' ;
GO

SELECT name FROM [SQL2].master.sys.sysdatabases ;
GO

But what I get are the databases on SQL1.

name                                                                                                                            
----------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                          
msdb                                                                                                                            
Data_Store            
Config_Log

The Config_Log database does not exist on SQL2.

not sure about why that happens.
I don't have sql servers available during my "off time" to play with this... :(

Yes this is setup with a Witness server.

This is what I came up with using your sample code:

set nocount on;
go
SELECT 'Database Name' = Left(db.name,25), 'Mirror Role' = Left(m.mirroring_role_desc,15), 'Mirror State' = Left(m.mirroring_state_desc,15), '# Times Failed Over' = left(m.mirroring_role_sequence,15)
FROM sys.database_mirroring m
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'Data_Store';
GO

Output is:

Database Name     Mirror Role     Mirror State    # Times Failed Over
------------------------- --------------- --------------- -------------------
DATA_STORE         PRINCIPAL       SYNCHRONIZED    3            

hmmm, just a sec.  Let me try just one more thing.

OK, got what I need now.

I created a batch file to gather parameters and then passed it to sqlcmd.

REM %1 is the principal sql server
REM %2 is the mirror sql server
REM %3 is the database name

echo Results for SQL Server: %1 >  c:\install\xenapp\logs\VerifyPrincipalMirrorLog1.txt
sqlcmd -S %1 -V 25 -v varDB=%3 -i c:\install\xenapp\VerifyPrincipalMirror.sql >> c:\install\xenapp\logs\VerifyPrincipalMirrorLog1.txt
if errorlevel 1 goto Error1

echo Results for SQL Server: %2 >  c:\install\xenapp\logs\VerifyPrincipalMirrorLog2.txt
sqlcmd -S %2 -V 25 -v varDB=%3 -i c:\install\xenapp\VerifyPrincipalMirror.sql >> c:\install\xenapp\logs\VerifyPrincipalMirrorLog2.txt
if errorlevel 1 goto Error2

Log1 is:

Results for SQL Server: SQL1
Database Name             Mirror Role     Mirror State    # Times Failed Over
------------------------- --------------- --------------- -------------------
DATA_STORE                PRINCIPAL       SYNCHRONIZED    3                  

Log2 is:

Results for SQL Server: SQL2
Database Name             Mirror Role     Mirror State    # Times Failed Over
------------------------- --------------- --------------- -------------------
DATA_STORE                MIRROR          SYNCHRONIZED    3                  

That will satisfy the QA people who want to see both servers.

Thanks for all the help.

OK, then there is also the mirror_server_name and principal_server_name in sys.database_mirroring_witnesses that you can check...

Oh, sorry, here is my .sql file:

set nocount on;
go
SELECT 'Database Name' = Left(db.name,25), 'Mirror Role' = Left(m.mirroring_role_desc,15), 'Mirror State' = Left(m.mirroring_state_desc,15), '# Times Failed Over' = left(m.mirroring_role_sequence,15)
FROM sys.database_mirroring m
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'$(varDB)';
GO

EE and MSDN are good resources for learning some basic SQL and T-SQL stuff. :)

On SQL1 (what I call my principal SQL server in my little lab setup),

select * FROM sys.database_mirroring_witnesses

returns 0 rows

Or should that be run on the Witness server (SQLWitness in my lab)?

Yep, on SQLWitness...

But it looks like you have a good working solution with your batch job - also provides "security" for your QA people being run from a process (ie sqlcmd) external to the SQL servers in being the same query but applied specifically to your individual servers.

You could add into your select statement an additional column : @@servername as Server

That way the logs will be self documenting (and maybe throw in a getdate() so you gain visibility on period).

Thanks for the help.  You gave me the "bone" I needed to go dig a little further and come up with something that works for my specific situation.

I hope the info I left on how I took your code and made it fit what I needed may help someone else in the future.