AddCustomAvailabilityDB.psm1
|
#Requires -Version 3 function Add-CustomAvailabilityDB{ <# .SYNOPSIS Adds an existing Database to an existing Availabilty Group on all replicas provided .DESCRIPTION Utilizes SMO to access SQL Server If SQL Client Tools are not loaded on the machine it will error and fail. Verifys DB exists on first server in list which is requried to be the Primary server. If not Aborts Verifys DB doesnt exist in an availabilty database already. If it does Aborts. Verifys DB is in Full Recovery mode if not puts db in Full recovery. .PARAMETER Servers Servers participating in AlwaysOn, first server will be assumed to be where the database is which needs to be replicated. .PARAMETER SQLAgName Availability Group Name database will be created in .PARAMETER db Databases which will be added to the Availability Group .PARAMETER BackupDirectory Backup location which all nodes will have access to. Used to backup and restore database from. If Paramater is not passed function assumes Join Only operation and Backup and Restore operations are skipped. .PARAMETER Timeout Connection Timeout for SQL Server connections. Function defaults this to 0 so that backup and restore will run as long as needed, however you have the ability to override. .EXAMPLE Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test16 -BackupDirectory \\ohnas001\SQLBackups -verbose .EXAMPLE Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test16 -verbose .EXAMPLE Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test16,Test17,Test18 -verbose .EXAMPLE Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001,MISQL3001,OHSQL3001 -SqlAgName AG-TNWA3004 -Databases Test16,Test17,Test18 -verbose #> [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [string[]]$Servers, [Parameter(Mandatory=$true)] [string]$SqlAgName, [string[]]$Databases, [string]$BackupDirectory, [int]$Timeout=0 ) [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") |Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ..."); $SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0] $SqlServerPrim.ConnectionContext.StatementTimeout = $Timeout #Loop through servers Backing up Database/Tranlog and Restore on each Secondary with NoRecovery $loopCnt = 0 foreach ($Server in $Servers){ $SqlConn = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server $SqlConn.ConnectionContext.StatementTimeout = $Timeout If($loopCnt -eq 0) #First Server is Primary Back up Full and TranLogs { foreach ($db in $Databases){ Try{ $DBcheck = $sqlconn.Databases | Where-Object {$_.name -eq $db} If(!$DBcheck){ Throw "$db doesn't exist on $Server please verify Primary Server is first in the list" exit } If($DBcheck.AvailabilityGroupName){ $DBAGCheck = $DBcheck.AvailabilityGroupName Throw "$db exists in $DBAGCheck availability group already." exit } if ($SQLconn.Databases[$db].RecoveryModel -ne "FULL") { $sqlconn.Databases[$db].RecoveryModel = "Full" $sqlconn.Databases[$db].Alter(); Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Database is not in Full Recovery Mode Setting to Full..."); } } Catch{ Throw "Failed to set $db to Full Recovery." Exit } #If BackupDirectory is not provided We will assume you want to do a Join Only If ($BackupDirectory){ Try{ $DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup $DbBackup.Database = $db $DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database $DbBackup.Initialize = $true $DbBackup.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_full.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File) $DbBackup.SqlBackup($SqlConn) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup Full for $db ..."); $DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup $DbBackup.Database = $db $DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log $DbBackup.Initialize = $true $DbBackup.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_log.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File) $DbBackup.SqlBackup($SqlConn) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup TranLog for $db ..."); } Catch{ Throw "Failed to backup $db" Exit } } Try{ $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName} $AvailabilityDb = New-Object -typename Microsoft.SQLServer.Management.Smo.AvailabilityDatabase -ArgumentList $AvailabilityGroup,$db $AvailabilityGroup.AvailabilityDatabases.add($AvailabilityDb); $AvailabilityDb.create(); $AvailabilityGroup.alter(); Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Primary db $db added to AG..."); } Catch{ Throw "Failed to Add $db on $SqlAgName..." Exit } }#For Each DB } else { foreach ($db in $Databases){ #If BackupDirectory is not provided We will assume you want to do a Join Only If ($BackupDirectory){ try{ $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore $DbRestore.Database = $db $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database $DbRestore.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_full.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File) $DbRestore.NoRecovery = $true $DbRestore.SqlRestore($SqlConn) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Full for $db on $Server..."); $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore $DbRestore.Database = $db $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log $DbRestore.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_log.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File) $DbRestore.NoRecovery = $true $DbRestore.SqlRestore($SqlConn) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Tran Log for $db on $Server..."); } catch{ Throw "Failed to Restore $db on $Server..." Exit } }#End IF No BackupDir Join Only Try{ While($true){ $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName} $AvailabilityDb = $AvailabilityGroup.AvailabilityDatabases | Where-Object {$_.Name -eq $db} $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName} $AvailabilityGroup.AvailabilityDatabases.Refresh() $AvailabilityDb=$AvailabilityGroup.AvailabilityDatabases | Where-Object {$_.Name -eq $db} if ($AvailabilityDb) {break} Start-Sleep -Seconds 15 } $AvailabilityDb.JoinAvailablityGroup(); Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": $db Joined to $SqlAgName on $Server..."); } catch{ Throw "Failed to Join $db on to $SQLAgName on $Server..." Exit } }#For each DB }#Else Secondary Servers $loopcnt=$loopCnt+1 }#End For Each Server #Remove all Backup Files which were creating during this excersize If ($BackupDirectory){ Try{ $DBBackupFile = "$BackupDirectory\$($db)_AgSetup_full.bak" $DBTRNFile = "$BackupDirectory\$($db)_AgSetup_log.trn" If (Test-Path -Path $DBBackupFile){Remove-Item -Path $DBBackupFile} If (Test-Path -Path $DBTRNFile){Remove-Item -Path $DBTRNFile} } Catch{ Throw "Failed to Cleanup Backup Files..." Exit } Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Cleaning Up Backup Files..."); } }#Function End #Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test18 -BackupDirectory \\ohnas001\SQLBackups -Timeout 10 -verbose |