Edytuj

Udostępnij za pośrednictwem

SQL projects tools

  • Artykuł

Tooling for SQL projects is available in several development environments and command line interfaces. The primary tools for SQL projects are the SqlPackage command line utility, SQL Server Data Tools (SSDT) in Visual Studio, and the SQL Database Projects extension for Azure Data Studio and Visual Studio Code.

Tools included in this article:

Graphical tools

These tools provide a graphical interface for SQL projects, a T-SQL editor, and a build and publish process.

SQL Server Data Tools (SSDT) is a Visual Studio component that provides a graphical interface for SQL projects. SSDT provides a visual designer for tables, a T-SQL editor, and a build and publish process.

SQL Database Projects extension is an extension for Azure Data Studio (ADS) and VS Code. This extension provides a graphical interface for SQL projects, a T-SQL editor, and a build and publish process.

Feature set comparison

Feature SDK-style SSDT SSDT ADS VS Code
Create new empty project X X X X
Create new project from existing database X X X
Open existing Microsoft.Build.Sql projects X X X
Solution management and operations X X
Project run build X X X X
Publish project to existing server X X X X
Publish project to a local development instance X1 X1 X2 X2
Publish options/properties X X X
Target platform can be updated X X X X
SQLCMD variables X X X X
Project references X X
Dacpac references X X X
Package references X
Publish profile creation X X X
SQL files can be added by placing in project folder X X X
SQL files can be excluded from build X X
Pre/post deployment scripts X X X X
New object templates X3 X X3 X3
Project files can be organized into folders X X X X
Schema comparison project to database X X
Schema comparison database to project X X
Graphical table designer X X
Code analysis – enable/disable rules X
Project properties – build output settings X X
Project properties – default schema X
Project properties – database settings X
Project run code analysis standalone X
Object renaming and refactoring X
Intellisense provided in database files from project model X
SQL Server object explorer connectivity/view objects X X X X
SQL Server object explorer context menu items X X X
SQL Server query editor connectivity X X X
  1. Local development instance is a SQL Server LocalDB instance.
  2. Local development instance is a SQL Server container.
  3. Limited subset of templates available

Command line tools

SqlPackage is the primary command line utility for the DacFx library, enabling automation of the database development tasks such as deploying a .dacpac to a database or extracting the objects of a database to a SQL project or .dacpac.

Custom console applications can be built using the DacFx .NET library to automate database development tasks. The Microsoft.SqlServer.Dac namespace contains classes for creating, deploying, and extracting database objects and is foundational to the rest of the DacFx library.

CI/CD pipelines can be built with command line execution or with tasks specific to .dacpac and SQL projects deployment. The GitHub sql-action and SqlAzureDacpacDeployment in Azure DevOps are examples of tasks that use SqlPackage underneath a management layer to facilitate deploying database changes.

Conversion tools

The process of converting an existing SQL project to an SDK-style project is done by manually editing the .sqlproj file to include the new SDK-style project format. Before beginning the process, it's recommended to both back up the project file and archive a .dacpac of the project. By comparing a "before" and "after" .dacpac built from the project, you can ensure that the conversion process has correctly completed.

Project/solution management

Multiple SQL projects (and other projects) can be logically grouped together in a solution file. The solution file is a container for one or more projects and is used to manage the projects as a group, including the build action. Large solutions can be broken down into smaller solutions to improve performance and manageability, or dynamically generated for the appropriate task at hand. The slngen solution file generator is available for Microsoft.Build.Sql projects and can be used to create a solution file for a set of projects programmatically and on-demand.

Third-party tools

There are third-party tools available that provide functionality related to SQL projects and database deployment. Some tools are open source, such as dbatools.

Developers have shared their projects utilizing extensibility points around SQL projects, including code analysis rules and customizing deployment plans. Some of these projects are:

Related content