Times have changed for the SQL Server® DBA. Not so long ago, SQL Server was a Windows-only application, and the SQL Server DBA didn’t really need to worry about other platforms. The toolset needed to manage SQL Server was well known and had been around for a quite a while. That all changed with the release of SQL Server 2017. SQL Server 2017 now supports Linux® as well as Windows®, which means that today’s DBA needs to work with a number of different platforms. The new support for Linux has resulted in Microsoft® creating a number of cross-platform tools for SQL Server. Let’s take a closer look at Microsoft’s new SQL Server cross-platform tools. All of the tools in this guide run on Windows, Linux, and macOS®.
SQL Operations Studio
Without a doubt, the most important new cross-platform tool for SQL Server is SQL Server Operations Studio (SQLOps). SQLOps is intended as a tool for both SQL Server developers and administrators. It offers a full range of T-SQL development features including IntelliSense®, keyword completion, code snippets, code navigation, and source control integration. It also enables you to perform common administrative tasks such as navigating through database objects and executing queries, as well as performing backups and restores. However, it does not provide the full range of administrative features found in SQL Server Management Studio. At the time of this writing, SQLOps is still in preview mode. It is expected to be generally available in 2018. You can see the latest release of SQLOps in Figure 1.
Figure 1 – SQL Operations Studio on Windows © 2018 Microsoft Corporation. All rights reserved.
You can find out more details about SQLOps at First Look at SQL Operations Studio. You can download the latest release of SQLOps for Windows from GitHub®, and see the Linux and Mac® installation instructions at Download and install SQL Operations Studio (preview).
SQLCMD
SQLCMD has basically been around since the inception of SQL Server. However, SQLCMD is now a cross-platform tool that has been ported to both Linux and macOS. The new cross-platform versions of SQLCMD are exactly like the older Windows version. SQLCMD enables you to connect to a SQL Server instance using parameters on the command line, execute T-SQL queries and scripts, and display the results. You can see SQLCMD running on Linux in Figure 2.
Figure 2 – SQLCMD on Linux © 2018 Microsoft Corporation. All rights reserved.
SQLCMD executes T-SQL statements when you type in the GO command and press enter. On Windows, SQLCMD is installed as a part of the relational database engine using SQL Server Installation Center. Alternatively, you can download the latest version of SQLCMD for Windows from the Microsoft Download Center. You can see the instructions for installing SQLCMD for Linux and Mac at Install SQLCMD and BCP the SQL Server command-line tools on Linux.
BCP
Like SQLCMD, BCP is a venerable SQL Server command-line tool that has been around forever. BCP is used for importing and exporting data. BCP has also been ported to Linux and macOS, and the new cross-platform versions are exactly like the old Windows version. BCP connects to a SQL Server instance from the command line, and uses a set of commands to define and import or export SQL Server data. You can see BCP running on Linux in Figure 3.
Figure 3 – BCP on Linux © 2018 Microsoft Corporation. All rights reserved.
BCP will prompt you for the definitions for each column that you are attempting to import or export. For repeated import or export operations, you can create BCP format (.fmt) files that contain the field definitions. BCP for Windows is installed alongside SQLCMD as a part of the SQL Server Installation Center. The Windows version can be downloaded from the Microsoft Download Center. The Linux and Mac installation instructions are at Install SQLCMD and BCP the SQL Server command-line tools on Linux.
MSSQL-CLI
You can think of MSSQL-CLI as the modern replacement for SQLCMD. Like SQLCMD, MSSQL-CLI is a text-based command-line tool that enables you to execute T-SQL commands. However, unlike SQLCMD, MSSQL-CLI is provides interactive command execution with many modern enhancements including T-SQL IntelliSense, syntax highlighting, automatic formatting of query results, and a multiline edit mode. MSSQL-CLI is based on the open source pgcli Python project, which means that you need to have Python installed in order to run MSSQL-CLI. Python is part of most Linux systems, and MSSQL-CLI can be easily installed using the following commands:
sudo apt install python-pip
pip install mssql-cli
The first command installs Python if it is not present, and the second command installs MSSQL-CLI. Most Windows systems will need to download Python and run the installation program before they can use this tool. You can get the Windows version at MSSQL-CLI on GitHub. You can see the new MSQL-CLI tool running on Linux in Figure 4.
Figure 4 – MSSQL-CLI on Linux © 2018 Microsoft Corporation. All rights reserved.
In Figure 4, you can see T-SQL IntelliSense support provided by MSSQL-CLI. In single line mode, commands are executed when you finish typing the command and press the Enter key. In multiline mode, commands are executed when you enter a semicolon and press the Enter key. MSSQL-CLI provides other enhancements beyond SQLCMD, including the ability to save and re-execute T-SQL statements.
MSSQL-SCRIPTER
The MSSQL-SCRIPTER tool is essentially the equivalent of the Generate Scripts Wizard in SQL Server Management Studio (SSMS). It enables you to produce T-SQL scripts that can be used to build or rebuild SQL Server databases. These scripts can contain just the metadata needed to build the databases and their objects, or they can contain the metadata and data required to completely duplicate a database and its contents. You have the option to display a progress bar as MSSQL-SCRIPTER runs, as it can take several minutes. Like MSSQL-CLI, MSSQL-SCRIPTER is built using Python®. For Windows, you can download Python from the Python.org site and then run the installation. For Linux, you can run the following commands to install MSSQL-SCRIPTER:
sudo apt install python-pip
pip install mssql-scripter
Like you saw with MSSQL-CLI, the first command installs Python, and the second command installs MSSQL-SCRIPTER. MSSQL-SCRIPTER requires several command-line switches. You can display the command’s various accepted parameters using MSSQL-SCRIPTER -h switch. The following line shows a simple example of running MSSQL-SCRIPTER using the AdventureWorks2014 sample database and outputting the schema to the file called AdventureWorks2014.sql.
mssql-scripter -S . -U sa -d AdventureWorks2014 –display-progress –file-path /temp/adventureworks2014.sql
You can see an example of the output of MSSQL-SCRIPTER in Figure 5.
Figure 5 – MSSQL-SCRIPTER on Windows ©2018 Microsoft Corporation. All rights reserved.
You can learn more about the MSSQL-SCRIPTER tool and download the Windows version from Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views.
The DBA’s Cross-Platform World
For many of today’s SQL Server DBAs, using Linux and a new set of tools is the new reality. That means you need to learn a new set of tools to get the most out of today’s new SQL Server database platforms. In this article, you got an introduction to the new set of cross-platform tools that Microsoft provides for SQL Server. Be sure to check out the video that accompanies this article to see these tools in action.
This document is provided for informational purposes only. SolarWinds makes no warranty, express or implied, or assumes any legal liability or responsibility for the accuracy, completeness, or usefulness of any information contained herein.
Leave a Reply