External Linked Servers - SQL Server

[jlegue]

Hi All,

I am doing some research about External Linked Servers and am hoping that someone can point me towards some best practices information and let me know about any gotchas that I should look out for when using this capability in applications.

Thanks in advance,

---Jay

[russell]

linked servers other mssql servers, or something else?
- query syntax different depending on which
- security concerns are different as well
- performance is drastically different

use oledb providers where possible instead of odbc data sources for increased performance. odbc may fail altogether for some providers.

need to carefully consider security context in which remote connection is made. for example, it is easy (though a mistake) to let a low priveleged user become SA on remote linked server.

[jlegue]

Hi Russel,

I am specifically thinking of MS SQL Servers as it is my impression that DTS should be used if one is linking to a different flavour of database (Oracle for example) and I am not all that interested in connecting to Access or any form of document.

Thanks for the information so far and I appreciate any further information you can point me to.

---Jay

[russell]

in that case, primary concern is going to be choosing what security context to connect with in connections tab of linked server properties. want functionality, but also security. are all servers on same network? performance can be an issue if one server is in california and another in texas...

query syntax for object needs to be fully qualified.

in other words, it is common to use shortcuts:
select fields from dbname..table but you cant do this cross-server. becomes select fields from serverName.dbName.ownerName.table

biggest "gotcha" is probably to think about the security and not just take the easy way out and type sa password into the connection properties

[russell]

also make sure u check out linked servers in BOL