Jeff Moden (9/19/2012)
opc.three (9/5/2012)
Jeff Moden (9/5/2012)
Examples of why I'd want to use xp_CmdShell are two fold.
The first is cost. It's not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.
This sounds like a bit of a pre-judgement. Why not?
Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities.
Sounds like you answered your own question, Orlando.
Not quite. I am by no means saying that SSIS and a database engine should never run on the same server Jeff. There is benefit to having a database on the same server where SSIS is running to remove one network hop between your SSIS and the database holding your staging tables. All that was said was that if you needed to scale out your SSIS workload it has become much easier and you do not need a database engine installed on every server where you want to run SSIS.
You never answered my question about where your staging tables reside in relation to your OLTP tables. Using only T-SQL and xp_cmdshell how do you get to a place where you can separate your staging databases from your OTLP databases?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato