This example has been in my list to post on for quite some time now and a recent conversation reminded me to bump it up the list.

The number one benefit of database projects is definitely the compiler, or as I like to call it – The TODO List. The compiler checks the syntax and references of objects to verify they are valid, and supplies you with anything that will cause the project to error when built.  Obviously, there are somethings that need to be configurable or abstracted in the project. Depending on what you are trying to abstract or reference there are different patterns for dealing with them.

Today, we are going to cover a couple different patterns for site specific deployment configurations.

Visual Studio Database projects developed using SQL Server Database Tools (SSDT) gives you quite a bit of deployment flexibility using project variables.  During deployment it uses SQLCMD and thus allows you to use variables in the same way as SQLCMD. Basically during deployment items starting with “$(” are anticipated to be the reference to a SQLCMD variable for replacement.

ANTI PATTERN – Deployment Variables in Code

One of the core benefits of using a database project is that it represents the current view of the code base. However, you can circumvent a primary tenet of this using deployment variables.

Consider that I have a stored procedure that I need to do something specific based on the site I have deployed it to.

The database project will have no problem what was done above. However, now we will have a different stored procedure definition at every site! This is a support nightmare.

A BETTER WAY – Data Controlled Configurations

We should instead make site specific configurations like this based on data.

Every site will have the same procedure in place using the above pattern. However, a configurable piece of data will control it the functionality. The data can be loaded during deployment using a post deployment script, or be manageable by the user through the tool.

Our standardized tools should have an identical code-set at every site and configurations should be handled using data.

The Exception – DDL

You knew one was coming.

While the custom ETL solution may be different at every site we should still use the above configuration pattern when applicable. This works well in DML (Data Manipulation Language) such as seen in the procedure above. However, the pattern does not work well for DDL (Data Definition Language).

Consider this DDL example.

SQL Server will error on this. In order to have it work you would have to make it dynamic SQL and handle potential SQL injection, as well as more complicated testing.

The solution is to isolate the objects that need to change from site to site. We do this by using synonyms as a layer of abstraction.  Synonyms are a named pointer to a different object. In this way we can have all of the code reference the synonym whose name does not change, but could point to a different server/database/table at each site.

Using the synonym all of the functional code at every site can be the same.

However, the synonym itself will point to the appropriate object at each site.

There are some limitations to the use of synonyms in Visual Studio SQL Server Data Tools (SSDT). Specifically they do not work well with oracle database references, which I have a future post coming on how we handle that problem.

Do you have questions about this technique?

Let’s discuss in the comments below.

Sample Project: VariablePatterns