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.
•Load Balancing
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.
Thank you so much for providing information about the best and most useful aspect of SSIS.
ReplyDeleteSSIS SSIS Upsert