Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm implementing a new feature which requires data from databases on multiple servers. I just need to union data from all these servers and sort it. The two options that come to mind are:

  1. Use linked servers and write a simple query to union and sort the data which will run from one server and gather data from the others.

  2. Use the application to gather the data from all servers, and send it back to SQL Server to sort (don't want to implement the sort in the application).

We run our servers in active/active clusters in SQL Server 2008 r2. All the databases have the same permissions, if you have access to one database/server, you have permission to them all. This is a public facing application (which requires user login).

What are the risks of using linked servers? Are there any security flaws I should be concerned with? Are there any issues running linked servers in active/active clusters? Would there be any significant performance issues compared to the alternative?

There seems to be a general negative "buzz" about linked servers, but I cannot find anything concrete that would lead me to believe there are any real concerns there.

share|improve this question
    
Future reference it is best to not post questions multiple times. You already have comments going on SO regarding your question, you can simply flag the question for moderator attention and ask they migrate the question over to DBA.SE. stackoverflow.com/questions/16045441/linked-server-risks –  Shawn Melton Apr 17 '13 at 2:51

2 Answers 2

Linked servers can work very well as long as you have thought out the implications:

  1. Security: a key consideration is that if you have linked servers, if one gets compromised they are all at significant risk. Even if you have different credentials for each user different servers (which would stop an attacker getting at other resources if the only attack vector was leaked/discovered/guessed credentials) the link can effectively bypass all that. The link will also bypass protections that are hiding the other databases from the public network, such as a cercumstance where one or more of the servers are not supplying data to a public interface so would not normally be visisble through your firewalls by any means. You might think "well, isn't that same risk an issue with replication?" to which the answer is yes, but replication is between individual application databases and the linked server route could possible compromise other databases on the same server(s) as the link is at server level not DB level (of course you may be able to mitigate this risk by careful control of user access rights, but you at least need to be aware of it in your planning). As a side note on security: if the servers are not on the same site make sure that you use some form of VPN to link them over, rather than making SQL Server available on a public interface.

  2. Bandwidth: If all the servers are in the same DC with nice, fast, unmetered connectivity between themselves then you might not need to worry about this one, but be more careful with more distant connections especially if your users will be able to run ad-hoc queries of some variety. Compression at the VPN link level will help greatly here for most data sets, but be aware that this will be at th eexpense of greater latency which could exaserbate the efficiency issue (see below).

  3. Efficiency: If you are simply pulling chunks of data down the line then this is not a massive issue (but consider locking: see my next point), but as soon as you do anything by way of joins and so forth there are limits to what the query planner can do to optimise your requests. If it needs to make many index seeks that will create very slow-running queries if the servers are not local to each other due to network latency (the same problem is definitely present for local servers too, but to a lesser extent of course), and it may instead use an index scan (trading off bandwidth use to gain latency benefits) eating bandwidth and if it is holding locks (to avoid dirty read issues and so forth) this wil affect other parts of the application too.

  4. Locking/Concurrency: Going off-server will increase the run-time of queries, which will exaserbate locking issues you may not yet know you have and thereby severly reducing your application's concurrency and scalabilty. You need to be very careful if using regular and/or long-running cross server queries that you keep an eye on the locking issue and give planner hints as appropriate.

As long as you have sufficient provisions in place to manage the security and performance issues, I would not see a problem with using linked servers, though there may be better/safermore-reliable/easier-to-secure ways to achieve the saame result.

share|improve this answer

I've experienced the same negative "buzz", but the only problem I've faced with linked servers is the ease with which you can pull large amounts of data across the network. From a DBA point of view this is scary if you have non-DBAs who can do this, even if they promise not to abuse it.

In your case there doesn't seem to be any benefit to writing your own application as this will still need to move the data. It sounds like you have a very simple permissions model, so depending on the environment it might be worth setting up some special permissions so that the link isn't used where it doesn't need to be.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.