In SQL Server 2012,
AlwaysOn Availability Groups
maximizes the availability of a set of user databases for an enterprise. In addition, SSIS introduces new capabilities which allow customers with the ability to easily deploy to a centralized
SSIS Catalog
(i.e. SSISDB user database).
In this article, we explore how you can make use of SSIS and AlwaysOn Availability Groups. Using these capabilities, it allows you to provide high-availability of SSISDB and its contents (projects, packages, execution logs, etc). We also discuss how to deal with patching with service packs.
Important Note: Enabling AlwaysOn with SSISDB does create some complications when you install a cumulative update or service pack which modifies the SSISDB schema. SSISDB patches are installed when the SQL Server instance first starts up, but putting a database into an availability group causes it to be in an OFFLINE state on startup, which will cause the patching process to fail. To address this, you MUST first remove SSISDB from the availability databases, then patch each node, then recreate the availability group.
If the above warning hasn’t scared you away, keep reading!
SSISDB in Availability Groups
To use SSIS with AlwaysOn, you’ll need to add the SSIS Catalog (SSISDB) into an Availability Group. You’ll need to do the following steps:
Failover
The primary reason to use SSIS with AlwaysOn is to leverage the failover capabilities. When a failover occurs, one of your secondary nodes automatically becomes the new primary node.
Unfortunately, when this happens with SSIS, you will get an error (15581) when the system tries to run an package on the new primary node:
“Please create a master key in the database or open the master key in the session before performing this operation”.
This occurs because of the way SSISDB encrypts sensitive information (such as connection string passwords, and certain parameters. To resolve this, the database master key needs to be re-encrypted by the service master key after failover occurs. To resolve it, you’d need to re-encrypt the database master key by running this T-SQL script:
OPEN master Key decryption by password = 'x' -- Password used when creating SSISDB
ALTER Master Key ADD encryption by Service Master Key
Once the key has been re-encrypted, the new primary node will be able to run SSIS packages.
Auto-Detecting Failover
Having to manually run a script every time failover occurs isn’t ideal. Unfortunately, AlwaysOn doesn’t expose any events we can plug into. To work around this, you can setup a SQL Agent job that polls the replica status, and detects when its node has become the new primary.
Step #1: We create a table containing with a single row in a database other than SSISDB (say, X):
CREATE TABLE [dbo].[replica_role](
[replica_role] [tinyint] NULL
)
INSERT INTO [dbo].[replica_role] ([replica_role]) (
SELECT [role]
FROM sys.dm_hadr_availability_replica_states
WHERE is_local = 1
);
Step #2: On every node, create an agent job (ex, “ssisdb_failover”) with a recurring step that runs below T-SQL script every certain amount of time (say, every 2 minutes):
DECLARE @last_role TINYINT;
SET @last_role = (
SELECT TOP 1 [replica_role]
FROM [dbo].[replica_role]
);
DECLARE @current_role TINYINT;
SET @current_role = (
SELECT ROLE
FROM sys.dm_hadr_availability_replica_states
WHERE is_local = 1
);
IF (@last_role = 2 AND @current_role = 1) -- Last time it was secondary,
-- currently it is primary: need re-encrypt
-- the database master key
BEGIN
USE SSISDB;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x' -- Should be encrypted! See below
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
USE X;
UPDATE dbo.[replica_role] SET [replica_role] = @current_role;
Note , in the above script, the password for the database master key is in plain text. As a security best practice, you can store it in a table using a simple symmetric key encryption , and retrieve it later. For example:
-- Add a column in which to store the encrypted data.
ALTER TABLE dbo.replica_role
ADD ssisdb_pwd VARBINARY(128);
GO
--If there is no master key for X, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'xxx' -- Your master key password
GO
CREATE CERTIFICATE ssisdb_cert
WITH SUBJECT = 'SSISDB Password';
GO
CREATE SYMMETRIC KEY ssisdb_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE ssisdb_cert;
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY ssisdb_key
DECRYPTION BY CERTIFICATE ssisdb_cert;
-- Encrypt the value in the column using the symmetric key.
UPDATE dbo.replica_role
SET ssisdb_pwd = EncryptByKey(Key_GUID('ssisdb_key'), N'x');
GO
To retrieve the password, replace the line “OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x'” in the agent job script with below lines:
USE X;
OPEN SYMMETRIC KEY ssisdb_key DECRYPTION BY CERTIFICATE ssisdb_cert;
DECLARE @pwd NVARCHAR(MAX);
SET @pwd = (SELECT TOP 1 CONVERT(NVARCHAR,DecryptByKey(ssisdb_pwd)) FROM dbo.replica_role);
DECLARE @sqlString NVARCHAR(1024);
SET @sqlString = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ''' + @pwd +'''';
USE SSISDB;
EXECUTE sp_executesql @sqlString;
Patching with Service Packs
SQL Server service packs usually run in single-user mode, while an availability database must be a multi-user database. Therefore, during installing a service pack, you may find all availability databases including SSISDB will be taken OFFLINE and thus fail to patch. To address this, you can first remove SSISDB from the availability databases, then patch each node (Failover Cluster Instances have to be in the same patch level ), then add SSISDB back.
Other Considerations
When an unplanned failover occurs, it is important that the status of SSIS package executions reflect the most up-to-date state. You can restart the instance so that a default SSIS cleanup job will be executed to fix the status of any packages/ deployments that were running at the time the instance went down. The status will be changed to Terminated; temporary data created during project deployment will be removed.
Note that your SSIS packages will still need to have their own failover logic in them. This article provides a good starting point for creating highly available packages.
This post was originally written by Ke Yang and posted back in June – we took the article down when we found the issue with patching SSISDB databases while they are in an availability group. We’ve since updated and re-posted the information!
