question

MichaelWalter avatar image
MichaelWalter asked

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.
migrationenvironment
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image Dave_Green ♦ commented ·
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
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MichaelWalter avatar image
MichaelWalter answered
Thanks Dave and Tom. A combination of the suggestions has got me much closer to what I wanted.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.