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:
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?
@UsersLinkedServer = ''
@UsersLinkedServer = 'Prod1.'
Every Users stored procedure can access this variable, this variable will only have to be changed in one place, etc.
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?