Deploying to many versions of SQL Server databases using one commit

Reading Time: 5 minutes

In this post I want to demonstrate deploying to many versions of SQL Server databases using one commit. Just to show how the Kevin method works within Azure DevOps.

Within this post you will see a working deployment pipeline which deploys updates to three different types of SQL Server databases. Which are SQL Server 2008, SQL Server 2019 and Azure SQL database. All done using only one commit.

Recap

For those of you who are not aware, in a previous post I came up with a different way to deploy to two versions of SQL Server databases using one commit. Which I called ‘The Kevin Method’. You can read that post in more detail here.

However, as a reminder the Kevin method does the below.

1. First sets all variables for that stage and which agent pool to use.
2. Afterwards it copies all the files in the repository to a subfolder the staging area. Apart from the yaml file.
3. It then runs an inline PowerShell task which replaces the DSP line in the copy of the sqlproj file in the staging area. Setting it to be for an Azure SQL Database instead.
4. It then runs a build against the new sqlproj file with the changed DSP line.
5. Once the build has completed a new artifact is published with only the contents of the ‘bin\release’ folder (release was what I name my configuration).

Because there is an easier way to do this if doing it for SQL Server 2019 and Azure SQL database I thought I would mix it up a bit. By demonstrating how this method works with multiple versions of SQL Server.

Before we get started, just a reminder of how the final pipeline looked before with the two artifacts.

Now let’s push this method a bit further.

SQL Server 2008 compatibility level

I decided to first test creating a new SQL Server database on a SQL Server 2019 instance and setting it to SQL Server 2008 compatibility level.

Afterwards I opened up Azure data Studio and changed the line in the sqlproj file to change the project to be for SQL Server 2008 databases.

<DSP>Microsoft.Data.Tools.Schema.Sql.Sql100DatabaseSchemaProvider</DSP>

After I synchronized this change my pipeline failed massively. I knew this would happen because I still had to change the yaml in my pipeline to convert the line in the copied sqlproj file the Kevin method uses. As per the below example.

$Old_DSP = 'Microsoft.Data.Tools.Schema.Sql.Sql100DatabaseSchemaProvider'
$New_DSP = 'Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider'
            
(Get-Content "kcdemoazureproject.sqlproj") -replace $Old_DSP, $New_DSP  | Set-Content "kcdemoazureproject.sqlproj"

Before I changed anything else in the pipeline I wanted to run it again. To check it failed against the SQL Server 2019 database. Of course, SQL Server 2019 deployment failed as expected.

Afterwards, I changed the destination database to the one which was set to SQL Server 2008 compatibility level. In addition, I changed all the SQL Server 2019 references to 2008. Because I wanted my stages to reflect the change.

However, my deployment failed. Why did it fail? Well the answer is simple. My database had syntax that was not SQL Server 2008 friendly. Because my database contains a sequence which was introduced with SQL Server 2012.

After removing the sequence from my project, I tested again, and it worked.

Which means you can do one update to a database project and have an update done to both a database set to SQL Server 2008 compatibility level and an Azure SQL database. In reality, testing all newer compatibility levels of SQL Server would produce the same result.

SQL Server 2008 Server

However, what about SQL Server versions themselves? Well to test this I installed an Azure virtual machine with SQL Server 2008 installed. After doing that all I changed was the ServerName value in my deployment task.

After doing that I ran the pipeline again and it worked as you can see below.

Use case

Now, there is a user case for doing this. For example, you might have migrated to a SQL Server 2008 virtual machine in Azure to keep it supported and now looking to migrate elsewhere.

In the meantime, you want to update the same database in the different environments by only doing one commit. Using only one copy of your database as your sole truth as below.

Of course, you can use this to migrate to other versions of SQL Server instead of an Azure SQL database.

Adding SQL Server 2019 back

I thought for fun it would a good idea to add a SQL Server 2019 database back to this deployment pipeline. Just to show that you can use the Kevin method with three different SQL Server database types and keep one source of truth.

You can see below that it created three artifacts and worked fine.

You can see the three different artifacts below.

Again, all three of these artifacts were created from that one repository shown earlier in the post. Which means you only have to update and commit once to deploy to multiple versions of SQL Server databases.

Final word

I hope this example of deploying to many versions of SQL Server databases using one commit was useful. In reality, you can use this method in various situations.

Like I said before I think the best thing about the Kevin method is that it is simple and effective.