SQL Server database continuous integration workflow COMMIT step – Initially committing a SQL database to a source control repository

Nowadays, it’s quite common that database changes are made several times a day and that a number of developers are making changes against the same database. Due to these often changes, a certain “process” has to be followed in order to avoid any potential problems in the later stages of development (Quality Assurance (QA), Staging, User Acceptance Test (UAT)) and at the end in production. When talking about the Continuous integration (CI) for SQL databases today, it’s often referred to a process of several steps:

  1. The Commit step, in which any new changes are committed to the source control repository and that actually invokes the CI process

  2. The Build step, which is triggered by changes committed to the source control repository, using the latest state of the source control repository to build a database

    For an article on how to trigger the Build step On-commit see How to automatically trigger a SQL Server database continuous integration and/or delivery process on a new source control commit

  3. The Populate step, in which a created database is populated with static data

  4. The Test step, where specified unit tests are executed, and the feedback of success/failure is provided to developers

  5. The Sync step, which will use the tested database to synchronize it with the database in another environment (QA), if all unit tests were successful

  6. The final step is the Document step, where database documentation is created, using the latest changed database version

This step isn’t generally included in Continuous Integration, as it is considered Continuous Delivery and would not be automated as part of the general CI workflow to allow for a manual overview of the QA database.

This is (normally) one-time step in which you will get your database into source control. After this, you won’t need to do this step again, if you plan to commit individual changes to source control with a tool like ApexSQL Source Control.

If you don’t plan to integrate your development with source control, you can simply re-execute the Commit step on demand, nightly etc. to keep your repository up to date. And you can even retain the commit step as the first step in your process, in which changes would be taken from a development database, committed to source control (ostensibly more as an audit than anything else) and then continue with the rest of the CI workflow pipeline.

For our purposes, and other articles on the subject, we’ll generally assume full SQL Source control integration and that the Build step will generally be the first step of any pipeline.

An automated CI workflow can be achieved by using some custom coding (PowerShell project and batch files) or by using a 3rd party solution in combination with batch scripts. These approaches will be explained in the following articles in this series.

In this article, the Commit (initial) step will be described by using two approaches:

  1. PowerShell project and a batch file

  2. 3rd party tool – ApexSQL Source Control

Using a PowerShell project and a batch file

In order to have a sort of “automated” process for committing database changes to a source control repository, the following needs to be created (and edited when needed):

  1. PowerShell project that will script all/desired SQL objects from a database

  2. The batch file that will commit previously scripted SQL objects to the source control repository

Learn more about creating a PowerShell project and a batch file for committing SQL objects to the source control repository from here: How to automate batch SQL object scripting and committing to source control.

Committing database changes with ApexSQL Source Control

ApexSQL Source Control is the add-in that integrates source control directly into SQL Server Management Studio (SSMS). It can show a detailed history of changes, set team policies for check outs and locks, create and apply label from source control, lock objects to prevent overwrites, and much more.

To implement this step, we are going to break it up in 2 different tasks:

  1. Script and commit your database objects to your repository. How to commit database objects to SQL Server database Source control.

  2. Script your database static data to your repository.

For this article, we’ll use ApexSQL Build itself, in the Build step to configure and create our new QA test database

In the next article, the Build step will be described as the next step in a CI workflow.

Useful links:

All articles in this series:

  1. SQL Server database continuous integration workflow COMMIT step – Initially committing a SQL database to a source control repository
  2. SQL Server database continuous integration workflow BUILD step – Building a SQL database from a source control repository
  3. SQL Server database continuous integration workflow POPULATE step – Populating the newly built SQL database with data
  4. SQL Server database continuous integration workflow TEST step – Running SQL unit tests against the changes
  5. SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script
  6. SQL Server database continuous integration workflow DOCUMENT step – Creating database documentation

A complete SQL Server CI/CD solution

Please see the ApexSQL DevOps toolkit for a complete, end to end SQL Sever CI/CD solution including full, open source PowerShell scripts. Learn more

 

Related posts:

  1. How to commit database objects to SQL Server database Source control
  2. SQL Server database continuous integration workflow BUILD step – Building a SQL database from a source control repository
  3. SQL Server database continuous integration workflow POPULATE step – Populating the newly built SQL database with data
  4. SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script
  5. Synchronize SQL database changes between local and remote repositories in distributed source control systems
April 22, 2016