SQL Server 2017 Management Tools
A number of database management tools have been built with support for SQL Server. Some of them are by Microsoft, others are third party tools. These tools can help you with your database administration and programming tasks.
SQL Server management tools enable you to administer SQL Server. They allow you to do things like create databases, run queries, manage users, run backups and restores, import/export data, and much more.
The SQL Server 2017 installation doesn't install any database management tools. These need to be installed separately. This hasn't always been the case. Prior to SQL Server 2016, management tools were included as one of the optional components of a SQL Server installation. Since SQL Server 2016, they need to be installed separately.
This means you have a choice of which management tools you'd like to use with SQL Server. Some tools are developed by Microsoft, other tools are by third parties.
But don't get confused by the number of options listed on this page. They all pretty much enable you to do the same thing — manage and administer SQL Server.
I've separated these tools into graphical user interface (GUI) tools and command line interface (CLI) tools.
GUI Tools
Here are some graphical tools that can be used to manage SQL Server.
-
Azure Data Studio
Azure Data Studio (formerly called SQL Operations Studio) is a free tool from Microsoft. It was first released in late 2017 as SQL Operations Studio (preview release), and then released as a General Availability (GA) version under the Azure Data Studio name. This tool can be used to manage SQL Server, Azure SQL Database, and Azure SQL Data Warehouse. It's available for macOS, Linux, and Windows.
You can use Azure Data Studio to create databases, query them, perform backups, restore databases, and more. Azure Data Studio lists your databases and their objects in a tree-like navigation menu, so you can easily navigate to a given database and expand its tables or other objects.
To install Azure Data Studio, download it from Microsoft's Azure Data Studio download page and follow the instructions for your operating system.
-
SQL Server Management Studio (Windows only)
SQL Server Management Studio (SSMS) has traditionally been the main GUI tool for managing SQL Server. This is no doubt due to the fact that it used to be included as an optional component during the SQL Server installation.
As from SQL Server 2016, SSMS is no longer included as a component during the actual installation. It is now offered as a completely separate installation in the SQL Server Installation Center. You can also download it from the Microsoft website. SSMS is a full-featured GUI management tool, so if you find SQLOPS too limited, try this option.
-
DBeaver
Another GUI option is DBeaver. DBeaver is a free open source database tool that works with many different database management systems (MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc).
You have a few options for installing DBeaver. These are all outlined on the DBeaver download page.
Command Line Tools
Below is a quick overview of some of the command line tools that you can use to manage SQL Server.
-
sqlcmd
sqlcmd is a command line utility that lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, from the SSMS Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job.
The Microsoft website contains more information about sqlcmd.
-
mssql-cli
mssql-cli is a new command line tool specifically built for SQL Server by the Microsoft team. mssql-cli was first announced in December 2017. mssql-cli offers some significant enhancements over sqlcmd, such as syntax highlighting, T-SQL IntelliSense, multi-line edit mode, pretty formatting for query results, and more.
To install mssql-cli, see the installation guide at GitHub.
-
sql-cli
sql-cli is another command line tool for managing SQL Server. Installation is as easy as running a single command (assuming you already have NodeJs installed).
To install sql-cli, see the sql-cli page at GitHub.
I've also written a tutorial on how to install sql-cli on a Mac if you're interested in this option.
-
SQL Server PowerShell Module
PowerShell is a powerful command line tool that allows you to script and automate routine tasks. It can also be used within SQL Server environments for managing and automating various administrative tasks.
There are two PowerShell modules for SQL Server; SqlServer or SQLPS.
The SQLPS module is included in the SQL Server installation, but it's no longer being updated. The SqlServer module is the most up to date one.
To install the SqlServer module, start a PowerShell session and run the following command:
The Microsoft website has more information on SQL Server PowerShell module if you'd like to learn more about this option.
There are many other SQL Server management tools that aren't listed above. Some are free, some are for purchase. There are also a lot of utilities for performing specific tasks, such as diagnostics, maintenance, bulk copying of data, etc. The Microsoft website contains a list of command prompt utilites that ship with SQL Server.
For the purposes of this tutorial, we'll just use SQL Operations Studio (now Azure Data Studio).