Category: dbops

A new version of dbops (0.4.3) was released, which introduced a few brand-spanking-new configuration and QoL features:

• Reset-DBODefaultSetting – resets all custom configurations made by a user to their original values defined by the module.
• Export-DBOConfig – you can now export configurations from packages and configuration objects into a json file and use it later for deployment.
• Send-DBOMailMessage – sends an informative email after the deployment is done. Supports custom email templates and configurations.
• New-DBOConfig – creates a new configuration object with current defaults. Previously, this functionality was covered by Get-DBOConfig, which was slightly confusing.

Some examples on how to use all the new features:

Hopefully, these changes will help you to fine-tune your deployments better.

Happy Halloween!

DevOps for databases

It’s been a long while since DevOps has become a hot topic, yet every time we talk about DevOps for databases, every solution tend to force user into some kind of framework, which usually has a set of rules binding them to a certain way of writing the scripts, deploying the code, and even following a specific naming convention. All of this only exists to control the flow of the deployment internally, but makes it more of a black box to the end-users: us.

Ever since I tried to deploy a set of scripts using infamous Invoke-SqlCmd (that was a terrible idea, by the way), I felt there was a gap in community-driven Powershell world, a SQL code deployment module. A module, that would be simple to use, enables users to implement Continuous Deployment and Continuous Integration scenarios, and will be absolutely transparent in the ways it works.

Eventually, in my searches, I stumbled upon the DbUp project: a pure .Net cross-platform deployment solution, which, however, required a fair amount of coding prior to starting using it. And more often than not, only .Net developers would risk venturing into the complications of the internal classes, building a deployment solution as a part of their application. The idea behind the project, however, was perfect: a fully customizable deployment library that can be wrapped into any kind of code and be used the way you like it. After some testing, I realized that it was exactly what I needed: an open-source engine, on which I can build a 100% customizable Powershell SQL code deployment module.

Continuous Integration and Continuous Deployment

DbUp performs deployments using so called migrations: sequential transformations of the database schema, that eventually leads to a fully deployed state. The scripts are supposed to be in a single folder and be properly named to maintain the deployment order. To maintain the deployment integrity, DbUp engine uses the database versioning approach, where each script is supposed to be stored inside the schema versions table, which tracks the progress of the deployment and prevents the migrations from being executed twice.

But what if people are re-using same scripts when deploying the database? Or the development process already has a defined naming convention and consists of multiple similarly named scripts and/or folders? I wanted the module to be able to deploy literally any kind of solution, as long as the scripts are simple plain-text SQL files. And not only deploy them, but also enable CI/CD operations for that code!

With that in mind, I came up with an idea of Builds, mimicking the application deployment pipeline: the code is going through a build phase first, where it’s being packed into a deployment package, ensuring the same deployment experience across the enterprise. To enable the databases to follow that same path, the module is using Builds that represent a set of scripts that bring the database to a specific database state after a migration.

Each build consists of one or more scripts deployed in a predefined order; once all the scripts from the first build are deployed, the next-in-line build will be picked up for the deployment. Each script is recorded to a schema versions table inside the deployment database ensuring that the same script would never be executed twice.

Such approach opens doors to CI/CD scenarios, where any following version of the package can be deployed on top of any database state, even though it’s just good old plain-text SQL code behind the scenes. It also enables users to use any file names and any deployment order as much as they need.

Since all previous SQL files are stored inside the package, it is quite easy to use that catalog to add new SQL files to it, as there is now way to distinguish new files in the source code folder, which enables users to leverage CI/CD features. The only catch here is that now the package would have to be stored somewhere so that it could be easily retrieved during next build phase.

DBOps module

The module takes care of all the aspects of the deployment, as well as grants full control over the deployment process. The code is distributed under MIT license and was recently made available on Github:  https://github.com/sqlcollaborative/dbops

To install this module run the following statement to get it directly from PSGallery:

Install-Module dbops

As of the date of this post, the module supports two major RDBMS: SQL Server and Oracle, but since DbUp supported platforms list is huge, it’s only a matter of time (and demand!) until all the other platforms are supported.

Basic help is available on the front page, however, the documentation is still in a work-in-progress state. Same goes for the module itself: as much as its features are now more prominent, its development is far from over and there is a whole set of features planned for future releases, such as: rollbacks, tSQLt integration, sqlcmd/sqlplus scripts parsing, etc.

Command list

Deployment

• Install-DBOSqlScript – script deployments from a set of files/folders
• Install-DBOPackage – deployments of the previously built packages

Package management

• New-DBOPackage – creates a new package from an existing set of scripts
• Get-DBOPackage – returns the package object with a multitude of fine-tuning scenarios
• Add-DBOBuild – adds a new build to the existing package
• Remove-DBOBuild – removes a build from the existing package

Configuration management

• Get-DBOConfig – loads configuration from a package or a configuration file
• Update-DBOConfig – updates configuration of the package
• Get-DBODefaultSetting – get a list of default deployment settings
• Set-DBODefaultSetting – modify default deployment settings

CI/CD pipelining capabilities

• Invoke-DBOPackageCI – integrate recent changes in the code into the package using incremental versioning

Artifact management

• Copy-DBOPackageArtifact – copy a specified version of the package from the repository
• Get-DBOPackageArtifact – retrieve a path to the specified version of the package from the repository
• Publish-DBOPackageArtifact – publish package into the repository

Cross-platform support

• Install-DBOSupportLibrary – download and install 3-rd party libraries for other database types, if needed

Thank you for support

Open-source projects are fueled by many people working towards the same goal. I really appreciate the support of the #dbatools team who made this project possible. Special thanks to:

• @mobileck – for testing and on-point feedback
• @cl – for dbatools groove, mentoring and bright ideas
• @sqldbawithbeard – for Test-Driven Development mentality
• @FredWeinmann – for showing me the ways of PSFramework thus improving multiple aspects of this module