• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

Recursive Linked Servers

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?
0
davidcahan
Asked:
davidcahan
  • 8
  • 8
3 Solutions
 
8080_DiverCommented:
What do you mean by "recursive linked servers"?
0
 
davidcahanAuthor 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.
0
 
8080_DiverCommented:
I don't think that SQL Server is going to like that concept. ;-)

How about creating some aliases for the servers and, initially, having them just be aliases for the one server.  Then, as you break things out, you can change the aliases as needed.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
davidcahanAuthor Commented:
links on aliases?
0
 
8080_DiverCommented:
No, use the alias in the 4-part table name.
0
 
8080_DiverCommented:
You may need to initially use the alias and not have any links set up.  Then, as you break things out, you will probably need to add the linked server and change the alias.
0
 
davidcahanAuthor 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


0
 
8080_DiverCommented:
Check out this link:
SQL Tips on Database Aliases
0
 
davidcahanAuthor Commented:
thanks!!
0
 
davidcahanAuthor 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.....
0
 
8080_DiverCommented:
what error code and message are you getting?
0
 
davidcahanAuthor Commented:
It's not allowing me to "connect" to that server.  It's giving me a timeout error.
0
 
8080_DiverCommented:
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. :-/

0
 
davidcahanAuthor 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....
0
 
8080_DiverCommented:
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.
0
 
davidcahanAuthor Commented:
Yea, I was starting to think the same thing....
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now