[SOLVED] SSISDB and Always On. How to schedule jobs?

SSISDB and Always On. How to schedule jobs?

Now that SQL Server 2016 enables SSISDB to be fully High Available, I have a question regarding the job setup.

When I do create a SQL Agent Job that executes a SSIS Package that is deployed in SSISDB, should in the job step the Server be the Listener Name or the physical host name?

I am asking that because if I use the physical host name and create the job in both replicas, the secondary jobs will always fail because the DB is in read only mode. I didn't try placing the Listener name yet, because I wanted to get opinions first.

Solution

The server name should be listener name ,if you follow this approach,it is enough to deploy job in one instance

you also can use Physical host names and deploy jobs in all instances,provided you have below piece of code as first step

- fn_hadr_group_is_primary
USE master;
GO
IF OBJECT_ID('dbo.fn_hadr_group_is_primary', 'FN') IS NOT NULL
  DROP FUNCTION dbo.fn_hadr_group_is_primary;
GO
CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN;
  DECLARE @PrimaryReplica sysname; 

  SELECT
    @PrimaryReplica = hags.primary_replica
  FROM sys.dm_hadr_availability_group_states hags
  INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
  WHERE ag.name = @AGName;

  IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
    RETURN 1; -- primary

    RETURN 0; -- not primary
END;

This post also deals with some of the common issues,that needs to be taken care off

https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/