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.