Any SQL Server DBA, Developer, or end-user always needs a client tool to connect to SQL Server and access its features. In this article, we will go through various Client Tools offered by SQL Server itself and learn about SQL Server Data Tools and its hidden potentials to make the lives of DBAs and Developers easier.
History of SQL Server Client Tools
SQL Server 2000 offered several Client tools for users to connect to SQL Server instances and perform certain activities.
- Query Analyzer – to connect to SQL Server instances and execute queries on SQL Server instances. Besides, the tool has the Object Browser and the Scripting option to script out objects.
- Enterprise Manager – to manage SQL Server Instances and perform Backup/Restore operations. Also, it can restart SQL Server services, import and export data across SQL Server instances, create SQL Server jobs and execute them on a scheduled basis, and create Linked Servers.
- DTS Designer – the first basic interface to perform ETL operations. It allows the users to create DTS packages and execute them via SQL Server jobs.
SQL Server 2005 enhanced all the above Client tools with more features and renamed them as below:
- SQL Server Management Studio (SSMS) – a single client tool with all features available across Query Analyzer and Enterprise Manager and several other new features.
- Business Intelligence Development Studio (BIDS) – an enhanced version of DTS Designer with many features on the ETL platform. This way, SQL Server competed with other ETL products on the market.
SSMS and BIDS are applicable for SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.
SQL Server 2012 added more features to the existing tools and introduced the new feature:
- SQL Server Data Tools (SSDT) – a completely rewritten version of the BIDS client tool using Visual Studio. Once it is installed on a Server, it is possible to perform Database Design, Database Queries Development, and manage SQL Server instances without the need for SSMS.
The SQL Server 2012 installation came with both SSMS and SSDT bundled together. Microsoft added more features across SSMS and BIDS and introduced enhancements to existing features across the Database engine, Analysis Engine, and Reporting Engine. It led to increasing the size of SQL Server installation media a lot. Hence, Microsoft started separating the Client tools of SQL Server Installation.
In SQL Server 2014, both SSMS and SDT were similar to SQL Server 2012 with the only difference in terms of packaging.
- SSMS – bundled together with SQL Server 2014 installation.
- SSDT – a separate web installer not bundled with SQL Server 2014 installation on the download page.
In SQL Server 2016, Microsoft reduced the installer size further by moving SSMS out of the installation file.
- SSMS became a separate web installer not bundled with the SQL Server 2016 installation.
- SSDT remained a separate web installer on the download page.
In SQL Server 2017 & SQL Server 2019, Microsoft further stripped out SQL Server Reporting Services (not a client tool but a specific solution for information purposes) as a separate installer. It introduced two more client tools to support Azure functionalities across various platforms:
- SSMS – the primary tool for all SQL Server Developers and Administrators with GUI support. Using SSMS, one can develop, manage, or administer components of SQL Server, Azure SQL Database, and Azure Synapse Analytics. This tool is for Windows only.
- SSDT – an enhanced development tool for building SQL Server Databases, Azure SQL Databases, Analysis Service models, Integration Service Packages, and Reporting Services Reports. As it is built on top of Visual Studio, the Developers with experience in Visual Studio can seamlessly develop components using SSDT. Windows-only tool.
- Azure Data Studio – a lightweight editor that connects to Azure instances and can execute on-demand queries and view and save results across many (JSON, Excel, or CSV). The tool is compatible with Windows, Mac, and Linux OS.
- Visual Studio Code – an official SQL Server extension to Visual Studio that supports connection to SQL Server with rich editing experience for T-SQL. A lightweight editor can write T-SQL Scripts across Windows, Mac, and Linux OS.
SQL Server Data Tools (SSDT)
As we already know, SQL Server Data Tools (SSDT) is an enhanced version of the BIDS tool that offers a complete development environment for SQL Server Databases, Azure SQL Databases, Analysis Data models, Integration Services Packages, and Reporting Services Reports.
In addition, many features aren’t much used by Developers or Administrators, and we will dive into those features later. But before we do it, let’s have a quick look into the installation options of SSDT.
SSDT supports the below platforms:
- Relational Databases – from SQL Server 2005 to all versions till now.
- Azure SQL Databases
- Azure Synapse Analytics (querying only)
- Analysis Service Models – from SQL Server 2008 to all versions till now
- Reporting Service Projects – from SQL Server 2005 to all versions till now
- Integration Service Packages – from SQL Server 2012 to versions till now
SSDT Installation
The SSDT installation comes in 2 options:
- Standalone SSDT Installer – is available from SQL Server 2012 to SQL Server 2017. The standalone version was stopped in SQL Server 2019. Now you will need to use the Visual Studio Installer to install SQL Server Data Tools.
- Visual Studio Installer – is available for configuring SQL Database Engine starting from SQL Server 2012. SQL Server 2019 started to provide a complete option to install Analysis Service modules, Reporting Services modules, and Integration Services modules.
Standalone SSDT Installer
The latest SSDT standalone installer for VS 2017 (at the time of writing) can be downloaded here.
Download the SSDT installer and run it to install the required components to a new instance of Visual Studio. Or, we can add them to an existing Visual Studio instance installed in our environment using the below option:
Once we click on Install, the process will start. It will install all selected features into the Visual Studio instance and create a menu. In our case, the menu is under Visual Studio 2017 (SSDT):
The standalone option installs SSDT with Data Storage, Analysis Services, Integration Services, and Reporting Services altogether. Hence, it is the preferred option for all SQL Server versions till SQL Server 2017.
Important: SSDT for VS 2017 does not support the SQL Server 2019 Database engine, Analysis engine, Integration Services, Reporting Services.
Visual Studio Installer
Almost all developers have Visual Studio already installed in their development environments. Thus, we can install the SSDT components based upon the Visual Studio version:
- Visual Studio 2012 till Visual Studio 2017
- Visual Studio 2019
Visual Studio 2012 to 2017
These versions support only the Database Engine Modules installation via the Visual Studio Installer.
To install SQL Server Database engine Modules via the Visual Studio Installer, perform the below steps:
1. Search from the Windows Start Menu and select the Visual Studio Installer. The below options will be listed only if Visual Studio is installed already. Thus, if not, you need to make sure to install it first.
2. Select the Visual Studio instance where we need to configure the Database engine tools.
3. Select SQL Server Data Tools under Data Storage and processing under the Workloads section. Once selected, complete the installation. You need to select the Database engine modules available in the Visual Studio instance during installation.
The remaining modules like Analysis Service Models, Integration Services Packages, and Reporting Services Reports should be installed via the SSDT Standalone Installer.
Visual Studio 2019
Microsoft canceled the SSDT Standalone installer option starting from SQL Server 2019, as we already know. Hence, all SQL Server modules are available under the Visual Studio installer only.
To install SSDT with all these features, perform the below steps:
1. In the Windows Start Menu, select the Visual Studio Installer. The below option will be listed only if you already have Visual Studio installed. If not, you must install it before performing this step.
2. Select the Visual Studio instance to configure the Database engine tools.
3. In the Workloads section, select Data Storage and Processing, and then checkmark SQL Server Data Tools. After that, select the Database engine modules available in the Visual Studio instance.
To install Analysis Services, Reporting Services, and Integration Services projects, follow the below steps:
- Open Visual Studio and click Tools > Extension and Updates. The window will appear:
2. Click on Reporting Services, Analysis Services, and Integration Services projects one by one and install them to your Visual Studio instance. Since I have all those installed earlier, they are listed as installed in my Visual Studio.
Thus, we have seen various methods to install SSDT with all features. To summarize it, the easiest way to install SSDT would be:
- SQL Server 2012 to SQL Server 2017 or Visual Studio 2017 – use the SSDT standalone installer to install Data Features, Analysis Services, Integration Services, and Reporting Services features altogether.
- SQL Server 2019 or Visual Studio 2019 – use the Visual Studio Installer to install the Data Features and add Analysis Services, Integration Services, and Reporting Services via Extensions.
SSDT Features
Now that we have installed SSDT, let’s see what it can offer to us in addition to SSMS client tools.
Reverse Engineer Databases – create a new Database from scratch or import Existing databases and start making enhancements to the existing database schema. To Import any existing database in SSDT, import the schema of an existing database seamlessly.
Disconnected Mode – create an SQL Server Database project without even connecting to the Production or QA SQL Server instance. Design and develop the database code or objects using the SQL Server Express Local Database Runtime. A complete Development Lifecycle is feasible. Once tested, you can publish the database scripts by choosing the target platform like SQL Server or generate SQL scripts to complete the deployment to Production.
Database Schema Compare – compare the Schema structure of two databases across two environments or versions and synchronize changes with the target version. Otherwise, merging changes done by two developers would be a nightmare for DBAs. SSDT detects differences and then can either update the target database directly or generate scripts to synchronize the target database schema with the source database schema.
Database Data Comparison – compare the data across selected tables during development phases. It is crucial in case of emergencies like accidental data deletion, updates by developers, or while dealing with corruption. SSDT has this feature built-in to identify any data changes across 2 tables and synchronize these changes either to the target database directly or generate scripts to apply those changes manually.
Source Control Integration – being a development tool built on top of Visual Studio, SSDT has native functionalities for developers to commit their solutions or projects to the Source control directly. Create a new branch or check-in changes to an existing branch seamlessly.
Publishing Databases – publish changes to a new database or an existing database.
Version Control Changes – generate the schema differences across 2 builds or published versions to identify the list of changes across any 2 builds.
SQL Refactoring – SSDT offers a limited set of SQL Refactoring options inside it. Rename a column in a table and make these changes reflected across all objects wherever the old column name was used out.
All these features are extremely helpful to SQL Server specialists. Still, in many cases, they also rely on third-party tools that offer more functionality. One of the most popular alternatives to SSDT is Devart dbForge Studio for SQL Server – a complete multi-featured solution that allows for doing all the jobs related to SQL Server databases in a visual mode, through the command-line, manually, and automatically.
Conclusion
Since all SQL Server Developers and Administrators are aware of SQL Server Management Studio aka SSMS from SQL Server 2005 onwards, anyone using SQL Server would most likely use it.
In this article, we have gone through the history of various SQL Server client tools and understood the importance of SQL Server Data Tools aka SSDT, and what SSDT offers compared to SSMS to improve productivity. The next article will focus on the details of those SSDT features.
Last modified: October 27, 2022
