Create your first SQL Database Project in Azure Data Studio

Are you already working with SQL Server Database projects? Do you think that SSDT is the only option to develop those projects?

SSDT was the only option but since a few weeks, there is a new extension for Azure Data Studio (ADS) available, that allows you to develop database projects in ADS.

Install the SQL Database Projects extension

There is currently one requirement to start your database project development in ADS, it is that you need the Insider build of ADS (that you can download here). After the installation, you’ll need to install the extension. Please search for it in the list of extensions and install it in your ADS instance.

Install the SQL Database Project extension in Azure Data Studio

After the installation, you’ll need to reload ADS (the easiest way is to restart the application).

Create your first database project in Azure Data Studio

And now, it’s time for the first database project. Select the Explorer menu and open the Projects entry. This is the starting place for your database project development. Currently, three options are provided:

Select the option how you would like to start your database development in ADS
  • Create a new project
  • Open an existing database project (created in SSDT for example)
  • Import a project from a database

In this example, I am going to create a new database project from scratch. Enter the name of the new project, hit ‘enter’ and select the storage location to put your projects files.

Enter the name of the new SQL database project in ADS
Empty database project in ADS

The initial project contains no elements, so it’s our turn to fill it with life. Open the context menu (right click), select the Add Table option and enter the name of the new table. ADS now creates a new file which contains a basic structure for a table CREATE script.

Context menu to add a new table to the database project

Change the SQL script to create your required tables. In my case, I am going to create several tables and group them in one folder (Tables) in the project.

First SQL script to create a Customer table

The final structure looks like this – four table scripts, everyone in a single SQL file.

Project structure in ADS

If you included some typos or other SQL syntax errors, ADS and the extension give you some hints what may be wrong in your scripts

Something went wrong here..

Deploy your first SQL Database Project with ADS

Well, this part of the story is easy.. it’s just a matter of some clicks and if you haven’t included SQL errors in your script, the database will be deployed within seconds. Open the context menu and select the Publish action. In the following dialog, enter the connection details and the database name. If you already have existing database project profiles, you can import them (but as of today, only database name and SQLCMD variables can be read from those files).

Start the publish process

Enter the connection details to start the publish process

There are two options how you can deploy your database structure to the server:

Generate script – Use this method to let ADS generate the deployment script for you. This is a good option if you would like to have a look at the generated script.

The generated SQL script

Publish – If you select this option, ADS does the magic for you – the database structure defined in the SQL scripts is deployed to the selected target server.

The deployed view on the database server

Incremental Development – Track the changes – Schema Compare

Usually, database development does not happen in one go or in one day. It’s more like an incremental process and therefore it will be necessary to compare your local development artifacts with already deployed versions on database servers. Let’s simulate this process by adding a view to the database project.

A new object was added to the project

To compare the SQL database project with an existing database (or another database project), select the Schema Compare option in the context menu and the target you would like to compare your source against.

Configure the comparison options

Start the comparison with the Compare button and after the it’s done you can have a look at the differences. Select the options you would like to deploy to the target and either generate an update script or apply the changes directly. The dialog and usage is quite similar to the Visual Studio Schema Compare option, so if you know that functionality there is no real new stuff in here.

What is different? A view on the comparison results

Conclusion

I hope I could provide you a short overview about the new SQL Database projects preview in Azure Data Studio. If you are familiar with SSDT database projects, it’s a similar approach in ADS, but as the extension is in preview, some of the existing functionality and usability approaches is still missing in the ADS version (like moving files to a folder that was created later in the development cycle).

But: basic database project functionality is already there. I am looking forward to the next updates of the extension.

With that – Try it and happy deploying,

Wolfgang

Links

About wolfgang

Data Platform enthusiast
This entry was posted in Best Practices, SQL Server, Tools, Uncategorized, Visual Studio. Bookmark the permalink.

3 Responses to Create your first SQL Database Project in Azure Data Studio

  1. Pingback: Creating a Database Project with Azure Data Studio – Curated SQL

  2. Pingback: Shop Talk: 2020-08-03 – Shop Talk with TriPASS

  3. Pingback: Tips about creating Database Projects in Azure Data Studio - Kevin Chant

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s