Sample Header Ad - 728x90

Execute T-SQL Only If AlwaysOn Database Is Primary

5 votes
1 answer
5549 views
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 into 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.
Asked by Datsun80 (51 rep)
Jul 31, 2014, 09:35 PM
Last activity: Apr 1, 2024, 11:40 PM