Should SSIS Packages run on a database SQL Server or a separate app server?

Question

It seems to be a policy in my company to place application code on a separate server than the database.

We are a data warehouse that mostly use SSIS and T-SQL to transform and load data into a SQL Server. We would like to use a C# .NET app to do some of the steps

For example, to iterate through a file and call a web service.

Given that SQL Server 2008 now automatically installs .NET 3.5 and supports .NET stored procedures, is it justified to prohibit .ETL code written in .NET from running on the database server? Having both SSIS and .NET code running on the same box will help simplify our workflow so we don't have to worry about a scheduling app having to control flow across servers.

I do understand that it would be appropriate, for example in a web app, to separate the business logic tier from the db tier.

One possible snag: In my company, the DBAs are not Admins of the App Servers and do not have rights to install the db Client tools to the app server and the App Serve admins probably should not have anything to do with installing database client tools. If they did, there would have to be coordination between the App server Admins and the DB server Admins. Perhaps the DBAs could share ownership of the App Server. How do companies usually handle this?

Answer 1

We implemented SSIS by placing DTS packages on same server with SSIS Server and we have winservice that leaves on another server and executing remotely DTS Packages.

Answer 2

Best practice is to leave SQL Server completely alone on the box. SQL Server uses a user mode cooperative multi-tasking and resource control that assumes 100% ownership of the systemn and does not play well if other processes are stealing memory or CPU. See Dynamic Memory Management and SQL Server Batch or Task Scheduling.

As for .NET doing ETL Web calls from inside SQLCLR: don't even think about it. NEver do any sort of blocking operations from SQLCLR, you'll starve the SQL scheduler.

Answer 3

As a general rule, you should have your SSIS server be separate from the SQL Server as SSIS is an app layer. Next, your application code should run on a separate server (can be the same as the SSIS server).

Keep your scaling concerns separate.

Answer 4

It depends on the code, the availability needed of the database, and the size of the box. If you're doing a lot in memory on the SSIS pipe or in your C# app, I'm a proponent of putting it on a separate box (all of the ETL, not just some of it). If you're just using SSIS to call stored procs on the database, it's fine to leave it on the same system.

That being said, I'd avoid splintering the ETL across boxes unless there's an overwhelming reason to do so. It adds a lot of complexity for not much benefit (usually).

That being said, if you need C# stuff to run, you could always use the script tasks in SSIS to control its execution.