Infrastructure at your Service

Microsoft Team

SQL Server 2016 : availability groups and the new SSISDB support

This blog post is focused on the new supportability of SSIDB catalog on AlwaysOn architecture.

Others studies are available here:

A couple of weeks ago, I was involved in an SSIS infrastructure project with SQL Server 2014. As you know, the SSIS architecture has fundamentally changed since SQL Server 2012 and has lead to a new way of administrating it from the DBA perspective. This is also particularly true when we have to take into account an AlwaysOn architecture with the new SSISDB catalog since SQL Server 2014..

Indeed, when you want to include the SSISDB catalog to an SQL Server 2014 availability group, you have to perform some extra steps that are required according to the Microsoft SSIS blog post here. The task consists in creating manually some SQL Server jobs to leverage a failover event that requires the re-encryption of the database master key by the service master key on the new primary replica. Likewise, you will have to deal with the SSIS Server Maintenance job that is not AlwaysOn aware by default. Thus, deploying the SSISDB catalog in an availability group’s environment is not an easy task with SQL Server 2014 but let’s take a look at the new support AlwaysOn support with SQL Server 2016.

Fortunately, Microsoft has built on the experience gained from the previous version. Unlike SQL Server 2014, the next version will provide an easier way to deploy and manage the SSISDB catalog in an AlwaysOn infrastructure. By referring to the BOL here, you will notice that all the configuration stuff is done directly from the availability groups wizard without scripting any additional object. So, my goal in this blog post will consist in understanding the internal changes made by Microsoft in this area.

First of all, let’s take a look at some changes by trying to add an SSISDB catalog. At this point, you will be asked to provide the password of your database master key before to continue as follows:


Yes, the SSIDB catalog uses intensively the encryption to protect sensitive data from projects, packages, parameters and so on. At this point,  you may notice a warning icon point. In fact, the wizard warns us about configuring the AlwaysOn support for SSISDB in a final step because it is required in order to leverage availability group failover events.


To enable AlwaysOn support we need to go the Integration Services Catalog node and we must include the concerned replica(s) as shown below:



My configuration is now finished. In a second step, we will have a look at the SQL Server agent jobs. Indeed, during my test I suspected that a lot of stuff was done by SQL Server behind the scene and I was right. It added two additional jobs as shown below:


First of all, the SSIS Failover Monitor Job is designed to run on regular basis in order to detect failover events by refreshing the state of the concerned replica(s) and finally by starting accordingly the SSISDB catalog with the SSISDB.catalog.startup stored procedure. I remember in the past having implemented this kind of failover detection mechanism with the first AlwaysOn release in order to monitor availability group failover events (see my blog post here).

DECLARE @role int
DECLARE @status tinyint
SET @role =(SELECT [role]
                    FROM [sys].[dm_hadr_availability_replica_states] hars
                    INNER JOIN [sys].[availability_databases_cluster] adc
                           ON hars.[group_id] = adc.[group_id]
                    WHERE hars.[is_local] = 1 AND adc.[database_name] =‘SSISDB’)
IF @role = 1
       EXEC [SSISDB].[internal].[refresh_replica_status]
             @server_name = N’SQL161′,
             @status =
             @status OUTPUT
       IF @status = 1
             EXEC [SSISDB].[catalog].[startup]

Moreover, we may also notice some changes about the second job SSIS Server Maintenance Job. Indeed, this job is directly designed to support the AlwaysOn feature regardless the current SSISDB configuration (included or not in an availability group)

DECLARE @role int
SET @role =(SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars
             INNER JOIN [sys].[availability_databases_cluster] adc
                    ON hars.[group_id] = adc.[group_id]
                    WHERE hars.[is_local] = 1 AND adc.[database_name] =‘SSISDB’)
       EXEC [SSISDB].[internal].[cleanup_server_retention_window]

Finally, just a quick search in the SSISDB database gives us a good overview of the new objects related to the AlwaysOn feature:

       name as [object_name],
from sys.objects
where name like ‘%replica%’


  • The procedure add_replica_info seems to be used for adding a new SQL Server instance as replica in the SSISDB catalog.
  • The update_replica_info procedure seems to be used for updating the replica state in the alwayson_support_state internal table.
  • The refresh_replica_status seems to be used by the SSIS Failover Monitor Job on regular basis for updating the replica configuration state as well as detecting failover events.
  • However, according to my tests, I didn’t found neither any explicit call of the delete_replica_info stored procedure or guideline that explains how to exclude a AlwaysOn replica from the SSIDB catalog. I will update this blog post when I get the response.

My feeling is that the new SSISDB support for AlwaysOn will be a pretty cool feature of the next version. This is not the most important improvement in this area for sure but it will help each DBA that wants to implement the SSISDB catalog in an AlwaysOn infrastructure by avoiding an additional burden required with SQL Server 2014. I’m looking forward the definitive package soon!

By David Barbarin


  • Munish Sharma says:

    i did not find “enable AlwaysOn support” when right click on Integration service catalog is this a problem ?

    • David Barbarin says:

      Hi Munish,

      Thanks for reading and apologize for the late response. “Enable AlwaysOn Support” option is only available when you have included the SSISDB database into your availability group. Are you in this case?


  • Scott Hoagland says:

    When I tried the procedure above I did not have any secondary replicas listed to connect?

    • David Barbarin says:

      After adding the SSIDB database to the AG, you need to enable support for SSIS by right-clicking on the Integration Services Catalog folder (Refresh SSMS if menu is still grayed). Once the new windows is open you have to connect to secondary to add it to SSISDB support.

  • Jeralynn says:

    SSIS Failover Monitor Job is created exactly has you detailed above but the job fails stating: The target database, ‘SSISDB’, is participating in an availability group and is currently not accessible for queries. Even though @role = 2 on my secondary for some reason it seems like it is checking that SSISDB is not readable before it does the check for @role.

    • David Barbarin says:

      Hi Jeralynn,

      I will consider your issue. I’ll keep you in touch after double checking my script and performing tests.

    • David Barbarin says:

      Hi Jeralynn,

      From my understanding you are running the procedure on the secondary. You have to run the [SSISDB].[internal].[refresh_replica_status] stored procedure on the primary.

      Did you manage to run it on the new primary after the failover occurs?


  • M. P. says:

    after putting the SSISDB database to the high availability group and connecting to the secondary in the “enable support for Alwayson” form on the primary and clicking ok, I receive this error:
    The operation cannot be performed on database “SSISDB” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
    ALTER DATABASE statement failed.

    sql server profiler traces this command on the secondary:
    IF EXISTS(SELECT name FROM sys.procedures WHERE name=N’disable_scaleout’)
    –Close existing connetion to the SSISDB
    EXEC [internal].[disable_scaleout]

    This command fits to the error, becaus an alwayson database cannot be in single_user_mode.
    Is this the normal behaviour? I use sql server 2017 cu 14.

    • David Barbarin says:

      Hi Patrick.

      Sorry for the delay.

      Not aware of this behavior. I’ll test it and I’ll come back to you soon unless you already fine a solution


  • tony cometti says:

    Question: I have Always On implemented in a test environment with SSISDB added to the group (SQL 2016 Dev, 2 AG nodes). Each time I fail over I’m forced to run the following code to get the packages on the new primary node to run. This has to be run on every failover, even if it’s previously been run. Is there another way to fix this issue rather than adding the code to SSIS Failover Monitor job?
    use ssisdb
    open master key decryption by password = ‘[email protected]
    Alter Master Key Add encryption by Service Master Key

    • David Barbarin says:

      Hi Tony,

      In order to avoid this extra step you must follow the following procedure:
      – Create SSISDB catalog on primary replica
      – Add SSISDB to your AG by using Backup / Restore method (not direct seeding at all) for secondary replicas
      – Enable AlwaysOn support for SSISDB from the primary


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Microsoft Team
Microsoft Team