Issue while Enabling Support For "Always On" At the time of Integration Service Catalog creation or after creation

47 Views Asked by At

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 :

  1. $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".
  2. $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

0

There are 0 best solutions below