Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-12-30
15
Medium Priority
?
1,388 Views
Last Modified: 2012-12-31
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?
0
Comment
Question by:Carl Webster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38732390
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

Open in new window

0
 
LVL 37

Author Comment

by:Carl Webster
ID: 38732447
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38732585
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.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 37

Author Comment

by:Carl Webster
ID: 38732593
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38732601
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
0
 
LVL 37

Author Comment

by:Carl Webster
ID: 38732718
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38732754
not sure about why that happens.
I don't have sql servers available during my "off time" to play with this... :(
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 38732813
You shouldnt have to worry (about query on the other server) - in a mirror, they must be one or the other of Principal or Mirror.

So if SQL1 is Principal then SQL2 must be Mirror. You might want to check to see that they are fully synchronised...

The "normal" query to check is :

SELECT db.name, m.mirroring_role_desc, m.mirroring_state_desc, m.mirroring_role_sequence 
FROM sys.database_mirroring m 
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'whatever_my_dbname_is'; 
GO

Open in new window


That should return Principal or Mirror, and the other column of interest is that state description.

When you issue the SET PARTNER FAILOVER you are effectively performing a manual failover and so the roles are indeed reversed.

You can see how many times the roles have swapped with m.mirroring_role_sequence

There really shouldnt be any need to query the other database, it simply cannot have two principals (or it is broken and the other server is not available or not fully synch'd so no failover is possible)

Worthwhile checking out those system mirror views : http://msdn.microsoft.com/en-us/library/ms178655(v=sql.100).aspx

If you have by chance set up a witness then there is good summary information in sys.database_mirroring_witnesses  also note the other links to these other views down the bottom of the link above.
0
 
LVL 37

Author Comment

by:Carl Webster
ID: 38732877
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.
0
 
LVL 37

Author Comment

by:Carl Webster
ID: 38732894
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38732895
OK, then there is also the mirror_server_name and principal_server_name in sys.database_mirroring_witnesses that you can check...
0
 
LVL 37

Author Comment

by:Carl Webster
ID: 38732906
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. :)
0
 
LVL 37

Author Comment

by:Carl Webster
ID: 38732910
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)?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38732924
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).
0
 
LVL 37

Author Closing Comment

by:Carl Webster
ID: 38732934
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.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question