Moving a script that has a hard-coded server name from test to production

JAlessi

We have a test environment that contains one sql server with a copy of each of our databases  on it.  In production we have multiple sql servers each with different DBs on them, so we do a lot of cross server scripting.

When we code in test (because there is only one server) the DBs have names like TestSRV.DB1.dbo.Table1 or TestSRV.DB2.dbo.Table2.  When these scripts get productionized the "TestSRV" server name needs to change to the actual production server name for the cross-server call.  Is there a way to automate (or simplify) this when productionizing so the developers do not have to go into tested code and make changes for production?

I know we can use find/replace but for large deployments it can be imprecise and sometimes things get missed.

Jon_Kirkwood

Hi @JAlessi

 Thank you for reaching out on the Redgate forums. 

There isn't a specific process that comes to mind for this using our tools, but perhaps the usage of synonyms in your code may be of use. SQL - Synonyms

 If you had two objects 'T1' & 'T2' that you are wanting to reference

On your DEV environment database you could, for example, create the following synonyms:

CREATE SYNONYM [dbo].[T1_SYN] FOR [TEST_DEV].[dbo].[T1]
CREATE SYNONYM [dbo].[T2_SYN] FOR [TEST_DEV].[dbo].[T2]

and on the PROD environment you would create the following:

CREATE SYNONYM [dbo].[T1_SYN] FOR [SERVER1_PROD].[dbo].[T1]
CREATE CYNONYM [dbo].[T2_SYN] FOR [SERVER2_PROD].[dbo].[T2]

 This would allow you to write code in test that can be executed in production without modification. 

SELECT * FROM T1_SYN 

Would run on both systems and query different servers

Outside of this it may be necessary to use a bulk rename process where you would have to define what server is used for each object