Working with Azure SQL Databases

Azure SQL database is a fully managed cloud based version of SQL Server. DB Version Control supports Azure SQL databases and we will go through how to start versioning your Azure SQL database as well as deploy changes using Azure DevOps.

Versioning Azure SQL databases is mostly the same as versioning an on-premise SQL database - the only differences are feature availability, some SQL statements (e.g. CREATE DATABASE), etc.

Getting started

In this tutorial, we assume that you already have an Azure account and have experience creating Azure SQL databases as well as configuring them.

First, let's create AdventureWorks database on Azure SQL - it is easy to do as there is a possibility to create a new database with a sample AdventureWorks database directly from Azure portal by choosing Use existing data setting with Sample option.

Next, create a new project within DB Version Control - note that we will use an additional Sources directory within AdventureWorksAzure directory as we will be adding additional files required for deployment in a separate directory later.

Use Azure administrator account or a new login that has dbmanager and loginmanager roles in the master database. If using another login (than administrator account), change the owner of the AdventureWorks database:

ALTER AUTHORIZATION ON DATABASE::AdventureWorks to [yourNewLoginName];

Next, add schema objects and static data tables to version control and click Initialize.

Unfortunately, the scripting will fail with a similar error:

This is due to DF_SalesOrderHeader_OrderID constraint under SalesOrderHeader table that references SalesOrderNumber table that does not exist (seems to be a bug with the lightweight version of AdventureWorks database). To continue, drop this constraint and try Initialize again:

ALTER TABLE [SalesLT].[SalesOrderHeader] Drop CONSTRAINT [DF_SalesOrderHeader_OrderID]

You should get Database scripted message.

Next, create a local GIT repository:

And publish this repository (we use github.com in this tutorial):

Great! The database has been scripted into the files that are being versioned with a version control system.

Deploying changes using Azure DevOps (Pipelines)

There are multiple ways to deploy changes (using the command prompt, MSBuild, or PowerShell cmdlet) as part of your application's deployment. In this tutorial, we will use Azure DevOps to deploy changes to the Azure SQL database. We assume that you already have an account in the Azure DevOps portal.

First, let's create a new project in Azure DevOps:

Next, choose where to take our code from:

Because we published to Github, we chose Github option - you will be asked to grant repository access permissions:

Next, choose the correct repository from the list and choose Starter pipeline to continue:

Finally, we can modify the deployment script (YAML file).

Deployment script

The deployment script (azure-pipelines.yml) can be modified directly from the Azure DevOps portal or can simply be modified from our local GIT repository.

In order to use Migration runner to deploy database changes, we need to add Migration runner files to our repository - download Migration runner and extract the files to Deploy/MigrationRunner directory. Commit and push changes.

 

Let's see how to modify azure-pipelines.yml from the Azure DevOps portal.

First, choose the branch (master).

Next, specify the path to azure-pipelines.yml - note that we put this file under Deploy directory.

Finally, add instructions on how to deploy the database as seen below:

Let's review each step in the deployment script.

Trigger - master

This part indicates that deployment should start automatically if there are changes in the master branch.

Pool - windows-latest

The deployment agent should use the latest available virtual machine image with the Windows operating system to download source files and execute deployment steps.

Variables

A list of variables that will be used later in the script:

  • ServerName - a SQL Server name where the database should be created/updated
  • DatabaseName - a SQL database name to be created or updated
  • AdminUser - a username for SQL Server to perform changes to the database
  • AdminPassword - a password for SQL Server user

Note that the credentials could be stored securely using encrypted secrets using pipeline variables.

Steps

There is a single step that executes inline Powershell script (as described in the Migration runner documentation):

  • first, import the module from the Migrations.dll
  • next, execute Get-MigrationRunner with parameters 

Do not forget to save azure-pipelines.yml and commit it to the repository.

That's it - the deployment script has been set up. To verify that everything works as expected, we can manually execute the pipeline by clicking the Run pipeline button.

Making changes

Once the deployment was set up, the database development process could be as simple as:

  • making changes to the AdventureWorks database
  • applying changes using the DB Version Control
  • committing and pushing the scripted files
  • the deployment will start automatically and will deploy changes to the AdventureWorksAzureDeploy database

Summary

In this tutorial, we showed that Azure SQL databases can be version controlled using DB Version Control in a similar way as on-premises databases. Also, Azure DevOps (pipelines) can be used to deploy changes automatically to the Azure SQL databases.

Leave a comment