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

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.
Tagged:

Answers

  • Jon_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 132 Silver 1

    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

    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.