Customer-specific database deployments

A question we often get asked is how to deploy specific variations of a database to different customers. Or how to deploy different static configuration data to each customer, and how to version control, test and deploy this per customer in an automated way.

In the post below I’m going to run through an example scenario on how to achieve customer-specific database deployments using SQL Source Control and DLM Automation with the Team City and Octopus Deploy add-ons.

Aim

  • I have two customers, who we’ll call and A and B
  • I want to deploy a database and web application to both customers
  • For each customer I need to deploy some customized stored procedures
  • For each customer I need to load different static configuration data
  • I want to keep the the web application code identical for both customers, and manage per customer variations through configuration

Version control and environments

I’m version controlling the database in git. I’m using SQL Source Control to version the database structure, and will use the static data feature to version control the static data per customer. I’m version controlling the web application in the same git repository.

Each customer has a Continuous Integration environment and a Production environment. After pushing code to a master git branch, all changes will be deployed to the CI environments and test executed per customer.

Database structure

I’m going to use schemas as the mechanism to separate out the shared ‘base’ parts of the database and the per customer procedures and data. I’ll use the dbo schema to contain all the shared elements, and then one schema per customer for other elements.

  • To Customer A I will deploy schemas: ‘dbo’ and ‘a’
  • To Customer B I will deploy schemas: ‘dbo’ and ‘b’

SQL Source Control filters

Filters are the mechanism that will allow us to separate out the required schemas. I’m going to check two additional filters into the git repository in order to split the code base at the SQL CI level. I want to be able to run my tSQLt tests during the CI process, so I’ll include those schemas in both databases too.

  • Filter Customer A: Include the schemas below
    • dbo
    • tSQLt
    • SQLCop
    • a
  • Filter Customer B: Include the schemas below
    • dbo
    • tSQLt
    • SQLCop
    • b

Note: I’m not going to save these filters as the standard Filters.scpf file in the SQL Source Control Database Scripts folder. I’m going to save them outside that folder and use them during the CI process. I created these by hand, but could have used the filter editor in SQL Compare or SQL Source Control and saved these files.

Filter: include_a.scpf

CI server setup

The Team City CI server will trigger the following steps on a commit. I’m adapting the process described in the Continuous Integration Using Redgate Tools Whitepaper.

  • Build the web application
    • I won’t go into details here, but the application is built using MSBuild and a NuGet package is created
  • Build two databases, one per customer
    • Duplicate my database script folders
      • Create a temp folder for each customer
      • Copy the database scripts folder into each temp folder
      • Copy the appropriate filter folder to each temp folder
    • Prepare the customer specific databases (repeated once per customer)
      • Empty the database scripts folder
      • Run SQL Compare to move the objects that match the customer specific filter to the database scripts folder
      • Run SQL Data Compare to move any static data that is required by the customer specific database in the database scripts folder
      • Create a NuGet package of this customer specific database scripts folder using the SQL CI Team City Plugin
    • Deploy the web application and database to the CI environments using Octopus Deploy and DLM Automation
      • Create a release containing both customer specific databases and the web application NuGet packages
      • Deploy release to Customer A CI environment
      • Deploy release to Customer B CI environment

Note: The critical step we have made by using the filters and SQL Compare and SQL Data Compare to build the database scripts directories is that each package will only contain the database objects and static data that each customer requires.

Duplicate database script folders step

Prepare customer specific database step (example for Customer A)

The packaging step uses the standard Redgate SQL Server Build Runner. The only specific configuration is:

Additional SQL Compare command line switches:

Note: The additional command line options are to make sure that the relevant filters are applied during the CI testing steps, and to not automatically add dependencies to the build. This is specifically to enable the static data scenario described below.

Deploy the web application and databases step

Octopus Deploy setup

Setup in Octopus Deploy is very simple. I’ve set up a single project which has four environments. The project has four deployment steps.

  1. Deploy the Web Application package to Customer A Environments (CI-A, Production-A)
  2. Deploy Database A package to Customer A Environments (CI-A, Production-A)
  3. Deploy the Web Application package to Customer B Environments (CI-B, Production-B)
  4. Deploy Database B package to Customer B Environments (CI-B, Production-B)

Note: If you’re using the same host machine for both CI environments you can use variables in Octopus Deploy to make sure that the database and web applications are deployed with different names for each customer.

Using views and table value functions (TVF) as a proxy to customer specific static data

What if I need the web application to refer to the same table in the shared base ‘dbo’ schema, and see different static configuration data for each customer? Here is one approach.

Create two tables, one in each schema which contain the static data required for each customer. Use the Static Data feature of SQL Source Control to version this data.

Create a view in the shared schema that calls a TVF:

This TVF then looks to see which table exists in the information schema and then returns the contents of that table:

Note: Creating the view is only required if your application needs to use the table/view format of dbo.RSSFeeds not the function format dbo.RSSFeeds(). Of course it’s totally possible to use configuration in the application instead to switch which schema you are looking at. However in this scenario we’re specifically not modifying our application code. For large quantities of static data this approach may not be appropriate, it depends on how this configuration data is then used within your application.