The case for building a consolidated SSIS environment

SQL Server Integration Services, SSIS, can be memory intensive, CPU hungry and will generally want to max out the server that it is running on. Your typical DBA is not going to want this running on the same server as the database engine taking up resources and making the monitoring of the database performance harder. Your typical developer is going to want to have SSIS as near as possible to the database for maintenance, possibly performance and to keep a tidy package for deployment and ongoing maintenance. SSIS packages can be heavily used in keeping data up to date and with the consistent approach to development in visual studio and SQL Server data tools creating integrated data solutions with Continuous Integration, CI and Continuous Deployment, CD is more common.

Deploying SSIS on the same box as your DB engine has been very common and can have benefits for ease of deployment, ongoing maintenance and logging/auditing. Another option is to deploy a separate SQL instance with your application/solution solely for SSIS. This can get costly and keeping track of multiple ETL servers for moving data around the data estate can be an unwanted overhead. In an estate where there is a large amount of SSIS being used it is worth considering a consolidation approach.

So with this in mind what are some of the benefits for consolidating:

Licensing

This will be one of the strongest arguments for consolidating but savings in this area depend on how you deploy SSIS. If you are installing SSIS on a SQL Server instance that has other services on it, it could be running the database engine or SSRS, then you will already be licensing the server. If you consistently separate the SSIS out to other hardware then you will need to license those instances and consolidating will obviously have merit from a cost perspective.

Ease of deployment

The project approach for SSIS has made deployment a much simpler matter. The use of ISPAC in a similar approach to DACPAC means the dev teams can pass across the deployment as a whole including the deployment specific variables, connections, folders within it. Deployment to a separate folder for the different applications/solutions combined with a single deployable pack makes manual deployment much easier. The possibility of integration with deployment management software for a CI/CD, approach is much stronger, but that is another blog! With these benefits, deploying to a single environment means that the dev teams and the teams doing the deployments will have one approach and environment to deal with for all releases.

Logging and auditing.

From an enterprise data architecture perspective this should be high on the list of benefits. Knowing where and when your data is moving is a key part of understanding who is using what, when and for what purpose, with growing regulation identifying this as required. With the SSISDB, SSIS Catalog this information starts to become easier to locate. The out of the box reports available in SSMS querying the SSISDB deliver a good start to viewing and keeping track of the packages that are moving data around. Having this data in one place can massively simplify the job of the data stewards.

Logical separation of applications/logs/reports.

This is more of a showcase of how project deployment makes separation possible within a consolidated environment. In the SQL Server 2012 release, the changes to SSIS around SSISDB for logging and the project deployment really started to support the case for a consolidated SSIS environment. A deployment of SSIS packages in earlier versions would allow for a single storage location but with no logical or role based grouping and minimal built in logging this would not provide any great benefit. Releases to either folders or into the MSDB would generally all sit in one area relying on naming conventions for any form of identification. Setting up different folders within the SSIS Catalog allows you to keep different applications separated and secured. The reporting will show the execution for these different folders/applications.

There are clearly benefits to putting together a consolidated environment but there are things that need careful planning. As with any environment you will need to consider an appropriate disaster recovery approach, considering high availability with SQL 2016 offering support for the SSISDB with availability groups. The approach for security for execution, separation of projects, reporting will need planning as well as permissions for CLR, third party installations on the environment and the actual execution method to use. With consolidated environment the upgrade path will be easier to identify but will also require more planning. When there are multiple SSIS installs, the sprawl can make standardising and upgrading difficult but with a shared platform if you upgrade, all projects will need to buy into the upgrade.

In addition to these obvious environmental design requirements, a shared platform will need to consider the possibility of setting up execution timetables. The benefit of a shared environment is that it is all located and maintained in one place. What it also means is that the resources are shared so administering the environment and executions will require knowledge and understanding of what the packages are doing and the time windows for execution, you might not want big jobs hitting each other.

There are clearly benefits to consolidating a SSIS platform but it needs a level of planning to make it run smoothly. The need for an appropriate design and architecture to specify the security, availability, disaster recovery and execution methods needs to go hand in hand with a consistent development approach by those producing the SSIS packages and the teams supporting the environment. If you are putting together an approach for consolidating SSIS these points can be used for validation and planning.