Friday, August 31, 2018

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.

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
SSIS – Primary

SSIS – Secondary

       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---------------------------------


  1. Replies
    1. IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble.Final Year Projects for CSE

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining .

      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

      The Angular Training covers a wide range of topics including Angular Directives, Angular Services, and Angular programmability.Angular Training

  2. keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore |

  3. Nice to be visiting your blog again, it has been months for me. Well this article that i've been waited for so long. I need this article to complete my assignment in the college, and it has same topic with your article. Thanks, great share. Magnitude 9.o

  4. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

  5. This is my first visit to your web journal! We are a group of volunteers and new activities in the same specialty. Website gave us helpful data to work. automated access