locked
Linked server recomended or not RRS feed

  • Question

  • Hi,

     

    I have a requirement for polling data from 4 diff SQL DBs hosted in different physical servers representing data storage for 5 diff applications or software systems. Is it advisable to recomend for linked server approach and fire select quesries across the DBs. Am I going to get chalenged in terms of enterprise architecture security isssues. I am going to only fire select queries against the DBs. I do understand the performance issues can be taken care by fintuning the querying process.

     If my linked server recomendation holds good can some one share recomendatons from Microsoft directly asking to go for linked server approach for such situarions.

    SQL server version am going to use is 2008.

    Please help......


    Thanks & Regards, Hari
    Monday, May 2, 2011 4:33 PM

Answers

  • It is always important to remember that everybody has their own beliefs and prejudices.  If those with greater responsibility do not accept your approach, you need to consider whether it is worth it to keep pushing.  Choose your battles wisely.

    1. Majority classified linked server concept is sort of Legacy, decades back approach for my requirement.

    It is a standard part of the T-SQL language, supporting 4-part names.  Yes, it does have its quirks, which should be improved, but it also works well when its limits are respected.  I like avoiding the dependency on other moving parts, such as SSIS, when possible.  This is (in my opinion) easy to do if you are just sipping some limited set of data. 

    Whether it is backward for your requirement depends both on understanding your full requirement and on how others view the requirements and exposures.

    2. There were comments like no where linked server concept will be allowed within enterprise since the DBs am trying to poll data from belongs to different business centric applications within the enterprise.

    This is a security settings issue.  The same issue will arise for using SSIS or any other approach.  Namely:  Who has rights to use the security context and who does not?  Who knows the passwords?  Who is able to exploit the path?  There is always an exposure of some kind.  A linked server does not imply full access to another server; the security must be granted.  (Of course, if everything runs as sysadmin on every server, then there is a security problem.)

    Plenty of Enterprise systems are using linked servers or other linking techniques.  And plenty of Enterprise servers do not allow or greatly restrict linked servers.  It is both a technology and a philosophy thing.

    3. Linked server establishment between the SQL servers will end up in serious security constraints.

    It is true that there are security constraints on linked servers.  For example, an impersonated account (however it is done) cannot pass through a linked server to another server because this would violate the security context.

    4.  Linked server would require special ports to be opened in compromise with security.

    There is not One-True-Port that is always safe.  Database mirroring and other features also require opening ports.  Choosing the port to open and the firewall rules to be enforced are all part of the process.

     

    Please note that thse are just comments that you asked for.  These points are not arguments that you are either right or wrong.  It is good for you to be seen as the guy who can get the job done, so focus on being that guy whatever the tooling decisions are. 

    RLF


    • Marked as answer by NS_HARI Friday, May 6, 2011 12:01 PM
    Tuesday, May 3, 2011 6:46 PM

All replies

  • It depends, of course, on many things, including how comfortable your programmers and your management are with that technology.  Also, how much data is being moved, the approach used in coding the polling, whether the code uses cross-server joins (don't do that), and so forth.  Some orevious discussions:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=142605

    http://www.sqlservercentral.com/Forums/Topic996743-147-1.aspx

    http://www.straightpathsql.com/archives/2010/07/linked-server-query-running-slow/

    http://dbaspot.com/sqlserver-server/348596-linked-server-vs-ssis-speed-difference-why.html

    With regard to security, a linked server connection can be secured quite easily, but it means determining the security context to be used and then setting it up. If accessing the other server is very sensitive, you will want to lock it down.  (But the same is true of non-linked server security options.)  It also requires your admins to be comfortable with this approach, but preparing your answers in advance may help.

    There are people who love and hate linked servers.  Linked server queries are subject to some behaviours that you need to take into account (as hinted above).  SSIS is also a fine tools, but if your needs are simple enough, it may not be worth the effort of coming up to speed on it.

    FWIW, I use linked servers quite frequently, but I also limit what we do between the servers.  Really sensitive data operations are run under the proper security context for that data.

    RLF

     

    Monday, May 2, 2011 8:37 PM
  • Hi,

     

    Thanks for your valuable suggestion for my queries. I did went a head with the recomendation to my peers but unfortunately the approach did not go well mentioning the following reasons...

    1. Majority classified linked server concept is sort of Legacy, decades back approach for my requirement.

    2. There were comments like no where linked server concept will be allowed within enterprise since the DBs am trying to poll data from belongs to different business centric applications within the enterprise.

    3. Linked server establishment between the SQL servers will end up in serious security constraints.

    4.  Linked server would require special ports to be opened in compromise with security.

    Could you please add your thoughts against the above comments? especially point 2 regarding linked server not allowed at all within enterprise. Your help is going to be very useful for me to finalise the approach for polling data from diff DBs.  Thanks once again

     

     


    Thanks & Regards, Hari
    Tuesday, May 3, 2011 6:37 AM
  • It is always important to remember that everybody has their own beliefs and prejudices.  If those with greater responsibility do not accept your approach, you need to consider whether it is worth it to keep pushing.  Choose your battles wisely.

    1. Majority classified linked server concept is sort of Legacy, decades back approach for my requirement.

    It is a standard part of the T-SQL language, supporting 4-part names.  Yes, it does have its quirks, which should be improved, but it also works well when its limits are respected.  I like avoiding the dependency on other moving parts, such as SSIS, when possible.  This is (in my opinion) easy to do if you are just sipping some limited set of data. 

    Whether it is backward for your requirement depends both on understanding your full requirement and on how others view the requirements and exposures.

    2. There were comments like no where linked server concept will be allowed within enterprise since the DBs am trying to poll data from belongs to different business centric applications within the enterprise.

    This is a security settings issue.  The same issue will arise for using SSIS or any other approach.  Namely:  Who has rights to use the security context and who does not?  Who knows the passwords?  Who is able to exploit the path?  There is always an exposure of some kind.  A linked server does not imply full access to another server; the security must be granted.  (Of course, if everything runs as sysadmin on every server, then there is a security problem.)

    Plenty of Enterprise systems are using linked servers or other linking techniques.  And plenty of Enterprise servers do not allow or greatly restrict linked servers.  It is both a technology and a philosophy thing.

    3. Linked server establishment between the SQL servers will end up in serious security constraints.

    It is true that there are security constraints on linked servers.  For example, an impersonated account (however it is done) cannot pass through a linked server to another server because this would violate the security context.

    4.  Linked server would require special ports to be opened in compromise with security.

    There is not One-True-Port that is always safe.  Database mirroring and other features also require opening ports.  Choosing the port to open and the firewall rules to be enforced are all part of the process.

     

    Please note that thse are just comments that you asked for.  These points are not arguments that you are either right or wrong.  It is good for you to be seen as the guy who can get the job done, so focus on being that guy whatever the tooling decisions are. 

    RLF


    • Marked as answer by NS_HARI Friday, May 6, 2011 12:01 PM
    Tuesday, May 3, 2011 6:46 PM