How to Deploy a Database Release to Multiple Environments?




Adapting efficiently to the changing dynamics of the corporate world is vital to stay relevant, and competitive. And this is exactly why employers are actively hiring DevOps engineers. If you are in pursuit of becoming a DevOps engineer or you’re already an IT professional looking to expand your skill-set by adding DevOps expertise to your portfolio, you should consider proper DevOps training.

By taking DevOps classes, you will be able to learn all that you need to know about deployment; and rise as a competent engineer in this domain. During the program, you’ll learn about different testing methods, deployment techniques, basic building blocks of DevOps and more. One crucial aspect of the program is the art of deploying data release in multiple environments. This is definitely an important skill that employers expect DevOps engineers to possess and demonstrate when needed.

Let us walk you through the basics on how to deploy a database release to different environments:

Create a Customized Deployment Configuration File for Every Environment

If you’re using Visual Studio 2010, you’ll see that there is a file already created in the database project by the name Database.sqldeployment. This is a single deployment configured file present by default. However, if you open it you’ll see deployment settings and a variety of options such as:

Deployment-Comparison-Collation 

This allows you to choose between source collation and target collation. Source collation is database collation of the project while the target one is related to the destination server. Mostly source collation is used to deploy in a test environment or development.

Deploy-Database-Properties

This gives you the option to apply the properties specified in Database.sqlsettings file.

Always Re-Create

As the name suggests, this option in the settings allows you to recreate a database. It gives you the option to decide whether you want to recreate your target database each time deployment takes place or make small incremental changes to update the target database to the new environment.

Block-Incremental-Deployment

This option allows you to choose whether or not the deployment should automatically stop if changes pose the risk of data loss to your database schema.

Database Back-Up Before Deployment

Creating backup is always good. This option helps you do just that. So set this setting as true to create backup and avoid data loss.

Deployment Execution In Single User Mode

Though you may not have to worry about changing the setting of this option if you’re creating database for development or test environment, you may have to set it to ‘true’ for staging, deployment and production environments. This is much needed to prevent the users from doing any kind of alterations to the database during the deployment.

Don’t Use Alter-Assembly Statements For Updating CLR Types

This is a setting that determines how the SQL server should do CLR (Common Language Runtime) updates to new assembly versions. In most of the scenarios it should be kept false.

However, you can adjust the settings as needed according to your target environment and requirements.

So, now that you know all about the setting options, let’s take a look at how to develop a configuration file that is specific to your environment.

  • Right click the database project in the solution explorer window.
  • Now click on properties.
  • Next, click on the tab that says Deploy, then in the row of deployment configuration file simply click New.
  • Now give the file a name and make sure to save it.
  • On the file that you have created and saved, set the desired deployment properties. Select options and configure settings according to your target environment. Once you’re done, save your file.
  • Now you’ll be able to see the newly created file in the properties folder of the database project.

In VSDBCMD Specify Your Deployment Configured File

When you build a configuration in Visual Studio 2010, the process generates manifest file that is deployment configuration specific. The purpose of this is to give the creator the ability to have full control over the deployment process without the need to use solution configurations. However, to tailor the database deployment to your target environment, use VSDBCMD command line option p:/DeploymentConfigurationFile switch and specify the deployment configuration file full path.

Set Parameters for the VSDBCMD Command in MsBuild (Microsoft Build Engine) project file

It’s important to set parameters for the VSDBCMD command in MsBuild project file to make sure that the VSDBCMD options are defined appropriately to your specific and target environment.

Besides this, it’s also important to store command-line parameters so that project files can be read easily and reused. By storing the command-line parameters users will find it easier to navigate their way through and provide property values in an environment specific project file. They will also find it quite simple to override default values in MsBuild command-line project file.

Please note that, in case if you’re using the split project file approach, then make sure to divide your MsBuild properties and instructions between 2 files as stated below:

The target environment specific settings such as database connection string, deployment configuration filename & target database name should be in the target environment project-file.

However, the VSDBCMD command that is run by MsBuild target along with other universal properties like VSDBCMD executable location should be inside the universal project-file.  

To get a good hold of the concept and master this skill, we highly suggest you to take proper DevOps training. Enroll in a DevOps certification online course today.

About The Author
Tim
Enterprise Account Executive

Tim Dieterich

Tim is an IT Education Consultant who works with SMBs, enterprise, and government organizations. He helps them achieve IT business objectives through comprehensive multi-mode learning. He assists clients and organizations by making recommendations and providing education for Information Technology, Project Management, Process Improvement, Soft Skills, and more.