What I achieved so far with this PowerShell Script is successfully creating catalog SSISDB and database SSISDB and adding them to the Availability group. What I wanted also is to Enable Support For Always on(An option we get while right-clicking on Integration service in SSMS). The PowerShell Script which I was using so far :
Write-Host "SSIS DB deployment"
try
{
# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")
# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
# Create a connection to the server
$sqlConnectionString = "Data Source=$reportAOAGListenerName;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
$sql=" exec sp_configure 'show advance options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'clr enabled',1;
GO
RECONFIGURE;
GO"
invoke-sqlcmd -Query $sql -ServerInstance $reportServerInstance -ErrorAction SilentlyContinue
invoke-sqlcmd -Query $sql -ServerInstance $reportSecondaryNodeInstance -ErrorAction SilentlyContinue
if ($integrationServices.Catalogs.Count -eq 0)
{
Write-Host "SSIS Not exist."
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, $ssisDBName, $ssisCatalogPassword)
$catalog.Create()
}
else
{
Write-Host "SSIS exists."
}
$sqlRecoverySettingChange = "USE [master]
GO
ALTER DATABASE [$ssisDBName] SET RECOVERY FULL
GO
"
invoke-sqlcmd -Query $sqlRecoverySettingChange -ServerInstance $reportServerInstance
$sql="select
case
when
hdrs.is_primary_replica IS NULL then 0
else
1
end
as InAG
from sys.databases as sd
left outer join sys.dm_hadr_database_replica_states as hdrs on hdrs.database_id = sd.database_id
WHERE sd.name ='$ssisDBName'"
$inAG= invoke-sqlcmd -Query $sql -ServerInstance $reportServerInstance
if( $inAG.InAG -eq 0)
{
Write-Host "AOAG Addition started"
$AOAGSQLPrimary="ALTER AVAILABILITY GROUP [$reportAOAGGroupName] ADD DATABASE [$ssisDBName]
GO"
invoke-sqlcmd -Query $AOAGSQLPrimary -ServerInstance $reportServerInstance
Write-Host "AOAG Addition done in group: $($reportAOAGGroupName)"
}
}
catch
{
Write-Host "LoadException";
#$Error | format-list -force
Write-Host $_;
}
What I have tried so far is :
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, $ssisDBName, $ssisCatalogPassword, $true)I found that this additional parameter helps in enabling but got an exception : New-Object : Cannot find an overload for "Catalog" and the argument count: "4".$createDBIfNotExists = $true; $catalogMode = "AlwaysOn"; $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, $ssisDBName, $ssisCatalogPassword, $createDBIfNotExists, $catalogMode, $reportServerInstance, $reportAOAGGroupName);Exception : New-Object : Cannot find an overload for "Catalog" and the argument count: "7".
if ($catalog -ne $null)
{
# Set AlwaysOnEnabled property to true
if ($catalog.Properties.Contains("AlwaysOnEnabled"))
{
$catalog.Properties["AlwaysOnEnabled"].SetValue($catalog, $true, $null)
$catalog.Alter()
Write-Host "AlwaysOnEnabled property set to true."
}
else
{
Write-Host "AlwaysOnEnabled property not found in catalog properties."
}
}
Output : AlwaysOnEnabled property not found in catalog properties. 4.
$catalog = $integrationServices.Catalogs[$ssisDBName]
if (!$catalog)
{
Write-Host "SSIS Not exist."
# Provision a new SSIS Catalog
$catalog = $integrationServices.CreateCatalog($ssisDBName, $ssisCatalogPassword, $true)
}
else
{
Write-Host "SSIS exists."
}
Exception : Not found function or method CreateCatalog 5.
$catalog = $integrationServices.Catalogs["SSISDB"]
# Set AlwaysOnEnabled property to true
$catalog.AlwaysOnEnabled = $true
$catalog.Alter()
Exception : Property "AlwaysOnEnabled" not found
The version of Integration service is 15.0.0.0
The list of properties I have found by running the query on primary server by using the below script is :
SELECT TOP (1000) [property_name],[property_value] FROM [SSISDB].[catalog].[catalog_properties]
DEFAULT_EXECUTION_MODE
ENCRYPTION_ALGORITHM
MAX_PROJECT_VERSIONS
OPERATION_CLEANUP_ENABLED
RETENTION_WINDOW
SCHEMA_BUILD
SCHEMA_VERSION
SERVER_CUSTOMIZED_LOGGING_LEVEL
SERVER_LOGGING_LEVEL
SERVER_OPERATION_ENCRYPTION_LEVEL
VERSION_CLEANUP_ENABLED
I found a property named: **InitialSupportForAlwaysOnPrimary **and InitialSupportForAlwaysOnSecondary. I don't know how to use it in my PowerShell Script. I tried it as $catalog.InitialSupportForAlwaysOnPrimary() but nothing happened. On enabling support for always on manually by RIGHT CLICK on INTEGRATION SERVICES CATALOGS after catalog creation and addition to an availability group, SSIS Server Maintenance Job and SSIS Failover Monitor Job get automatically created on primary as well as secondary server