2

Is it possible to have cross database queries that span databases on two different 2008 MS SQL Servers?

If so, what configuration, if any, needs to be done in MS SQL? Also, could someone show me an example query?

Scope Creep:
When linking two Servers:

  • Does this couple the servers in any ways that might cause disadvantages outside of the queries that span the linked servers?
  • These servers are geographically dispersed. However, the queries that use this feature will be generally rare. Any particular challenges with this and SQL server from experience?
Improve this question

4 Answers 4

Reset to default
3

This is called a distributed query, and it's quite common. A linked server is the most common, and easiest, way to do what you describe; if that won't work for you, you could try OPENQUERY or OPENROWSET, but you probably won't need to. Others have covered the "how" of creating a linked server, so I won't go into that detail.

Queries that use a linked server can take a performance hit, but performance is not degraded on the server as a whole. Since your servers are geographically dispersed, the performance hit will be larger than if they were on the same LAN; you'll have to try it to see if the performance is acceptable, but assuming the remote server has decent response times, you'll probably be fine.

The most important thing to remember when using a linked server is to keep the result sets being returned from the remote server as focused as possible. SQL Server does not provide an active link between the two servers to do a "live" join between objects on different servers the way it does when everything is on the same server. What it does instead is to break up the distributed query into pieces that can run independently on the remote servers, then bring the results back to the calling (local) server and finish the job. This means that anything - joins, conditions, calculations, etc. - that involves more than one server has to be kept on the local server, and any data that is required to support that has to be brought back. As a simple example, if a 10,000,000 row table on a remote server is joined to a 15 row table on the local server, SQL Server will bring back all 10,000,000 rows from the remote server in order to perform the join! Needless to say, that will cause a significant performance hit, especially over a slower connection. Use estimated execution plans to verify the remote queries; use things like derived columns and CTEs to "explicitly" create atomic queries that can be passed wholesale to the remote server; and if you're seeing a serious issue, don't be afraid to break up your queries into separate queries to force it to return a small result set.

Hope this helps!

Improve this answer
2

Yes, you can, with Linked server.

SELECT * FROM SERVER1.DBO.DB1.TABLE1
Improve this answer
1

Caveat: I'm a dev, not a dba - my view may vary ;p

Config and how: linked servers and 4-part identifiers

But should you is a trickier question; the DBA team in my last place were keen to minimise such "abominations"; I'm sure they had good DBA reasons; as it happens, I agreed from a dev perspective, purely out of maintenance/manageability.

Improve this answer
1

When I create linked servers for my organization I always map all logins ("For a login not defined in the list above, connections will: Not be made" - The first radio button in the linked server security section in the gui).

I have complaints about this but from my view when I explicitly map logins I know who is going from the starting server to the target server. Interestingly, I have never had an auditor ask about linked servers.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .