Create and Publish SQL Database Project without using SQL Server Data Tools (SSDT) in Visual Studio

By: Haroon Ashraf   |   Updated: 2021-05-03   |   Comments (1)   |   Related: > DevOps

Problem

As a database developer how can I instantly create and publish a SQL Database Project without using SQL Server Data Tools (SSDT) in Visual Studio?

Solution

The solution is to use Azure Data Studio to build and publish SQL Database Projects just like the way you would use SQL Server Data Tools (SSDT) in Visual Studio.

Using Azure Data Studio to Build SQL Database Projects

Azure Data Studio is an alternative multi-platform tool particularly for database developers to instantly do many database related tasks including creating and publishing database projects.

If we look at the rich history and solid built of SQL Server Data Tools (SSDT) in Visual Studio for building SQL Database Projects, Azure Data Studio is relatively new despite the fact that the main functionality to build SQL Database Projects is the same as provided by Microsoft.

Please refer to the tip if you are not at all familiar with SQL Database Projects or check the following tips to see how easy and comfortable is to create a database project in Visual Studio:

• SQL Database Project with Git Feature Branch Workflow
• SQL Database Project with SQL Server Data Tools and GIT

However, we are more interested in achieving a similar objective in Azure Data Studio and for this we need to ensure that we have gone through the required steps including any new things need to be added to the already selected tool.

Basic Requirements: Azure Data Studio

One of the basic requirements is to download and install Azure Data Studio in order to proceed further with database project development.

Please refer to the following tutorial to see the detailed installation steps:

• Azure Data Studio Step By Step Tutorial

Next, you are required to do a couple of more things.

Basic Requirements: SQL Server Schema Compare Extension

You need an extension called SQL Server Schema Compare to work with a SQL Database Project.

Open Azure Data Studio and switch to Extensions side bar and type "SQL Server Schema Compare".

Please install the extension by clicking Install button:

Once installed you are going to see the following status of the extension:

SQL Server Database Projects Extension

Finally, SQL Server Database Project extension must be installed in order to start building database projects.

Please type SQL database project in the search box in the Extensions section and click install to add the SQL Database Projects extension:

Create a SQL Database Project in Azure Data Studio

Please switch to the Projects sidebar that is now available as a result of the installed extensions and click Create new button:

Please type the name of your database project WatchesDB followed by selecting a location to store your project along with selecting a folder where project workspace is going to be created:

Please remember you can choose any location for the project workspace and the project itself, however, in a professional life scenario this can be a location for the fully managed repository via source control such as Git.

Once you click OK you will be informed that a new workspace will be created and opened in order to open the project, please click OK to proceed further:

Close and reopen Azure Data Studio by loading your project workspace where you can add many things including SQL Database Projects.

When Azure Data Studio restarts switch to the Explorer side bar (CTRL+SHIFT+E) and view the workspace that has been created to manage the project.

Add Watch Table to the Project

Let us add a new table called WatchType to the project.

Go to the Projects and right click on WatchesDB and click Add Table:

Type the name of the new table as WatchType since it is going to be instantly added to your project with one column - Id.

Please update the WatchType script as follows:

CREATE TABLE [dbo].[WatchType]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [Name] VARCHAR(50)
)

The output should be similar to the screenshot below:

This is the time to build the project to see if it compiles successfully or not.

Right click on the Project and click Build:

The project should be successfully built if no errors are there:

Finally, you can change the platform to publish to your compatible local/remote SQL Server instance or Azure SQL Database.

Right click on the project WatchesDB and click Publish, then connect to your local/remote instance and click Publish:

Switch to the Connections and expand Databases (once you are connected to your desired SQL instance) and locate the debug database published via SQL Database Project:

Congratulations, you have successfully learned how to create and publish SQL Database Project without using SQL Server Data Tools in Visual Studio.

Next Steps

• Please modify the WatchesDB project further by adding another table called Order and specifying OrderId, WatchType, OrderDate columns and publishing the changes to the debug database
• Create and publish SQL Database Project for the University database mentioned in the tip
• Please refer to this tip to create and publish a database project for the CarsSample database

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips