Separating SSIS and SQL Server in Production Environment

  • We have a reasonably large Sales Data database going live in a few weeks and it has a large number of SSIS Packages that load data to it. Some of these packages tend to make the processors a little busy from time to time, although it has not been an issue as they are usually the ones that run over night. A contractor we had here has suggested it is never a good idea to run SSIS and your production database on the same server. I looked around here and can find much information, a lot of it contradictory, and none of it particularly helpful to me yet.

    The first question I have is, how does one determine if it is necessary to separate them?

    If we should separate them, how do we deal with those portions of the SQL jobs that are database specific? I can use configurations in my SSIS packages to connect to the correct database but what about special sql command steps. Should the jobs be run from the DB server or the SSIS server?

    Is there anything available that explains how to actually separate SSIS and DB servers? Again, I've found many mentions of it, but no real information yet.

  • mail.lists (10/26/2011)


    The first question I have is, how does one determine if it is necessary to separate them?

    The same way you decide if any instance needs to be put on another box. It's not playing nicely with memory, CPU, or I/O bottlenecks with the rest of the instances on that server.

    If we should separate them, how do we deal with those portions of the SQL jobs that are database specific? I can use configurations in my SSIS packages to connect to the correct database but what about special sql command steps. Should the jobs be run from the DB server or the SSIS server?

    Once you start doing this you'll find you're going to want to consolidate all your jobs/packages to a single repository. This way you don't have concerns that you missed something when you're looking at the push side of a process and someone pulled on another instance. From a maintenance perspective a dedicated SSIS instance is quite powerful. The only thing we leave as jobs on the primary instances now are maintenance and backup jobs.

    Is there anything available that explains how to actually separate SSIS and DB servers? Again, I've found many mentions of it, but no real information yet.

    Nope, not really... the reason is because if SSIS is running, you have to license the machine for an engine. At that point, give it an engine with a gig or two of room to work with, and let 'er rip. Btw, this is an awesome time to swap over to table stored configurations and keep them on that engine, too.


    - 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

  • mail.lists (10/26/2011)


    A contractor we had here has suggested it is never a good idea to run SSIS and your production database on the same server.

    Anyone who uses always or never needs to be taken with a grain of salt. In this case I completely disagree that you should never... If SSIS doesn't play well on the same box you need to consider either adding resources to the existing box which has a certain cost or adding an entire nother machine and licensing SQL again for it. Either way there are costs and trade offs.

    You can install SSIS without installing the database engine.

    CEWII

  • Elliott Whitlow (10/27/2011)


    You can install SSIS without installing the database engine.

    True, just still need to have equivalent licensing as though you had installed the full engine... At which point, why not have a local SQLAgent. 🙂


    - 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

  • Evil Kraig F (10/27/2011)


    Elliott Whitlow (10/27/2011)


    You can install SSIS without installing the database engine.

    True, just still need to have equivalent licensing as though you had installed the full engine... At which point, why not have a local SQLAgent. 🙂

    I covered that point 2 sentences before..

    CEWII

  • Elliott Whitlow (10/27/2011)


    Evil Kraig F (10/27/2011)


    Elliott Whitlow (10/27/2011)


    You can install SSIS without installing the database engine.

    True, just still need to have equivalent licensing as though you had installed the full engine... At which point, why not have a local SQLAgent. 🙂

    I covered that point 2 sentences before..

    CEWII

    *facepalm* I read it before, I re-read it twice just now, and still almost missed it again. You're right, sorry. You did.


    - 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

  • Its ok man... It happens..

    CEWII

  • I tend to agree with Elliott on this one.

    If you separate SSIS from the SQL Server instance, there are definitely a few trade-offs which in my opinion is not always worth it.

    If you have to move a lot of data for instance, a separate SSIS instance will alleviate (some) processing on the SQL Server but add a lot more network traffic. If your SSIS packages aren't memory and CPU intensive then you are really just moving things around and not gaining much.

    Running SSIS on a separate instance without a really good reason is a bad choice IMHO. As always though...it depends on the environment and requirements.

    Martin.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply