Top Features in SQL Server Data Tools for Database Projects


By:   |   Updated: 2021-02-23   |   Comments   |   Related: More > Tools


Problem

SQL Server Data Tools allows you to develop database projects inside Visual Studio. It's a versatile IDE, and it can do more than create tables or stored procedures. In this tip, we'll give an overview of the more interesting features of this product.

Solution

SQL Server Data Tools (SSDT) is a development tool from Microsoft for building SQL Server relational databases in Visual Studio. In an SSDT Project you can design database objects for SQL Server, but also Azure SQL DB and Azure SQL Data Warehouse. In this tip we'll focus on SSDT for database projects, because SSDT can also be used to develop solutions for SQL Server Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS). The business intelligence components are outside the scope of this tip.

In earlier versions of Visual Studio, you could download SSDT as a separate installer, but since Visual Studio 2017 the database projects have been integrated into the Visual Studio installation. In Visual Studio 2019 for example, you need to select the workload Data storage and processing to enable SSDT.

Let's take a look at some features to discover why SSDT can be a valuable tool in your database development toolbox.

1 – Treat your Database like Code

With SSDT, all of your database objects – tables, views, stored procedures, functions – are code. You write it in Visual Studio, you build it in Visual Studio and you publish it to your desired destination. SSDT supports source control integration, such as a git repository in Azure Devops for example. Having all your database code in source control means you won't lose any code when the database goes down and cannot be recovered.

A couple of tips have already been written on the subject of database projects in SSDT and git integration:

When using source control, you can also create multiple versions of your database, by using git branches for example. Suppose you need to hotfix a stored procedure in production. In development however, you're already working on a new version of the stored procedure which doesn't need the fix. But it's not finished yet, so you cannot deploy the new version to prod. To solve this, you can create a branch using the code from production, implement the fix and publish this to production. To continue working on the new version of the stored proc, you switch your branch back to the development branch.

2 – Reverse Engineer Existing Database

You can create a database from scratch in SSDT, but what if you already have existing databases? You can import their schema into SSDT with just a few clicks:

You can find more details in the tip Reverse Engineer SQL Server Databases with Visual Studio.

3 – Schema Compare of Two Databases

This feature allows you to compare the schema of two databases and report all the differences between them. You can easily check for example what the differences are between your test and production database. A database can either by a physical database, or a database described in a database project in SSDT. Once the comparison is done, you can choose to update the target database or to generate a .sql script with all the changes. This feature is also useful when someone has been making database changes outside of SSDT – in other words, by running DDL statements directly on the database – and you want to sync those changes to source control.

The following video shows a schema compare of a database. After the compare, three changes were found: a table was added, a column was added and one column had its name changed.

The tip SQL Server Data Tools Connected Database Development also has an example of a schema compare.

4 – Publishing Database Schema

When you finished the development of the database objects, you can publish the solution. This will build the solution – checking for any discrepancies – and push out the database object to a target server. You can do an incremental deployment, where only changes are pushed to the database. You can also do a re-create of the database, although I wouldn't recommend doing this in production. You can also configure to block the deployment if any data loss should occur.

publish settings

Publishing is useful to push out a brand new database. For example, suppose you have configured a new development server. If you want your database with all the objects but with no data, you can publish the database from SSDT to the new server. If you want data though, a back-up/restore from an existing database might be more useful.

If you frequently push your solution to the same database with the same settings, you can create a publish profile.

create publish profile

This profile is saved in the solution. Next time you want to publish your database, you can just double click the profile and hit publish.

Another option is to generate a script. This can be useful if you want to inspect which changes SSDT will push to your database.

generate script in publish screen

Here you can see a piece of the deployment script when a column has been added to a table:

deployment script

This script uses sqlcmd parameters, so you cannot copy paste it in SQL Server Management Studio (SSMS) and execute it, unless you run in sqlcmd mode (as explained in the tip Using the SSMS Query Editor in SQLCMD Mode).

A script is generated each time you publish, even when you don't choose to generate a script but choose to publish directly. You can find the scripts in the bin/debug folder:

generated scripts in debug folder

5 – Generate Report with Changes

Every time you build the solution (or publish, which will invoke a build), a .dacpac file is created which contains all the information about the database schema. Using the tool sqlpackage.exe, you can generate an XML report with all the changes that will be deployed when a publish is executed. This tool can be downloaded here.

We can run the following command line command to generate such a report:

sqlpackage /action:DeployReport /SourceFile:"mydatabase.dacpac" /TargetConnectionString:"Data Source=localhost;Integrated Security=True;Initial Catalog=mydatabase" /OutputPath:"C:\temp\DeployReport.xml"

For example:

calling sqlpackage

You might want to add the location of the sqlpackage executable to the path environment variable. The following report is generated:

deployment report

Every time you publish from Visual Studio, a text-based report is generated as well:

text based report generated after publish

However, this report is not as easy to interpret automatically as an XML report:

text based report as example

The combination of sqlpackage.exe and dacpac allows for many automation opportunities. Generating a report is only one of the available options. Check out the documentation for other commands.

6 – Editor and Templates

If you don't like to write the entire SQL for creating a table with many columns, there's a tabular editor where you can enter/edit all of the information:

table editor

If needed, you can still edit the CREATE TABLE DDL command in the bottom pane.

In SSDT, you can add many different types of objects:

add new object

For many of them, a skeleton template will be provided for the new object. For example, here's the one for a table-valued function:

template for sql code

This is useful when you don't know all of the syntax by hard.

7 – Refactor Code

SSDT also supports refactoring. For example, when you rename a table, it will also change the name of the table in objects referencing the table. Suppose we have the following sample table:

sample table

And the following view, which just selects all columns from the table:

sample view

If we rename the table manually in the T-SQL script, this will break the view.

view is kaput

But if we use the refactor functionality, this will update the view as well.

You can also use the refactor functionality to expand wildcards:

Other options for refactoring are fully qualifying object names and moving objects between schemas.

Next Steps


Last Updated: 2021-02-23


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Connecting to SQL Server Using SQLCMD Utility

Different Ways to Connect to SQL Server Using sqlcmd

Installing and Using mssql-cli on Linux for SQL Server

SolarWinds Database Performance Analyzer for SQL Server

SQL Server DBA Utility Dashboard














get free sql tips
agree to terms