x

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.

more ▼

asked Sep 02, 2015 at 02:52 PM in Default

avatar image

MichaelWalter
30 3

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

Sep 02, 2015 at 04:36 PM Dave_Green ♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Sep 02, 2015 at 05:14 PM

avatar image

Tom Staab ♦
14.5k 7 14 20

(comments are locked)
10|1200 characters needed characters left

Thanks Dave and Tom. A combination of the suggestions has got me much closer to what I wanted.

more ▼

answered Sep 03, 2015 at 03:00 PM

avatar image

MichaelWalter
30 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x85
x4

asked: Sep 02, 2015 at 02:52 PM

Seen: 33 times

Last Updated: Sep 03, 2015 at 03:00 PM

Copyright 2016 Redgate Software. Privacy Policy