DTSRUN: To multi-tier or not to multi-tier. that is the question...

Greetings,

We are trying to set up a set of "Leading Practices" for our
developers, as well as ourselves, and hope some gentle reader can
recommend some documentation in favor of what appears to be the right
position to take.

We do not allow third party applications to run on our SQL Servers. We
want to include DTS Packages under the definition of third party
applications, insisting instead that the developers save their
packages as COM Formatted files into their source code control systems
and run them from their app servers. The devlopers would like to hear
this from someone besides ourselves.

While strong recomendations to remove guest access to MSDB altogether
abound, I have been unable to find a straight forward discussion of
the advantages of structured file storage and app server off load of
DTS packages.

Can anyone suggest any articles, white papers, rants, etc attempting
to formulate a solution to the benefits of taking msdb away from
guest, with the advantages of running DTS from an App server or
workstation platform, with the packages protected in source code
control?

Thank you
John Pollins
jpollins @. eqt . comThere is no right or wrong answer but let me try to highlight some
important points.

The COM structured storage file format makes it easier to version DTS
packages in a source control system. Also, deployment is quite simple
with files and we've been using this method for years with good results.
Note that the DTS storage format decision is mostly one of configuration
management and deployment and not related to performance and
scalability.

DTS packages are application code so I suggest you treat DTS packages
like other application code. SQL Server is often deployed on a
dedicated machine so that database services don't compete for the same
hardware resources as the other application tiers. Consequently, it
usually makes sense to run DTS packages on application servers..
However, for smaller applications, it may be most cost effective to run
all application components as well as the database on the same server.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"John Pollins" <jpollins@.eqt.com> wrote in message
news:c64e5ac0.0308191052.671fa0f3@.posting.google.c om...
> Greetings,
> We are trying to set up a set of "Leading Practices" for our
> developers, as well as ourselves, and hope some gentle reader can
> recommend some documentation in favor of what appears to be the right
> position to take.
> We do not allow third party applications to run on our SQL Servers. We
> want to include DTS Packages under the definition of third party
> applications, insisting instead that the developers save their
> packages as COM Formatted files into their source code control systems
> and run them from their app servers. The devlopers would like to hear
> this from someone besides ourselves.
> While strong recomendations to remove guest access to MSDB altogether
> abound, I have been unable to find a straight forward discussion of
> the advantages of structured file storage and app server off load of
> DTS packages.
> Can anyone suggest any articles, white papers, rants, etc attempting
> to formulate a solution to the benefits of taking msdb away from
> guest, with the advantages of running DTS from an App server or
> workstation platform, with the packages protected in source code
> control?
> Thank you
> John Pollins
> jpollins @. eqt . com|||Thanks Dan.

I'm glad that what we are trying to convince them to do makes sense to
you. I am despairing of finding someone who has sat down and come out
and said it. I suppose I could write it up under a pen name and then
quote myself liberally <g
JIV