This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:
- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples
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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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,mirro
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?
I found this:
select mirroring_state_desc,mirro
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
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_addlinkedser
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrv
GO
SELECT name FROM [SQL2].master.sys.sysdatab
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.
USE [master]
GO
EXEC master.dbo.sp_addlinkedser
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrv
GO
SELECT name FROM [SQL2].master.sys.sysdatab
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... :(
I don't have sql servers available during my "off time" to play with this... :(
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 :
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_wit
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
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_wit
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
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.
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
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\Ver
sqlcmd -S %1 -V 25 -v varDB=%3 -i c:\install\xenapp\VerifyPr
if errorlevel 1 goto Error1
echo Results for SQL Server: %2 > c:\install\xenapp\logs\Ver
sqlcmd -S %2 -V 25 -v varDB=%3 -i c:\install\xenapp\VerifyPr
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.
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\Ver
sqlcmd -S %1 -V 25 -v varDB=%3 -i c:\install\xenapp\VerifyPr
if errorlevel 1 goto Error1
echo Results for SQL Server: %2 > c:\install\xenapp\logs\Ver
sqlcmd -S %2 -V 25 -v varDB=%3 -i c:\install\xenapp\VerifyPr
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_wit
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
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. :)
set nocount on;
go
SELECT 'Database Name' = Left(db.name,25), 'Mirror Role' = Left(m.mirroring_role_desc
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_wit
returns 0 rows
Or should that be run on the Witness server (SQLWitness in my lab)?
select * FROM sys.database_mirroring_wit
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).
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).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial