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.
Best Answer
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