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
Last activity: Apr 1, 2024, 11:40 PM