T-SQL for SQL Server 2008 to detemine who is Principal and who is Mirror
I have setup mirroring for a database using SQL Server 2008 SP2.
When I look in the SQL Mgmt Studio my SQL1 server shows as (Principal, Synchronized) and my SQL2 server shows as (Mirror, Synchronized/Restoring...).
When I issue the follow T-SQL:
USE [Master]
GO
ALTER DATABASE [$(varDB)] SET PARTNER FAILOVER
GO
The GUI shows the reverse. i.e SQL2 is now Principal and SQL1 is now Mirror.
Is there some T-SQL code I can use to show the Principal and Mirror status for the database?
Microsoft SQL Server 2008
Last Comment
Carl Webster
8/22/2022 - Mon
Guy Hengel [angelIII / a3]
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
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?
Guy Hengel [angelIII / a3]
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?
Guy Hengel [angelIII / a3]
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
Carl Webster
ASKER
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
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.
Carl Webster
ASKER
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
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.
OK, then there is also the mirror_server_name and principal_server_name in sys.database_mirroring_witnesses that you can check...
Carl Webster
ASKER
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. :)
Carl Webster
ASKER
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)?
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).
Carl Webster
ASKER
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.
To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement:
Open in new window