April 30, 2007 at 8:20 am
I'm looking for a reality check into the best approach to solving an issue. We currently have a single server that houses many databases. We are undertaking an effort to migrate these databases off onto two different servers. Some of the databases contain confidential data while other do not. So our plans are to place the confidential databases on one server and all the other non-confidential databases on a different server. Here comes where I need the reality check. The issue is we have a couple of applications that need to query data from both the confidential databases and the non-confidential databases. So the application development manager has decided we should not have two different connection strings in an application, but should only have one. He is asking that we create linked server definitions, setup delegation, and create views one server to be use, so the application can connect to a single sql server. Which will work, but is it more complicated and prone for additional problems, verses having two different connection strings? So here is my question, not understanding all the application issues associate with having multiple connection strings. How much extra effort is required to use two connection strings in an application so the application can query one server or other depending on where the data lives? Also would managing a single linked server and a number of views be less work and have less long term maintenance issues, then having two connection strings in an application? If they have a need to join data from both servers then maybe using linked servers makes sense, or does it? Please help me out with my reality check so the best long term decision can be made.
Gregory A. Larsen, MVP
April 30, 2007 at 9:13 am
I would say your application development manager "is right". Make your Non-Confidential server the access point (server) and define there your linked server and views and manage only only connection string. You probably haver done it, and it seems to you to be complicated or difficult, it is not. You will not regret it.
Just make sure your views, sp and functions using data from the cross-referenced server have a prefix so you know where the data is coming from.
It is my daily job to handle my linked servers to Teradata and SQL, made easier thru just one SQL db.
Once you create your Linked Server, make sure to script it, with user/pass to a secure location so you can re-create when necessary. You may one to study the posbility to create it on the fly when needed to access the secured data, since you create the LnkSrv with one's credentials and cannot prompt for login. At least I have not been able to do so.
Good luck.
April 30, 2007 at 11:20 am
Thanks for the input.
We are planning on using Linked Servers, but we do have a requirement to use windows authentication. Therefore we will be setting up deligation. We are probably going with linked-servers since I'm thinking it provides the most flexibility, but does cause addition maintenance and security issues.
Gregory A. Larsen, MVP
May 1, 2007 at 2:01 pm
May 2, 2007 at 3:41 am
They can be slow, depends how you query them.
May 2, 2007 at 9:26 am
I think it is a very poor and short-sighted development decision to go the linked server route, at least for direct data access, manipulation or sproc calls that does not rely on joining/combining data from both servers.
1) It is VERY simple to have two connections in an application and use one of them to access data on one server and the other to access a different server.
2) It is wasteful to reference sql server data through another sql server when you could do the access directly. Say you want 1000 rows of data from the linked confidential server. Your call to the open server will have to be marshalled and sent to the confidential one, the data returned to the open server, then forwarded on to the client. That is two steps that could/should be avoided.
3) Security is actually more complex using a linking scenario.
4) There are a number of limitations and shortcomings to linked servers. See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-cbee8013c995.htm in SQL2K5 BOL. I also believe you cannot specify hints in remote queries either.
5) Distributed transactions carry significantly more overhead than transactions contained within a single server.
You will need a linked-server metaphor for those situations where data must be joined or referenced from both servers.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 2, 2007 at 12:37 pm
The application should be written to look at a config file for however many connections are needed. The overall maintenance with Linked servers is much greater than changing a server name in a file, not to mention opening up security holes where they aren't needed. If the develeopment team is using any of the .Net languages, it is just as easy to join data from multiple sources as one hence the reason for the dataset structure.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply