This article describes how a piece of database code can be moved to production, painlessly and quickly, through the necessary testing environments and then via staging to releasing the database code into the production environment. The more of the process of release that you can automate, the smoother the process becomes. The smoother the process becomes, the faster it will be and the need is to be able to get code into production as fast as possible while still protecting the production environment. The release process outlined here will make that possible.

The Release process aims to create a speedy method for getting code out of the development and into production while, at the same time, also attempting to protect the production environment to ensure uptime and data integrity. To reconcile these conflicting aims, Database Lifecycle Management recommends that you must stick to the Release process tightly, as its defined, and bring as much automation to bear as you possibly can on that process. Automation is the key to providing speed and protection for your software releases.

Governance, Delivery and Release management.

We’ll start by defining just a few terms that we’ll use.

The successful Delivery process reaches the point where the new application, the new project, the new piece of functionality, is ready to be delivered to Operations, to put it into the hands of the business people to help meet a business need. A Release consists of a series of Deployments of code after the development process has determined that this code is potentially ready for production use. A deployment consists of the steps required to support this process by getting new code into the environments necessary to support the release. To deliver this release out of development and into production via a series of deployments requires governance to define a process that will ensure that it is done swiftly, securely and safely. This is what Release Management is about.

Next, we’ll describe the different delivery environments that your code may need to pass through on the way to production and why you need to have at least some aspect of some of these delivery environments within your process.

Delivery Environments and Their Purpose

There are a few extremely rare circumstances where it is viable to release straight from development into production. Except for those unicorns, the rest of us are required to go through some sort of process prior to releasing our code into production. This means that, at a minimum, we have a development environment as well as our production environment. However, for most businesses, especially mid-sized and larger businesses, even more testing and validation of your code will be required in order to ensure that it meets the specification, is scalable and performs well. This will require other types of delivery test environments prior to release into the production environment.

Test Environments

Let’s first talk about some of the mechanisms that can be used to set up a testing environment and then we’ll talk about the different kinds of testing environments that you might use.

Testing Environment Setup

In a perfect world, testing wouldn’t even be necessary. In a slightly imperfect world, testing would be easy to do and the environment would exactly match your production environment, both in terms of processing power and in the quantity and quality of the data. However, none of that is true in reality. Instead, getting a testing environment setup involves a series of trade-offs that allow you to adequately simulate the production environment while working on smaller machines, frequently with less data that is also different from production.

The basic server setup for a QA environment is required to emulate the functionality of production, but it doesn’t have to emulate its performance. This means that, if you’re running a particular version of SQL Server in production, you should also run the same version in testing. If you have Enterprise-level functionality in production that could be impacted by changes that are a part of this given release, then it’s a good idea to have a testing environment with that same level of functionality, achieved through either installing the Development or Enterprise version of SQL Server (this is not going to be an article on licensing best practices, to ensure your compliance, work with Microsoft). You would be unlikely to ever need to emulate all production functionality in all your various testing environments (we’ll cover the different kinds of environments in the next section), but, in order for the QA/Testing process to assist in protecting your production environment, by providing meaningful tests against a production-like system, the behavior of your production systems will need to be emulated somewhere. Other than that, for most of the testing environments, it doesn’t matter if you’re running virtual or physical boxes. You could be running it all locally or within a cloud environment. The physical set up of disks, memory, CPU, really won’t matter that much in your testing environment, with a couple of exceptions which I’ll outline in the next section. Server setup is easy. Data is the challenge in QA and testing.

For most purposes, the single best data set for testing is your production data. However, there are a large number of reasons why you’re not going to be able to use production data within your testing environment. First, and most important, there are legal ramifications related to the exposure of certain kinds of data to unauthorized people. Next, depending on the functionality being developed and tested, you don’t want production information within the test environment (imagine testing email processing with your production email list). Finally, the size of your production system may preclude having a full copy of the production system in your testing environment. For all these reasons, the data set you’ll be using in testing, can’t be a precise copy of production.

Before we talk about the different mechanisms for setting up testing data, let’s talk about some of the requirements for the data in a production environment. In order to support a repeatable, automated, set of tests, the data must be:

• Small enough that you can quickly reset the QA environment for multiple tests
• Have known data sets with known behaviors for test automation and repeatability
• Represent the data in production accurately in terms of the type of data, its distribution across the database, and its functionality in relation to the code
• Not expose any contractual or legal data in an inappropriate fashion
• Have a structure that mirrors production in order to support testing of the release being deployed

These limiting factors help determine which of the following methods you’re going to use to set up your testing data.

There are a number of permutations and possibilities for creating and maintaining a testing data set, so don’t feel like this list is all-encompassing. This is merely a starting point. Further, you may find that different environments at different times may need to use different versions of these methods or even combinations of these methods. Here’s the list, in order of my preference:

• A backup of a clean copy of production
• A backup of a created data set
• A created data set for import

Let’s detail what each of these entails.

Clean Production Data

This is my preferred mechanism when the production data is not overly large. The process is fairly simple and easy enough to automate.

1. Restore a production backup to a non-production server (possibly, your Staging/Pre-Production server, more on that in the appropriate heading)
2. Run an automated process to mask or modify any sensitive data
3. Shrink the database
4. Backup the database to a location separate from your production backups for use in your testing environments

This approach is very simple to automate. Presumably you have backups of production ready to go and you can get them on a regular basis. How often you would schedule this set up of your QA data is completely dependent on your deployment process needs. That step should be easy to set up. Next, cleaning the data. This can be done through T-SQL, PowerShell, or SSIS. It’s mainly a question of identifying the data that should not be moved out of the production environment and removing it, modifying it, or masking it within this copy of production. This step has to be automated in order to make it repeatable and reliable. You can’t risk accidently releasing production information. The next step, shrinking the database, may not be necessary, but depending on the changes you’ve made as part of the data cleanup, the database probably contains less data in it. Shrinking it will make the process of restoring it to your QA system faster. Finally, you backup the smaller, clean database to a location so that it’s available as part of your QA process.

Backup Created Data

I’m calling it created data, but what I’m referring to is either an exported data set or a generated data set (automatic or manual) that is stored outside of a database. It’s not production data. It’s a different data set that you’ve had to curate or manufacture on your own. Here’s the basic process:

1. Create the data set
2. Build an empty database based on the version currently in production
3. Import the data set to the empty database
4. Backup the database to a location for your testing environments

The hardest part of this process is not the task of creating the data set. The hardest part of this process is maintaining the data set over time. You’ll have to plan for changes to your data set when data structures change in production and as the distribution of production data changes. These will need to be taken into account. The next step of building an empty database should be made possible by pulling from a known state, a defined version, for your production environment using source control and labelling or branching. The next steps should be fairly straight forward. At the end, you have a database backup that is ready for use within any kind of testing environment that can easily be restored at the start of a testing process.

Import Created Data

This is my least favorite approach because it’s not as easy to automate across lots of environments and several deployments with multiple releases. However, depending on your circumstances, it might work better for you than using the backup as I’ve outlined above. The starting point, and some of the pain, is the same: You’ll have to curate or create a data set:

1. Create the data set
2. Build an empty database in your test environment based on the version currently in production
3. Import the data set

While it looks easier than the previous approach, it’s actually more error-prone. Presumably with the previous approach, you’re going to run it once a day or once a week or maybe even less. If it breaks, it breaks once, you fix it, and then you’re good to go with all your testing deployments. Whereas with this process, if it breaks, it stops all deployments to testing until you get the process of data creation fixed again.

It’s much easier to just use a backup of either clean production data, or created data, rather than trying to import the created data each time.

Regardless of the mechanism you use to create your test data, with an environment and test data in hand, you can automate creating your testing environment and have it accurately reflect your production environment.

Types of testing as part of release

Now, the question comes up: ‘What kind of testing are we talking about?’

Testing Environment Functionality

I’m very careful to separate the testing environment from the pre-production or staging environment. The needs and functionality of these two environments are different. I’ll talk about staging in the next section. Your testing environment can serve a very large number of purposes. Each of these purposes might require you to set up a separate test environment, depending on the needs of the process and your environment. This list is also not all-inclusive, but represents the different types of testing that I’ve seen. In the order of its ubiquity

• Quality Assurance (QA)
• Performance Testing
• User Acceptance Testing (UAT)
• Financial Testing
• Integration Testing
• Security Testing
• Load Testing

Let’s briefly run through these and note any exceptional needs they might have.

Quality Assurance (QA)

This is your standard testing environment. It’s the fundamental and most common environment with simple processes where you have a development environment, a testing environment and production. The setup and mechanisms around this are pretty much as already outlined. In smaller shops, this acts as all the different types of testing environments, rolled into one.

Performance Testing

Depending on the system, performance can be important or even vital. When performance becomes vital, it is essential to have a test environment where you can check performance separately from standard functional testing. The main thing about a performance-testing environment is that you do need to worry more about having the server configuration closer to production. It’s not a requirement, but it is something you should consider

User Acceptance Testing

The main reason for separating this type of testing from the QA testing is that it might take a lot longer than your QA cycle would allow for, and it involves exposing a wider range of people to the data. You may be doing QA releases once a day, but if the user acceptance testing isn’t done, the process of resetting the QA environment could disrupt the tests. Other than the need for isolation and thorough data masking, there’s nothing special about this type of testing environment.

Financial Testing

I’ve seen situations where the finance department needed to be able to run multiple tests processes on data. This requirement, like user acceptance testing, extends the testing window. Isolating this environment allows them to get their tests done separately in parallel. In some instances, I have seen this environment having actual, live, production data. You may need to have a completely different security and data access regime if that’s the case.

Integration Testing

If you have a system with many integration points, meaning your application interacts with other applications, your database interacts with other databases, you may need to ensure that you have a set of tests that validates that the changes reflected in the current release do not cause problems. The key issue with this type of testing is that you will need to be able to set up all of the various links between systems as part of your automated build of your QA environment.

Security Testing

Security testing is frequently done as part of a QA process. This helps to ensure that changes won’t compromise the security of the production environment. The challenge here is in adequately emulating the production set-up without causing issues with either the testing environment or violating your production security.

Load Testing

This type of testing is completely different from performance testing. In performance testing, you’re validating whether the new functionality in this release either helps performance or doesn’t hurt performance. With load testing, you’re intentionally finding the point at which the functionality being tested breaks down. The load testing I’ve done is either on an exact mirror of production, or on the production environment prior to releasing it to the business for the first time. Load testing on an environment that doesn’t match production is likely to be a waste of time because the production environment will behave differently.

Pre-Production/Staging

I’ve separated the pre-production or staging environment (which I’ll just call staging from here on out) because the purpose of this environment is very different to that of the QA/Testing environments. Staging is specifically set up in support of a Release Management process to act as the door into your production environment. You might have more than one QA deployment in a day or week. You’ll only have a single, successful, deployment to staging for any given release. The goal of this environment is to successfully test the release in a copy of production, immediately prior to releasing the product to production. That puts different requirements on this system:

• It must be a near match for the production environment
• It must have data and structure as close to production as possible
• It must be possible to reset it automatically

Let’s start with the need for this environment to match production as closely as possible. Because this is supposed to be the point at which a final release is tested prior to a deployment to production, you’ll want this environment to mirror production as closely as possible in order to accurately test the production release. The data and structures also have to be as close as possible. In most cases, I’ve only ever used a restore of the production server, or a cleaned up restore of the production server, similar to what I would do for a QA environment. If the size of your system is such that you can’t have a staging environment, emulating a scaled down version of production just won’t do enough to justify having this as a separate step. Your QA deployments can then cover for staging.

Dealing with Server Level Objects

When you’re defining a release for a database, most of the time the work will involve tables, views and issues of data persistence. However, sometimes, you’ll also be deploying new server functionality, added maintenance or even import/export processes. All of that has to be taken into account when talking about a release. Further, each of the different environments you’re dealing with have a varying set of requirements concerning functionality and even security. Let’s address a few of the issues that are going to have to be taken into account with your releases

Server Settings

For the most part, servers are something you set up once and then leave alone. There’s very little maintenance normally required for a server. However, if you have particular server settings that you use, such as setting maximum memory, it’s a good practice to do two things. First, script out each server setting you manipulate. Don’t rely on the GUI as your mechanism for manipulating servers. This is because you want to always be in a position to automate your server maintenance and your server installations. Scripting all the changes is the best way to take care of that. Next, put these scripts into source control. Further, if, as part of your deployment process, you make different choices on setting up different environments, it’s a good idea to script out all the changes and put that into source control separated for each environment. Then it can be a part of your automation process.

SQL Agent Jobs

DBAs use SQL Server Agent jobs for all manner of tasks, such as running background processes, maintenance tasks, backups, and ETL. Databases of any size will also have Agent jobs that are entirely part of the database functionality. Because of this, the scheduled tasks that are part of the release should be stored in the development VCS rather than be ‘owned’ by Ops and stored in the CMS archive. Although there could be Agent job steps that have little to do with individual databases, many of them are involved with such jabs as involve ETL, replication, Analysis services and Integration Services.

Agent jobs are generally, but by no means always, T-SQL batches. They can even be PowerShell scripts, ActiveX Scripting jobs or executables. SQL Server stores jobs for all databases on the SQL Server instance, all together, in the SQL Server Agent, in MSDB.

It is best to start with these jobs being in source control. It is not easy to unpick the scheduled jobs on a server with more than one database on it to script out the ones that are appropriate to a particular database application

It isn’t a good idea to involve several logically separate databases with the one job unless it is an administrative server job such as backups or maintenance. For application tasks it is one job, one database. Otherwise, what happens when the database versions get ‘out-of-sync’?

Agent jobs often reference scripts in file locations. Different servers may have these in other locations so these need to be parameterized in the build script so that the build process can assign the correct filepath.

It is wise to document the source code of agent jobs along with the name and description of the job which database(s) it is accessing. Use the description to provide special detail, such as a PowerShell script on the server that is called from a PowerShell-based job and which should also be saved.

SSIS tasks that are called by SQL Agent must be in Source Control too, along with batch files that are called from job steps need to be saved as well. Is that PowerShell script executing a file as a script block? (That means that the file must be saved as well) Are server-based executable files involved? Someone needs to ensure all of this is in Source Control and then check they have not changed since the last deployment (The description field of the job step may help).

Just as with server settings, it is possible to use the GUI to set up and maintain SQL Agent Jobs. While you might use the GUI in development of the jobs, each job, its schedule and any and all steps within the job should be scripted out, and those scripts maintained in source control. Every aspect of the database should be treated with the diligence that you treat your code. The ability to get back to a previous version of a SQL Agent Job is only possible if you store them into source control.

Once the Jobs are in source control, you’ll want to automate their deployment through the release process in the same way you will your database deployments. The difference being, there are few, if any, tools on the market that are going to assist in this part of the process. You’ll need to work out your own mechanism for automation of the deployment of SQL Agent Jobs.

Agent Alerts

Agent alerts are generally more relevant to the server than the database, but they are part of the build process. For the application, it is a good practice to set up special alerts for severe database errors. Operations people will have definite opinions about this. Alerts should, for example, be fired on the occurrence of message 825 (tried to read and failed), and a separate alert for each severity 19, 20, 21, 22, 23, 24, and 25. It is also wise to alert on severity levels 10 – 16, indicating faults in the database application code, usually programming errors and input errors. Usually, the end user hears about these before the developers, or Ops people. There will also be various performance alerts, set up in order to alert the operator when a certain performance condition happens.

Alert notifications will change according to the type of installations in order to specify the right recipient. For integration or UA testing, the database developers need to know about it, whereas in production, the production DBA will need to know about these. The build needs to take these alerts from the appropriate place, the development VCS or the operations central CMS archive.

Wherever they are held, we need to script all these alerts and, like jobs, we should use the name to signal which alerts belong to which database.

Server Triggers

Server-scoped triggers track server-wide DDL changes, such as a database creation or drop, and Login attempts to the current server. If specified, the trigger fires whenever the event occurs anywhere in the current server. Their main use is for server security and audit, but an application might use them for security purposes.

LinkedServers

An application might use Linked Servers for ETL, distributed database layering or data transfer. They are sometimes used for archiving old data. We need to install these, with the application, and it makes things neater for the build process if we obey the following rules:

• One linked server, one database
• Keep the source of the linked server in source control
• Ensure that the creation or update is repeatable.

Security and the Release Process

The demands of security will require a high proportion of the work of automating a release through a variety of release environments. The definition of the release itself is made during development, so the main job is to shepherd around a predefined set of changes as defined by the release version in source control. However, each environment is likely to have different security settings. This security must be taken into account as you build and rebuild your environments. It also has to be taken into account when you deploy the code, since it might have different behaviors associated with it. Dealing with the differences in security can be somewhat problematic. There are some options available to help you automate this within SQL Server

• Use of Roles in databases as a method of defining security
• Pre- and Post- Deployment Scripts built into automation engine
• Contained databases

Using a Role is a good way to set up security because you can create a set of security behaviors that can be deployed with your database, regardless of the environment, and then all you have to worry about to give someone more or less access is moving them to a particular Role. It makes the management of security much easier.

Pre- and Post- Deployment scripts are scripts that respectively run ahead of, or after, your deployment. They are a useful mechanism for dealing with security. You can create a T-SQL script that looks at the server name and uses that as the mechanism for choosing to how to set up security.

Contained databases allow you to define the users, roles and their behavior within the database definition and then have that stay within the database throughout the process. The security in this case would travel with the database through backup and restore processes, all without the need to maintain or create a login on the server. This isn’t as universally useful as the other two tips, but it can come in handy.

Methods around Automation of Release

One of the goals when setting up a database deployment methodology should be automation. When you consider the number of deployments that you’re going to be making, automation becomes a necessity. The mechanisms and tools used in automating deployments across environments don’t differ much from the tools used for automating testing or automating a continuous integration process. The primary differences come from needing to take into account the fact that you have multiple different environments, with changing security, differing drives and server architecture. There are other differences that affect how any given script might run from one server to the next.

Scripts and scripting

Database deployments are done through T-SQL scripts. That’s how the manipulation is performed. The choices you make when deploying across environments are affected by the way that your scripts get generated and run. One trick for T-SQL is to set up your scripts to run in SQLCMD mode. This is a mechanism that allows you to do several things that are designed to assist a deployment. First, you can run your scripts through your SQL Server Management Studio (SSMS) query window in the same way that they would be run if you were running them from the command line. Since you’re planning to automate your scripts, probably using release management software or continuous integration management software, the command line usage is assumed. Next, you can add additional commands such as :CONNECT to connect to a particular server as well as other commands to help you in your automation through stuff like where the output of scripts goes, sub-scripts to run, what to do on error, and more. Finally, SQLCMD allows you use :SETVAR which is a way to set variables. This is vital when automating scripts across multiple environments. When the server name may be different, the file locations could change, or even the database name might be different, rather than having to edit each and every script, you want to be able to set variables and then simply modify the values in those variables, which can be set from the command line at execution time, in order to modify the behavior of your T-SQL without ever changing the T-SQL script in any way.

Automation Engines

A lot of the same tools already discussed in the chapter on Continuous Integration can be used when deploying across environments. You just have to take into account the various differences between the environments and make handling them a part of your scripting process. However, one tool set is a little different, release management servers. There are a number of these on the market. Microsoft has a tool called Release Management Server built into its Visual Studio and also into its Team Foundation Services offering. More sophisticated and powerful third party tools such as Octopus Deploy are also available.

These tools offer you two things:

• Enhanced abilities for automating deployments across environments
• The ability to track what has been deployed to each environment

Just to use Octopus as an example, it provides you with the mechanisms for creating multi-step deployments with the facility to mark different steps for inclusion on different release environments and/or different servers. This makes it possible to create a deployment process that takes into account all the various permutations that each environment requires. Further, you can easily set up multiple steps to allow for different types of deployments. All this can be automated through PowerShell within the tool and calls to the tool from PowerShell. This kind of behavior makes it very easy to set up multi-server automated deployments.

Then, the tool will track which deployment has been run on which servers. You can check whether or not a deployment was successful simply by looking at a report. You’ll also be able to track the versions in use across all the different environments since you’re never going to see just one set of changes in flight towards your production systems. It makes it far easier and less error-prone to be able to manage all these various and sundry systems if you know what has been deployed where, or even what has failed to deploy and where.

Protections for Production

The goal of all this is to get a release successfully into your production system. All the automation and all the testing are there to help ensure a deployment to production that doesn’t fail. However, things can still go wrong. You might have unique data in the production system that responds poorly to a change script. You may see drift due to emergency patches or out of stream updates to the servers which can cause a deployment to fail. It’s necessary to build protection for your production servers into the release process. Automating each of these protections as part of your deployment process is straightforward, depending on the protection. There are tradeoffs with each protection system that requires discussion.

Backups

The standard protection for databases on your servers is likely to be backups. Automating a backup before a deployment is a pretty standard practice in most environments already. You just need to be sure you’re taking into account whether or not you’re using differential backups. In which case, you will have to be sure to use COPY_ONLY for this out of process backup.

There are a couple of issues that come up when using backups as your protection mechanism for production. The first is time. It can take quite a while for a full backup to complete. Then, it can take just as long, or longer in some cases, for the restore to be run in the event of a failed deployment. You need to make sure that the time it’s going to take for the restore doesn’t violate any kind of Recovery Time Objective or Service Level Agreement you have to maintain with your business.

Another issue that comes up with backups is when the failure of a deployment is found. Sometimes, it’s found immediately. The deployment script fails and you need to rollback any changes made immediately. Sometimes though, you don’t find the problem until days later. In which case, you can’t simply restore the old database because so much other data has moved on. This is when you’ll need to use a different approach.

Snapshots

Another kind of backup is available, depending on your hardware or your SQL Server instance. Some SAN systems have a mechanism to reliably create a snapshot of a database that can be used for a restore process. If you’re running the Enterprise version of SQL Server you can perform database snapshot. The beauty of these snapshots is that they’re very fast to perform and very fast to recover from. The only shortcoming is the same as with backups. If the problem with the deployment is found later in the process, the snapshot is no longer a good recovery mechanism.

A/B Deployments

Sometimes called Blue/Green Deployments, the A/B Deployment process is straightforward. You have two servers or two databases, one of which is currently online. You deploy all changes to the second database and then, after the deployment is successful, you switch the databases so that the updated one is now online.

This process works extremely well, but the shortcomings are obvious. You’re going to have to have double the storage requirements. If you have a transactional system, you have to have mechanisms in place for coordinating the data migration so that the A and B database/servers are in sync. In my experience, this doesn’t work at all well with Online Transactional Processing (OLTP) systems. I have seen it work very well for reporting systems where the data is refreshed nightly, but no other data changes are made during the day.

Rollback Scripts

A frequent process put in place is to have a script ready that will remove the changes you’ve introduced to the production system. This can be a very effective approach since you can take into account exactly the changes you’re making in order to provide a script that protects existing data. It makes the rollback somewhat safer than either snapshots or backups.

The main problem with the rollback approach is that you’re going to have to spend absolutely as much time developing and testing the rollback as you do developing and testing the deployment scripts. You don’t want the first time that you run a rollback script to be on your production system. That completely violates the process of testing and automation that you’ve put in place. Further, rollback scripts can suffer from the same shortcomings as backups and snapshots when the problem with the deployment is found long after the deployment is completed, making the rollback script invalid. This is especially true if the problem with the deployment is at all isolated. Instead, you’re much better off using the following approach.

Fail Forward

Along with backups and snapshots, my preferred mechanism is to use fail forward, or roll-forward scripts. In short, when you find a problem with a completed deployment, instead of attempting to undo that deployment, simply apply the mechanisms you’ve created for developing new code to quickly test and put into production an immediate fix to the problem.

With this approach, you don’t want to abandon the traditional protections of a backup or a snapshot. Their utility is well tested and well defined. However, the overhead and time involved in creating and maintaining a rollback script is extreme. With all the testing and protection mechanisms you have in place for creating your automated release, it is very rare that you will need to use a rollback script. If you can be confident that you have a plan to get a fix in place rather than try to undo what you’ve done, then you will save considerable time in the development and deployment process.

An Example Release Process

There are many ways in which you can mix and match various tools, techniques, processes, hardware and software in support of a Release process. Any process I define here is unlikely to work perfectly in your environment because of the variety of software development processes, tool-sets or business requirements. However, in order to illustrate how you would bring together all the various tools and techniques outlined in this chapter, I want to illustrate one example of a process flow from development to production. Take this as an example as a starting point for developing your own processes, not as a ironclad definition of the “right way” to do this.

Defining a Release

The release environment that I’m going to work with assumes that we have all our database code in source control alongside the application code and that within development we have a fully functional continuous integration (CI) process in order to validate our code changes prior to release

A release is defined as a set of code that has passed our CI process. If there are any failures in CI, then we cannot release this set of changes. The code is either branched or labeled at the conclusion of the CI process, and the QA team is informed that a release is ready.

QA

When a release is defined as ready, the QA team will decide if they want to deploy that release. If they’re in the middle of testing another release, they may defer implementing this one. Although, I’ve also had QA teams who just did a weekly build of the last good release and always worked from there.

However the QA team decides to take on a release, the process is initiated from Octopus Deploy. The process will restore the QA database from a clean copy of the production database that is created nightly through a separate automation process. The steps within QA are as follows:

1. Restore a clean copy of production database
2. Get the defined release version from source control
3. Deploy the release to the new QA database
4. Run automated scripts that validate whether the deployment is successful
5. Alert the QA team that a successful deployment is ready for testing

If the QA build fails, an alert that there’s something wrong with the latest release must be sent to the development team. Additional testing may be required. In the event of a failure in QA, the previous successful release is defined within Octopus, it will be used to recreate the QA system so that the QA team can continue their work.

Staging

The staging system uses the same process as the QA system. The only real difference is the one additional step. Instead of simply deploying the release, an artifact must be generated. This can be as simple as a T-SQL script or more complicated; executed as a sequence of scripts and commands. The artifact is what is used to perform the staging deployment. If the process is successful, that same artifact is used in production. This approach avoids using tools against the production server that rely on automation to ensure the deployment of the release in production. This is a level of paranoia that has served me well.

Production

Only appropriate aspects of the production deployment are automated. The creation of a database snapshot and the deployment of the artifact generated in Staging are automated, but the execution of this automation process is triggered manually. Validation scripts must also be run in order to ensure that the production deployment was a success. If there is any failure at this point, then the snapshot is used to immediately restore the production instance. This approach does assume downtime during the release.

Database Lifecycle Management

Database Lifecycle Management aims to make all the delivery processes repeatable and reliable. The release process is central to delivery. The first stage is to determine beforehand, as part of the governance process, the details of the release process that is required in order to meet the requirements of the data and application. Once everyone is clear about the tests, checks, alerts, logs and release-gates, then these processes must be automated where appropriate. The release process is one of the most important to get right because it has to do two things that, to a degree, are diametrically opposed. It’s attempting to create a speedy method for getting code out of the development and into production. It’s also attempting to protect the production environment to ensure uptime and data integrity. Just remember, however you define your process, two things are necessary, you must stick to that process without deviating from the way it is defined, and you need to bring as much automation to bear as you possibly can on that process. Automation is the key to providing speed and protection for your software releases.