本文共 13623 字，大约阅读时间需要 45 分钟。
脚本有2个部分，1.证书交换，2.配置镜像
只实现了简答的功能，比如镜像证书配置的检查，容错目前都还没有做
1.证书交换
$SourceServer='192.168.5.7'$SourcePath='d:\SQL Backups'$SourceUser='adminator'$SourcePassword='Fam901'$SourceDBUser='sa'$SourceDBPassword='Fam901'$SourceCertName='SQL17'$DestServer='192.168.0.16'$DestPath='d:\SQL Backups'$DestUser='adminitor'$DestPassword='Fam901'$DestDBUser='sa'$DestDBPassword='Fam901'$DestCertName='SQL16'Function SetupCertificate { Param([string]$server,[string]$database,[string]$uid,[string]$password,[string]$CertName) $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $CnnString ="Server = $server; Database = $database;User Id = $uid; Password = $password" $SqlConnection.ConnectionString = $CnnString $CC = $SqlConnection.CreateCommand(); if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } $str=" USE master CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>'; USE master; CREATE CERTIFICATE $CertName WITH SUBJECT = '$CertName certificate for database mirroring', EXPIRY_DATE= '08/27/2099'; CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE $CertName , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); BACKUP CERTIFICATE $CertName TO FILE = '$SourcePath\$CertName.cer';" $str $cc.CommandText=$str $cc.ExecuteNonQuery()|out-null $SqlConnection.Close();}Function LoadCertificate { Param([string]$server,[string]$database,[string]$uid,[string]$password,[string]$CertName,[string]$CertPath) $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $CnnString ="Server = $server; Database = $database;User Id = $uid; Password = $password" $SqlConnection.ConnectionString = $CnnString $CC = $SqlConnection.CreateCommand(); if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } $str=" USE master; CREATE LOGIN "+$CertName+"_login WITH PASSWORD = '1Sample_Strong_Password!@#'; USE master; CREATE USER "+$CertName+"_user FOR LOGIN "+$CertName+"_login; USE master; CREATE CERTIFICATE $CertName AUTHORIZATION "+$CertName+"_user FROM FILE = '$CertPath\$CertName.cer' USE master; GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ["+$CertName+"_login];" $str $cc.CommandText=$str $cc.ExecuteNonQuery()|out-null $SqlConnection.Close();}Function CheckRmoteDir{ ####################################### #Check and create dir remote ###################################### # $password = $SourcePassword | ConvertTo-SecureString -asPlainText -Force$succredential = New-Object System.Management.Automation.PSCredential $SourceUser,$password$password = $DestPassword | ConvertTo-SecureString -asPlainText -Force$destcredential = New-Object System.Management.Automation.PSCredential $DestUser,$password $sourcesession=New-PSSession -ComputerName $SourceServer -Credential $succredential $destsession=New-PSSession -ComputerName $DestServer -Credential $destcredential # invoke-command -session $sourcesession -scriptblock { if (-not(Test-Path $args[0])){ mkdir $args[0] } } -ArgumentList $SourcePath invoke-command -session $destsession -scriptblock { if (-not(Test-Path $args[0])){ mkdir $args[0] } } -ArgumentList $DestPath }CheckRmoteDir$srcUNC = Join-Path "\\$($SourceServer.Split('\\')[0])" $($SourcePath.Replace(':','$'))if (-not(Test-Path $srcUNC)) { New-PSDrive -Name s -psprovider FileSystem -root $srcUNC -Credential $succredential }$destUNC = Join-Path "\\$($DestServer.Split('\\')[0])" $($DestPath.Replace(':','$'))if (-not(Test-Path $destUNC)) { New-PSDrive -Name d -psprovider FileSystem -root $destUNC -Credential $destcredential }Test-Path $srcUNCTest-Path $destUNCSetupCertificate -server $SourceServer -database "master" -uid $SourceDBUser -password $SourceDBPassword -CertName $SourceCertNameSetupCertificate -server $DestServer -database "master" -uid $DestDBUser -password $DestDBPassword -CertName $DestCertName$bkpfile = $SourceCertName+".cer"Copy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose$bkpfile = $destCertName+".cer"Copy-Item $(Join-Path $destUNC $bkpfile) -Destination $srcUNC -VerboseLoadCertificate -server $SourceServer -database "master" -uid $SourceDBUser -password $SourceDBPassword -CertName $DestCertName -Certpath $SourcePathLoadCertificate -server $DestServer -database "master" -uid $DestDBUser -password $DestDBPassword -CertName $SourceCertName -Certpath $SourcePath 2.配置镜像
<# .SYNOPSIS Set up a mirrored database .DESCRIPTION Backs up a database and tlog, copies it to the destination, Restores the database on the mirror server, sets up the partner, and starts the mirror. .PARAMETER database The name of the database to be mirrored .PARAMETER SourceServer The name of the primary server .PARAMETER SourcePath Local Path for the backup .PARAMETER DestServer The name of mirror server .PARAMETER DestPath Local path for restore file .EXAMPLE PS C:\> Invoke-Mirror -database 'string value' 1 -SourceServer 'string\string' -SourcePath 'string' ` -DestServer 'string\string' -DestPath 'string' .NOTES AUTHOR: John P. Wood CREATED: July, 2010 VERSION: 1.0.5 The SQL connections rely on Windows authentication and assumes Endpoints already exist. Error checking is minimal (i.e. no check is made to verify the recovery model is FULL).#>#Param(# [Parameter(Mandatory=$true)]# [string]$database,# [string]$SourceServer='lcfsqlvs3\sqlvs3',# [string]$SourcePath='U:\SQL Backups',# [string]$DestServer='ldrsqlvs3\sqlvs3',# [string]$DestPath='U:\SQL Backups'# ) $database='mirror_test' $SourceServer='192.168.5.17' $SourcePath='d:\SQL Backups' $SourceUser='adminiator' $SourcePassword='Fam901' $SourceDBUser='sa' $SourceDBPassword='Fam901' $DestServer='192.168.5.16' $DestPath='d:\SQL Backups' $DestUser='adminisor' $DestPassword='Fam901' $DestDBUser='sa' $DestDBPassword='Fams901' Set-StrictMode -Version 2[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")Function Get-FileName { Param([string]$path) $names = $path.Split('\\') $names[$names.Count - 1]}Function New-SMOconnection { Param ( [string]$server, [string]$usr, [string]$password ) $pwd = $password | ConvertTo-SecureString -asPlainText -Force $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server,$usr ,$pwd) $conn.applicationName = "PowerShell SMO" $conn.StatementTimeout = 0 $conn.Connect() if ($conn.IsOpen -eq $false) { Throw "Could not connect to server $($server) for database backup of $($dbname)." } $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn) $smo}Function Invoke-SqlBackup { $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup') $dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database $dbbk.BackupSetDescription = "Full backup of " + $database $dbbk.BackupSetName = $database + " Backup" $dbbk.Database = $database $dbbk.MediaDescription = "Disk" $device = "$SourcePath\$bkpfile" $dbbk.Devices.AddDevice($device, 'File') $smo = New-SMOconnection -server $SourceServer -usr $SourceDBUser -password $SourceDBPassword Try { $dbbk.SqlBackup($smo) $dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log $dbbk.SqlBackup($smo) $smo.ConnectionContext.Disconnect() } Catch { $ex = $_.Exception Write-Output $ex.message $ex = $ex.InnerException while ($ex.InnerException) { Write-Output $ex.InnerException.message $ex = $ex.InnerException }; continue } Finally { if ($smo.ConnectionContext.IsOpen -eq $true) { $smo.ConnectionContext.Disconnect() } }}Function Invoke-SqlRestore { Param( [string]$filename ) # Get a new connection to the server $smo = New-SMOconnection -server $DestServer -usr $DestDBUser -password $DestDBPassword $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($filename, "File") # Get local paths to the Database and Log file locations If ($smo.Settings.DefaultFile.Length -eq 0) { $DBPath = $smo.Information.MasterDBPath } Else { $DBPath = $smo.Settings.DefaultFile} If ($smo.Settings.DefaultLog.Length -eq 0 ) { $DBLogPath = $smo.Information.MasterDBLogPath } Else { $DBLogPath = $smo.Settings.DefaultLog} # Load up the Restore object settings $Restore = new-object Microsoft.SqlServer.Management.Smo.Restore $Restore.Action = 'Database' $Restore.Database = $database $Restore.ReplaceDatabase = $true $Restore.NoRecovery = $true $Restore.Devices.Add($backupDevice) # Get information from the backup file $RestoreDetails = $Restore.ReadBackupHeader($smo) $DataFiles = $Restore.ReadFileList($smo) # Restore all backup files ForEach ($DataRow in $DataFiles) { $LogicalName = $DataRow.LogicalName $PhysicalName = Get-FileName -path $DataRow.PhysicalName $RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") $RestoreData.LogicalFileName = $LogicalName if ($DataRow.Type -eq "D") { # Restore Data file $RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName } Else { # Restore Log file $RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName } [Void]$Restore.RelocateFiles.Add($RestoreData) } Try { $Restore.SqlRestore($smo) # If there are two files, assume the next is a Log if ($RestoreDetails.Rows.Count -gt 1) { $Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log $Restore.FileNumber = 2 $Restore.SqlRestore($smo) } $smo.ConnectionContext.Disconnect() } Catch { $ex = $_.Exception Write-Output $ex.message $ex = $ex.InnerException while ($ex.InnerException) { Write-Output $ex.InnerException.message $ex = $ex.InnerException }; continue } Finally { if ($smo.ConnectionContext.IsOpen -eq $true) { $smo.ConnectionContext.Disconnect() } }}Function Set-Mirror { Param([string]$server,[string]$db,[string]$uid,[string]$password,[string]$partner) $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $password" $SqlConnection.ConnectionString = $CnnString $CC = $SqlConnection.CreateCommand(); if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } $str= " ALTER DATABASE $database SET PARTNER off ALTER DATABASE $database SET PARTNER = 'TCP://" + $partner + ":5022'" $str $cc.CommandText =$str $cc.ExecuteNonQuery()|out-null $SqlConnection.Close();}Function CheckRmoteDir{ ####################################### #Check and create dir remote ###################################### # $sourcesession=New-PSSession -ComputerName $SourceServer -Credential $succredential $destsession=New-PSSession -ComputerName $DestServer -Credential $destcredential # invoke-command -session $sourcesession -scriptblock { if (-not(Test-Path $args[0])){ mkdir $args[0] } } -ArgumentList $SourcePath invoke-command -session $destsession -scriptblock { if (-not(Test-Path $args[0])){ mkdir $args[0] } } -ArgumentList $DestPath }$password = $SourcePassword | ConvertTo-SecureString -asPlainText -Force$succredential = New-Object System.Management.Automation.PSCredential $SourceUser,$password$password = $DestPassword | ConvertTo-SecureString -asPlainText -Force$destcredential = New-Object System.Management.Automation.PSCredential $DestUser,$passwordCheckRmoteDir$srcUNC = Join-Path "\\$($SourceServer.Split('\\')[0])" $($SourcePath.Replace(':','$'))if (-not(Test-Path $srcUNC)) { New-PSDrive -Name s -psprovider FileSystem -root $srcUNC -Credential $succredential }$destUNC = Join-Path "\\$($DestServer.Split('\\')[0])" $($DestPath.Replace(':','$'))if (-not(Test-Path $destUNC)) { New-PSDrive -Name d -psprovider FileSystem -root $destUNC -Credential $destcredential }Test-Path $srcUNCTest-Path $destUNC$bkpfile = $($SourceServer.Replace("\", "$")) + "_" + $database + "_FULL_" + $(get-date -format yyyyMMdd-HHmmss) + ".bak"Invoke-SqlBackupCopy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose$bkpfile = $DestPath +"\" + $bkpfile$bkpfileInvoke-SqlRestore -filename $bkpfile# Establish Mirroring from the mirrored databaseSet-Mirror -server $DestServer -db "master" -uid $DestDBUser -password $DestDBPassword -partner $($SourceServer.Split('\\')[0])# Start the mirrorSet-Mirror -server $SourceServer -db "master" -uid $SourceDBUser -password $SourceDBPassword -partner $($DestServer.Split('\\')[0])
转载地址：http://uwmfl.baihongyu.com/