Can database references be replaced during deployment?

a.higgins

My team currently follows an environment deployment process that places our development, test, and Production databases on separate servers.  Within our current loads, we often have scripts that collect information from multiple databases, and merge it into a single target, as follows:

MERGE DataWarehouseTable AS Target
USING
       (
              SELECT Fields
              FROM
                     StagingDatabase.StagingTable
                      INNER JOIN
                     OtherStagingDatabase.StagingTable ON
                           Condition
       ) AS Source

I've been asked to evaluate a scenario where our development and test databases would be co-located on a single server, with a suffix denoting which "environment" a database belongs to, e.g., StagingDatabase_DEV and StagingDatabase_QA.

Under that scenario, on the development database the load script above would need to look like this:

MERGE DataWarehouseTable AS Target
USING
       (
              SELECT Fields
              FROM
                     StagingDatabase_DEV.StagingTable
                      INNER JOIN
                     OtherStagingDatabase_DEV.StagingTable ON
                           Condition
       ) AS Source

OK so far as it goes, but the same code on the QA server couldn't keep the _DEV references, so it would need to look like this:

MERGE DataWarehouseTable AS Target
USING
       (
              SELECT Fields
              FROM
                     StagingDatabase_QA.StagingTable
                      INNER JOIN
                     OtherStagingDatabase_QA.StagingTable ON
                           Condition
       ) AS Source

So, when I use SQL Compare to generate a deployment script, I'd need to

1. Ignore those differences during the Compare operation, and
   
2. Replace the references to _DEV when generating deploy script
   

My question: is this possible?  Or would using the tool result in it seeing differences everywhere, and creating deployment scripts that need to be manually edited after-the-fact to replace database references?

Sergio R

SQL Compare deployment scripts are created dynamically: you specify a source and a target.
You need to pre-create the target database and the name of the target database is largely irrelevant once you compare and deploy (assuming you don't have for example Stored Procedures which use three-part name references)

a.higgins

Hi Sergio: sadly, my question is indeed describing a situation where I have stored procedures using three-part name references.

Sergio R

In that case not, SQL Compare can't replace references inside Stored Procedures.

A workaround that can be used is to use synonyms: this means that you can use an unchanged Stored Procedure regardless of the database, you can then pre-create the synonyms differently on each database and use a SQL Compare Filter to exclude synonyms from the comparison.

a.higgins

Thanks for the information!