Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mambo
Starting Member
3 Posts |
 Posted - 2007-09-20 : 11:36:11
|
| We have about 150 SQL servers and basically we're considering the pros and cons of installing SSIS on a central SSIS server - that is responsible for all DTS jobs - as opposed to installing SSIS on the local SQL instance. On the plus side so far: 1./ Central administration, alerting, change management etc2./ Possible performance gain on the local instance not having SSIS installed? On the negative side: 1./ Central point of failure2./ Possibility that it would need to be a clustered...3./ Compatibility issues may mean having to make the central SSIS server 32-bit?4./ Possible performance cost of remote SSIS?5./ With multiple DTS packages running at different times, when would we take the server down for maintenace...?Would appreciate your thoughts. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-20 : 13:15:54
|
i'd put 1./ Central point of failure to the plus side  well if it would be a dedicated server i don't see a real problem.although... i'm not quite understanding this correctly:do you mean install the SSIS visual studio on the server or something else?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2007-09-20 : 17:33:16
|
| I believe he means not installing the SSIS service on any other server other than this server, development would be done locally (am I right?). That seems like a good idea for his scenario, this would make deployment, maintenance and development much easier on his 150 servers scenario. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-20 : 17:43:37
|
| In response to 2 in your negative points, SSIS can be clustered.I don't recommend putting SSIS on the same box as the database engine, especially in a production environment. SSIS is a client tool and should be used on client machines or centralized server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
mambo
Starting Member
3 Posts |
Posted - 2007-09-21 : 09:36:39
|
| I mean a dedicated SQL Server with Integration Sevices installed that would be responsible for handling all packages from 150 SQL instances. The BI development would be done elsewhere on a dev box and then deployed to this central SSIS server when ready to go live...naturally connection etc would need to be updated.Tara, so you've followed the centralized DTS server route after measuring the cost of SSIS on the same box as the database engine? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-21 : 12:42:07
|
| I haven't measured any costs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
mambo
Starting Member
3 Posts |
Posted - 2007-09-25 : 16:53:50
|
| But you've deployed a centralized DTS server with success?! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-25 : 17:10:45
|
| We don't have any SSIS packages in production, so we haven't gotten that far yet. What I've been stating so far is based upon recollection of best practices.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
SSIS Central Server?