I am planning for a "multi-subnet" AG group . 2 at the primary data center in DC and one at San Jose. We are planning to test a vendor application which requires SSIS. I am planning for the SSIS to be in a separate instance and not on the AG, and if a failover was performed to SanJose, how do I configure SSIS to make sure it's available?
The application will be creating 3 databases, one of which I am planning to add to the AG. The renaming two will be on the SSIS server itself. Due to restrictions, I cannot install SSIS on the SQL Servers itself. This is one of the reasons I had to separate the SSIS into a standalone instance
Best Answer
If you are setting up an availability group listener across multiple subnets and plan to use static IP addresses, you need to get the static IP address of every subnet that hosts an availability replica for the availability group for which you are creating the listener. Usually, you will need to ask your network administrators for the static IP addresses.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server#Prerequisites
So your SSIS packages will point to the Listener name which will have multiple static IP addresses each on a different subnet.
https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/
Regarding failover of SSIS, you can have the SSIS Packages deployed on the Primary as well as the secondary server either on the AG Instance itself or on a different standalone instances of the servers. As part of deployment process, you can make sure that every package you deploy on primary SSIS instance is also deployed on the secondary. Either on Primary or Secondary SSIS Instance, the packages will always point to the Listener name of the AG and hence failover of AG instance should not impact the SSIS package executions.
If the server on which the primary SSIS instance resides goes down the you can enable the SSIS jobs on the Secondary standalone SSIS Instance.