We have a new head DBA, and he has decided he does not want us to use linked servers for development moving forward. In addition he would like to replace all existing code that uses linked servers to use SSIS packages instead. This presents a difficulty for the development staff, because rewriting all existing code to use SSIS packages represents a substantial time commitment, hundreds of hours of development and testing due to the volume of code already in place.
So my questions are this:
1) Have linked servers really fallen out of favor in the development community?
2) Do they represent a security risk?
3) Are there performance considerations that make SSIS packages superior to linked servers?
4) If linked servers are problematic, why has the code that uses them been functioning without issue for the last eight years?
5) Are there any functional benefits for using SSIS packages over linked servers that would justify such a massive development effort?
For context, the majority of code the uses linked servers is moving data between in house and vendor databases. For example, in one case it inserts all records that exist in a table in the custom database that don't yet exist in a corresponding table in the vendor database. In another it updates columns in the custom database based on the values in the vendor database. There's just a substantial amount of code spread out between a number of different databases that uses linked servers.
