Announcing General Availability of Azure Data Studio extension SQL Database Projects

[Guest DrewSkwiersKoballa]

The SQL Database Projects extension in Azure Data Studio is now generally available and brings project-based development to databases. Compatible databases include SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse SQL.

 

 

 

What are SQL projects?

 

 

SQL projects are a local representation of SQL objects that comprise a single database, such as tables, stored procedures, and functions. Beyond the objects of a database, the database-as-code concept is extended with database-level settings and pre/post-deployment scripts in SQL projects. When a SQL project is built, the output artifact is a dacpac file that represents the state of a database.

 

 

 

 

The SQL Database Projects extension is also available in VS Code and will be updated to general availability soon. SDK-style SQL projects based on Microsoft.Build.Sql remain in preview and provide improved functionality for building projects in cross-platform environments. We look forward to bringing Microsoft.Build.Sql to general availability in the coming months.

 

 

 

 

 

Recent improvements

 

 

In recent releases of the SQL Database Projects extension, the project tree has been enhanced with the ability to move/rename files and visibility to SQLCMD variables. SQLCMD variables are a powerful capability of SQL projects that enable a variable to be placed in T-SQL code that is not set until a dacpac is deployed, creating flexibility and reusability in the dacpac build output artifact.

 

Database Projects view in Azure Data Studio with SQLCMD Variables and database objects

 

 

 

SQL projects deployment

 

 

The SQL Database Projects extension also provides a convenient workflow to create development environments from SQL projects quickly. Behind the scenes, Azure Data Studio starts a container on your local workstation and deploys the SQL project to it. Containers are available for local development environments from projects targeting SQL Server and Azure SQL Database.

 

Publish project dialog with Publish to new SQL server local development container selected

 

 

Ease of deployment doesn’t stop at the local development environment. SQL projects are readily deployed to test, staging, and production environments with automation through the SqlPackage command line tool or with built-in actions for Azure DevOps and GitHub Actions. The deployment process determines the differences between the source and target, automatically applying the necessary changes to obtain the desired state. Through SQL projects, starting with the SQL Database Projects extension in Azure Data Studio, you can drive your entire SQL development lifecycle.

 

 

 

 

Getting started

 

 

Getting started with SQL projects can be intimidating, but there are flexible options for your first steps. When working with SQL projects in an established environment, you may want to create a project directly populated with objects from an existing database. With the SQL Database Projects extension installed in Azure Data Studio, a new option is available in Object Explorer to “Create Project from Database”.

 

 

 

 

In the future, you won’t need to fret if your database has changes that aren’t in the project, because established SQL projects can also be updated from a database. The same schema compare interface that is used for comparing databases and dacpacs can also be used to review changes before applying them to SQL project.

 

Schema compare displaying differences between a SQL project and a database

 

Schema compare displaying differences between a SQL project and a database

 

 

 

Learn more

 

 

Learn more about the SQL Database Projects extension, now generally available, from our documentation: SQL Database Projects extension - Azure Data Studio

 

 

 

Continue reading...