Have you ever wondered how to increase performance and availability of SSIS? To achieve it in terms of SQL SSIS Scale-out 2019 you can implement scale-out setup, which allows to distribute workload between several servers and make sure that at least one set of services is always available to execute your workload. In this tutorial you will understand why you should have SSIS Scale-out on clustered SQL environment.
Prerequisites
- Installed at least 2 MS SQL servers 2019 in windows clustered environment
- Separate Active Directory account for running SSIS Scale-out Master and SSIS Scale-out Worker
Always On VS SQL cluster
To set-up SSIS scale-out environment you can use either availability group or SQL cluster. Main difference is that in one case you will put SSIS database (catalog) in always on availability group or in SQL cluster.
- SSIS scale-out with SSIS database in HA AG
- While patching need to brake availability group. More
- HA AG is more complicated to get in my case
- While patching need to brake availability group. More
- SSIS scale-out with SSIS database in SQL cluster
- Understandable scenario for patching
- More experience in SQL clusters
- Understandable scenario for patching
In this case i have chosen SQL cluster solution.
Architecture
As shown in picture below we can see that in configuration we have 2 servers in windows cluster. On top of that we have SQL server with one active and one active node. SSIS in this case instead of one runtime service has spitted in master which coordinates work and worker which actually executes packages. Master and SSIS database are in part of cluster meaning that in case of need or disaster they are failing over together and always available. SSIS workers are installed as single services and are not part of cluster although they can work in parallel which increases performance.
Advantages
Advantage | Comment |
Increase availability | This is being achieved by minimizing downtime during maintenance (in comparison with simple SSIS). No additional effort from developing side is not needed because by default package will be executed on active server. At one time moment at least one worker is always available. Since master and it’s SSIS database is in cluster it will be always available. |
Use of idle passive node resources |
|
Considerations
- Permissions for worker and master services account has to be reviewed
- Should consider if enable scale-out server-wide which will allow SSIS package work execution distribution between 2 workers. Users should be aware of the fact that secondary worker may be restarted any time planned or not
- Should take into account that during failover SSIS packages will be terminated anyway
- If child packages are called out of master package then they will not be distributed between workers
- Work is not distributed in case if each SSIS package is called as separate SQL agent job
- The best scenario is to call packages using stored procedures where possible to specify whether use parallelism and what workers should execute package. More.
In following posts i will tell how to set up SQL 2019 Server Integration Services Scale-out and how to call packages using stored procedures to get maximal gain from SSIS scale-out.
