Create a dacpac for SQL Server 2022 databases using sqlpackage

Reading Time: 3 minutes

In this post I want to cover how you can create a dacpac for SQL Server 2022 databases using sqlpackage. So that you keep the new SQL Server 2022 compatibility level when you deploy new databases.

Just to clarify, a dacpac file is a special type of file which contains details about SQL Server database objects. Which you can use to deploy database updates to other SQL Server databases.

As you are probably aware, this week at Microsoft Build Microsoft announced that the SQL Server 2022 preview was available. You can register for the free trial now.

You can view the announcement in the official Microsoft Build Book of News.

By the end of this post you will have four things to takeaway about SQL Server 2022.

First attempts to create a dacpac for SQL Server 2022 databases

Anyway, I downloaded the SQL Server 2022 preview and installed it in a fresh virtual machine to test a few things.

I created a new database and ran the below code to check that the compatibility level was 160. Which is the new compatibility level for SQL Server 2022.

SELECT compatibility_level  
FROM sys.databases WHERE name = 'SourceDB';  
GO  

So far so good. From there, I tested creating a dacpac using the ‘Extract Data-tier Application’ wizard that is in the latest version of SQL Server Management Studio that is available. Afterwards, I renamed the created dacpac file to make it a zip file. So that I could navigate into it easily.

I than opened up the ‘model.xml’ file and noticed that the DSP (Database Schema Provider) stated ‘Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider’. Which is the one for SQL Server 2019.

To cut a long story short, I did the same with both Azure Data Studio and a copy of sqlpackage I had downloaded earlier this week with same results.

In reality, the deployments would work. However, it would deploy databases that had an older compatibility level.

Create dacpac for SQL Server 2022 databases using sqlpackage

After some investigation I discovered that SQL Server 2022 came with a newer version of sqlpackage than one I had downloaded this week.

So, I added the location of the new sqlpackage to the PATH environment variable. To make my life easier whilst running sqlpackage commands.

From there, I ran the below code to create a dacpac from the database again.

sqlpackage.exe /Action:Extract /TargetFile:SourceDB.dacpac /p:ExtractAllTableData=false /p:VerifyExtraction=true /SourceServerName:localhost /SourceDatabaseName:SourceDB /SourceUser:sqlpackageuser /SourcePassword:N0tThe@ctualPw

I then renamed the dacpac file to be a zip file again. From there, I navigated to the ‘model.xml’ file. This time around it stated a newer Database Schema Provider (DSP). Which is ‘Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider‘.

To check that I deploy this dacpac to SQL Server 2022 and create a database with the correct compatibility level I changed the dacpac file back to its original name and than ran the below code.

sqlpackage.exe /Action:Publish /SourceFile:SourceDB.dacpac /TargetServerName:localhost /TargetDatabaseName:TargetDB /TargetUser:sqlpackageuser /TargetPassword:N0tThe@ctualPw

Afterwards, I ran the below code to check that it stated the right compatibility level.

SELECT compatibility_level  
FROM sys.databases WHERE name = 'TargetDB';  
GO  

Four main takeaways

Twelve hours after testing this a new version of sqlpackage was made available to download online. Which is the same version that comes with SQL Server 2022. With this in mind, there are four main takeaways in this post.

1. You can register for the free trial of SQL Server 2022 now.
2. Compatibility level for SQL Server 2022 is 160.
3. Download the latest version of sqlpackage if you want SQL Server 2022 databases to keep the new compatibility level. I do recommend adding it as a PATH variable as well.
4. It would appear that the new Database Schema Provider name is ‘Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider’. So, when MSBuild gets updated you can look to use it with deployment pipelines in both Azure DevOps and GitHub a few different ways.

Of course, you can look to use the latest version of sqlpackage in your deployment pipelines as well. However, using MSBuild is seen by many as the more graceful approach.

At some stage I might make a template available for SQL Server 2022 deployments. Like I have done for other services, which I announced in a previous T-SQL Tuesday post. That depends on a few factors.

If you have any comments or queries about this post feel free to reach out to me.