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 }