Question on Deployment

hello everyone,

I have a question on deployment of SSIS.

My team plans to develop a lot of SSIS packages.

I want my deployment environment to be as centralized as possible, but the processing environment to be distributed.

Here is what I had in mind. I have one server running SQL Server DB and SQL Server Agent. I have all the SSIS packages deployed on this server and schedule them as jobs in SQL Server Agent.

Now I have two other machines just running SSIS service (no other component). When the agent executes the packages, he should use either of the two machines. (Load balancing would be good, or I can manually assign the packages to the machines too).

is this possible? recommended?

Another approach would be that each SSIS Server, also has SQL Server and Agent installed. And respective packages are installed and scheduled on their respective servers. But here I have to maintain the packages and schedules on 2 machines.

regards,
Abhishek.

MSDN Student wrote:

hello everyone,

I have a question on deployment of SSIS.

My team plans to develop a lot of SSIS packages.

I want my deployment environment to be as centralized as possible, but the processing environment to be distributed.

Here is what I had in mind. I have one server running SQL Server DB and SQL Server Agent. I have all the SSIS packages deployed on this server and schedule them as jobs in SQL Server Agent.

Now I have two other machines just running SSIS service (no other component). When the agent executes the packages, he should use either of the two machines. (Load balancing would be good, or I can manually assign the packages to the machines too).

is this possible? recommended?

Another approach would be that each SSIS Server, also has SQL Server and Agent installed. And respective packages are installed and scheduled on their respective servers. But here I have to maintain the packages and schedules on 2 machines.

regards,
Abhishek.

You cannot execute packages remotely so the first scenario you outlined is not possible.

-Jamie

|||

OK. How about this one

On one machine I have SQL Server installed where I deploy all my SSIS packages. On the other two machines I have SQL Agent and SSIS Service installed.

The two machines will read the common SQL Server repository to read SSIS packages and execute them on the respective machines.

I will still be able to keep all the packages in one central SQL Server location, but I will have to configure the SQL Jobs on both the SSIS servers.

Will this work?

regards,
Abhishek.

|||

Yep, that will work fine.

I have to ask why do you want to do this? Unless you are re-using exactly the same (version) package across several machines, it seems like the central dependency doesn't offer that much.