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.

  • Linked servers certainly have some issues - Mr. LaRock describes some here - thomaslarock.com/2013/05/… and on this site more are described here - dba.stackexchange.com/questions/40184/linked-server-risks. Many of these are certainly performance and security. Whether or not that is true for your scenario will depend on how everything is implemented. Obviously there is a place for Linked servers, but they do have a tendency to be abused. Hopefully that is not your scenario because that would be a big effort. – mskinner Aug 13 '15 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.