Integration Services service is not a clustered or cluster-aware service and does not support failover from one cluster node to another. Therefore, in a clustered environment, Integration Services should be installed and started as a stand-alone service on each node in the cluster.
Fig. showing SSIS Configuration for Fault Tolerance
•There will be only & only manual failover between the SSIS instances once SQL Agent send Job failure email notification to respective team
SSIS Manual Failover Cases:
•Case 1- SQL Server A is down
oIt will switch to SQL Server B because of AlwaysOn
oSSIS B instance will refresh the SQL Server B.
•Case 2- SSIS A instance is down
oSSIS B instance will run all the packages to refresh the database in Server B
o Server A & Server B databases will be in sync because of AlwaysOn
•Case 3- SQL Server B is down
oIt will switch to SQL Server A
oSSIS A instance will refresh the SQL Server A.
•Case 4- SSIS B instance is down
oSSIS A instance will run all the packages to refresh the database in Server A
oServer A & Server B databases are in sync because of AlwaysOn
•Restarting of the packages
oIf SSIS instance goes down during package execution
•SQL Server Agent job will fail and send the Job failure email notification to the respective team
•Manual failover will be performed to switch to another SSIS instance
•Process will be start from starting Point.
•ETL & SQL Error & transactional handling will be used to start the process from starting point
oIf the package failed during execution then
•Package will be restarted from the point of failure by using Checkpoint capabilities of the SSIS.
•ETL & SQL Error & transactional handling will be used for starting the process from point of failure.
oPackage Level load balancing can be performed by using SSIS -Balanced Data Distributor/ performing parallel processing in the package.
oSSIS Instance level load balancing is not recommended because SSIS supports manual failover only for which if a server is down another server should be available for complete data processing.