locked
How to determine the database mirroring role via Powershell? RRS feed

  • Question

  • Is there a way to determine the current mirroring role of a database via Powershell without the use of t-sql?

    With the Microsoft.SqlServer.Management.Smo namespace and database class I can determine the status, witness and partner of the mirrored database, but I can't determine the mirroring role of the current database. I basically want to get the same result as the following t-sql query in powershell without using t-sql:

    SELECT m.mirroring_role_desc 

    FROM   sys.database_mirroring m JOIN sys.databases d

    ON     m.database_id = d.database_id

    WHERE  d.name = 'databasename'

    I find it very strange that there is no class property to check for this, while it is an important property of the database. Of course I can use the output of this query in my powershell script for further processing, but I still hope that there is a property where I can get this information.

    TheSultan8

     

    Tuesday, November 29, 2011 11:33 AM

Answers

  • Just to let you know, I've already found it. Here is a part of the code that I use for checking each mirrored database what mirroring role it has:
    $sqlconnection="MYLAPTOP\SQL2008R2"
    $smo = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlconnection
    $mirroreddbs=$smo.Databases | where {$_.IsMirroringEnabled -eq "True"}
    $sqlversionmajor=$smo.VersionMajor
    foreach ($database in $mirroreddbs)
    {
    # Versions lower than SQL 2008 can't query a database in recovery mode
    if ($sqlversionmajor -ge 10) 
    {
    $dbrolevalue=$database.DatabaseOptions.Properties | where {$_.Name -eq "MirroringRole"}
    $dbrole=$dbrolevalue.Value
    }
    }
    One catch though, this only works on SQL 2008 and higher version.
    TheSultan8
    • Marked as answer by thesultan8 Tuesday, November 29, 2011 2:17 PM
    Tuesday, November 29, 2011 2:17 PM

All replies

  • http://powershell.com/cs/media/p/169.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 29, 2011 11:43 AM
  • http://powershell.com/cs/media/p/169.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    This link shows how to execute a sql query from a text file, not related to my question.
    Tuesday, November 29, 2011 12:41 PM
  • Just to let you know, I've already found it. Here is a part of the code that I use for checking each mirrored database what mirroring role it has:
    $sqlconnection="MYLAPTOP\SQL2008R2"
    $smo = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlconnection
    $mirroreddbs=$smo.Databases | where {$_.IsMirroringEnabled -eq "True"}
    $sqlversionmajor=$smo.VersionMajor
    foreach ($database in $mirroreddbs)
    {
    # Versions lower than SQL 2008 can't query a database in recovery mode
    if ($sqlversionmajor -ge 10) 
    {
    $dbrolevalue=$database.DatabaseOptions.Properties | where {$_.Name -eq "MirroringRole"}
    $dbrole=$dbrolevalue.Value
    }
    }
    One catch though, this only works on SQL 2008 and higher version.
    TheSultan8
    • Marked as answer by thesultan8 Tuesday, November 29, 2011 2:17 PM
    Tuesday, November 29, 2011 2:17 PM
  • It is indeed possible to check the mirrored status on anything older than SQL2008 using if($database.IsAccessible.Equals($True)){ $dbrole = "Primary" } instead of the $database.DatabaseOptions.Properties line. 

    if it is the primary it will return $True. If it is the mirror it will return $False. Same general info just a different way of going at it.

    Tuesday, November 4, 2014 5:32 AM