2

Trying to wrap my head around what's going on under the hood when integration services is installed, and how that relates to SQL instances on the same machine.

Currently we are aiming to have a full BI stack with each component (SQL Server containing DW, SSAS and SSIS) on separate VMs so resource requirements can be siloed. We want to use the project deployment model for SSIS so are targetting SSISDB rather than MSDB, but the server with SSIS installed doesn't have a SQL instance to host SSISDB, so currently it's installed on the SQL box.

My question is, is it necessary to install a SQL instance on the SSIS box in order to host SSISDB or can it reside just within the integration services instance somehow? If so, does that mean that connecting to the SSIS instance through SSMS is just a legacy feature to manage MSDB or file system packages?

This isn't a question about licensing, it's about infrastructure.

2

If you want to run packaged in the project deployment mode (SSISDB), you need to install SQL Server DB engine on the SSIS box.
If you want to execute packages from files system or other stores, you can skip DB engine.
In both cases you need a license for the SSIS box.

In SSISDB mode you will manage packages using SSMS connection to the DB engine.

Steps how to install SSISDB:
1. Open SQL Server Management Studio.
2. Connect to the SQL Server Database Engine.
3. In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
4.Click Enable CLR Integration. The catalog uses CLR stored procedures.

From: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017

1

It is not necessary to add a SQL Server instance on the box running integration services. it can reside on any other database server.

But, if you install Integration Services on a server that server must be covered with a license, even if it is only running the integration services service.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.