3

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.

2
  • What is the output of this query on the secondary server? Use master; SELECT * 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' Aug 29, 2017 at 12:34
  • Sorry about formatting, not sure how to make this more readable... 2A809377-D391-47E1-AC1F-47CFAFC85634 FF3D6351-A970-4149-9C76-CB34D05B50AF 1 2 SECONDARY 2 ONLINE 1 CONNECTED 1 ONLINE 2 HEALTHY NULL NULL NULL FF3D6351-A970-4149-9C76-CB34D05B50AF EF729514-4166-4623-9E07-7EF6E7953CEE SSISDB 48000000005700000
    – Bendy22
    Aug 30, 2017 at 18:05

3 Answers 3

4

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

4
  • 1
    This makes the job succeed. I will make sure that it does its intended job when I failover the AG. Thanks! If it works, time to create a Connect item...
    – Bendy22
    Aug 30, 2017 at 18:11
  • Something still isn't right here. Using this code, I get an error: Executed as user: ##MS_SSISServerCleanupJobLogin##. Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. [SQLSTATE 42000] (Error 214) on the second node. However, when I fail over the SSISDB AG, this job's original code works on the first node somehow. When the code from the job in a query window, it still fails with the error that SSISDB isn't up. I fail it back and the job fails again on the second node. How can run without error in a SQL job on the first node when it is not the primary replica?
    – Bendy22
    Aug 31, 2017 at 14:11
  • I've edited the script for a version that I have tested on a local AG running SSIS, and it works on both the primary and secondary, just be sure to change YourServerName to whatever server the script will be running on.
    – Nic
    Aug 31, 2017 at 15:03
  • Ah yes, @status needs to be placed inside the dynamic SQL. Do you know why the error message reported a variable called @statement? I have tested failovers and it works on either node in either role. Thank you!
    – Bendy22
    Aug 31, 2017 at 15:53
1

Here is an improved version of the above answer which uses the @@SERVERNAME global variable to populate the @server_name parameter.

This meas that the script will work without modification on any SQL Instance

DECLARE @role INT
DECLARE @sqlCmd NVARCHAR(MAX)
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
    SET @sqlCmd = N'DECLARE @status tinyint;
    EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N''' + @@SERVERNAME + ''', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]';
    EXEC sys.sp_executesql @sqlCmd;
END  
1

I know this has already been answered, but here is a slight variation of the above code that uses:

  • Dynamic SQL so the job does not fail when a server is a secondary replica
  • A parameter definition list
  • The OUTPUT parameter for sys.sp_executesql to capture the output value of the @status parameter

DECLARE
    @role                   AS INT
    , @status               AS TINYINT
    , @sql                  AS NVARCHAR(500)
    , @parameter_definition AS NVARCHAR(500)
    , @server_name          AS NVARCHAR(256) = @@SERVERNAME;

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

    SET @sql = N'EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = @server_name, @status = @status OUTPUT;';
    SET @parameter_definition = N'@server_name AS NVARCHAR(256), @status AS TINYINT OUTPUT';

    EXEC sys.sp_executesql @sql, @parameter_definition, @server_name = @server_name, @status = @status OUTPUT;

    IF @status = 1
        EXEC [SSISDB].[catalog].[startup];
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.