Best Practices - SSIS location

Question

Best Practices - SSIS location

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
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!

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

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

frederico_fonseca SSCoach Points: 16164 More actions · Answer 1

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.

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 2

SQL!$@w$0ME

SSChampion

Points: 12349

April 14, 2016 at 6:37 pm

#1872204

Thanks Frederico.

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 3

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

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

SQL!$@w$0ME

SSChampion

Points: 12349

April 14, 2016 at 10:33 pm

#1872214

thanks Tava!

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 6

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)

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 7

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.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 8

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