We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Recursive Linked Servers

Medium Priority
524 Views
Last Modified: 2012-05-11
I am manually partitioning my database according to the Domain model of the business (Users, Accounts, Shows, etc).  

Anyway, at first all these databases will live on the same server.  Eventually, they will be broken out onto their own servers.  I'd like to avoid using dynamic SQL for all my stored procedures and I'd also like to avoid having to go back and recode all the stored procedures in order to add the linked servers.  My idea was to set up recursive linked server connections which will eventually be updated and pointed to the separate DB servers.  

Questions:
1) is this possible?
2) is there a better way to do this?
Comment
Watch Question

What do you mean by "recursive linked servers"?

Author

Commented:
So that I don't have to go back and change the code for a bunch of stored procedures, I want to set up linked servers that point back to themselves.  as an example, I might have the following in my SP:

LinkedServer.Users.dbo.UsersToGroups

at first, "LinkedServer" will point back to itself (point back to the same server that the SP is already on).  Then, once I actually move the DB to a different machine it will point to that SQL Server instance instead.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
links on aliases?
No, use the alias in the 4-part table name.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I don't think I'm following.  can you provide me with an example.  the only aliasing I know of is table name aliasing:  Select * from foo as f


Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
thanks!!

Author

Commented:
I've looked at the link you provided and I've set up an alias but either I'm not using it correctly or it isn't helping to resolve my issue.  Here's what I've done:

Set up an alias called "LinkedServerUsers".   Pointed that alias to the local instance of the my DB Server.  Tried to query from that alias

Select * from LinkedServerUsers.Users.dbo.Users

That obviously doesn't work.....
what error code and message are you getting?

Author

Commented:
It's not allowing me to "connect" to that server.  It's giving me a timeout error.
Hmmm, did you try linking to that aliased server?  

I have dealt with aliasing servers when the alias was actually to a separate server but I have never tried this "recursive" linking. :-/

Author

Commented:
OK, so I got it working.   But, so far, I've only gotten it working when my client is on my laptop and the server I'm aliasing is on our development server.  Now, that doesn't mean anything yet.  Because technically when I use Management Studio to connect to a server that happens to exist on the same machine it should work.

Here's my question, assuming I can get this working, I'm not sure I'm seeing how this helps me.  Talk this through with me.  All the alias does is affect the connection string.  If I try to write a select statement that then attempts to use this server alias (using the 3 dot method) it tells me there is no linked server set up....
Theoretically, you should be able to use the database alias to establish the connection for the linked server.  that should then let you access tables on the linked server.  If you need to change the server being linked to, again theoretically, you should be able to modify the alias instead of unlinking one server and linking another via MS.

However, as I mentioned, I have never really considered trying to use "recursive" linked databases.  I have done pretty much what you seem to be trying to but that was when I was writing Delphi code and I could create multiple database connections (whether they all pointed to one database or each pointed to a separate database).  Within the SQL Server world, though, this sort of planning for dispersed databases seems to be quite unusualy.

I guess I am kind of wondering why you don't just go ahead and break the data outinto separate databases from the start.  There would be no licensing issues.  Even though the data in any given database might be small to almost trivial, in the beginning, having the databases already defined as separate entities would seem to resolve this whole issue.  In other words, if you expect that the database will eventually be (or reasonably soon) need to be separated into a set of logical table groupings, then setting things up in that manner from the beginning should preclude any potential issues that might arise from a later partitioning and any failure to update a reference that might break at an inconvenient time in a most inconvenient manner.

Author

Commented:
Yea, I was starting to think the same thing....
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.