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 etc
2./ Possible performance gain on the local instance not having SSIS installed?
On the negative side:
1./ Central point of failure
2./ 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.
First, you presented us a root whitout leafs, in other terms what are data transforming/changing with these 150 SQL Servers ?
SSIS server is used to run packages and, let's say you have 150 packages to run, can the central server resolve this workload ?
Depends on business logic i should build a SSIS grid with 10-15 nodes that can run the packages and haave many point of failures (not single).
To the other part, moving data from a SQL Servers network (that is homogenous and i guess it don't need data cleaning/transforming) to another can be made using replication or service broker.
I guess you have to build a DataWarehouse that centralize data from 150 SQL Servers. That is made obviously nightly when the people (OLTP applications) sleep so the SSIS operations can't affect performance.
