October 1, 2007 at 9:07 am
When creating a linked server for SERVER1 do you create one linked server to be used by all applications/developers or do you typically create one linked server per application?
I ask because we have about 50 production SQL Servers and only a couple of development servers. If we have a server named SERVER1 and 7 other servers (SERVER2 - SERVER8) require access to this server the question is should we create the 7 linked servers using the name SERVER1 or name the linked server more generically such as giving it an application name? If we use the server name, we can run into security issues because SERVER2 may need to map LoginID_1 to LoginID_10, SERVER3 may need to map LoginID_7 to LoginID_4 and SERVER6 may need to map LogidID_7 to LoginID_22. On the development server we would need to make certain the linked server named SERVER1 has security mapping for all three of these servers. While this will work, it's not a true representation of the production environment.
How do you handle this situation?
Thanks, Dave
October 2, 2007 at 9:24 am
both approaches have their sterengths and weaknesses. On the one side - I've found that it can be best to abstract all of the linked server names, so that you can then "decide" where you are going to keep your data without having to rewrite all of your procedures (akin to Codd's "physical data independence" concept). It then becomes rather easy to move a database from one server to another should the mood strike you.
On the other hand - if you do abstract, you tend to make the names app-specific, so if you use the same data in multiple places, you've now got multiple aliases to update, and multiple things to monitor when communication between two servers isn't doing so well. It also doesn't allow for the connections to be shared (should you have a lot of chatter) across apps (might be a good things, but might not be).
Finally - one thing you DON't want (in prodcution at least) is an alias that might "look" like some other physical server with the same underlying name. In other words (in production) - don't "reuse" a name like SERVER1, if you have a machine called SERVER1 and you're not pulling the data from there: the mental gymnastics required to keep that straight is the last thing you need when you get that frantic call at 3AM that your critical app is logging all sorts of errors that SERVER1 is not available..... In dev - that's clearly an option (like you have).
In short - if you have a difference in security (like you want a different app to use a different security mapping than your default one), then that's a clear incentive to have another linked server "alias". Otherwise - it's a bit of a toss-up in my mind.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply