I believe I got what I needed. Someone on EE finally chimed in on my question and threw me a hint. I took his suggestion and came up with my own solution.
What I have is a SQL Mirror with Witness setup. What I needed was a way to verify who is Principal and who is Mirror without having to use the GUI. The code the guy on EE gave me was: 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 But that only gave me the info on the SQL server I ran the query on. I needed the same info on both SQL servers. What I came up with is a batch file to gather parameters and pass them to sqlcmd. Here is (what I can share of) my batch file: REM %1 is the principal sql server REM %2 is the mirror sql server REM %3 is the database name setlocal enabledelayedexpansion if %1!==! goto usage if %2!==! goto usage if %3!==! goto usage ECHO Verification of database started Echo. REM -S is the principal server REM -V anything below errorlevel 25 causes an abort REM -i input file 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 Echo. ECHO Verification of database completed GOTO End :usage Echo required parameters are missing Echo SQL7_VerifyPrincipalMirror.bat PrincipalSQLServerName MirrorSQLServerName DatabaseName GOTO end :Error1 Echo AN ERROR HAS OCCURED in verifying the %3 database on server %1 !!!!! Echo AN ERROR HAS OCCURED in verifying the %3 database on server %1 !!!!! > c:\install\xenapp\logs\SQL7_VerificationErrorLog.txt GOTO End :Error2 Echo AN ERROR HAS OCCURED in verifying the %3 database on server %2 !!!!! Echo AN ERROR HAS OCCURED in verifying the %3 database on server %2 !!!!! > c:\install\xenapp\logs\SQL7_VerificationErrorLog.txt GOTO End :End 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 And my two output files: Results for SQL Server SQL1 Database Name Mirror Role Mirror State # Times Failed Over ------------------------- --------------- --------------- ------------------- DATA_STORE PRINCIPAL SYNCHRONIZED 3 Results for SQL Server SQL12 Database Name Mirror Role Mirror State # Times Failed Over ------------------------- --------------- --------------- ------------------- DATA_STORE MIRROR SYNCHRONIZED 3 I had been struggling with this all weekend (along with a much more complicated issue I will hit you up for). I thought no one was ever going to respond on EE. Sometimes all I need is just a hint to break the log jam. For someone who can barely spell SQL, not a bad solution. Thanks Webster From: Manuel Santos [mailto:[email protected]] Sent: Monday, December 31, 2012 10:22 AM To: NT System Admin Issues Subject: Re: Need some Microsoft SQL help I'm pretty good on that. You may contact me 2012/12/31 Webster <[email protected]<mailto:[email protected]>> Is there any one on this list that is REALLY good with Microsoft SQL Server and T-SQL that I can contact off the list? Specifically SQL Server 2008 SP2 if that makes any difference. Thanks Webster ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected]<mailto:[email protected]> with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected]<mailto:[email protected]> with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin
