|

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


Introduction

'm continuously mentoring enterprise companies in how they can simplify their lives. The worst comment I hear on a regular basis is, "doing it manually is why I have job security". ARGHHHHH! No it is why you can be replaced in a heartbeat.

So DBAs, wake up! If you do the same task, manually, over and over, you're doing it wrong. When do you have time to study and play with the new features? When do you have time to watch your Pluralsight videos? How do you improve your skills... when you have to take all of your time doing the same thing manually when you can automate it??

Over the next blog posts I'm going to give you some tips and tricks and how to's for automating your installs using PowerShell... the easy way. 

Unattended Install

There have been so many people blog about how to do an unattended install I'm not going to go into great detail. First you must go here and read this article: "Install SQL Server from the Command Prompt"

No joke, this is the best starting point for understanding the unattended install. 

Now you want to go to a friend of mine, Joey D'Antoni's blog and read this one thoroughly! "Are You Still Installing SQL Server from the GUI?"

See, I didn't really need to re-write his blog post. Short and sweet and how I have always started by unattended install process at every employer and now client since the first one. 

The rest is tweaking the passed in parameters from command line or PowerShell script. I highly recommend you pass in the passwords for install. You can save the admin groups in the configuration.ini file. 

Its what follows that takes some work and PowerShell to configure... but, if you have a few PowerShell modules installed and everything is much easier. 

 

Modules you will need to install on your "management server". Click the link for each of the following for instructions on how to install:

ActiveDirectory 

NTFSSecurity

SQLServer

dbatools

dbachecks

ReportingServicesTools

That will be enough work to get you started so I'll end this episode now. Follow the instructions and you'll be off to a good start!


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