Changing FQN when migrating databases between environments
Hi I have queries in our database relying on linked servers. In the test environment I therefore have views / queries similar to SELECT name FROM [TESTSERVER\TESTINSTANCE].testdb.dbo.employee I am intending on using the database project from visual studio 2013/2015 & SVN. I'd like to be able to use the schema compare to generate change scripts, but with substitution to SELECT name FROM [LIVESERVER\LIVEINSTANCE].livedb.dbo.employee I've used schema compare quite a lot, but not to make these changes. Would I be along the right lines to assume I can set up variables something like $env = [TESTSERVER\TESTINSTANCE].testdb and then reference this in the project like: SELECT name FROM $env.dbo.employee so than on migration I just change $env=[TESTSERVER\TESTINSTANCE].livedb Many thanks for your help.
You may want to consider using Synonyms, rather than explicitly referencing a server. That way, the code is the same in the environments, just the synonyms are different. That means your testing is not invalidated by changing the code, and you're not as prone to a typo. More details at
https://msdn.microsoft.com/en-us/library/ms187552.aspx
Best Answer
This is precisely why I always recommend functionally-named linked servers rather than having the linked server names match the physical servers. If the linked server name is the same in dev and prod, you won't have this issue. Regarding the use of project variables for this or anything else, you would reference it like this: SELECT name FROM [($env)].dbo.employee; We do that in 1 situation where we have one database name in dev but a different one in integration and production. As Dave suggested, synonyms can also help. I recommend using them with linked servers to make it easier to find all references to the linked server.
Thanks Dave and Tom. A combination of the suggestions has got me much closer to what I wanted.
