Install only SQL SERVER 2022 integration services on host

Question

Install only SQL SERVER 2022 integration services on host

Alen Cappelletti 1,047

Hi,

during the SQL SERVER 2022 installation process, I selected only the Integration Services and the master-out.
With my surprise the process not required installation of the SQL SERVER engine.
At the end, as I suspected, it also installed a SQL instance.

User's image

This passage is not clear to me. Why the process didn't force me to install SQL engine, if it created it anyway?

I was wondering how I could create the "sissdb" to support them in project deployment mode... naturally needs a DB/Instance

What are the benefits of this type of installation... without selecting the engine if the sql service then installs it anyway?

Is an engine still mandatory? The ssis could all be on file systems... but without scheduling (SQL SERVER AGENT) it would be difficult.

ALEN

0 comments

4 answers

Your answer

Answer 1

I found the correct answer: https://learn.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver16&source=recommendations#install-a-dedicated-server-for-etl-processes

Install a dedicated server for ETL processes

To use a dedicated server for extraction, transformation, and loading (ETL) processes, install a local instance of the SQL Server Database Engine when you install Integration Services. Integration Services typically stores packages in an instance of the Database Engine and relies on SQL Server Agent for scheduling those packages.

If the ETL server doesn't have an instance of the Database Engine, you have to schedule or run packages from a server that does have an instance of the Database Engine. As a result, the packages aren't running on the ETL server, but instead on the server from which they're started. As a result, the resources of the dedicated ETL server aren't being used as intended. Furthermore, the resources of other servers may be strained by the running ETL processes.

From my point of view it makes no sense to dedicate an instance to integration services without having the possibility of scheduling on it. So it always makes sense to also install the engine... (which the license includes) and limit the use of maximum memory with a CAP.

ALEN

Answer 2

ZoeHui-MSFT 41,551

Hi @Alen Cappelletti,

Starting in SQL Server 2012 (11.x), Integration Services stores objects, settings, and operational data in the SSISDB database for projects that you've deployed to the Integration Services server using the project deployment model. The Integration Services server, which is an instance of the SQL Server Database Engine, hosts the database. For more information about the database, see SSIS Catalog. For more information about deploying projects to the Integration Services server, see Deploy Integration Services (SSIS) Projects and Packages.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Alen Cappelletti 1,047 Reputation points

2024-05-09T07:16:34.0966667+00:00

Clarify one of your passages for me... "which is an instance of the SQL Server Database Engine,". Do you mean that by installing only the service, under the wood, there is always an ssisdb? It seems impossible to me since by installing only the service there is no engine/instance that contains any database.

Answer 3

Erland Sommarskog 134.3K MVP Volunteer Moderator

If you have the Database Engine installed, I think it is because you selected it during install. Or it was there already.

I made the selection you did: Integration Services + Scale Out Master. This is what I see in SSCM:

User's image

This is what I see in the Services applet:

User's image

Alen Cappelletti 1,047 Reputation points

2024-05-08T21:47:20.6933333+00:00

Hi Erik...

Yes you're right I just end an installation into Windows Sandbox and actually I got your same image....
Alen Cappelletti 1,047 Reputation points

2024-05-08T22:23:29.3133333+00:00

But what is the point of installing only the service?

Since I want to centralize all the packages in one dedicated place... I need the ssisdb and certainly I don't want them to be on the filesystem.

Furthermore, without an engine + SQL SERVER AGEN... I don't dare think about completely manual scheduling using "windows tasks".

What do you think? Have you ever installed only the integration service without anything else on a dedicated machine?

I'd like to know a case in which something like this would make sense.
If I have to move all the ssis from multiple machines I would like to centralize them and therefore terminate the service on these, in order to have a single dedicated instance with commensurate exclusive resources.

ALEN

Answer 4

Olaf Helper 47,616

SSIS is a "shared service", mean's per machine you can have SSIS only once in one version and that one can run as stand-alone service. The SSISDB can be hosted anywhere else

You can install the database engine up to 50 times as "named instance" per machine and this in different versions & editions.

See

https://learn.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server?view=sql-server-ver16

Alen Cappelletti 1,047 Reputation points

2024-05-08T12:53:50.1133333+00:00

Thanks for your reply Olaf,

but my point was to understand the reason for installing the SQL Server Instance even though I had not selected the engine checkbox.... SSIS service implicitally install also a SQL instance.

Share via

Install only SQL SERVER 2022 integration services on host

4 answers

Your answer