5

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.
  • 3
    All items in the script are compiled; you may need to put the SSISDB related T-SQL into a VARCHAR(MAX) and sp_executesql on it instead. – Max Vernon Jul 31 '14 at 22:01
  • 1
    That's what I thought was going on. I could also switch the secondary to readable 'Yes' rather than 'Read only'. Thanks! – Datsun80 Aug 1 '14 at 13:54
4

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.

  • In this case it is a job I am running on the secondary server. The solution I went with was to wrap the query within the If statement in quotes and use sp_executesql. It only executes the code if the server is primary and therefore no longer readonly. – Datsun80 Aug 7 '14 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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