Skip to content

70-451:Linked Servers

2011 April 6
by Shannon Lowder

I’m coming to the end of my posts on identifying which SQL Server components to use to support different business requirements.  I’ve already covered SQL Server Agent, DB mail, Service Broker and Full-Text Search  This time I’m going to go over linked servers.

Link!

Not exactly the link I was talking about!

Linked servers allows your clients to connect to your server, yet have access to data on other servers or in other OLEDB/ODBC data sources.

It’s a lot like using Add/Remove Data Sources back in Windows 98 or XP.  You can set up your connection strings, then you don’t have to go through authentication each time you want access to that data.  With linked Servers, the security concerns are made invisible to the end user trying to query data.

The most common use I’ve had for this component is querying server A from server B.  Many years ago I worked at a company who decided to split up the database workload by moving some of the databases to one server, and the rest to a different server.  This works great 90% of the time.  But as we all know business requirements change.

Elektrostandard LED light bulbI received a request that wanted to summarize data from one database and include that data with results from another server.  Our reporting solution didn’t support that, and it would take programmers time to extend the reporting solution to support it.  In the mean time, linked servers provided us the ability to connect to each server from the other. 

Within just a few minutes I was able to produce the results management required.  It was a few more days until the reporting solution was updated, by then, it wasn’t really needed.  SQL Server had solved the problem alone.

One of the cooler uses of linked server would be to set up a linked server connection from your Microsoft SQL database to a completely different database system.  Oracle, MySQL, IBM DB2.  No matter what the source, if you have a ODBC or OLEDB driver for the data source, you can set up a connection to that server and query it from the SQL Server.

You may have a slightly different syntax in your linked server queries.  For example you can query named ranges in Excel.  You may have to handle your TOP 10 a bit differently when connect to MySQL, just consider that one of the trade-offs before beginning to build a custom solution to allow that kind of cross-server functionality!

How have you used Linked Servers?  What pitfalls would you share with others?  Fill in your thoughts below!

Next time, we’ll cover the last SQL Server Component, Distributed Transaction Coordinator (DTC).  After that, we’ll dive into database design.  If you have any questions about this topic, or any other in preparing for the 70-451, please send those in!  I’m here to help!

No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS