|

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!


Ed Watson (b|) and I will be presenting our full day pre-conference session on performance tuning. This session, named Performance Tuning for Mere Mortals will take the attendee through basic configuration for performance, troubleshooting performance as well as some performance tuning. 

We will be taking a beginners look at execution plans as well as many other great free tools for performance tuning. 

We'll look at Performance Monitor, Performance Analysis of Logs (PAL), Plan Explorer by SQLSentry as well as Query Store, DMVs, and much, much, more!

 

Sign up today seats are limited! EventBrite

Don't forget to visit www.sqlsatnash.com for details about SQLSaturday Nashville!

Slides and Demos will be here! -> Performance Tuning for Mere Mortals Precon.zip


Save

Part 1 > Part 2 > Part 3

 In the last post, part 2, I talked about the the plan and project for implementing Availability Groups.Today I will discuss the creation of an unattended install.

First, what is an unattended install, well, simply it is the ability to use a "template" configuration file to automatically complete all of the possible parameters required to install, in this case, SQL Server. It is critical to adopt a standard configuration setup, where to put the data and log files, etc., so you're unattended install will configure all servers the same.

 The easiest way to create the configuration file "ConfigurationFile.ini" is to do an install, make sure you chose all of the parameters as you would like them to be saved and used for every install. Once you get to the end of the install, on the "Ready to Install" screen prior to clicking the Install button you will see the path to the "ConfigurationFile.ini". Copy the path and file name from the text box so we can get to it later. You can now cancel the install as you've captured all of the parameters need to install SQL Server.

 

That was easy!

 Now lets make sure all of the configuration settings are correct. At this point paste the path in File Explorer to open the file in Notepad (or your favorite text editor). It should look something like the following, keep in mind that I would not copy my example as it is for another environment.

 Unattended Install ConfigurationFile.ini

Look at each parameter in the configuration file and change the ones that you want to change. You will want to test the file until it is exactly what you want, then you can put it on a network drive somewhere and run it quiet when you want to install SQL Server.

   ;SQL Server 2014 Configuration File  
[OPTIONS] 
 
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.  
ACTION="Install" 
 
  ;Accept Terms <- Saves you from having to accept the license terms   
 IACCEPTSQLSERVERLICENSETERMS="True"  
 
  ; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.   
ENU="True"  
 
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.  
;UIMODE="Normal" 
 
  
; Setup will not display any user interface. (when set to true)   
QUIET="False" 
 
  ; Setup will display progress only, without any user interaction. (when set to true)   
QUIETSIMPLE="True" 
 
  ; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.    
 UpdateEnabled="True"  
 
; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.  
ERRORREPORTING="False" 
 
; If this parameter is provided, then this computer will use Microsoft Update to check for updates.  
USEMICROSOFTUPDATE="False" 
 
  ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.    
 FEATURES=SQLENGINE,CONN,BC,SSMS,ADV_SSMS  
 
  ; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.  <-- where are the SPs or CUs?   
 UpdateSource="Z:\Apps\SQLServer2014"  
 
; Displays the command line parameters usage  
HELP="False" 
 
  ; Specifies that the detailed Setup log should be piped to the console.  <- Good for when the install errors   
 INDICATEPROGRESS="False"  
 
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.  
X86="False" 
 
  ; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.    
 INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"  
 
 ; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.   
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server" 
 
  ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).    
INSTANCENAME="MSSQLSERVER" 
 
; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.  
SQMREPORTING="False" 
 
; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.  
INSTANCEID="MSSQLSERVER" 
 
  ; Specify the installation directory.    
 INSTANCEDIR="C:\Program Files\Microsoft SQL Server"  
 
  ; Agent account name  
AGTSVCACCOUNT=planetpayment\SQLAgentSvc 
AGTSVCPASSWORD=YOURPASSWORD 
AGTSVCSTARTUPTYPE="Automatic"  
 
; CM brick TCP communication port  
COMMFABRICPORT="0" 
 
; How matrix will use private networks  
COMMFABRICNETWORKLEVEL="0" 
 
; How inter brick communication will be protected  
COMMFABRICENCRYPTION="0" 
 
; TCP port used by the CM brick  
MATRIXCMBRICKCOMMPORT="0" 
 
; Startup type for the SQL Server service.  
SQLSVCSTARTUPTYPE="Automatic" 
 
; Level to enable FILESTREAM feature at (0, 1, 2 or 3).  
FILESTREAMLEVEL="0" 
 
; Set to "1" to enable RANU for SQL Server Express.  
ENABLERANU="False" 
 
; Specifies a Windows collation or an SQL collation to use for the Database Engine.  
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS" 
   ; Account for SQL Server service: Domain\User or system account. (Remember your password will be clear text) <- these parameters can be passed in the command execution   
 SQLSVCACCOUNT=mydomain\SQLEngineSvc 
SQLSVCPASSWORD=sqlsvcpassword  
   ; Windows account(s) to provision as SQL Server system administrators. (Remember your password will be clear text) 
   SQLSYSADMINACCOUNTS="mydomain\ProdSQLAdmin" 
SAPWD=SAPASSWORD  
 
; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication.  
SECURITYMODE="SQL" 
 
  ; The Database Engine root data directory.    
 INSTALLSQLDATADIR="D:"  
 
  ; Default directory for the Database Engine user databases.    
 SQLUSERDBDIR="D:\SQLServer\SQLData"  
 
  ; Default directory for the Database Engine user database logs.    
 SQLUSERDBLOGDIR="D:\SQLServer\SQLLogs"  
 
  ; Directory for Database Engine TempDB files.    
 SQLTEMPDBDIR="G:\SQLServer\TempDb\SQLData"  
 
  ; Directory for the Database Engine TempDB log files.    
 SQLTEMPDBLOGDIR="G:\SQLServer\TempDb\SQLLogs"  
 
; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express.  
ADDCURRENTUSERASSQLADMIN="False" 
 
; Specify 0 to disable or 1 to enable the TCP/IP protocol.  
TCPENABLED="1" 
 
; Specify 0 to disable or 1 to enable the Named Pipes protocol.  
NPENABLED="0" 
 
; Startup type for Browser Service.  
BROWSERSVCSTARTUPTYPE="Disabled" 

Pre-Installation Script File

I usually create a script that runs prior to the install and one that runs after the install. This will allow you to automate some prep work. For instance, create folders for the data and log files on the drive.

I named my file PreInstallationScript.ps1 and this one contains the creation of the file folders for the data and log files. This script can be used to set many different configurations.

 
 New-Item D:\SQLServer\SQLData -type directory 
New-Item D:\SQLServer\SQLLogs -type directory 
New-Item F:\SQLServer\SQLData -type directory 
New-Item F:\SQLServer\SQLLogs -type directory 
New-Item G:\SQLServer\SQLData -type directory 
New-Item G:\SQLServer\SQLLogs -type directory 
New-Item G:\SQLServer\TempDb\SQLData -type directory 
New-Item G:\SQLServer\TempDb\SQLLogs -type directory 
 

Post-Installation Script File

(Uses PoSh script by Eric Humphrey (http://www.erichumphrey.com/category/powershell/)

 # Set the Windows Power Options to High Performance
Powercfg -SETACTIVE SCHEME_MIN
cls
set-location $PSScriptRoot
#Set Trace Flags 1117, 1118, 3226, 4199
.\Add-SqlServerStartupParameter.ps1 '-T1117'
.\Add-SqlServerStartupParameter.ps1 '-T1118'
.\Add-SqlServerStartupParameter.ps1 '-T3226'
.\Add-SqlServerStartupParameter.ps1 '-T4199'
$compname = $env:computername
Write-Host $compname
#cd SQLSERVER:\SQL\$compname\DEFAULT
Enable-SqlAlwaysOn -ServerInstance $compname -Force
Invoke-Sqlcmd -InputFile ".\AddTempDBFiles.sql"
Invoke-Sqlcmd -InputFile ".\Configs.sql"
Invoke-Sqlcmd -InputFile ".\GlenBerryCriticalAlerts.sql"
Invoke-Sqlcmd -InputFile ".\AlwaysOnNotifications.sql"
Invoke-Sqlcmd -InputFile ".\OlaMaintenanceSolution.sql"
read-host "Press enter to continue"

Then I wrap it Up in a Bow!

I create an UnattendedInstall.bat file to kick off the install. 

 
 net localgroup administrators /add yourdomainname\SQLEngineSVC 
cd "Z:\Apps\SQLServer2014" 
Setup.exe /ConfigurationFile=ConfigurationFile.INI 
pause 

Summary

 The unattended install can be customized to your environment using the scripts. I have left out some of the scripts here because the method of calling them is more important that the code.

This concludes Part 3 of this post. Next time we'll look at the Availability Groups.


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