[CmdletBinding()] Param( [Parameter(Mandatory=$True)] [emDash.Workflow.Step.Interface.PowershellContext]$ctx ) try { $emdashStepName="RemoveDBFromAvailabilityGroupOnPrimary" $emdashStepVersion="0.1" $loc = Get-Location $ErrorActionPreference = 'Stop' $VerbosePreference='SilentlyContinue' $WarningPreference = 'SilentlyContinue' Import-Module Sqlps -DisableNameChecking 3>$null $WarningPreference = 'Continue' $VerbosePreference='Continue' Set-Location $loc $envName = $ctx.TargetEnvironment.Name $endurDatabase = $ctx.TargetEnvironment.DatabaseAllocations | Where-Object { $_.Databases.Type.ToLower() -eq "endur" } | Select -first 1 $sqlServerInstance = $endurDatabase.Databases.ServerName $ctx.LogProgress("Endur database server is $sqlServerInstance") $endurDB = $endurDatabase.Databases.Name $ctx.LogProgress("Endur database name is $endurDB") $checkAGSQL = "SELECT SERVERPROPERTY ('IsHadrEnabled') as Answer" $checkAG = Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database master -Query $checkAGSQL -ConnectionTimeout 0 -QueryTimeout 65334 -ErrorAction SilentlyContinue -ErrorVariable sqlerr if ($sqlerr) { Write-Error("Error running SQL statement $($checkAGSQL): $($sqlerr.Exception.Message)") } if($checkAG.Answer -eq 1) { $ctx.LogProgress("$($sqlServerInstance) is in an Availability Group. We need to turn it off before the database can be restored.") } else { $ctx.LogProgress("$($sqlServerInstance) is NOT in an Availability Group. No further action required.") return $True } $AGDetailsSQL = "SELECT AGC.name -- Availability Group , RCS.replica_server_name -- SQL cluster node name , ARS.role_desc -- Replica Role , AGL.dns_name -- Listener Name FROM sys.availability_groups_cluster AS AGC INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id WHERE ARS.role_desc = 'PRIMARY'" $ctx.LogProgress("Getting Availability Group Details from SQL Server...") $AGDetails = Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database master -Query $AGDetailsSQL -ConnectionTimeout 0 -QueryTimeout 65334 -ErrorAction SilentlyContinue -ErrorVariable sqlerr if ($sqlerr) { Write-Error("Error running SQL statement $($AGDetailsSQL): $($sqlerr.Exception.Message)") } $ctx.LogProgress("Availability Group Name is $($AGDetails.name)") $availbilityGroupName = $AGDetails.name $ctx.LogProgress("Primary Server Name is $($AGDetails.replica_server_name)") $primaryAGServerName = $AGDetails.replica_server_name $sqlCommands = "ALTER AVAILABILITY GROUP $($availbilityGroupName) REMOVE DATABASE $($endurDB)" foreach ($sqlCommand in $sqlCommands) { $ctx.LogProgress("Executing SQL Command: $($sqlCommand)") Invoke-sqlcmd -ServerInstance $primaryAGServerName -Database master -Query $sqlCommand -ConnectionTimeout 0 -QueryTimeout 65334 -ErrorAction SilentlyContinue -ErrorVariable sqlerr if ($sqlerr) { Write-Error("Error running SQL statement $($sqlCommand): $($sqlerr.Exception.Message)") } } Return $True } catch [Exception] { Write-Error ("Error: {0} {1}" -f $_.Exception.Message, $_.Exception.StackTrace) # returning False will set the step status to Failed Return $False }