Sql-server – Execute T-SQL Only If AlwaysOn Database Is Primary

availability-groupssql serversql-server-2012ssis-2012

Little background on the system. It is SQL Server 2012 AlwaysOn Availability Group with 1 primary and 1 secondary.

I have been following this article (SSIS with AlwaysOn) to make SSIS work with AlwaysOn. I have a table that records the current role of the server and a job that runs every two minutes checking if it recently failed over. The problem I am running in to is the secondary only accepts readonly connections for the SSISDB and my script won't execute because of it.

Here is the script:

USE master;

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)
BEGIN
    USE SSISDB;
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END

USE master;
UPDATE dbo.[replica_role] SET [replica_role] = @current_role;

I am confused as to why the error is happening because the IF statement is evaluating to false so the USE SSISDB is not getting executed. I could turn the statements into strings and use exec but I would rather not if possible. When I execute the script this is the error I get:

The target database ('SSISDB') is in an availability group and is currently accessible
for connections when the application intent is set to read only. For more information
about application intent, see SQL Server Books Online.

Best Answer

The client connection role for secondary replicas determines whether your database is always available, or only available to read-only connections, or not available at all. If you set the database to read-only connections, then your connection string has to include the parameter ApplicationIntent=ReadOnly as defined in that Books Online page.

When you try to connect to that database without specifying ApplicationIntent=ReadOnly, your query will fail.

Related Question