• SQL Compare
• Database Builds and Deployments
• Database Source Control

Custom Deployments from Source Control using SQL Compare Command line

In my previous article, From Source Control to Database using SQL Compare Command Line, I explained how my team use SQL Compare Pro Command line to automate database deployments for our customers, without having access to the real staging or production databases, which is impossible given that this is a banking database. I demonstrated how, with access only to our development database, contained under TFS Source Control, we used SQL Compare Pro command line to do deployments of the database metadata and code that are used in common by all customers.

However, as well as this shared code, each of our customers must be provided with a unique variant of every software release, to supply them with customized routines that account for their different business and compliance requirements. Every one of our customers require some of these routines and of course this exclusive functionality should always be deployed only to that customer’s production database: it is essential that no customer can see logic that has been written specifically for another customer.

How do we achieve this? Conventional wisdom seems to be that we treat each customer-specific variant for a database version as a separate branch, within each release. However, this can add complexity to the build. With some creativity, we can avoid this and instead create just one branch for each new release, which we can use to maintain and deploy all the shared logic for each of our customers, plus the dedicated logic for an unlimited number of customers.

In this article, I’ll explain how we make all this work, using SQL Compare command line, synonyms and a little ingenuity.

How we store customer specific routines in a single database

So, to get closer to how our development database is constructed, let’s assume that we have Trunk that combines all the logic we have. Let’s assume that we have three customers, Cust1, Cust2, Cust3.

We also have some code, a SQL Stored procedure called loan.calculate_effective_rate, which calculates the actual interest rate paid on a loan over a period, known as the ‘effective rate’, according to some algorithm. It is the same code for all customers, but one day customer Cust1 asks us to change the algorithm for their version, which means we now need to maintain, and deploy, two different versions of the ‘effective rate’ procedure.

First, we need to implement the alternative algorithm for the effective_rate procedure (I’ll use this abbreviated form of its name, from here on), as required by Cust1. We have just one development database, called Trunk, and because the name of each routine, in any one schema, must be unique, it means that we must have a naming strategy to distinguish these variants.

The way we do this is to:

1. Rename the effective_rate procedure to effective_rate_default, creating a default implementation of the calculation for our customers.
2. Create a new procedure called effective_rate_cust1. It should have the same signature as the old one, in terms of the parameters and parameter types. This must be deployed only to the Cust1 organization.

This means that the application code for all customers must now call the effective_rate_default procedure, except for Cust1, which must call effective_rate_cust1. However, we do not want to make any changes to the calling code for any of our customers. After all, the purpose of the procedure has not changed, but we now have more than one implementation of the same action (calculating the loan’s effective rate). Instead, we use synonyms to represent any action that has more than one implementation. In other words, caller code never calls the exact implementation directly, but instead calls the action (the SQL Synonym).

Therefore, in this case, we create and deploy to every customer a SQL Synonym with the name effective_rate that refers to the effective_rate_default procedure. Of course, for Cust1 only, this synonym must reference not the default procedure but the Cust1 variant. However, as I already mentioned, we maintain only one database in source control, and in Trunk (and any release branch) each synonym will always refer only to the default implementation of the related action. So how do we achieve this?

The answer is a 2-phase deployment process. The first phase deploys to every customer the effective_rate_default procedure and the effective_rate synonym that refers to it. The second phase deploys to Cust1 only the effective_rate_cust1 procedure and then drops the effective_rate synonym in the Cust1 database and creates a new one that refers to effective_rate_cust1.

By following this method, based on the use of SQL Synonyms and a 2-phase deployment process for each customer, we can contain as many dedicated routines as we need in one development database, and so still allow us to provide our customers with code that takes into account different business and compliance requirements.

Let’s see how we implement this 2-phase deployment process using SQL Compare command line.

2-phase deployments with SQL Compare

We’ll expand slightly on our simplified deployment example and assume that there are three customers (Cust1, Cust2 and Cust3) who all need bespoke variations of the “effective rate” algorithm.

The first phase of our deployment process generates a single deployment script that we will deliver to and run on every customer database. This will bring all common database routines (i.e. any that don’t have names ending in a customer alias) up to the same version.

The second phase generates zero or more synchronization scripts that are specific to a customer and which create or update only the SQL routines that are specific to that customer, meaning any routines that have names that end with the alias of the customer, in this case, cust1, cust2 or cust3. It will also drop and recreate any synonyms, in this case just the effective_rate synonym, so that each one is always bound to the correct underlying implementation.

To meet these requirements, we execute SQL Compare Command Line once for each synchronization script that we need.

Phase 1: Generating the general deployment script

In the first phase of our deployment process we execute SQL Compare CL just once, passing in all the parameters that instruct it which databases to compare and how, by supplying an XML argfile called “shared.xml“:

"%programfiles(x86)%\Red Gate\SQL Compare 13\sqlcompare" /Argfile:"shared.xml"

I explained the basic contents of this Argfile in my previous article, but the important points are, firstly, that it is comparing two point-in-time versions of the database, the new version and the previous version, directly from their source control locations, and secondly that it includes a reference to its corresponding filter file (shared.scpf). This filter file excludes any customer -specific versions of the schema objects (in this case, any ending in Cust1, Cust2 or Cust3), using a filter expression like this:

Of course, if your customer aliases all follow a standard pattern, like they do in this simplified example, then you could use a more generic filter, such as (@NAME NOT LIKE' %[_]cust[0-9]'). However, all our real customer names are different, and no such pattern matching is possible.

As a result, SQL Compare will generate a SQL synchronization script that will create just the effective_rate_default stored procedure, then drop the effective_rate synonym and then recreate it so that it references the default procedure (CREATE SYNONYM effective_rate FOR effective_rate_default).

We deliver this script to every customer.

Phase 2: Generating the customer-specific deployment scripts

In effect, there are 2 parts to this phase, for each customer:

1. SQL Compare auto-generates a script that will create or modify the required customer-specific routines
2. We ‘reset’ any synonyms in each customer’s database so that they refer to the correct underlying implementation (stored procedure, or function and so on). We do this by ‘injecting’ the required code onto the end of each auto-generated custom script

We execute SQL Compare CL once more for each customer who requires custom code, simply switching the argfile each time to instruct SQL Compare to include only the objects with names that end with that customer’s alias. We store the list of all customer aliases within the development database.

"%programfiles(x86)%\Red Gate\SQL Compare 13\sqlcompare" /Argfile:"cust1.xml"
"%programfiles(x86)%\Red Gate\SQL Compare 13\sqlcompare" /Argfile:"cust2.xml"
"%programfiles(x86)%\Red Gate\SQL Compare 13\sqlcompare" /Argfile:"cust3.xml"

The content of each of these argfiles is almost the same as for the shared.xml file, with the only difference being each customer-specific argfile contains a reference to that customer’s filter file (e.g. Cust1.scpf), which instructs SQL Compare CL to detect only changes specific to that customer, suing an expression such as the following

When the comparison runs, such as for Cust1, SQL Compare will generate a deployment script that will create, modify or drop any *_cust1 objects in the branch of trunk that represents the Cust1’s installed version, so it is synchronized with our very latest version in source control. In this case, it will create the effective_rate_cust1 stored procedure.

However, SQL Compare’s auto-generated deployment script for each customer will not replace the current effective_rate synonym (created by running the general script in phase 1), with the one that references the effective_rate_cust1 stored procedure, because the synonym in Trunk, or in any single branch for each major release (v241, v242 and so on), always refers to the default implementation.

So, each time SQL Compare auto-generates a customer-specific synchronization script, we need to modify it, to ‘reset’ any synonym in the script so that it references the correct customer-specific implementation of the related action, or so that it reverts to the default action if a custom variant is no longer needed.

We cannot use standard SQL Compare post-deployment scripts for this, firstly because the tool doesn’t currently support their use, when comparing directly to a source control location. In any case, because of our simple “one branch per release” strategy, we have no way to dynamically generate a post-deployment script per customer, to alter the synonyms in each script to refer to the correct customer-implementation. The only way to make it work would be to use the more complicated build scheme of “one branch per customer variant per release”, scripting out each branch to a folder and then adding a post deployment script to it that reset the synonyms just for that customer.

However, we prefer to use our simpler source control methods, so how so we do it? When SQL Compare writes a customer-specific synchronization file to our chosen directory (specified by the out parameter in that customer’s XML argfile), we have a homegrown tool that opens it and adds to the end of the auto-generated code a line that executes a stored procedure that we wrote called switch_synonyms_to_customer. This procedure accepts the parameter of the alias of the organization, to which synonyms should be bound, and then runs through all the SQL Synonyms, dropping them one by one and recreating them with the appropriate name of the base object that the synonym references, either the default one or the dedicated routine, if the particular customer requires it.

So, for cust1:

For cust2:

And so on…

Running the deployments for each customer

We supply to every customer the general deployment script for deploying all common objects and, if required, we supply each customer with an additional custom deployment script for their bespoke logic, which will also reset all synonyms correctly. They must always run the general script first, and only when this is complete, run their custom script.

Conclusion

If you are developing a database application for many different customers, the time will soon come that the requirements of your customers will start to diverge and can’t be met by a single deployment. After all, local taxes and laws, as well as different businesses practices, will dictate how certain financial values are calculated by your customers.

SQL Compare command line can do 99% of what is needed in order to deliver all the variants in the logic that are required, only to the customers that require it. It generates one general synchronization script to deploy any changes, made from release to release, required by every customer, and then generates separate synchronization script files, for each customer with ‘special’ requirements.

By using synonyms to represent each required business action and ‘manually’ resetting them at the end of each customer-specific synchronization script, so that they always refer to the correct implementation of this action, we avoid the need to make any changes to the caller code.

With every release it is likely that either some new logic is created for the customer, existing custom logic is updated or is removed. If custom implementation is no longer needed for some action, it is removed, so the customer returns to the default implementation of that action.

 

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more