A piece of my work around PowerShell and IIS (or usefull things I've found on the web). Mostly some reminders for myself!

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
}

1 Comment
Paul Brewer
Paul Brewer

To save having to take a backup before the restores, assuming existing BAU type backups and history exist, you could call the RestoreGene framework –

http://paulbrewer.wordpress.com/2013/10/12/database-restore-automation/

It may save hours not having to take a backup of large, busy databases, it will dynamically calculate the optimum restore sequence based on existing backup history and perform the restores for you. There are overrides available for multiple parameters such as backup and data file paths, databases can be left in NORECOVERY mode.

Best wishes
Paul

Name*Email*WebsiteComment

Leave a Reply

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

Scroll to Top