3

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

  • What deployment model are you going to use for SSIS? – Shawn Melton Apr 3 '17 at 4:50
  • Are you planning on the SSIS server staying up in DC or it failing over to San Jose as well? – Shawn Melton Apr 3 '17 at 8:28
  • The SSIS can stay in DC. This is because the application will be used only once every week and that is when the SSIS will be used . What are the options if I want to fail over the SSIS as well? – user2923332 Apr 3 '17 at 12:20
  • @ShawnMelton please guide me with this situation. I just want the standalone SSIS to stay in DC but connect to the listner. How do I do it? – user2923332 Apr 18 '17 at 2:59
1

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.

  • On execution of the packages, he could also add a step to check if the server where the package is being run is on the primary node. If not, cancel execution. That way, no one would have to disable the jobs when a failover occurred. – Shooter McGavin May 3 '18 at 17:23

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.