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


By:   |   Updated: 2021-05-03   |   Comments   |   Related: More > 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:

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:

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:

Installing SQL Server Schema Compare extension

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

Installed 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:

SQL Database Projects extension installed

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:

Creating new database project

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:

Selecting Name, Location and Workspace for the SQL Database Project 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:

Clicking OK to proceed further as a new workspace will be created and opened up

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.

WatchesDB SQL Database 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:

Adding new table WatchType to the project

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:

WatchType table successfully added to the project

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

Right click on the Project and click Build:

Clicking Build (project)

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

SQL Database Project successful Build

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:

Publishing the project to the local/remote SQL instance

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:

SQL Database Project debug database WatchesDB successfully published

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





get scripts

next tip button



About the author
MSSQLTips author Haroon Ashraf

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

View all my tips



Article Last Updated: 2021-05-03

Comments For This Article





download














get free sql tips
agree to terms