|

SQL Server and Other Topics

rss

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver


SQL Server Database Mirroring

Yes, mirroring is a deprecated feature with the addition of AlwaysOn Availability Groups. I needed to test my process of moving from Mirroring to AGs over and over and needed a quick way to implement Database Mirroring and tear it down and start over. 

The process actually involves setting up the Mirror, then creating log shipping from the mirror across a slow WAN. That article will come out some time in the near future.

It took me some research because this is my first real foray into PowerShell, so I found a blog by John P. Wood, "Using Powershell to Set Up SQL Server Mirroring", and bastardized it. I rewrote most of the script and kept some important steps.

 

* Warning - I'm not a PowerShell Guru, this code can be written different ways, some may be better. Let me know when improvements can be made.

Script

The script pulls the needed variables from csvFileName which is passed into the script.

 

 
 Param( 
    [Parameter(Mandatory=$true)] 
    [string]$csvFilename 
) 
 

The csv file has the following columns:

 database SourceServer
 SourcePath  DestServer DestPath
 QA_PSW_RC_NY_SWITCH  Q-MSSQL01-LB
 \\Q-MSSQL01-LB\Backup\ Q-MSSQL11-LB

\\Q-MSSQL01-LB\Backup\


Domain
Mode
DestDataFilePath DestLogFilePath Witness Timeout BreakMirror FileName
 domain.com  complete C:\SQLServer\Data C:\SQLServer\Logs
QA-MSSQL03-NY
30
true

Functions

Function Get-FileName { 
    Param([string]$path) 
    $names = $path.Split('\\') 
    $names[$names.Count - 1] 
}

 I'm sure there is a better way to do this, I got this from someone else.

 

Function New-SMOconnection { 
    Param ( 
        [string]$server 
    ) 
    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server) 
    $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 { 
    Write-Host "Performing Backup of"$database 
    $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" 
    Write-Host "$SourcePath\$bkpfile" 
    $device = "$SourcePath\$bkpfile" 
    $dbbk.Devices.AddDevice($device, 'File') 
    $smo = New-SMOconnection -server $SourceServer 
    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, 
        [string]$DestDataFilePath, 
        [string]$DestLogFilePath 
    ) 
    # Get a new connection to the server 
    $smo = New-SMOconnection -server $DestServer 
    $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($filename, "File") 
    # Get local paths to the Database and Log file locations 
 
    #Set Data File Path 
    If ($DestDataFilePath.Length -gt 1){ 
        $DBPath=$DestDataFilePath         
    } 
    ElseIf ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath } 
    Else { $DBPath = $smo.Settings.DefaultFile} 
 
    #Set Log File Path 
    If ($DestLogFilePath.Length -gt 1){ 
        $DBLogPath=$DestLogFilePath 
    } 
    ElseIf ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath } 
    Else { $DBLogPath = $smo.Settings.DefaultLog} 
  
    $backupFile=$DestPath+"\"+$filename 
 
    # Load up the Restore object settings 
    Write-Host "Preparing Restore" 
    $Restore = new-object Microsoft.SqlServer.Management.Smo.Restore 
    $Restore.Action = 'Database' 
    $Restore.Database = $database 
    $Restore.ReplaceDatabase = $true 
    $Restore.NoRecovery = $true 
    $Restore.Devices.AddDevice($backupFile,[Microsoft.SqlServer.Management.Smo.DeviceType]::File) 
  
    # Get information from the backup file 
    $RestoreDetails = $Restore.ReadBackupHeader($smo) 
    $DataFiles = $Restore.ReadFileList($smo) 
 
    # Restore all backup files 
    #Write-Host "Restoring all data 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 
            Write-Host 'Restoring Data File' $DBPath"\"$PhysicalName 
            $RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName 
        } 
        Else { 
            # Restore Log file 
            Write-Host 'Restoring Log File' $DBPath"\"$PhysicalName 
            $RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName 
        } 
        [Void]$Restore.RelocateFiles.Add($RestoreData) 
  
    } 
    Try { 
    $Restore.FileNumber = 1 
    $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]$database,[string]$partner) 
    $conn = "Server=$server; Integrated Security=SSPI; Database=Master" 
    $cn = New-Object "System.Data.SqlClient.SqlConnection" $conn 
    $cn.Open() 
    $cmd = New-Object "System.Data.SqlClient.SqlCommand" 
    $cmd.CommandType = [System.Data.CommandType]::Text 
  
    $cmd.CommandText = "ALTER DATABASE $database SET PARTNER = 'TCP://" + $partner + "." + $Domain + ":$Port'" 
    $cmd.Connection = $cn 
     
 
 
 
    try { 
      $cmd.ExecuteNonQuery() 
      } 
    catch [System.Net.WebException] { 
      # $_ is set to the ErrorRecord of the exception 
      if ($_.Exception.InnerException) { 
        Out-Log $_.Exception.InnerException.Message 
      } else { 
        Out-Log $_.Exception.Message 
      } 
    } 
     
    $cn.Close() 
 
}

  
Function Set-Witness { 
    Param([string]$server,[string]$database,[string]$witness) 
    $conn = "Server=$server; Integrated Security=SSPI; Database=Master" 
    $cn = New-Object "System.Data.SqlClient.SqlConnection" $conn 
    $cn.Open() 
    $cmd = New-Object "System.Data.SqlClient.SqlCommand" 
    $cmd.CommandType = [System.Data.CommandType]::Text 
  
    $cmd.CommandText = "ALTER DATABASE $database SET WITNESS = '$Witness'" 
    Write-Host $cmd.CommandText 
    $cmd.Connection = $cn 
 
    try { 
      $cmd.ExecuteNonQuery() 
      } 
    catch [System.Net.WebException] { 
      # $_ is set to the ErrorRecord of the exception 
      if ($_.Exception.InnerException) { 
        Out-Log $_.Exception.InnerException.Message 
      } else { 
        Out-Log $_.Exception.Message 
      } 
    } 
     
    $cn.Close() 
}

 

Function Set-Timeout { 
    Param([string]$server,[string]$database,[string]$timeout) 
    $conn = "Server=$server; Integrated Security=SSPI; Database=Master" 
    $cn = New-Object "System.Data.SqlClient.SqlConnection" $conn 
    $cn.Open() 
    $cmd = New-Object "System.Data.SqlClient.SqlCommand" 
    $cmd.CommandType = [System.Data.CommandType]::Text 
 
    $cmd.CommandText = "ALTER DATABASE $database SET PARTNER TIMEOUT "+$Timeout 
    $cmd.Connection = $cn 
    try { 
      $cmd.ExecuteNonQuery() 
      } 
    catch [System.Net.WebException] { 
      # $_ is set to the ErrorRecord of the exception 
      if ($_.Exception.InnerException) { 
        Out-Log $_.Exception.InnerException.Message 
      } else { 
        Out-Log $_.Exception.Message 
      } 
    } 
     
    $cn.Close() 
}

Function Break-Mirror { 
    Param([string]$server,[string]$database) 
    Write-Host "Breaking Existing Mirror" 
    $conn = "Server=$server; Integrated Security=SSPI; Database=Master" 
    $cn = New-Object "System.Data.SqlClient.SqlConnection" $conn 
    $cn.Open() 
    $cmd = New-Object "System.Data.SqlClient.SqlCommand" 
    $cmd.CommandType = [System.Data.CommandType]::Text 
 
    $cmd.CommandText = "ALTER DATABASE $database SET PARTNER OFF " 
    $cmd.Connection = $cn 
    try { 
      $cmd.ExecuteNonQuery() 
      } 
    catch [System.Net.WebException] { 
      # $_ is set to the ErrorRecord of the exception 
      if ($_.Exception.InnerException) { 
        Out-Log $_.Exception.InnerException.Message 
      } else { 
        Out-Log $_.Exception.Message 
      } 
    } 
     
    $cn.Close() 
}

 

The Process - The loop

This is where the magic occurs. The variables are populated from the csv file that will be used to pass to the functions for each step that needs to be completed to instantiate the mirror.

 FOREACH ($Server in $ServerList) { 
    [string]$database=$Server.database 
    [string]$SourceServer=$Server.SourceServer 
    [string]$SourcePath=$Server.SourcePath 
    [string]$DestServer=$Server.DestServer 
    [string]$DestPath=$Server.DestPath 
    [string]$Port=$Server.Port 
    [string]$Domain=$Server.Domain 
    [string]$Mode=$Server.Mode 
    [string]$FileName=$Server.FileName 
    [string]$Witness=$Server.Witness 
    [string]$Timeout=$Server.Timeout 
    [string]$BreakMirror=$Server.BreakMirror 
    [string]$DestDataFilePath = $Server.DestDataFilePath 
    [string]$DestLogFilePath = $Server.DestLogFilePath

 

 
    if ($mode -eq 'Restore'){ 
    $bkpfile =$FileName 
    } 
    else { 
    $bkpfile = $($SourceServer.Replace("\", "$")) + "_" + $database + "_FULL_" + $(get-date -format yyyyMMdd-HHmmss) + ".bak" 
    } 
 
 
If a mirror exists on the source database remove the mirror if specified in the csv file. This is mostly for testing, so you can rerun the process over and over.  
    if ($BreakMirror -eq 'True'){ 
    Break-Mirror -server $SourceServer -database $database 
    } 
 This step creates the backup and if it is the Backup only step, it tells you where to put the backup based upon the csv data. This is for a large file to be copied across the slow WAN.  
    if ($mode -eq 'Backup') { 
        Invoke-SqlBackup 
        Write-Host "Copy your backups from: $SourcePath" 
        Write-Host "Put your backups here: $DestPath on Server: $DestServer" 
        Write-Host "Put your log backups here: "$DestPath\$database"_LOG on Server: $DestServer" 
        } 
    Elseif ($mode -eq 'Complete') {Invoke-SqlBackup} 
 
    if ($mode -ne 'Backup'){ 
 
        if ($mode -eq 'Complete') { 
            if($SourcePath -ne $DestPath){ 
                Copy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose 
            } 
     
        } 
        This step restores the backup to the new location.  
        Invoke-SqlRestore -filename $bkpfile -DestDataFilePath $DestDataFilePath -DestLogFilePath $DestLogFilePath 
   
         This step sets the mirror on both mirror and principal.  
        if ($mode -eq 'Complete') { 
            # Establish Mirroring from the mirrored database 
            Set-Mirror -server $DestServer -database $database -partner $($SourceServer.Split('\\')[0]) 
            # Start the mirror 
            Set-Mirror -server $SourceServer -database $database -partner $($DestServer.Split('\\')[0]) 
         
 Add the witness if specified in the csv file. 
            if ([string]::IsNullOrEmpty($Witness)){ 
                Write-Host "Witness parameter not provided. Configure manually" 
                Write-Host "ALTER DATABASE $database SET WITNESS = [put witness name here]" 
                } 
                else 
                { 
                Write-Host 'Adding Witness' 
                $WitnessFile="TCP://"+$Witness+"."+$Domain+":"+$Port 
                Set-Witness -server $SourceServer -database $database -witness $WitnessFile 
                } 
  For a mirroring configuration across a WAN you'll want to modify the Timeout value (default of 10 seconds) to something greater. I start at 30 seconds based upon the network latency. 
                if ([string]::IsNullOrEmpty($Timeout)){ 
                Write-Host "Timeout parameter not provided. Default (10) set." 
                } 
                else 
                { 
                Set-Timeout -server $SourceServer -database $database -timeout $Timeout 
                } 
             
            } 
            else 
            { 
            Write-Host $SourceServer 
            $DestServTrim=$($DestServer.Split('\\')[0]).Trim() 
            Write-Host 'Backup Tail of Log and copy all log backups to destination server and restore.' 
            Write-Host "-- After logs have been restored, run:" 
            Write-Host "-- ALTER DATABASE [$database] SET PARTNER = 'TCP://$DestServTrim.${Domain}:$Port'" 
            Write-Host "TCP://"$Witness"."$Domain":"$Port 
         
 
                 
            } 
        } 
 
}

 

 

 Sample csv File

 PowerShell Mirroring Script