Sql-server – How to add linked server SQL Server 2008 R2

A few quick ideas:

The local server initiates a connection to the remote server, runs a query and fetches appropriate rows from the remote server. This all takes time. Worse, if you trace such activity with SQL Profiler, you may find that rows are fetched from the remote server to the local server one at a time, or in very small batches. This is very inefficient, and the more rows it fetches the worse and worse it gets.

If all data is local, none of that happens and the data can be retrieved much more quickly, especially if it is sitting in the data cache.

Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

For very large remote result sets (in terms of data size), you may run into issues where SQL needs to grow data or log files to accommodate the data. 7 seconds probably doesn't qualify, since it probably isn't bringing back much data.

Lastly, if the remote query is to a production system and the local query is to a dev or test system, load on the production server (which does not exist on the dev server) may cause blocking of the remote query. This would also slow down the observed performance of the remote query.

I'm assuming that you are using a "sql server login" for the vendor's server, and you are using that when creating the linked server.

When you use a linked server, a query running on your server connects to the vendor's server. If a firewall blocks your local server from connecting to the vendor's server, your connection attempt will fail.

If connecting directly from your workstation to the vendor's server you go through a different firewall or no firewall, then the connection may succeed.

This scenario matches the behavior you describe. My usual tests would be: 1. Can I ping from my server to the vendor's server? Both by IP and by hostname? ping usually gets through firewalls. 2. Can I connect using SSMS from my server (using an RDP session) to the vendor's server? Both by IP and by hostname?

If you can ping OK but not connect with SSMS, this usually indicates that the ports for SQL Server probably need to be opened on the firewall.

In short, check all of the firewalls involved. That would include any software firewall on your server or on the vendor's server, or any hardware firewall between them.

In these situations, ping and traceroute are your friends. Ping and traceroute may help you locate the IP of the router that doesn't send your packets on to the vendor's server.