patternsqlModerate
Running SSIS from a different server than the primary database instance
Viewed 0 times
theprimaryssisthandatabasedifferentinstancerunningserverfrom
Problem
We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2. On the current production server SSIS is installed on the same server as the database instance and the package execution is managed by the SQL Agent via a Job.
We are upgrading our SQL infrastructure to be clustered and the senior database administrator would like to have SSIS on it's own server, so the ETL server was set up by just installing integration services service onto the machine.
Does the SSIS server also need it's own database instance and/or SQL Agent to manage the execution of the packages?
I'm having some trouble understanding how all the pieces would work in this scenario.
We are using SQL Server 2008 R2 SP1.
We are upgrading our SQL infrastructure to be clustered and the senior database administrator would like to have SSIS on it's own server, so the ETL server was set up by just installing integration services service onto the machine.
Does the SSIS server also need it's own database instance and/or SQL Agent to manage the execution of the packages?
I'm having some trouble understanding how all the pieces would work in this scenario.
We are using SQL Server 2008 R2 SP1.
Solution
I would suggest reading up on SSIS at MSDN:
Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.
Quote from above link:
SQL Server Integration Services is installed through the SQL Server
2008 Setup program. You can install Integration Services alongside
other SQL Server components or you can install Integration Services as
a stand-alone component. Integration Services includes both client and
server applications.
To use a dedicated server for extraction, transformation, and loading
(ETL) processes, we recommend that you 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 does not have an instance of the Database
Engine, you will have to schedule or run packages from a server that
does have an instance of the Database Engine. This means that the
packages will not be running on the ETL server, but instead on the
server from which they were started. As a result, the resources of the
dedicated ETL server are not being used as intended. Furthemore, the
resources of other servers might be strained by the running ETL
processes.
Some additional information could be found here as well: Considerations for Installing Integration Services. You might want to take under consideration the licensing as well and still validate the standalone server is licensed properly.
Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.
Quote from above link:
SQL Server Integration Services is installed through the SQL Server
2008 Setup program. You can install Integration Services alongside
other SQL Server components or you can install Integration Services as
a stand-alone component. Integration Services includes both client and
server applications.
To use a dedicated server for extraction, transformation, and loading
(ETL) processes, we recommend that you 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 does not have an instance of the Database
Engine, you will have to schedule or run packages from a server that
does have an instance of the Database Engine. This means that the
packages will not be running on the ETL server, but instead on the
server from which they were started. As a result, the resources of the
dedicated ETL server are not being used as intended. Furthemore, the
resources of other servers might be strained by the running ETL
processes.
Some additional information could be found here as well: Considerations for Installing Integration Services. You might want to take under consideration the licensing as well and still validate the standalone server is licensed properly.
Context
StackExchange Database Administrators Q#25152, answer score: 10
Revisions (0)
No revisions yet.