Dynamically Add Reference to Linked Server

We are creating a "partitioned" database so that in the future when we need to do load balancing the enterprise database solution will already be broken up into parts that can live on separate servers.  So, for example, we are going to have the following databases that, at first, live on the same server:

Users
Accounts
Shows
Content
Units
Schedules

If they eventually live on separate servers then we will need to add the name of the linked server to any query:

ex. Select * from Prod1.Users.dbo.UserMaster

I don't want to have to go back later and update hundreds of stored procedures.  Is there some way to create system wide variables, which at first will be empty strings and later be the name of the Linked Server?

Example

Initially
@UsersLinkedServer = ''

Eventually
@UsersLinkedServer = 'Prod1.'


Every Users stored procedure can access this variable, this variable will only have to be changed in one place, etc.

OR

Can I use a linked server connection for each that recursively points back to itself at first?  If so, is there any major performance hit associated with this?

Thanks
davidcahanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx

you would have to use a table to store those values. But otherwise there is no user defined global variables that persists across sessions and restarts.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davidcahanAuthor Commented:
What about recursively pointing the linked server back to itself at first?
MlandaTCommented:
The idea to use a self-linking linked server works, but am not too sure of the performance implication. I would however speculate that the server will still need to spend a few seconds resolving this linked server so it obviously will not be as good as not using this approach.

Also keep in ming that even with using some form of variable, you will then have to make your queries dynamic, and probably use the EXEC command... which isnt such a good idea because you lose the performance benefit of precompilation of that dynamic sql - though the server will cache commonly executed queries... but am no sure the benefit you gain i.e. "simplifying" future maintenace is really worth the performance costs for queries running "now".

integrated security

EXEC sp_addlinkedserver 
	@server		= 'self', 
	@srvproduct 	= '', 
	@provider 	= 'MSDASQL',
	@datasrc 	= NULL,
	@location 	= NULL,
	@provstr 	= 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;Integrated Security=SSPI;'


sql server security

EXEC sp_addlinkedserver 
	@server		= 'self', 
	@srvproduct 	= '', 
	@provider 	= 'MSDASQL',
	@datasrc 	= NULL,
	@location 	= NULL,
	@provstr 	= 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=mypwd;'

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.