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---------------------------------
No comments:
Post a Comment