Effortless deployment of code between environments with synonyms

One typical data warehouse or BI problem is keeping the code dynamic and consistent over multiple environments. Say that you have a BI solution and in said solution you have two databases, Stage and DataModel. Every day you truncate and reload your stage environment and every day you refine and transform your data model with new data from stage (Stage –ETL–> DataModel).

Let’s also say that you move and manipulate data with stored procedures. (Just to make this example a bit better. But the idea is also applicable to a fully SSIS based solution.)

The setup/environment

This is how it looks like with one test and one production server with two databases in each.

  • TestServer
    • StageTest
    • DataModelTest
  • ProdServer
    • Stage
    • DataModel

On the TestServer we have procedures that goes to the StageTestdatabase and ETL’s said data into the tables in DataModelTest. However, when accessing StageTest we need to supply a connection string (since we currently stand in DataModelTest). The SELECT statements could look something like:


This means that if we would develop a procedure with this code on our TestServer, we would need to manually change the database string (StageTest -> Stage) when deploying said procedure to production. This can of course be solved with dynamic SQL but that can be a bit cumbersome to create and/or read if you have a big procedure. However, there is an excellent function for this – SYNONYM. With this function we can create an alias for our table that is the same in both environments but references different tables. For example the creation could look like this:

CREATE SYNONYM dbo.sys1TableA FOR StageTest.sys1.TableA
CREATE SYNONYM dbo.sys1TableA FOR Stage.sys1.TableA

Then by querying dbo.sys1TableA (I prefer this naming structure since I am used to a structure were schemas are used to separate data for different systems) we would get the content of sys1.TableA in both our test and production environment.

You could write something like this and place it in your database that best corresponds with ‘DataModel’ in this example.

The script

Instead of hard coded databases you could use:
  SELECT name FROM sys.databases WHERE name LIKE '%Stage%' 
if you have a good naming structure and want to make it more dynamic. 
  [DatabaseName]         = N'StageTest'
  ,[SourceSchemaName]    = b.name
  ,[SynonymSchemaName]   = N'dbo'
  ,[TableName]           = a.name
  ,FullTableName         = N'StageTest.' 
                           + QUOTENAME(b.name) + '.' 
                           + QUOTENAME(a.name)
  ,[SynonymName]         = N'dbo.' + b.name + a.name
  ,[CreateSynonym]       = N'CREATE SYNONYM dbo.' 
                           + Syn.FullName + N' FOR ' 
                           + '[StageTest].' + QUOTENAME(b.name) + '.' 
                           + QUOTENAME(a.name) + N';'
  ,[DropSynonym]         = N'DROP SYNONYM dbo.' 
                           +  b.name + a.name + ';'
  ,[DoesItExist]         = CASE 
                             WHEN c.name IS NULL 
                             THEN N'N' 
                             ELSE N'Y' 
  StageTest.sys.tables a
  StageTest.sys.schemas b ON (a.schema_id = b.schema_id)
  (VALUES (b.name + a.name)) AS Syn(FullName)
  DataModelTest.sys.synonyms c ON (c.name = Syn.FullName)

Personally I run a statement similar to this daily (I drop and re-creates the target table) to update the active synonym list. You could ask for why I just don’t either creates all synonyms or drop and re-creates all of them at the same time that I deal with the table but the answer is that I just have not got around to it yet.

That was all for this week. But feel free to drop a line about how you use SYNONYMs.



Leave a Reply

Your email address will not be published. Required fields are marked *