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---------------------------------
Really informative Blog...Thanks for sharing...Waiting for next update...
ReplyDeleteWordpress Training in Chennai
Wordpress course in Chennai
Wordpress Training Chennai
Wordpress Training in Porur
Wordpress Training in Anna Nagar
Struts Training in Chennai
clinical sas training in chennai
Spring Training in Chennai
Photoshop Classes in Chennai
LoadRunner Training in Chennai
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
DeleteSpring 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
The blog which you have shared is more creative... Waiting for your upcoming data...
ReplyDeletePython Training in Chennai
Python course in Chennai
Python Training
Best Python Training Institute in Chennai
Python training in vadapalani
Python training in porur
Hadoop Training in Chennai
Android Training in Chennai
Big data training in chennai
JAVA Training in Chennai
Informative content,thanks for sharing...waiting for next update...
ReplyDeletejavascript training in chennai
javascript course in chennai
javascript training institute in chennai
javascript training in Thiruvanmiyur
javascript training in Tambaram
javascript training in chennai
core java training in chennai
Photoshop Classes in Chennai
Manual Testing Training in Chennai
Drupal Training in Chennai
Great blog!!! The information was more useful for us... Thanks for sharing with us...
ReplyDeletePython Training in Chennai
Python course in Chennai
Python Classes in Chennai
Python Training Institute in Chennai
Pyhton training in Adyar
Python Training in Tnagar
Big data training in chennai
Hadoop training in chennai
Digital Marketing Course in Chennai
Selenium Training in Chennai
Very informative blog! I am glad that I came across your article. I'm learning a lot from here. Keep us updated by sharing more such blogs.
ReplyDeleteAWS Training in Chennai
Amazon web services Training in Chennai
AWS course in Chennai
UiPath Training in Chennai
Blue Prism Training in Chennai
Machine Learning course in Chennai
AWS Training in OMR
Thanks for sharing informative article with us..
ReplyDeleteDOT NET Training in Chennai
asp .net training in chennai
DOT NET Training Institute in Chennai
.net course in chennai
dot net training in vadapalani
Html5 Training in Chennai
Spring Training in Chennai
Struts Training in Chennai
Wordpress Training in Chennai
SAS Training in Chennai
Content Writing Company in Delhi
ReplyDeleteContent Writing Services in Delhi
Mobile App Development Company Delhi
PPC Company in Delhi
PPC Company in India
Thanks for sharing valuable information.
ReplyDeleteDigital Marketing training Course in Chennai
digital marketing training institute in Chennai
digital marketing training in Chennai
digital marketing course in Chennai
digital marketing course training in omr
digital marketing certification in omr
digital marketing course training in velachery
digital marketing training center in Chennai
digital marketing courses with placement in Chennai
digital marketing certification in Chennai
digital marketing institute in Chennai
digital marketing certification course in Chennai
digital marketing course training in Chennai
Digital Marketing course in Chennai with placement
digital marketing courses in Chennai
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
ReplyDeletedigital marketing training in bangalore | https://www.excelr.com/digital-marketing-training-in-bangalore
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
ReplyDeleteGreat 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.
ReplyDeleteDigital 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
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
ReplyDelete