Mirror-Database

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Database mirroring setup is a pretty easy thing. The point here is not really to make a complicated process easier but to standadize the way we can set this up.
This script has to be runned on the “Principal” server and can be used either there is a witness server in the infrastructure or not.
A validation of all the used parameters is asked before doing any configuration of the mirroring.

Param(
    $PrincipalServer=([System.Net.Dns]::GetHostByName(($env:computerName)) | Select -ExpandProperty HostName),
    [Parameter(Mandatory=$true, HelpMessage="You must provide a server address to host the mirror; ie: 192.168.1.2 or 192.168.1.2\NamedInstance.")]
    $MirrorServer,
    $WitnessServer,
    $PrincipalServerPort="1433",
    $MirrorServerPort="1433",
    $WitnessServerPort="1433",
    [Parameter(Mandatory=$true)]
    [ValidateScript({
        if ($_ -match "^(master|msdb|temp|model)$") {Write-Host "Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb or model databases.`n" -ForegroundColor Red; Break} else {$True}
    })]
    $DbName,
    [Parameter(Mandatory=$true, HelpMessage="You must provide the Windows Service Account for MS SQL Server; Must be a Domain Account.")]
    $SQLDomainAccount
)
$ErrorActionPreference="SilentlyContinue"

Function Ask-YesOrNo
{
param([string]$title="Confirmation needed",[string]$message)
$choiceYes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", "Answer Yes."
$choiceNo = New-Object System.Management.Automation.Host.ChoiceDescription "&No", "Answer No."
$options = [System.Management.Automation.Host.ChoiceDescription[]]($choiceYes, $choiceNo)
$result = $host.ui.PromptForChoice($title, $message, $options, 1)
    switch ($result)
    {
    0
        {Return $true}
    1
        {Return $false}
    }
}

function Test-Port ([string]$Server, [int]$Port)
{
  Try
  {
    $c=New-Object System.Net.Sockets.TcpClient($Server, $Port)
    $c.Close()
    return $true
  }
  Catch
  {
    [System.Exception]
    return $false
  }
} #EndOf function Test-Port
if(!(Test-Port $PrincipalServer $PrincipalServerPort)) {Write-Host "`nUnable to connect to Principal server (${PrincipalServer}:${PrincipalServerPort}). Exiting now." -ForegroundColor Red; Break}
if(!(Test-Port $MirrorServer $MirrorServerPort)) {Write-Host "`nUnable to connect to Mirror server (${MirrorServer}:${MirrorServerPort}). Exiting now." -ForegroundColor Red; Break}
if ($WitnessServer) { if(!(Test-Port $WitnessServer $WitnessServerPort)) {Write-Host "`nUnable to connect to Witness server (${WitnessServer}:${WitnessServerPort}). Exiting now." -ForegroundColor Red; Break} } else { $WitnessServer="N/A" }

Write-Host "`nAutomatic Database Mirroring tool - Parameters" -ForegroundColor Yellow
Write-Host "*" -ForegroundColor Yellow -nonewline; Write-Host " Principal Server (-PrincipalServer)`t`t`t:`t" -nonewline; Write-Host "$PrincipalServer" -ForegroundColor DarkGreen -nonewline; Write-Host " (TCP $PrincipalServerPort)"
Write-Host "*" -ForegroundColor Yellow -nonewline; Write-Host " Mirror Server (-MirrorServer)`t`t`t`t:`t" -nonewline; Write-Host "$MirrorServer" -ForegroundColor DarkGreen -nonewline; Write-Host " (TCP $MirrorServerPort)"
Write-Host "*" -ForegroundColor Yellow -nonewline; Write-Host " Witness Server (-WitnessServer)`t`t`t:`t" -nonewline; Write-Host "$WitnessServer" -ForegroundColor DarkGreen -nonewline; if ($WitnessServer -notmatch "N/A") {Write-Host " (TCP $WitnessServerPort)"} else {Write-Host ""}
Write-Host "*" -ForegroundColor Yellow -nonewline; Write-Host " Database Name (-DbName)`t`t`t`t:`t" -nonewline; Write-Host "$DbName" -ForegroundColor DarkGreen
Write-Host "*" -ForegroundColor Yellow -nonewline; Write-Host " SQL Service Domain Account (-SQLDomainAccount)`t:`t" -nonewline; Write-Host "$SQLDomainAccount" -ForegroundColor DarkGreen
if (!(Ask-YesOrNo -Message "The parameters above will be used. Are you sure you want to continue?")) {Write-Host "You have chosen to end this script execution. That's a wise decision!"; Break}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | Out-Null
$PrincipalServerConnect=New-Object Microsoft.SqlServer.Management.Smo.Server $PrincipalServer
$MirrorServerConnect=New-Object Microsoft.SqlServer.Management.Smo.Server $MirrorServer
$MirrorDataPath=$MirrorServerConnect.MasterDBPath

Write-Host "`nPrerequisites checks" -ForegroundColor Yellow
$PrincipalServerVersion=[string]::Concat($PrincipalServerConnect.Version.Major, ".", $PrincipalServerConnect.Version.Minor, ".", $PrincipalServerConnect.Version.Build, " - ", $PrincipalServerConnect.EngineEdition, " Edition")
$MirrorServerVersion=[string]::Concat($MirrorServerConnect.Version.Major, ".", $MirrorServerConnect.Version.Minor, ".", $MirrorServerConnect.Version.Build, " - ", $PrincipalServerConnect.EngineEdition, " Edition")
if ($PrincipalServerVersion -match $MirrorServerVersion) {Write-Host "* OK, both principal server ($PrincipalServerVersion) and mirror server ($MirrorServerVersion) are running on the same version and edition of Microsoft SQL Server." -ForegroundColor DarkGreen} else {Write-Host "Principal server ($PrincipalServerVersion) and mirror server ($MirrorServerVersion) are NOT running the same version/edition of Microsoft SQL Server. Exiting now." -ForegroundColor Red; Break}
if ($WitnessServer) {
$PrincipalServerVersionShort=[string]::Concat($PrincipalServerConnect.Version.Major, ".", $PrincipalServerConnect.Version.Minor, ".")
$WitnessServerVersionShort=[string]::Concat($WitnessServerConnect.Version.Major, ".", $WitnessServerConnect.Version.Minor, ".")
if ($PrincipalServerVersionShort -match $WitnessServerVersionShort) {Write-Host "* OK, both principal server ($PrincipalServerVersionShort) and witness server ($WitnessServerVersion) are running on the same version of Microsoft SQL Server." -ForegroundColor DarkGreen} else {Write-Host "Principal server ($PrincipalServerVersionShort) and witness server ($WitnessServerVersion) are NOT running the same version of Microsoft SQL Server. Exiting now." -ForegroundColor Red; Break}
}

$PrincipalServerCollation=$PrincipalServerConnect.Collation
$MirrorServerCollation=$MirrorServerConnect.Collation
if ($PrincipalServerCollation -match $MirrorServerCollation) {
Write-Host "* OK, principal server is configured to use the same collation as mirror server ($PrincipalServerCollation)." -ForegroundColor DarkGreen
} else {
if (!(Ask-YesOrNo -Message "* Principal server is configured to use a different collation ($PrincipalServerCollation) as mirror server ($MirrorServerCollation).Differences can cause a problem during mirroring setup. Are you sure you want to continue?")) {Write-Host "You have chosen to end this script execution. That's a wise decision!"; Break}
}

$RecoveryModel=$PrincipalServerConnect.Databases | Where-Object {$_.Name -match $DbName} | Select-Object RecoveryModel
if ($RecoveryModel -match "Full") {Write-Host "* OK, the recovery model for database `"$DbName`" is Full." -ForegroundColor DarkGreen} else {Write-Host "The principal database must be in the FULL recovery mode. Exiting now." -ForegroundColor Red; Write-Host "Here is the SQL query to run to enable the FULL recovery mode on your database:`nALTER DATABASE $DbName`nSET RECOVERY Full"; Break}

$ExistingMirror=$PrincipalServerConnect.Databases | Where-Object { $_.IsMirroringEnabled -and ($_.Name -match "$DbName") }
$ExistingMirrorPartner=$ExistingMirror | % {$_.MirroringPartner}
$ExistingMirrorPartnerInstance=$ExistingMirror | % {$_.MirroringPartnerInstance}
$ExistingMirrorWitness=$ExistingMirror | % {$_.MirroringWitness}
if (!$ExistingMirrorWitness) {$ExistingMirrorWitness="N/A"}
if (!$ExistingMirror) {
Write-Host "* OK, $DbName database is not already mirrored." -ForegroundColor DarkGreen
}
else {
Write-Host "$DbName database is already mirrored with $ExistingMirrorPartnerInstance (EndPoint: $ExistingMirrorPartner - Witness: $ExistingMirrorWitness). Exiting now." -ForegroundColor Red; Break
}
$ExistingMirror
Write-Host "Prerequisites checks completed successfully.`n" -ForegroundColor Yellow

$DbBackUpQuery=@"
USE master
GO
BACKUP DATABASE $DbName
TO DISK = '\\${MirrorServer}\C$\${DbName}-temp_for_mirror.bak'
GO
BACKUP LOG $DbName
TO DISK = '\\${MirrorServer}\C$\${DbName}-temp_for_mirror.bak'
GO
"@

$DbRestoreQuery=@"
USE master
GO
RESTORE DATABASE $DbName
FROM DISK = 'C:\${DbName}-temp_for_mirror.bak'
WITH FILE = 1, MOVE '${DbName}_log' TO
N'${MirrorDataPath}\${DbName}_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10;
GO
RESTORE LOG $DbName FROM DISK = 'C:\${DbName}-temp_for_mirror.trn' 
WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
"@

Add-PSSnapin SqlServerCmdletSnapin100
Write-Host "Backup of the $DbName database started. Please wait..."
Try { Invoke-Sqlcmd -ServerInstance $PrincipalServer -Query $DbBackUpQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Backup of the $DbName database completed successfully." -ForegroundColor Yellow

Write-Host "Restore of the $DbName database started on $MirrorServer. Please wait..."
Try { Invoke-Sqlcmd -ServerInstance $MirrorServer -Query $DbRestoreQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Restore of the $DbName database on $MirrorServer completed successfully." -ForegroundColor Yellow

Remove-Item \\${MirrorServer}\C$\${DbName}-temp_for_mirror.bak

$DbPrincipalEndpointQuery=@"
USE master
GO
DROP ENDPOINT DBMirror_Principal
GO
CREATE ENDPOINT DBMirror_Principal
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING (ROLE=PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE);
GO
"@
$DbMirrorEndpointQuery=@"
USE master
GO
DROP ENDPOINT DBMirror_Mirror
GO
CREATE ENDPOINT DBMirror_Mirror
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING (ROLE=PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE);
GO
"@
$DbWitnessEndpointQuery=@"
USE master
GO
DROP ENDPOINT DBMirror_Witness
GO
CREATE ENDPOINT DBMirror_Witness
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING (ROLE=WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE)
GO
"@

$DbGrantPrincipal=@"
CREATE LOGIN [${SQLDomainAccount}] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Principal TO [${SQLDomainAccount}]
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Principal TO [$MirrorServer]
GO
"@
$DbGrantMirror=@"
CREATE USER [${SQLDomainAccount}] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Mirror TO [${SQLDomainAccount}]
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Principal TO [$PrincipalServer]
GO
"@
$DbGrantWitness=@"
CREATE LOGIN [${SQLDomainAccount}] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Witness TO [${SQLDomainAccount}]
GO
"@

$DbPrincipalPartnerQuery=@"
USE master
GO
ALTER DATABASE $DbName SET PARTNER = 'TCP://${MirrorServer}:5022'
GO
ALTER DATABASE $DbName SET PARTNER SAFETY FULL
GO
"@
$DbMirrorPartnerQuery=@"
USE master
GO
ALTER DATABASE $DbName SET PARTNER = 'TCP://${PrincipalServer}:5022'
GO
"@
$DbPrincipalWitnessQuery=@"
USE master
GO
ALTER DATABASE $DbName SET WITNESS = 'TCP://${WitnessServer}:5022'
GO
"@

Write-Host "Granting access on the Endpoints for $SQLDomainAccount account. Please wait..."
Try { Invoke-Sqlcmd -ServerInstance $PrincipalServer -Query $DbGrantPrincipal }
Catch { $_.Exception.Message ; Break }
Try { Invoke-Sqlcmd -ServerInstance $PrincipalServer -Query $DbGrantMirror }
Catch { $_.Exception.Message ; Break }
if ($WitnessServer -notmatch "N/A") {
Try { Invoke-Sqlcmd -ServerInstance $PrincipalServer -Query $DbGrantWitness }
Catch { $_.Exception.Message ; Break }
}

Write-Host "Creation of the Endpoints for Mirroring. Please wait..."
Try { Invoke-Sqlcmd -ServerInstance $PrincipalServer -Query $DbPrincipalEndpointQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Creation of the Endpoint 'DBMirror_Principal' on $PrincipalServer completed successfully." -ForegroundColor Yellow
Try { Invoke-Sqlcmd -ServerInstance $MirrorServer -Query $DbMirrorEndpointQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Creation of the Endpoint 'DBMirror_Mirror' on $MirrorServer completed successfully." -ForegroundColor Yellow
if ($WitnessServer -notmatch "N/A") {
Try { Invoke-Sqlcmd -ServerInstance $WitnessServer -Query $DbWitnessEndpointQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Creation of the Endpoint 'Mirroring' on $WitnessServer completed successfully." -ForegroundColor Yellow
}

Write-Host "Creation of partnership for Mirroring. Please wait..."
Try { Invoke-Sqlcmd -ServerInstance $PrincipalServer -Query $DbPrincipalPartnerQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Partner for $PrincipalServer created successfully." -ForegroundColor Yellow
Try { Invoke-Sqlcmd -ServerInstance $MirrorServer -Query $DbMirrorPartnerQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Partner for $MirrorServer created successfully." -ForegroundColor Yellow
if ($WitnessServer -notmatch "N/A") {
Try { Invoke-Sqlcmd -ServerInstance $PrincipalServer -Query $DbPrincipalWitnessQuery }
Catch { $_.Exception.Message ; Break }
Write-Host "Partnership for $WitnessServer (Witness) created successfully." -ForegroundColor Yellow
}

PS Fab:\>Get-RelatedPosts

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top