Feature Flag

You may be asked to make some changes to an application that will require many different user stories. To put it differently, you may be re-factoring an application where that process is expected to take months. At the same time, you know your business may deploy database code every 2 weeks. The issue becomes how you develop T-SQL code where you can confirm the code works in the existing database structure but also make sure that those database changes do not end up in Production before they are ready.

This is a question not only many database developers but also software developers have asked themselves. At the heart of the issue is how I can write database code that can be turned on and off. One method that attempts to handle this is the use of feature flags. There are many ways to implement feature flags. The goal remains the same, to create database code and database objects that can be configured to work in one scenario or another.

When using feature flags, you have the option to enable or disable new functionality at will. This method of managing your database code can allow you to write code, deploy the code to production, and enabled the new functionality at a later time chosen by the business. In addition to being able to determine exactly when to enable new functionality, you also get the added benefit of being able to roll back changes almost instantaneously by updating a single value in a database. There are some foundations you want to have in place before fully embracing feature flags. For instance, you want to be accustomed and in the habit of unit testing your database code. When it comes to feature flags, you will not only need to unit test when the feature flag is enabled, but you will also need to unit test when the feature flag is disabled to confirm that your applications are using the pre-existing database logic.

I would suggest you wait until you have fully implemented source control for your databases before embracing feature flags. One of the reasons for this recommendation is that there is additional effort in managing your feature flags. When you create feature flags for your database objects, you will need to create some additional logic to allow your applications to use the pre-existing T-SQL code or the new database code. This will not only require discipline as you write your T-SQL code, but you will need to have defined processes to determine when to remove the feature flags from your database code. While feature flags will work great for database objects like stored procedures, functions, or views, feature flags are not the solution for everything that you are developing. Some changes such as changes to database columns cannot be toggled on and off. To manage these types of changes to database objects, we will discuss some solutions at the end of this section.

When using feature flags for your T-SQL code, you have a couple options to determine what feature flags are enabled at any given time. There are two main solutions I’ve heard proposed when it comes to feature flags and databases. The first one may be more application code based. That is supplying the feature flag values in a configuration file. The second option is more of a T-SQL-based solution. This solution involves creating a table to store the feature flags in their current status such as enabled or disabled. There are benefits and downsides to each option. Using application code to manage feature flags may be easier to implement and manage. However, it may be more difficult for the database administrators to support these feature flags. If you keep the feature flag values in the database, you will need to be disciplined in managing those feature flags and removing them. It can become very easy to end up with a table that is cluttered with deprecated feature flags. The downside is that only users with access to the database table can see the value associated with each feature flag.

When you deploy your database code, you can deploy the feature flag as disabled. Once you are ready to enable the new functionality, you can set the feature flag to enabled. You will want to define a process on how to determine when it is time to switch entirely to the new functionality. When you are ready to operate entirely on the new functionality, you will want to remove the previous T-SQL code. You will also remove any reference to feature flags as part of this process. The challenge with this method is it can become easy to skip over the process of removing the prior database code. If your T-SQL code is not cleaned up on a regular basis, this can greatly diminish the manageability of your code going forward.

This stored procedure pulls various information about all recipes. In the preceding T-SQL code, the stored procedure does not differentiate between active and inactive recipes. Information about all recipes are returned. I may find out that this stored procedure was only supposed to return active recipes. While I need to update this stored procedure to only return active recipes, there may be a business reason why this code change cannot be enabled as soon as it is deployed.

Enabling this feature flag will cause the stored procedure dbo.GetRecipe to now only return active recipes.

The database code in the stored procedure dbo.GetRecipe has been updated. Before any code changes, this stored procedure returned all recipes. After creating the feature flag, the stored procedure was updated to include logic to either return all recipes or only active recipes depending on the feature flag status. When the changes have been confirmed, you can remove the logic for the feature flag. This will leave the stored procedure in place with only the updated T-SQL code.

Depending on how you manage your branching and merging in source control may determine how frequently you need to deploy database code that is incomplete. You may find yourself in a situation where you are working on developing database code, but it is not complete before your next deployment. It is often easiest to write code in a way that it can be deployed when it is completed. However, with the move toward Agile software development, it has become increasingly important to write T-SQL code in a manner where it can be deployed at any moment in time. This is where the true benefit of feature flags can be realized.

Methodology

Each development environment is different. Before deciding how to deploy T-SQL code, it will help to get a better understanding of how development is handled at your company. You will want to know how many database developers are at your company and how many different development teams use SQL Server. Another factor that you will want to know is how those development teams write and deploy code. Collecting all this information will help you determine the best method to deploy T-SQL code that will work for your environment.

When working with SQL Server, often the risks associated with code issues can be larger than with application code. These risks are compounded when there are multiple individuals accessing the same T-SQL code. If you are in an environment where you are the only database developer, there may be less of a chance of having issues with merge conflicts. If there are multiple database developers or teams that may be writing T-SQL code, there is more of a chance to have more than one person working on the same database code. While some of this can be managed through source control, it is also important to be mindful of how database code is deployed.

There are several different ways that database development teams can manage their workflows. Depending on the methodology that is used can contribute to what type of deployment method should be used. My first job that involved consistently writing database code did not have any strict timelines. The goal was to make changes and deploy them as quickly as possible. This is often because I was creating SSRS reports. Typically, this type of development can be referred to as Kanban. I have also worked in environments where changes to the database were only deployed as part of a full sprint cycle. For these deployments, all user stories in the Sprint were deployed as part of the Sprint. While there are some ways to deploy everything in a Sprint without changing functionality, our code was often not written that way. If your business is trying to move toward being able to deploy changes at any point in time, you want to consider changing how you develop your database code. This change can be a shift in how you think about your solutions and how you write your code. Concepts like using feature flags will help you. Essentially you want to write your T-SQL code in a way that it can be deployed at any point in time and your applications will not break. However, to get to this point requires several fundamental steps. The first of which is knowing how your company develops your T-SQL code. You may want to handle deployments differently if you have many developers working on T-SQL code for a single application than if you have each developer working on a separate application. This will help you determine what method of deployment will work best for you.

Part of determining your deployment method will include understanding the current process you use to deploy your database code. Right now, your business may have scripts that are manually deployed. Depending on your organizational structure, you may have an environment where those scripts can be deployed at any point in time to a specific environment or you may have conditions about what days of the week those T-SQL scripts can be deployed. If there are certain days that you can deploy to various environments, this is known as a gated deployment. You may be using database projects in a version control system. In Chapter 10, the topic of branching and merging was covered. Your company may only use one branch for your database project. All development work by all individuals is done in the same place. On the other hand, your company may utilize branching. This is where your developers use a copy of the main code base and make changes to that copy. Some companies may choose to deploy directly from a branch. This can be particularly helpful if you are not writing your database code to be able to be deployed at any point in time. Other companies particularly those who are writing code to be deployed at any point in time may choose to have all branches merged back to master or the main branch once the work is complete. In this case, all deployments would be from the main branch.

Sometimes how teams manage their workflows is determined by the frequency of their deployments. For many companies, the goal is to be able to deploy frequently. However, that does not mean that every company is ready for that frequency of deployments. For the development teams that are writing code in a more Kanban style, there may be no pattern for when code is deployed to Production. Other teams may have a set cadence or sprint cycle for when they deploy their code. These Sprint cycles can range from weeks to months. If your company is still in the process of determining how often to release code, I would caution against longer Sprint cycles as it often means more changes are being deployed at once. This increases the risk of having issues in your deployment.

You will also want to understand the volume of database changes happening on an average deployment. You may find that on average there are not that many database changes getting deployed every Sprint. If this is the case, you will want to make sure you do not end up having a deployment with a significant number of database changes. When there are multiple changes happening to the database during a single deployment, there is not only a greater risk of having a bug, but there is also the possibility depending on your deployment method that one change to T-SQL code may overwrite another.

Before getting into two of the main methods of deploying database code, there is one additional factor you should consider. While we all want every database deployment to go out and work as expected, there may come a time where you need to undo or roll back one or more database changes from a deployment. I would recommend that if you do not have a rollback strategy in place, now is a good time to start considering a solid rollback strategy. Often when a rollback is needed is not the time that you want to start figuring out how to quickly and effectively roll back your T-SQL code. Having a method to do this repeatedly can significantly help increase the confidence you have in your deployments. Your company may use rollback scripts to streamline when you need to revert to the previous version of database code. If you are using version control, you have the option to revert to a different version of your database code. To build upon that if you are using continuous integration, you may have a prepackaged version of the previous database code that can be deployed against your Production database in a manner of minutes.

This ultimately leads us to consider the deployment methods commonly used for database deployments. One such method consists of bundling all the scripts that will be deployed together. This is considered a migration-based approach; another option is to take everything that is existing in source control and treated as the source of truth for your database. When using this type of method, whatever database you are deploying to will be overwritten to match the database objects that exist in source control. This is commonly called a state-based deployment. The information collected so far about who is developing database code, how those changes are coded and managed, and the frequency of your deployments can help you determine whether a migration or state-based approach is best for you.

Now that we know what the migration-based deployment method is, we can start trying to determine if that is the best method for our deployments. One of the main benefits of using the migration-based approach is that you can control exactly what is deployed to your database. This method of deployment often involves having all the scripts that will be deployed saved in a single location, and these scripts are usually named in a way that allows them to be deployed in a specific order. This can make your deployments easy to manage. You can quickly look at the folder where the scripts are saved and know exactly what is getting deployed.

If you use this deployment method, I recommend that you have a separate folder that keeps track of the rollback scripts that need to be deployed and the order in which these rollback scripts should be deployed. This will not only help if you have to roll back on deployment night, but it should help you quickly find the code that needs to be rolled back if you need to roll back several deployments at once. There are some limitations with the migration-based deployment method. One of the main challenges is if you need to roll back a specific piece of code. Depending on how your source control is managed, it may not be as simple as viewing the history of that database object and restoring a previous version from source control.

Using a migration-based deployment method can be set up manually. The rest of your team and you can write your T-SQL code files and save them in numerical order. This really consists of creating a script in SQL Server Management Studio and saving the file with a specific naming convention. This naming convention would include specifying the deployment order such as prefixing the filename with the step number to indicate the deployment order.

Once I have completed my changes to the database code, I can save this code as a SQL script. I will save this code with the filename 002_20190723_2024_RemoteDates.sql. In order to make sure that my deployment runs smoothly, I can put both files in the same folder. Figure 12-1 shows the two files as they would be displayed in a file folder.

The first script that I created begins with 001. This is the first script in order by filename. This is also the first script that should be run during the next deployment. The second script that I created begins with 002. This will be the second script that will be run during the next deployment. Writing T-SQL scripts and saving them manually is not the only method that can be used as part of migration-based deployments. You also have third-party tools that can help manage this process for you.

There are many proponents of migration-based deployments. One of the main challenges in developing any database code is managing how the code is maintained when multiple developers are working off the same code base. In Chapter 10, I covered branching and merging. This is the main method used to make sure that everyone developing T-SQL code for the database project can work in a manner that limits inconsistencies when multiple developers are working on the same database object. If you are working with a team that is not accustomed to source control, using a migration-based approach may be more logical. A migration-based deployment process is better suited to handle multiple changes to the data in your database. This can be part of a data cleanup or related to re-factoring database objects. Another advantage of using a migration-based deployment method is that it can be easier to pick and choose exactly what database objects will be deployed. If you make frequent changes to your databases, you may end up with many scripts that may increase the amount of time required for deployment.

Due to these factors, there are some scenarios where you may find a migration-based deployment is more effective than a state-based deployment. Smaller development teams may find a migration-based approach easier to use. If you are using source control for your database, you will still need to make sure that your team frequently pulls down the latest version of database code for their development. The same may also be true for environments that have fewer development teams. If your team is not doing frequent deployments during the same day or has a larger maintenance window available for deployments, then the migration-based deployment method may also work well for you.

The one risk you will need to keep in mind when using a migration-based approach involves database changes that happen outside of source control. You may find yourself in a situation where you need to deploy a change immediately to production. Often these changes are deployed to production without having these changes deployed to the lower environments or checked into source control. If you need to deploy a hotfix or a patch and this code does not end up in your source control, you may end up with your environments being out of sync. While future deployments will not overwrite your changes, you may find inconsistent behavior between your environments.

Migration-based deployments are not the only option available when deploying your T-SQL code. Another popular option for database deployments involves using a source for your database schema and updating your target environment to have that same database schema. This is what is known as a state-based approach. Typically, this is used with source control, but that is all not always necessary. The concept is that the target database will end up looking like the source database or source control once the deployment is complete. This method does not require source control, but it is easier to manage through source control.

Whereas each change is saved individually in its own script file when using the migration-based approach, this is not the case for a state-based deployment. In a state-based deployment, you can have many changes to the same database object, but there will only be one set of T-SQL code that is deployed to your target database instance. This single T-SQL script will combine all the changes into one net change. If the database object is in source control or the source database is different from the target, the change script will be run on the target instance. I also like the fact that I know exactly what state the database will be in once a deployment is complete. The database in the target instance should have the same database objects that exist in the source location after the deployment is complete.

Using the same example as the one for the migration-based approach, I will walk through how this is handled as part of the state-based deployment. The first step will involve going into source control and making sure that you have the latest version. Once you have the latest version, you can open the stored procedure dbo.GetRecipe. When you open this SQL script originally, you will see the code as shown in Listing 12-1. You can make the necessary modifications so that the T-SQL code matches Listing 12-6. After you have made these changes, you can check these changes into source control. Suppose someone else will be making the changes to match the T-SQL code in Listing 12-7. This developer will also need to pull down the latest version of source control. They can then make changes to the dbo.GetRecipe stored procedure. These changes can then be merged back into the master branch. When it is time to deploy this code, the source location will have both changes. These changes will be to only show active recipes and to remove the date columns. Instead of deploying these changes one at a time, the stored procedure dbo.GetRecipe will be deployed to the target instance once. This single update to the stored procedure will include both changes.

Working with many developers or many different development teams there may be a benefit from using a state-based deployment method. This is due to the frequency of changes that may be happening in the overall database project. While the state-based migration can work for database projects that have a few or many changes, this deployment method is better at managing frequent changes. When using the state-based approach, you can be confident that any changes in your source location will exist in your target location after the deployment is complete. This also means that if there are many changes made to Production that were not put into source control, then these changes will be overwritten during the next deployment. If your team is performing frequent updates especially throughout the day, you may find the state-based approach takes less time for a deployment. At the very least, this is since there are usually less changes being deployed through the state-based approach when there are frequent deployments.

One of the largest challenges in using the state-based approach has to do with deploying data manipulation changes. The state-based approach is excellent for comparing the overall database schema. However, there are limitations when it comes to changing the data within the database. In most cases, this is done through manual files that may be executed after the deployment. If you are using source control, these can be managed through the pre-deployment and post-deployment scripts. There is only one pre-deployment and one post-deployment script if you are using source control. This can make the process of changing data somewhat more complicated as you will either need to write your T-SQL code to be able to run once and then be ignored or go into source control and frequently change either your pre-deployment or post-deployment script.

Automated Deployment

There are many ways to handle your database deployments. You have various options available to you to help automate these deployments. There are not only different tools you can use to automate your database deployments, there are also different deployment strategies that you can use. Determining what method to use for your database deployments depends on what types of issues you are trying to prevent or resolve. Some of these deployment strategies will depend on what type of T-SQL code is being deployed. Other methods of deploying database code involve deploying your database in a way that can help you catch issues in production before the changes are deployed everywhere.

There are ways to streamline and automate deploying T-SQL code using SQL Server Management Studio, Visual Studio, or PowerShell. If you want to try to automate your database deployments without having your database in source control, you will want to take some additional steps to protect your database. Your T-SQL code should already be written in a way to allow your database code to be run more than once. This may not be the method that you are using now, but you should consider what would happen if someone accidentally tried to deploy the same scripts again. Write your scripts so that they will run successfully no matter how many times they are executed. If you are using source control already, your source control should manage this functionality for you.

When deploying T-SQL code as part of a migration-based deployment, the process may function differently if you are not using source control. For migration-based deployments, you will usually have a set of scripts that need to be run for your deployment. I have found that when you are not using source control, the most complicated step can sometimes be determining exactly what should be deployed. When you are ready to deploy your T-SQL code, ideally the scripts will be saved in the same folder. At this point, you can either manually run all these migration scripts or see what you can do to automate running the scripts. If you choose to run the scripts manually, you will need to open each script and make sure that you are connected to the correct instance of SQL Server. There are other options you can use to improve the consistency and speed of deploying these migration scripts. Using batch files or PowerShell can help you automate these deployments.

If you are using a state-based migration approach and you are not using source control, it can be a little trickier to manage your deployments. The process of having a state-based deployment without using third-party tools consists of using data-tier applications also known as DACPACs . These DACPACs can be generated from a database that already exists and an instance of SQL Server. There is a tool that comes with SQL Server that will help you generate these DACPACs. The tool that you will be using is an executable file named SQLPackage.exe. When using SQL package, you can generate a DACPAC from an existing database. SQL Server has the functionality that allows you to compare this DACPAC to a different database. Depending on how you choose to work with DACPACs, you can either have the target database updated to match the code from the DACPAC or you can create a script file based off the differences between the DACPAC and the target database.

The easiest way to move towards a fully automated deployment is to use third-party tools. However, that is not the only option available to you. If you are interested in migration-based deployments, there is at least one free third-party tool available. This would be DbUp. While this tool may be helpful for managing migration-based deployments, I will not be covering DbUp as part of this book. Be aware that if you are going to use migration-based deployments and source control, you will need an extension or another tool like DbUp.

If your database is in source control and you are using state-based migrations, you have several alternatives you can use to deploy your database changes. You have the option of deploying the changes directly from Visual Studio to your target database instance. However, this does not get you any closer to automating your deployments. Without using third-party tools to automate your deployments, you will need to work on creating PowerShell scripts to create the DACPAC or SQL script file and deploying those files to the target instance.

In the previous section of deployment methods, I went over migration-based and state-based deployments. When deploying changes to the data within a database, you often want to make certain that you cannot accidentally update this data more than once. While we may want to make sure that every script can be written in a way where they could not update the data more than once, there may be times where it is not possible to write a script to prevent this from happening. If this happens, you can use a similar concept to the feature flags. This is where you create a table to record when a data modification script has been run. The first time this script completes, the table can be updated with a value indicating that all records were updated. The script that is run can also check to make sure that this value does not exist in the table prior to running the script. This table can also be checked and populated either as pass or fail for the entire T-SQL script or each individual record can be recorded when the update is complete.

You may also find that there are times where database code is ready to be deployed but the business is not ready to enable the new functionality. Earlier in this chapter, we covered how feature flags can help us control whether applications use the current state or future state of database code. There is a deployment method that can help in these situations. The largest advantage here is that you can deploy database code without having your applications use this new T-SQL code. Depending on the database objects that are changing, this can be relatively easy or difficult to manage.

When you want to deploy T-SQL code changes, but you are not ready for those changes to be released to the Production environment, you can use a deployment method to help you. The deployment method you will want to use is referred to as dark deployment. This method will use a feature flag or a similar concept. You will deploy T-SQL code so that the database code continues to function as it always has. When you are ready to enable the new functionality, you can enable the feature flags. This will switch how the database code works so that it uses the new functionality instead of the original database code.

The beginning state of the application and the database is shown in Figure 12-2.

The next step in a dark deployment is to update the database by deploying the SQL scripts. If you are using feature flags, the feature flags should be disabled. The database in Figure 12-3 has been updated with the new database code.

After the T-SQL code with feature flags has been deployed, you are now ready to deploy the application. Once you deploy the application and enable the feature flag, your application and database will be in the same state as Figure 12-4.

These steps can allow you to deploy your database changes dark.

There are many risks involved with database deployments. These risks can involve T-SQL code or application code that no longer work as expected. There are also risks that the same code may appear to work but does not work as expected. In some cases, this issue may be cosmetic. There are other times that a bug that is introduced into database code may negatively affect the quality of the data that is saved in the database. This can include altering the data in a way that the data is no longer usable. In order to avoid these types of scenarios, there are different deployment methods that can be used to protect the database and the applications.

One potential deployment method is where one set of hardware is swapped out for a set of hardware with the updated software. This type of deployment method is known as blue-green. This can work well for applications, but this can be more difficult when databases are involved. If your application is only using a database to read data, you can use the blue-green method as is. However, since the concept is to replace the code entirely, this does not work well for databases that need to allow for write activity. There is a modified version of the blue-green method that can be used for databases. In this method, you would still have two sets of applications: the original application and the new application.

During the time that users were connecting to the original application, you would deploy any scripts that could be updated and still allow the original application to work as intended. If you were using feature flags, you could deploy those database objects that were using feature flags. Once you were ready to start using the new application code, you could update the stored procedures to start using the feature flags for the new application code. After you were confident that the updates to the applications and the database code were working as intended, you could remove the feature flags. At this point, you would have transitioned entirely over to the new application code, and all feature flags would be removed from the T-SQL code.

The initial state for your blue-green deployment is shown in Figure 12-5.

The application and the database are both unmodified before the deployment. The next step in a blue-green database deployment is to deploy the database changes that will work for either the original or new application. An image of the database having some changes applied is shown in Figure 12-6.

The original application will continue to connect to the modified database. The blue-green deployment method is based on the concept of replacing code not overwriting code. In order to follow this deployment method, you will want to stand up the hardware and software needed for the new application. Figure 12-7 shows the state of the database and the updated application.

With the feature flags in place, both applications will continue to function in the same manner as the original application. Now that the application code is ready, the feature flags can be enabled in the database. The image in Figure 12-8 shows the updated application connecting to the updated database.

The updated application and database are up and running, and the original application has been decommissioned. The new state looks like the image in Figure 12-9.

This is the process that you can use for a blue-green deployment. This deployment method lets you manage how you deploy and enable your new database code.

While impacting the quality of the data is one issue, there are also concerns related to the performance of changes that are made to the database code. In some cases, database objects may be added or modified in a way that causes a severe degradation of performance. Depending on how the applications are configured, this can cause the applications to fail. There are some deployment strategies that can be implemented to help identify potential performance issues before they are visible to the end users.

You may also want to try to test your new code before letting everyone use the new T-SQL code. It is possible to get a general idea of the performance of your T-SQL code by using a separate database where the changes to the T-SQL code have been applied. Due to the nature of SQL Server at this time, you will only be able to test read transactions on this secondary database. When you use this method, you are using a canary deployment method. The concept is that the application will connect to both databases. The second database will only perform read transactions. With additional hardware, you can have most of the transactions sent to the original database and a small portion of the activity sent to the second database. As you gain confidence in the new T-SQL code, you can increase the volume of activity sent to second database.

When designing code that is deployable, you want to start at the very beginning. You must understand how your teams are structured, your development cycle, and when your code is deployed across your environments. The goal is to write maintainable and manageable T-SQL code. You can choose to deploy your T-SQL code using a migration-based approach where you can control exactly what is getting deployed to your environments. You could instead choose to use a state-based deployment method where you control what your database will look like after the deployment. You also have the option to determine how to handle developing code that is not ready to be deployed. Using branching and merging strategies, you can keep the code that is in development separate from the code that will be deployed. There is also the option of using feature flags so that you can deploy code at any point in time and control when the new functionality is enabled. Regardless of the methods you choose, you will want to determine a deployment strategy that can be defined and is repeatable.