SQL Server provides SQL Server developers and administrators with dedicated client tools for day-to-day usage. Namely, they are SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT).
Since SSMS is widely popular among all SQL Server developers and administrators, we will focus on the SSDT client tool more to understand the value of its features in making our lives easier.
What is SQL Server Data Tools (SSDT)
Microsoft introduced SQL Server Data Tools as an enhanced version of its Business Intelligence Development Studio (BIDS), and it offered a complete development environment for SQL Server Databases, Azure SQL Databases, Analysis Data models, Integration Services Packages, and Reporting Services Reports.
Business Intelligence (BI) developers primarily use SSDT to develop SSRS Reports, Integration Services packages, or SSAS Models. Both SSMS and SSDT offer features overlapping across SQL Server Database-related tasks. Thus, we’ll check the built-in features of SSDT, for which SSMS users rely on third-party tools.
How to install SQL Server Data Tools
The Database Projects functionality has become an integral part of the Visual Studio installation since 2017, and it remains such in versions 2019 and 2022. So, you don’t have to install SSDT separately if you have Visual Studio 2017, 2019, or 2022. Instead, you can get SSDT included in the list of Visual Studio workloads:
- Launch the Visual Studio Installer: Windows Start menu > search for “installer”.
- Select your Visual Studio edition to add SSDT to > Modify.
- In the Data storage and processing section, select SQL Server Data Tools.
To enable the Analysis Services and Reporting Services for Visual Studio 2019/2022, you need to get the Visual Studio extensions (Visual Studio > Extensions > Manage Extensions or find and download the required extensions directly from the Marketplace). Note: Extension for Integration Services are not available for Visual Studio 2022.
If you have Visual Studio 2017, you need to install SSDT separately to create SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services.
Before you install SSDT, check and uninstall all previous versions of Analysis Services Projects and Reporting Services Projects extensions if you had them, and close all Visual Studio instances. Then do the following:
- Download the SSDT standalone installer from the official page, save it, and double-click to execute.
- On the Welcome screen, click Next.
- Select the tools to install – SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), and SQL Server Integration Services (SSIS)
- Click Install.
It may take some time to complete the installation process.
SSDT vs SSMS Features
In the previous article about SSDT, we defined popular features of the tools and briefly described their functionality. This article examines each feature in detail.
Reverse Engineer Databases
Reverse Engineering helps the developers work with any existing database by viewing Database Objects within SSDT or in the Visual Studio code.
To import the Schema of any existing database, follow the below steps:
Open SSDT > New Project in SSDT > SQL Server Database Project, and name the new Project. I’ve named it AdventureWorks.
Right-click on the AdventureWorks project and click Import > Database to get the schema of any existing database.
Connect to the SQL Server instance and choose the database (I’ve selected AdventureWorks)
Once the import is completed, we can see the AdventureWorks Schema in the SSDT Project, displayed under Schema or Users as shown below.
Expand the HumanResources Schema to view the list of Tables, Views, Stored Procedures, or Functions available inside that Schema. For instance, click the Department table inside the Schema, and it will display the table structure along with the T-SQL script to modify the object structure:
Disconnected Mode
Starting with SQL Server 2012 in SSDT, Microsoft provides a local on-demand server instance named SQL Server Express Local Instance for developers working on their projects. This Local instance can be used as a Sandbox for building, testing, and debugging SQL Server Database projects. One can access it via the SQL Server Database project only.
The main purpose of the SQL Server Disconnected mode feature is to allow the development to work and test locally with or without access to the Production Live instances of SQL Server or Azure SQL Databases and then deploy the changes to the destination.
In SSDT, click View > SQL Server Object Explorer > (LocalDB)\Solution Name > Databases. When you find the database name matching the project name, it will be the working area for this database.
After completing the tasks, developers can right-click on the solution and choose the Publish option to apply the changes to an existing database or a new database as shown below:
Click on Publish or Generate Scripts to deploy these changes to the database:
We have imported the AdventureWorks database, disconnected it from the SQL Server Live database, made our changes, tested them, and then published them back to the Live Server seamlessly.
Database Schema Compare
Almost every SQL Server DBA would need a third-party tool to do the Schema comparison of 2 databases – it is a continuous necessity. However, this feature is already present in SSDT. We can invoke it by right-clicking on the project and selecting Schema Compare:
Open the Schema Comparison tool within your Visual Studio. By default, our existing project is the Source. However, we can change it and take another target database. Click Select Target.
The Select Target Schema allows for taking another Project or the Database. I’ll choose a different copy of the AdventureWorks database for testing purposes:
Once the target database is selected, click Compare to start the Schema comparison process. After completion, you will get the Results page with the below groupings by default – you can modify these settings to display the results based upon Schema or Object Types.
- Delete – Object is present in the Target and missing in the Source database
- Change – the Object property or script does not match across Source and Target
- Add – Object is missing in the Target database
Also, SSDT has more options:
- Update – apply changes directly to the target database or environment.
- Generate Scripts – the sign next to Update. Create a script file for further verification.
- Settings – the standard cogwheel icon. Change the Schema Compare options.
- Grouping – change the default grouping options.
- Filter/Clear Filter – exclude certain objects from Schema Compare.
- Sort – sort the results.
As we have seen, the Database Schema Comparison is done without any third-party paid tools. The options to generate scripts and synchronize the target database are present as well.
Note: Schema Compare works only on the Schema level. To compare Data, we need to choose another feature.
Database Data Comparison
In addition to Schema Comparison, SSDT has options to perform Data Comparison at the table or view level by performing the below steps:
In SSDT, go to View > SQL Server Object Explorer > SQL Server > RRJ (Connect to SQL Server instance if not added already) -> Databases. Right-click on the database for which we need to do the Data Compare job. The necessary option will be in the menu:
Click on Data Comparison to launch the wizard:
Choose the Source and Target databases and click Next.
Note: Data Comparison may take more time depending on the size of databases or tables selected. Make sure the environment where we are running the task is capable of handling huge data.
Once the comparison is completed, the tool will list down the changes and give us options to either Generate Scripts or Update Target database directly:
Source Control Integration
All Visual Studio Developers use the Source Control integration via Visual Studio environment in their day-to-day work. As SSDT uses the Visual Studio framework, it also offers the Source Control Integration:
Right-click on Solution name > Source Control > Add Solution to Source Control.
New Query Window
With SSDT installed in an environment, we can open a New Query window without SSMS installed.
In SSDT, click Tools > SQL Server > New Query:
You will see the following options:
Type in your Server and Database details to have a New Query window opened and connected to the SQL Server instance selected.
Besides, we will have SQL Server Object Explorer showing the Databases and objects similar to SSMS. The Query Window allows us to do many things similar to SSMS.
We can view the query results in Grid, Text, or File format with other options like Execution Plan, etc. SSDT also has the SQL Server Object Explorer that shows databases grouped into several categories:
If you collapse the Servers, you can see different types of databases:
- (localdb)\MSSQLLocalDB – SQL Server Express Local Instance with system databases
- (localdb)\ProjectsV13 – SQL Server Express Local Instance with databases we have imported to the projects like AdventureWorks
- RRJ – SQL Server instance we have connected for querying purposes
Publishing Databases
To Publish changes to any Database, simply right-click on the Solution > Publish. You can either deploy changes directly to the Live Database or generate scripts and apply changes to the Live Database manually.
We have seen how to publish the database for the Disconnected mode feature. Now, let us see how to publish databases in general. You will see many advanced options to use if needed:
General Options
Drop Options
Ignore Options
For those who will publish databases frequently, SSDT offers the possibility to select from these Advanced Options and save the selection as a Profile. We won’t need to modify these settings every time.
Profiles can be created in the Publish screen:
- Load Profile – load existing Profile configurations
- Create Profile – create a new Profile with the Publish configurations
- Save Profile as – save any existing Profile with modifications as a new Profile
Version Control Changes
In SSDT, every time a Solution is built or published, it will create a *.dacpac file containing all information about the database schema. Microsoft offers a tool named sqlpackage.exe which you can download here to read the content of every such package and identify the changes that happened across every *.dacpac file revision. Even though the result will be in *.XML format, we can easily decode the changes and store them for reference purposes.
SQL Refactoring
SSDT offers the SQL Refactoring options too. Right-click the Solution you are working on:
There are 3 refactoring options available in SSDT at the moment:
Rename Server/Database References – you can rename any objects within the Server or Database along with referenced objects. For example, renaming a column within a table will rename the column name in all referencing Views, Functions, and Stored Procedures as well.
Expand Wildcards – replace the wildcard * in any SELECT statement with the column names:
Fully-qualify Names – provide fully qualified names for objects wherever the proper values are missing. For example, a missing schema name can be replaced with dbo or an object from another database, and be replaced with the DATABASE_NAME.SCHEMA_NAME before the table name as shown below:
Conclusion
We have familiarized ourselves with the features of SSDT which aren’t available in SSMS but can help the DBAs and Developers improve their productivity significantly. It is not the only opportunity, of course. Many database specialists turn to third-party tools like dbForge Studio for SQL Server, as its functionality is superior. Still, SSDT remains a decent and helpful tool for all tasks related to SQL Server databases.
This article reviewed the most prominent features of SSDT and explained how to operate them to perform the tasks. Stay tuned for the next materials!
Last modified: October 27, 2022
