NewCustomAG.psm1
|
#Requires -Version 3 function New-CustomAG{ <# .SYNOPSIS Creates AlwaysOn availabilty group for servers provided. .DESCRIPTION Utilizes SMO to access SQL Server to backup database and create Availabity Group Based on Params If SQL Client Tools are not loaded on the machine it will error and fail. If AlwaysOn is not enabled on the Instance of SQL Server the service will be restarted to enable it. This is required for it to continue with AG Creation. If you do not want the service to be recycled enable prior to executing the script. .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 CNO Cluster Name Object which was created IN Active Directory. If not passed assumes its derived from server names. .PARAMETER AgListenerName Listener Name to be created that references availability group if no listener is passed it will be created same as SQLAgname .PARAMETER SqlAgDatabase Database which will be placed in Availability Group. If HADRSeed database will be created and dropped. .PARAMETER AgListenerPort Port listener will listen on .PARAMETER IPAddresses One or Many Ip addresses which will be tied to the listener. One Ip is required for each subnet .PARAMETER AgListenerSubnetMask Subnetmask for Listeners (Assumption all will utilize the same subnetmask) .PARAMETER BackupDirectory Backup location which all nodes will have access to. Used to backup and restore database from. .EXAMPLE Creates Availability Group AG-TNTNTX10 for three servers cross subnet New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010 -SqlAgName AG-TNTNTX10 -IPAddresses 192.168.81.211,192.168.91.212 -BackupDirectory \\ohnas001\SQLBackups .EXAMPLE New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CNOTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose #> [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [string[]]$Servers, [Parameter(Mandatory=$true)] [string]$SqlAgName, [string]$CNO, [string]$AgListenerName, [string]$SqlAgDatabase="HADRSeed", [string]$AgListenerPort="1433", [string[]]$IPAddresses, [string]$AgListenerSubnetMask ="255.255.255.0", [Parameter(Mandatory=$true)] [string]$BackupDirectory ) Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Loading SMO Assemblies ..."); [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-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ..."); Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ..."); $HadrEndpointName = "Hadr_Endpoint" $HadrEndpointPort = 5022 $LoginType = "WindowsUser" if (!$AgListenerName) #Listener was not passed default to AG Name { $AgListenerName = $SqlAgName } $SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0] #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 Try{ If($SqlConn.IsHadrEnabled -eq 0) { Enable-SqlAlwaysOn -ServerInstance $Server -Force Get-Service -computer $Server -DisplayName SQL*Server*MSSQL* -Exclude *Agent*|Restart-Service Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Finished Enabling AlwaysOn on the nodes ..."); } } catch{ Throw "Failed Enabling AlwaysOn on the $Server" Exit } If($loopCnt -eq 0) #First Server is Primary Back up Full and TranLogs { Try{ If($SqlAgDatabase -eq "HADRSeed" -and ($SqlConn.Databases[$SqlAgDatabase].Name -eq $null)) { $Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList $SqlConn,"HADRSeed" $db.Create() } } Catch{ Throw "Failed create $SqlAgDatabase database." Exit } Try{ if ($SQLconn.Databases[$SqlAgDatabase].RecoveryModel -ne "FULL") { $sqlconn.Databases[$SqlAgDatabase].RecoveryModel = "Full" $sqlconn.Databases[$SqlAgDatabase].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 $SqlAgDatabase to Full Recovery." Exit } Try{ $DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup $DbBackup.Database = $SqlAgDatabase $DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database $DbBackup.Initialize = $true $DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_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 $SqlAgDatabase ..."); $DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup $DbBackup.Database = $SqlAgDatabase $DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log $DbBackup.Initialize = $true $DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_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 $SqlAgDatabase ..."); } Catch{ Throw "Failed to backup $SqlAgDatabase" Exit } }#if First Server Backup else { try{ $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore $DbRestore.Database = $SqlAgDatabase $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database $DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_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 $SqlAgDatabase on $Server..."); $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore $DbRestore.Database = $SqlAgDatabase $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log $DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_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 $SqlAgDatabase on $Server..."); } catch{ Throw "Failed to Restore $SqlAgDatabase on $Server..." Exit } }#Else Secondary Servers $loopcnt=$loopCnt+1 }#For Each Loop through servers Backing up Database/Tranlog $AvailabilityGroup = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroup -ArgumentList $SqlServerPrim, $SqlAgName $AvailabilityGroup.AutomatedBackupPreference="Primary" #Loop through servers and create Endpoints for AlwaysOn $loopCnt = 0 Foreach($Server in $Servers){ Try{ $SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server $Endpoint = $SqlConn.Endpoints | Where-Object {$_.EndpointType -eq [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring} $ServiceAccount = $SQLconn.ServiceAccount if(($SqlConn.logins | Where-Object {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $ServiceAccount)}) -eq $null){ $login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $SqlConn ,$ServiceAccount $login.LoginType = $LoginType $login.Create() } if(!$Endpoint){ $Endpoint = New-Object -typename Microsoft.SqlServer.Management.Smo.Endpoint -ArgumentList $SqlConn,$HadrEndpointName $Endpoint.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring $Endpoint.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp $Endpoint.Protocol.Tcp.ListenerPort = $HadrEndpointPort $Endpoint.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::All $Endpoint.Payload.DatabaseMirroring.EndpointEncryption = [Microsoft.SqlServer.Management.Smo.EndpointEncryption]::Required $Endpoint.Payload.DatabaseMirroring.EndpointEncryptionAlgorithm = [Microsoft.SqlServer.Management.Smo.EndpointEncryptionAlgorithm]::Aes $Endpoint.Create() $Endpoint.Start() Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Create Endpoint on $Server..."); Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Granting connect Permission on $Server to $ServiceAccount ..."); #$ConnectPerm = New-Object Microsoft.SqlServer.Managment.Smo.ObjectPermissionSet(Connect) #$Endpoint.Grant($ConnectPerm,$ServiceAccount) $Cmd = "GRANT CONNECT ON ENDPOINT::[" + $HadrEndpointName + "] TO [" + $ServiceAccount + "]" $con = "server=$Server;database=master;Integrated Security=True;" $da = new-object -typename System.Data.SqlClient.SqlDataAdapter -ArgumentList $Cmd, $con $dt = new-object -typename System.Data.DataTable $da.fill($dt) | out-null Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done granting connect Permission on $Server to $ServiceAccount ..."); } If($loopCnt -eq 0){ $PrimaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlServerPrim.NetName $PrimaryReplica.EndpointUrl = "TCP://$($SqlServerPrim.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)" $PrimaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic $PrimaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit $AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Primary Replica..."+$PrimaryReplica.EndpointUrl); } Else{ $SecondaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlConn.NetName $SecondaryReplica.EndpointUrl = "TCP://$($SqlConn.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)" If ($loopCnt -eq 1) { $SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic $SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit } Else{ $SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Manual $SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::ASynchronousCommit } $AvailabilityGroup.AvailabilityReplicas.Add($SecondaryReplica) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Secondary Replica..."+$SecondaryReplica.EndpointUrl) } } Catch { Throw "Failed to Create/Grant Endpoints on $Server..." Exit } $loopCnt=$loopCnt+1 }#For Each Create Endpoints $AvailabilityDb = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityDatabase -ArgumentList $AvailabilityGroup, $SqlAgDatabase $AvailabilityGroup.AvailabilityDatabases.Add($AvailabilityDb) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Database to AG..."+$AvailabilityDb); #if No ip Addresses Passed we will not create a listener if ($IpAddresses){ #If CNO is not passed we will derive it from server names #First three Char ="CLU" #Next Series are the first two char of server name which is location ex. NY for NewYork #Final is the Last four digits of the Primary Server #For the following Servers MISQL5010, TNSQL5010, OHSQL5010 CNO would be CluMITNOH5010 If (!$CNO){ Try { $CNO = "Clu" foreach ($Server in $Servers) { $CNO = $CNO + $Server.Substring(0,2) } $CNO =$CNO + $Server.Substring($Server.Length - 4,4) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Captured Cluster Name as $CNO...") } Catch { Throw ": Failed to Get Cluster Name" exit } } #Verify Active Directory Tools are installed, if they are load if not Throw Error If (!(get-module -ListAvailable | Where-Object {$_.Name -eq "ActiveDirectory"})){ Throw "Active Directory Module is Required." Exit } else{import-module ActiveDirectory -ErrorAction Stop} try{ $CNO_OU = Get-ADComputer $CNO $Trim = $CNO.length+4 $CNOlgth = $CNO_OU.DistinguishedName.Length - $trim $OUPath = $CNO_OU.ToString().Substring($Trim,$CNOlgth) } catch{ Throw ": Failed to find Computer in AD" exit } #Create Computer Object for the AgListenerName #Grant Full control to CNO Computer Object using DSACLS $m = Get-ADComputer -Filter {Name -eq $SqlAgName} -Server $env:USERDOMAIN | Select-Object -Property * | Measure-Object If ($m.Count -eq 0) { Try{ New-ADComputer -Name $AgListenerName -SamAccountName $AgListenerName -Path $OUPath -Enabled $false } Catch{ Throw "Failed to Create $AgListenerName in $AG_OU" Exit } $SucccessChk =0 #Check for AD Object Validate at least three successful attempts $i=1 While ($i -le 5) { Try{ $ListChk = Get-ADComputer -filter {Name -like $AgListenerName} If ($ListChk){$SuccessChk++} Start-Sleep -Seconds 10 If($SuccesChk -eq 3){break} } Catch{ Throw "Failed Validate $AgListenerName was created in $OUPath" Exit } $i++ } } Try{ $AG_OU = Get-ADComputer $AgListenerName DSACLS $AG_OU /I:T /G $CNO_OU":GA" | Out-Null Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Assigned permisions to $SqlAgName and $CNO in $OUPath") } Catch{ Throw "Failed Validate grant permissions on $AgListenerName in location $OU_PAth to $CNO in location $CNO_OU" Exit } $AgListener = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener -ArgumentList $AvailabilityGroup, $AgListenerName $AgListener.PortNumber = $AgListenerPort Foreach($IP in $IPAddresses){ Try{ $AgListenerIp = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress -ArgumentList $AgListener $AgListenerIp.IsDHCP = $false $AgListenerIp.IPAddress = $IP $AgListenerIp.SubnetMask = $AgListenerSubnetMask $AgListener.AvailabilityGroupListenerIPAddresses.Add($AgListenerIp) Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added $IP to $AgListenerName..."); } Catch { Throw "Failed to Add $IP to $AgListenerName..." Exit } }#For Each IP Address Try{ $AvailabilityGroup.AvailabilityGroupListeners.Add($AgListener); } Catch{ Throw "Failed to Add $AgListenerName to $SqlAgName..." Exit } }#If Ipaddresses are passed #Create Availabilty Group Try{ $SqlServerPrim.AvailabilityGroups.Add($AvailabilityGroup) $AvailabilityGroup.Create() Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Created AG..."); } Catch{ Throw ": Failed to Create Availability Group $SqlAgName..." Exit } #Loop through Secondary Servers and Join them to the Availability Group $loopCnt =0 Foreach($Server in $Servers){ Try { if ($loopCnt -ne 0){ $SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server $SqlConn.JoinAvailabilityGroup($SqlAgName) $SqlConn.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].JoinAvailablityGroup() Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Join Secondary $Server to AG..."); } } Catch{ Throw "Failed to Join $Server to $SqlAgName..." Exit } $loopCnt=$loopCnt+1 }#For Loop for Joining Secondaries Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Creating Adding Replicas..."); #Remove all Backup Files which were creating during this excersize Try{ $DBBackupFile = "$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak" $DBTRNFile = "$BackupDirectory\$($SqlAgDatabase)_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..."); #If we created a dummy database to create the AG Remove the database from AG and drop it If($SqlAgDatabase -eq "HADRSeed"){ $loopCnt = 0 $SqlServerPrim.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].Drop(); Foreach($Server in $Servers){ Try { Start-Sleep -Seconds 30; $SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server If($loopCnt -eq 0){$sqlconn.KillAllProcesses($SqlAgDatabase)}; $sqlconn.Databases[$SqlAgDatabase].Drop() Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Removed $SqlAgDatabase from $Server"); } Catch{ Write-Verbose -Message "Failed to remove $SqlAgDatabase from $Server" } $loopCnt=$loopCnt+1 }#ForEach Server Remove Seed DB }#If HADRSeed }#Function End #New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CluTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose |