SQL Server Tools

By: Ben Snaidero

Overview

There are many tools that come standard with SQL Server that help you to perform many different tasks. From complex configuration tasks and performance tuning all the way down to simply querying data there are many standard tools that Microsoft provides. Below is a list of these tools along with a short description of what can be done with them.

SQL Server Management Studio

SQL Server Management Studio (or SSMS as it's widely referenced) the main utility that is used to manage, configure, administer, basically it's your go to tool for all things SQL Server. It has references that allow you to launch many of the other related SQL Server tools and also contains a comprehensive graphical environment for querying (DML and DDL) your database. There are also many built in GUI tools/wizards/reports that allow many administrative tasks to be done using just point and click as opposed to writing complex TSQL commands.

Azure Data Studio

Azure Data Studio is a cross-platform tool that can be used to manage SQL Server systems for people running Windows, macOS or Linux. It's mainly used by people who only need to edit/run queries or perform administrative tasks using Powershell or SQLCMD as it does not contain the wide range of GUI tools and wizards that are available in SSMS.

SQL Server Profiler/Trace

SQL Profiler is a utility that is used to create, manage, analyze and replay SQL Trace events captured from a SQL Server instance. These traces can be saved and opened for later analysis when trying to diagnose an issue or they can also be replayed on a test server. The events that make up the trace can include almost any event from your database system. Some examples would be the start or end of a statement, acquiring or releasing a lock, event written to sql error log, etc. The data captured around the event does depend on the event type but all this data is displayed in the tool in a single row. Note: This feature has been deprecated and will be removed from a future release of SQL Server, Extended Events should be used instead.

Extended Events

Extended Events is a lightweight performance monitoring system that can be used to collect as much or as little information as required to troubleshoot an issue. There are many more events available in this framework, compared to SQL Profiler/Trace, as many new features that have been added to SQL Server over the last few years have only had their events added to this framework. Another benefit of Extended Events is that when directing the output to Event Tracing for Windows you can correlate your database system events with operating system and application event data.

Distributed Replay

With this feature you can replay a trace file captured from on SQL Server instance against another SQL Server instance. This can be used to test hardware or software upgrades as well as system configuration changes (both operating system and database level). Using distributed replay better simulates a real system in that it allows you to replay the trace from multiple clients (SQL Profiler just replays statements in sequence).

SQL Server Configuration Manager

This utility is used to manage/configure any service that is related to SQL Server. This would include services such as Analysis Services and Integration Services. Although a lot of what can be done by configuration manager, start/stopping a service or changing the startup type, can also be done from the Microsoft Windows Services dialog, the configuration manager utility also applies things like permissions to the account running the service. Editing the service from the Windows Services dialog can miss changes like this and cause the service to malfunction. In addition to service changes the configuration manager also allows you to enable/disable and configure SQL Server network protocols on the server side. On the client side you can create aliases for server connections and also configure the protocol order when trying to connect to an instance.

SQLCMD

The sqlcmd utility is a program that allows you to execute TSQL scripts and script files from the windows command line or from a job step within a SQL Server Agent Job.

Database Tuning Advisor

The Database Tuning Advisor is a GUI utility that can be used to analyze individual queries or workload's and provide recommendations on indexes and/or partitioning strategies that can improve your query performance. Workloads can be created from the plan cache, query store or from trace tables/files create by SQL Profiler. There is also a command line utility, dta, that allows you to perform the same analysis programmatically.

SQL Server Data Tools

This tool provides functionality that allows you to design and deploy databases, both in SQL Server and Azure. Along with the ability to perform light database administrative functions like renaming objects it also gives you the ability to compare two schemas, functionality not available within management studio. It also provides you with a graphical user interface where you can design, build and deploy Analysis Services models, Reporting Services reports and Integration Services packages.

Database Quality Client

The Data Quality Client is part of Data Quality Services but runs as a standalone application. It allows users edit and run data quality projects. In addition to that is also allows administrators to create and edit the knowledgebase as well as perform other administrative tasks with the system.

SSB Diagnose

Ssbdiagnose is a utility that can be used to report on issues within SQL Server Service Broker conversations as well as the configuration of any service broker services. A report can be generated in either plain text or an XML format that can be used to pass the data on to another program or service.

PowerShell

There are two modules within Powershell that allow you to interact with a SQL Server database. The "sqlps" module which is older and is no longer updated and the "sqlserver" module which contains all the functions that make up the sqlps module as well as new ones that let you interact with the last SQL Server features. These functions include things like backing up and restoring a database, create a new database, reading the error log, etc. In addition to those and other specific functions, the Invoke-Sqlcmd function allows you to run any command supported by the SQLCMD utility. Prior to SQL Server Management Studio 17.0 both modules were installed when you installed SSMS but starting with version 17.0 the sqlserver module must be installed from the Powershell Gallery.