Infrastructure at your Service

David Barbarin

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:

blog_54_-_1-_aag_ssidb_database_master_key

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.

blog_54_-_2-_aag_ssidb_validation_step

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

blog_54_-_3-_aag_ssidb_services_integration_services_node

blog_54_-_4-_aag_ssidb_services_integration_services_alwayson_support

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:

blog_54_-_5-_aag_ssidb_catalog_jobs

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
BEGIN
       EXEC [SSISDB].[internal].[refresh_replica_status]
             @server_name = N’SQL161′,
             @status =
             @status OUTPUT
 
       IF @status = 1
             EXEC [SSISDB].[catalog].[startup]
END

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’)
                   
IF DB_ID(‘SSISDB’) IS NOT NULL AND(@role IS NULL OR @role = 1)
       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:

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

blog_54_-_6-_aag_ssidb_objects

  • 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!

 

7 Comments

  • 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?

      Regards

       
  • 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?

      Regards

       

Leave a Reply


seven − 6 =

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader