SQL Server Integration service (SSIS) 2012 – High Availability & Automated Fault Tolerance Proposed Solution

1.     Objective

The Objective of this article is proposed a solution for SQL Server Integration Service for high availability of SSIS envrionment as 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.

https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster

2.     Proposed Solution

The proposed solution will use the following native capabilities of SQL Server

                                 I.            SQL Server Always On to ensure Database instance(s) High Availability

                               II.            SQL Server Agent Capability for automated triggering & switching from one envrionment to another

                             III.            SQL Server Database mail configuration for alert/notification

                            IV.            SQL Server Database Engine/T-SQL programming for communicating with SSIS Server and storing their response

3.     Proposed Architecture

Fig-1 shows proposed Architecture – SSIS 2012 High availability & automated Fault Tolerance

4.     Steps to implement SSIS HA & Fault Tolerance

4.1.    Table(s): Proposed solution consists of 2 table(s):

•       SSIS_Server_Health_Monitoring – To store SSIS server health status which we get from stored procedure which will ping the server and store the response in the table. Below is the table structure

SSIS Instance Name	Health_Flag	Timestamp
SSIS – Primary	0
SSIS – Secondary	1

•       Fault_Tolerance_Configuration - This table will be configuration table in which we can put configuration for automated fault tolerance between Primary/Secondary server(s).

SSIS Instance Name	SSIS Failover Instance Name	Triggering Job
SSIS – Primary	SSIS – Secondary	Fault Tolerance Job

      

4.2.    SQL Agent Job(s): Proposed solution consists of 2 SQL Agent Job(s):

•       Monitor_SSIS_Server_Health Job: which will communicates to both the primary & secondary SSIS server and store the response in the DB

•       Automatic_Fault_Tolerence Job:

•       It will keep check the SSIS_Server_Health_Monitoring table

•       Whenever there is a zero flag found for a server, it will start the job into another server based on the Fault_Tolerance_Configuration

5.     SSIS Package Level Load Balancing

·         Package Level load balancing CAN BE PERFORMED by using SSIS -Balanced Data Distributor/ performing parallel processing in the package.

6.     SSIS Server Level Load Balancing

·         Sever level load balancing has been proposed by adding more no of SSIS server(s) with Read committed Isolation snapshot.

·         Following are the issue(s) if we go for multiple server level Load balancing with default read committed isolation level:

o   Process of one SSIS instance has to wait for another to complete its execution. e.g. If SSIS 1 is processing the a table then SSIS 2 has to wait till SSIS 1 done with its execution.

o   Implementing Load Balancing in ETL operation may result into query wait state, locking & Deadlock causes serious performance issues on the reporting portal

o   To avoid the locking /deadlocking issue, we can use Read committed Isolation Level which ensures that Report will show only those data that were committed before the start of the transaction.

7.     Restarting of Packages

·         If SSIS instance goes down during package execution

o   Process will be start from starting Point.

o   ETL & SQL Error & transactional handling will be used to start the process from starting point

·         If the package failed during execution then

o   Package will be restarted from the point of failure by using Checkpoint capabilities of the SSIS.

o   ETL & SQL Error & transactional handling will be used for starting the process from point of failure.

8.     Other Options

·         Using existing version of SQL Server - SSIS 2012  & perform Manual failover with the help with support team

·         SSIS 2016 – Always on for SSIS Catalog. Need to upgrade the existing SQL 2012 to SQL 2016

9.     Conclusion :

By using above solution, High Availability & Automated Fault Tolerance can be implemented for SSIS 2012 instances.

                        -------------------------------------End of Article---------------------------------