April 28, 2011 at 3:21 pm
Is it a good practice that try not to use linked server as possible?
I found there are no linked server setup in our enviroment which I'm new to job.
April 28, 2011 at 8:43 pm
For me it depends on the requirement. There are a few operations which linked servers can manage very effectively. Like creating partioned view where you need to pull data accross multiple servers\Instances.
Also, to some extent pulling data accross hetrogenous data sources(Oracle,Textfiles, Excel etc). Even though we could do the same using DTS\SSIS packages also . but i have found linked server realy fast where i need to pull records accross multiple servers.
But yes it could cause some performance issues as well it depends upon the N/W also whether the server you are pulling data from are on the same LAN or different LAN or you are trying to pull data through WAN . So, sometimes you need to do a tradeoff between your need and your network setup to ensure the solution works well for you.
We should always try to pull as little data as we could using linked server because it is synchronous medium so even a single drop of data packet could cause the entire resultset worthless.
So, it depends...
Like you said you didn't find any linked server in your enviornment. it could be because there is no requirement for it at the moment.
April 28, 2011 at 10:17 pm
Good points sachnam.
Also, it is not best to necessarily 'sell' the linked features to folks who aren't asking for that sort of functionality. Linked servers open up a security risk and a performance risk. Thst is, if it is not configured properly and if the code using it is not carefully written.
It also makes the environment a little more complicated, as well as opening up SOX issues with developers getting into production (via the link), etc.
So unless you have a need that another technology cannot fill, it may be best to not advertise linked servers. Meanwhile, you may want to start learning it so when the day comes, you don't improperly set it up in haste.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 1, 2011 at 8:52 am
It's not a good practice not to use a Linked Server. They work well with heterogeneous data sources. If you have good knowledge of configuring the security, they don't pose any security concerns. Linked Servers are a mature technology.
I have been using them for many years without any issues. However you can use other solutions like DTS\SSIS for the same purpose too. Everything has its own pros and cons.
Thank You,
Best Regards,
SQLBuddy.
May 1, 2011 at 9:33 am
sqlbuddy123 (5/1/2011)
If you have good knowledge of configuring the security
Yep. Exactly my point. A technology which new unfamiliar to a person and involves security should not be implemented in haste, but instead well researched. This is why I recommended he not arbitrarily implement the technology if he doesn't need to, and also why, whether he needs it or not, to begin researching linked servers now so he is ready when/if it is needed at a future date.
Linked servers are quite easy to do a default-like setup, and introduce major security holes.
sqlbuddy123 (5/1/2011)
I have been using them for many years without any issues.
As have I. In fact, I currently have linked servers carefully implemented in several places and the queries carefully written to work well with them (in most cases).
Edit: clarified 'new' to mean 'unfamiliar to the user'
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 2, 2011 at 9:15 am
When we need to use linked servers, we use a script. Generally that script will add the linked server, perform some data manipulation across the linked servers (i.e. copy a table), and then drop the linked server. Also, when adding the linked server, we restrict the login that is used to only access the necessary databases.
May 2, 2011 at 11:05 pm
Linked servers per se are not bad when you want to pull small amounts of data.
Second point is when you are having a explicitly defined transaction in distributed transaction across your sql statements, involving linked servers ( CROSS SERVER LINKED SERVERS). Here MTS( Microsoft Transaction Server) is responsible for maintaining state of transaction. So any orphaned connection will result in requiring a restart of SQL server or MTS or both. Though they have partially corrected this issue in SQL Server 2005 onwards, it still exists. hence avoid Explicitly defined transactions in linked servers.
Third is avoid joins of linked server tables with remote tables. This will cause a server side cursor in remote server.
Undermentioned link will help you how to pull data across linked server.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply