Home Forums SQL Server 2012 SQL 2012 - General SSMS connection to SSIS with packages on remote MSDB server - is that even remotely possible? RE: SSMS connection to SSIS with packages on remote MSDB server - is that even remotely possible?

  • Unlike Jason, I work in a shop where we have a dedicated SSIS box. There are benefits, but they're not readily apparently. However, it's also not a scalable solution like you're looking for.

    Basically there's three reasons we run our SSIS on a separate box (well, vm instance, but same thing to us). First is memory control. SSIS runs outside the server cache and they'll compete. We keep a small engine on the same box simply for configuration controls and SQL Agent but that's it. Second is security. SQL Agent requires a large amount of rediculous control to create test jobs (to test network security for filepaths, doublehops, etc) and share them amongst a team (group ownership isn't allowed for a job yet).

    By splitting the SSIS box off to a separate server we've guarateed any memory pressure on the box is self-generated and won't detract from production data systems and that our job builds even on the dev boxes are well-maintained except for the SSIS sandbox.

    You'll notice I neglected the third reason. That's because it depends on where you work. If your workplace is organized and all SSIS packages are 'pushes' or 'pulls', you're good to go. But when they start to mix and match, or they're communicating both directions, it can get hard to keep track of them. A centralized box for ALL packages relieves the entire discussion of 'was there a package on server 8 that needed this database on server 5 that I'm about to adust the schema on?' 'Nope.' '9?' 'Erm, maybe?'


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA