0

My understanding is that the database engine feature of sql server enables us to create databases.

In the SSIS documentation, https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver15#install-integration-services, it is mentioned that:

On the Feature Selection page, under Shared Features, select Integration Services.

Under Instance Features, optionally select Database Engine Services to host the SSIS Catalog database, SSISDB, to store, manage, run, and monitor SSIS packages.

What is the purpose that one would not select the database engine service while installing integration services?

It is clear from the documentation quote above that - if we do not select the database engine, then we cannot host the SSIS catalog db (ssisdb). So what is the purpose of such an install?

3
  • 2
    The database engine does not need to be installed on the same server as SSIS.
    – AMtwo
    Aug 17, 2021 at 12:41
  • Package is executed on server that has database engine. So what is the purpose of integration services being on separate server?
    – variable
    Aug 19, 2021 at 7:37
  • Can you help me understand what is benefit to installing ssis elsewhere? I understand package will run where the package is located (in server that had the db engine and the ssisdb).
    – variable
    May 11 at 18:12

2 Answers 2

0

The answer is on the same document that you referred to. Why would someone do that, I do not know but there are options available to install Integration Services without installing Database Engine Services on the same host.

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

1
  • 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.. What could be the reason of calling that server an ETL server?
    – variable
    May 6 at 21:06
-1

What is the purpose that one would not select the database engine service while installing integration services?

Well this is mostly done for isolation and security the packages reside on remote ETL machine to which limited people have access but run on machine from where you can monitor the package. This is done to secure the ETL packages. Below is quote from Installing dedicated instance of SSIS for ETL

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

9
  • Your last quote suggests that the package does not run on the etl server. So what is the purpose of etl server? I am assuming etl server is the one with integration services installed and there is no database engine.
    – variable
    Aug 17, 2021 at 15:03
  • @variable Yes you are correct, it does not runs on ETL server. It runs on servers from where this package is called ( this server has DB engine). This is done so that no one can access the package, for securing the ETL packages. People may be able to run the package but not access it.
    – Shanky
    Aug 17, 2021 at 15:07
  • I didn't understand what is the purpose of the etl server in this example (on which only integration services is installed)?
    – variable
    Aug 17, 2021 at 15:23
  • On ETL servers the packages are "located" on the other server having DB engine the package actually runs. Clear ? In such scenario to isolate the packages ETL server is built with just SSIS installed.
    – Shanky
    Aug 17, 2021 at 15:27
  • 1
    Please read my answer again
    – Shanky
    Aug 19, 2021 at 11:34

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.