• Flyway
• Database Deployments

Deploying Different Sequence Objects to SQL Server Databases Using Flyway

The high-level solution to this problem is that we use a table to track which surrogate key values should be used in each database. We then use this information to deploy a different configuration for the sequence object to each database.

The Production Scenario

We try to ensure with Flyway that all our deployments are reliable and repeatable. One of the ways we do this is by ensuring that we track deployments, and we know the state of objects in each target database. Our drift check ensures that we don’t deploy to databases in an unknown state, potentially causing a failed deployment.

However, deploying the same changes to every database isn’t always desirable. In this case, a customer has two production databases (A and B, say) that are both used for write workloads. They manage the load balancing outside of SQL Server, but two separate systems are needed to scale-out resources for the number of changes being submitted.

Some changes are written to A, some to B. For example, perhaps orders from US clients go to database A and orders from French clients go to database B. As a part of these changes, they use a sequence object to create a surrogate key for each insert. A separate process merges these changes into a data warehouse for analytics, so we can’t have duplicate OrderIDs. This means each database needs to have a unique OrderID, generated by the sequence. For this customer, they decided to use odd numbers for the US and even numbers for France.

This is not hard to do with the DDL for a sequence, but how do we manage this with a database DevOps deployment pipeline? Let’s see how this can work below. While we will show this working with two targets, this could easily be extended to more than two if the need arises.

Setting Up the Proof of Concept

In this section, we will set up a simple proof of concept for this use case. We will install a single ‘tracking table’ called TargetValue into each database. This is used to determine the configuration of the sequence object that will subsequently be created in that database and used to supply the required sequence of unique surrogate key values. The sequence object supplies the values to a DEFAULT constraint, on the “ID” column of the table into which data is loaded.

The demo uses a Flyway Desktop project, connected to a single development database, sequence_1_dev (plus shadow), and linked to a Git repository. This article will walk you through you through the project setup. We also have two production databases that are the deployment targets, sequence_5a_prod and sequence_5b_prod. All databases are empty, initially:

Build the tracking table

To start, let’s create our tracking table, called TargetValue. It’s a single column, single row table that, in effect, identifies the database on which we’re working and therefore the values that should be generated by that sequence object that will be created in that database.

We will create the table in SSMS, with this code:

Once we execute this script in SSMS, Flyway Desktop will detect the changes and we can save them to the project, on disk.

Next, I’ll generate a migration script for this change. This migration script will contain the base table change, but not the insert, as we are not tracking static data. We don’t do this as the data in each database needs to be slightly different. I won’t show the process of picking our object or generating the script, but here’s our migration script in the Migrations tab. Note there is an Open in editor button.

I also haven’t shown the baseline process here, but if this were an existing database in which we were starting development, we would create a baseline. In the customer’s case, this was a database under active development with Flyway, so they had already created the baseline.

I’ll click the Open in editor button and I get the script in an SSMS window. I’m going to edit the script to add an IF statement to determine if the deployment database is named either sequence_5a_prod or sequence_5b_prod. I could similarly include an ELSE statement, or a specific name for an intermediate environment, like QA. Here’s the edited script (top few lines not shown):

First, this script creates a specific dependency on the names of the production databases, at this point in time. I would usually recommend against this, but this is a one-time script for developers to set things up, and I assume the target databases are known at this time.

Second, this script sets specific values for specific databases, but for intermediate systems, like QA/test systems, they all receive the value 1. This would match those up with the development system(s). This means my development systems (and shadow) will be in sync.

The only code that isn’t tested in this script is the insert of value 2. Normally I don’t like sending code to production that hasn’t been tested, but this is a very simple, one-time execution of a data change. This is worth the low risk to ensure this solution works as intended. It is no different than asking a DBA to execute the insert in each database, but with this method, the data change is logged and tracked.

I’ll save this change and then commit the script to version control. Normally, we’d deploy these changes with an automated pipeline, but to keep things simple in this article, we’ll just execute the migration script manually, on each of our production databases. I’ve added both production databases as targets in Flyway Desktop and I’ll use the Migrations tab to deploy them. In the following screenshot, I’m using the Run Migrate button to execute the Flyway migrate command on the sequence_5a_prod database:

When this is complete, I’ll change targets and migrate the sequence_5b_prod database as well. I get success messages and the script is marked as success in both systems (only one shown below):

If I check in SSMS, I can confirm that the dbo.TargetValue table has been created in each database:

I can also query both and see the data is different.

This gives me the table I need to track which database is which in each system. We can use the data in the dbo.TargetValue table to determine how we want to deploy our sequence, without dependency on knowing the instance or database name.

For simplicity in this example, all the databases are on a single instance, but they could be on completely separate instances, where the database names are the same. In that case, I’d want to ensure that my script was checking the server instance name rather than the database.

Now let’s see how we can deploy different sequence objects to each database.

Create the Sequence Object

A sequence object is one that returns a number each time it is called. The configuration of the sequence determines the sequence of numbers it will generate. The common situation is that the sequence is set to increment by 1 with each call.

For this client, however, there is a need to return different numbers in each production database. In their case, there were only two production databases, so their decision was to return even numbers in one database and odd numbers in another. To return odd numbers, we create a sequence that looks like this:

This increments by 2 and starts at 1, so the values returned with the first 5 calls are: 1, 3, 5, 7, 9. To get even numbers, we use this code:

We want one of these two definitions to be applied to the production target. Again, we won’t really test the code in QA systems for one of these snippets as there can only be one dbo.IDCounter object in any database. However, as with our first data alteration with the migration script, this is low risk. We can test both these snippets of code in development and then choose one to commit to version control. In this case, I’ll choose the first one that produces odd numbers.

We bind this sequence to a table, MyTable, which will use the dbo.IDCounter sequence object to generate surrogate key values for the myid column in this table. Note that we have explicitly named the constraint, which is always the best practice:

On the Schema model tab, we can see that Flyway Desktop has detected these two changes, the creation of the sequence object and the table that uses it, so we can save them to the project:

Once this is done, I’ll select both these objects in the Generate Migrations tab:

We generate the migration script, and as above, we will open this in our editor. Once this is done, we see this script. We will add code to this, but in a slightly different way than we did above.

This generated script, as it stands, will create the same sequence in every database. Instead, we need to create different sequences, per production database, as determined by the value stored in the TargetValue table. If there is a 2 in this table, we deploy a sequence that generates a series of even numbers, starting at 2. Otherwise, we deploy a sequence of odd numbers, starting at 1.

In other words, we want the default behavior to be to deploy the sequence that generates the odd numbers. This makes the system much easier to extend to more than two targets, and it ensures that all intermediate systems (Dev, Test, Staging) use the same “odd number” sequencing.

This system also allows for the possibility that a database might not have the TargetValue table installed, for example due to the data entry script that creates it not deploying correctly. Remember that the data entry script was dependent on the database name, and this can change over time, and even between the time we deployed the TargetValue table and the time we deployed the sequence. I assume there is some testing time with an application here, so I wouldn’t necessarily assume these are deployed close together.

To introduce this behavior, we edit the generated migration script as follows, introducing an IF…ELSE statement:

We could look for a 1 or a 2, but if we were to extend this to more than 2 systems, we only want our development and test systems to keep in sync with one of the test and prod systems. Since we always have a 1, we will keep development in sync with the downstream databases that have a 1. While downstream databases should have a target value, if there is an issue, we still want to deploy the sequence as the create table, as possibly the app won’t work if we fail to deploy an object.

Once we deploy this to both targets successfully, we can open SSMS and check that this works as expected. If I get the values from sequences after inserts, I see this in sequence_5a_prod:

And in sequence_5b_prod:

test sequence object in database A

Success!

Extending this to More Targets

In this article, I’ve shown how I deploy sequences with alternating numbers to two targets. How would I deploy this to 3 or more targets? That’s easy. I’d insert values into each TargetValue table that were sequential. For 3 targets, we’d have the values 1, 2, and 3 in the various databases. Then each sequence would increment by 3. The process to extend to four or more targets would be similar to that for three.

If we took this existing system and needed to add a third, we’d need to deploy a 3 to the third system. Then we’d need to write more complex code that would alter the sequences to change the increment and use RESTART to likely jump a few values. Coordinating deployments here would be important to avoid number collisions. I would likely ensure I had a way to pause sequence generation, perhaps by pausing a portion of the application that causes sequence generation.

This would be a non-trivial change, which would depend on timing, so this would need to be carefully architected in coordination with application developers and the operational staff that need to perform a deployment.

An alternative scheme, although not possible for this customer, could have been to have all such keys allocated and issued from a single, central service. This allows extra databases to be allocated dynamically, although it is more complex to set up.

Summary

In this article, I’ve presented a way to deploy a sequence to multiple targets, altering the code to match the requirements for each target. In this case, we first deploy a table to help us track the target and then we use a migration script with logical branching to determine how to deploy the sequence to each environment.

Flyway Desktop’s ability to track both the schema creation scripts for object level history, and the migration scripts for deployment make this simple and easy to implement. If you have complex deployment issues that need to be resolved, using Flyway and a migrations approach gives you the flexibility and control to stage your deployments and ensure they are executed in order.

What other complex database deployment scenarios do you have? Let us know in the comments below.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Desktop

Flyway Desktop helps you easily and safely version control your database schema and prepare validated deployments

Find out more

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more