Freigeben über

SQL Server 2016 HA Series Part 2 – SSIS and Availability Groups

  • Artikel

This series is intended to provide a short focused write up on some of the key new SQL Server 2016 HA features.

Got questions?  Stick them in the comments below and we will do our best to answer them!

SSIS and Availability Groups

 

Current challenges in SQL Server 2012 and 2014:

  • SSISDB relies on objects which are not stored in the SSISDB such as stored procedures, tables, logins stored in master and msdb.  If the SSISDB is in an Availability Group then upon failover the SSISDB will not be able to use these objects anymore and the integration services catalog doesn’t work properly anymore.
  • If a database contains encrypted columns protected by the database master key (DMK) and then added to an Availability Group, when and if a failover occurs, the new primary replica cannot decrypt the data in the database.
  • You cannot upgrade SQL Server when the SSISDB is part of an availability group because SQL has need to change to single-user mode to do the upgrade which cannot happen when the db is in an Availability Group.

 

Enhanced in SQL Server 2016

To solve the first challenge we now call an API which creates the required “additional” objects on each replica (login, tables, stored procedures) meaning upon failover the integration services catalog can continue to function as normal.

To aid in this there were some new objects created:

  • New View
    • catalog.alwayson_replicas
  • New Table
    • internal.alwayson_support_state
  • New Role
    • ssis_monitor
  • New Stored Procedures
    • internal.add_replica_info
    • internal.delete_replica_info
    • internal.update_replica_info
    • interal.refresh_replica_status

 

To solve the second challenge, the GUI has been enhanced to allow the user to add the database master key password when adding the database to an availability group.  This will run the sp_control_dbmasterkey_password stored procedure to each replica creating the credentials required to decrypt the SSISDB.

Enter the password for the SSISDB database.  Note: you may need to hit refresh after entering the password for the status to refresh to “Meets prerequisites”, then again once you tick the box for the “next” button to become available. image

Checking the output of the wizard you may notice the following warning message:

image

 

As the warning message above states, you now need to enable AlwaysOn support so that the Failover Monitor and Maintenance jobs exists on all replicas.

From the primary replica, right click Integration Services Catalogs folder and select “Enable AlwaysOn Support”:

image

 

Connect to your secondary replicas and click ok.

image

 

Both jobs are now available on all replicas in the SSISDB AG.

The SSIS Server Maintenance Job runs every day. The job removes operation records from the SSISDB database that are outside the retention window and maintains a maximum number of versions per SSIS project.

The SSIS Failover Monitor job monitors the AG for failover events and runs every 2 minutes.  It works by querying HADR DMV’s to determine if @role=1 / the primary role.  If it’s in the primary role then it executes SSISDB.internal.refresh_replica_status.

If a failover happens then the job does 2 things:

  1. Calls stored procedure SSISDB.internal.refresh_replica_status – this updates the failover monitor metadata stored in some internal tables of the SSISDB database
  2. Calls stored procedure SSISDB.catalog.startup – this executes another procedure to do status clean work in the new primary node (eg mark the package running status to terminate since the package doesn’t execute in the old primary node anymore).

 

  • Finally for the third challenge SSISDB is now updated separately using the SSIS upgrade wizard.  There does look to be similar challenges with this. Check out the following link for the info. Also note that an upgrade could still be blocked if upgrading from a previous version. https://msdn.microsoft.com/en-us/library/mt163864.aspx#Upgrade

 

Learn More:

sp_control_dbmasterkey_password - https://technet.microsoft.com/en-us/library/ms182754.aspx

What’s new in Integration Services in SQL Server 2016 - https://msdn.microsoft.com/en-us/library/bb522534.aspx#AlwaysOn

AlwaysOn for SSIS Catalogs - https://msdn.microsoft.com/en-us/library/mt163864.aspx

Comments