Tips about creating Database Projects in Azure Data Studio

Reading Time: 4 minutes

In this post I thought I would share some tips about creating Database Projects in Azure Data Studio Insiders build. Because the extension to do this for SQL Server databases is now in preview.

I first became aware of this new addition to Azure Data Studio after a post from Wolfgang Strasser. Which you can read in detail here.

I recommend going through his guide to install Azure Data Studio Insiders build and installing the SQL Database Projects extension before you read my tips. You will know if you have Azure data Studio Insiders build running if the logo in the top right-hand corner is green like below.

Since the colour of the logo for the stable build is mostly blue a good way to remember these is to think of the blue-green deployments.

At some stage this feature will end up in the stable build. However, some of the tips here will still be relevant for it.

Importing Project from Database tips

In this section I will give you advice relating to importing a project from a database. Before you do that however I want to give you a piece of advice relating to Git.

If you intend to store this database project in a local Git repository I recommend importing the project first. Otherwise you will end up seeing the hidden .git folder that gets created like below.

You can import a new project from a database by selecting the highlighted box below, and then follow the guide.

Alternatively, you can run the ‘Database Projects: Import New database Project’ from the Command Palette instead. You get various options when you go through this process.

My tip here is to know the following in advance.

1. Which database you want to import.
2. Name you want to call your database project.
3. Location of your project (you can create this during wizard).
4. Folder structure for your project.

Otherwise you may end getting a stern message like the one below.

As far as the last option is concerned, I prefer to select ‘Schema/Object type’. However, it is all down to personal choice.

Creating a dacpac file

One of the options within the SQL database Projects extension is that you can publish your project to another SQL Server database. Of course, this is only for one database.

So, what do you do if you want to update multiple databases with one project? Well one option is to create a dacpac from your project and use that dacpac to update multiple databases.

You have a couple of options if you wish to do this.

First option is that you can build your database project within Azure DevOps as shown below.

If you build it within Azure Data Studio the created dacpac within Windows it gets created in the \bin\Debug location of wherever you created your project.

Now, in case you are not aware you can use source control within Azure Data Studio. Which means that you can sync it to somewhere that hosts Git repositories. For example, Azure DevOps.

If you already have the folder your database project folder is created in setup as a Git repository you will be notified after a build is done that your local repository has pending updates.

Once you have done this you can synchronize it with your Azure DevOps repository and use that dacpac as part of your deployment pipeline.

Database Schema Provider

One thing to watch out for when doing this is that I have noticed the database projects within Azure Data Studio can default to SQL Server 2016. Even if imported from an Azure SQL database.

With this in mind, you need to change something before you create your dacpac file. You must manually change the line stating the Database Schema provider (DSP) as per the below example which makes sure your database project is for an Azure SQL database.

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

Doing this can save you a bit of effort if it’s only yourself working with a single database project.

However, if working on multiple projects with multiple users I recommend sticking to building your dacpac within Azure DevOps. Besides, doing it this way gives you much more flexibility. Plus, it makes it easier to publish an artifact which can be your one source of truth.

Final word

I hope my tips about creating Database Projects in Azure Data Studio Insiders build are useful for some of you.

Of course, there are other ways you can do some of the things discussed in this post. However, I’m really excited that I can use this alongside Azure DevOps. So much that I use it within a demo I discussed in my previous post here.

Like I said at the start, this is all based on the current version of the extension which is currently in preview. Which could well change over time Whatever happens, I’m excited this is now an option.