自动创建数据库镜像，证书交换

87632219

2017-03-16

脚本有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 $srcUNC
Test-Path $destUNC


SetupCertificate -server $SourceServer -database "master" -uid $SourceDBUser -password $SourceDBPassword -CertName $SourceCertName
SetupCertificate -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 -Verbose


LoadCertificate -server $SourceServer -database "master" -uid $SourceDBUser -password $SourceDBPassword -CertName $DestCertName -Certpath $SourcePath
LoadCertificate -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,$password

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 $srcUNC
Test-Path $destUNC

$bkpfile =  $($SourceServer.Replace("\", "$")) + "_" + $database + "_FULL_" + $(get-date -format yyyyMMdd-HHmmss) + ".bak"

Invoke-SqlBackup
Copy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose
$bkpfile = $DestPath +"\" + $bkpfile
$bkpfile
Invoke-SqlRestore -filename $bkpfile
# Establish Mirroring from the mirrored database
Set-Mirror -server $DestServer -db "master" -uid $DestDBUser -password $DestDBPassword  -partner $($SourceServer.Split('\\')[0])
# Start the mirror
Set-Mirror -server $SourceServer -db "master" -uid $SourceDBUser -password $SourceDBPassword   -partner $($DestServer.Split('\\')[0])

string 数据库镜像镜像