Post reply

Best Practices - SSIS location

  • April 13, 2016 at 11:34 am

    #313287

    Hi Guys,

    What are the best practices to implement SSIS server.

    Application server?

    Existing DB server?

    Dedicated server per environment?

    one dedicated server for all environments?

    Or any other options.

    Please share your thoughts. SSIS is new to our environment. We are looking for the best practices to implement.

    Many thanks!

  • April 14, 2016 at 6:16 pm

    #1872200

    In my opinion it all depends on how big your SSIS process is going to be and if the existing servers have a window where the SSIS jobs can run without affecting the remaining processes running on the server.

    What is really important to do from start is ensure that all packages can run using configurations and have no hardcoding of connection strings or of database names.

    Also avoid using connections that require the package to run on the database server unless a decision is taken to do it for performance. In this particular case I am talking about the "Sql Server destination" on the cases where it is faster than the other available connections.

    Consideration here is that you should be able to run any package against any server/database without having to manualy change the package.

    If you take the above in consideration you can start by having the processes running on an existing server and if at a later stage you need to move to a independent SSIS server you can do it with minimal impact.

  • April 14, 2016 at 6:37 pm

    #1872204

    Thanks Frederico.

  • April 14, 2016 at 9:32 pm

    #1872212

    For our SSIS process we have a dedicated ETL Server per environment (TST/STG/PROD - TST way lower specs), all our packages run on here and perform all the Extract, Transform processes prior to then Loading it directly onto our production DBs.

    We have a ton Packages and all packages will run either once a day, or on a minute / hourly schedule. Due to the nature of our work (global companies) packages run 24 hours a day / 7 days a week - heavily.

    So far no issues with this setup

    edit: Added missing text

  • April 14, 2016 at 10:33 pm

    #1872214

    thanks Tava!

  • April 15, 2016 at 8:39 am

    #1872377

    Tava (4/14/2016)

    For our SSIS process we have a dedicated ETL Server per environment (TST/STG/PROD - TST way lower specs), all our packages run on here and perform all the Extract, Transform processes prior to then Loading it directly onto our production DBs.

    Do you one have dedicated ssis server per application(prod) or one server for all applications(prod)? Thanks

  • April 15, 2016 at 8:07 pm

    #1872544

    SQL!$@w$0ME (4/13/2016)

    Hi Guys,

    What are the best practices to implement SSIS server.

    Application server?

    Existing DB server?

    Dedicated server per environment?

    one dedicated server for all environments?

    Or any other options.

    Please share your thoughts. SSIS is new to our environment. We are looking for the best practices to implement.

    Many thanks!

    What were you using before? And what will you be using SSIS for? ETL? If so, what kinds of files?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 17, 2016 at 6:29 pm

    #1872631

    SQL!$@w$0ME (4/15/2016)

    Tava (4/14/2016)

    For our SSIS process we have a dedicated ETL Server per environment (TST/STG/PROD - TST way lower specs), all our packages run on here and perform all the Extract, Transform processes prior to then Loading it directly onto our production DBs.

    Do you one have dedicated ssis server per application(prod) or one server for all applications(prod)? Thanks

    Only 1 server per environment. We have approx. 7 applications, all are OLTP... with 3 of those being heavily used the other 4 not so much but still being worked.

  • April 18, 2016 at 11:12 am

    #1872844

    This SAP application I'm setting up runs SSIS on one of the app boxes.

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

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