SQL Server 2016 – SSISDB AlwaysOn Support and Failover Monitor Job Issues

availability-groupssql-server-2016ssis-2016

Setting up SSISDB with the new AlwaysOn support in SQL 2016 Enterprise. This creates two jobs, one of which is the SSIS Failover Monitor Job. The secondary is not readable. The code for this job is:

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'SQL2', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]
END

Every time this job runs, I get the below error due to SSISDB not being online on this secondary instance. This is because SQL Server must parse all parts of the query, regardless if conditional code paths are currently valid or not. Since SSISDB is offline, it cannot be parsed.

Executed as user: ##MS_SSISServerCleanupJobLogin##. The target database, 'SSISDB', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. [SQLSTATE 42000] (Error 976). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.

This code is generated by SQL Server, and since it is such a simple setup, I have a hard time believing it is a bug. But I don't know 1) what else it could be and 2) how one would work around it and provide the designed functionality without the secondary being readable.

Best Answer

Consider wrapping the statement in the BEGIN END block inside of sp_executeSQL so that it bypasses the syntax checking until runtime.

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 @role = 1
BEGIN
    exec sp_executeSQL N'DECLARE @status tinyint;
    EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N''YourServerName'', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]'
END

Note: edited the script

Related Question