SQL Server 2017 Linux Command Line Tools


1. Installation

The Linux command line tools can be installed as per:

Notes came from Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views

We see that dbfs depends upon FreeTDS common which depends upon libsybdb5 - showing the Sybase roots for SQL Server!

2. mssql-scripter

mssql-scripter is a tool for scripting out database objects and has many options

This tool is available on github at Microsoft/sql-xplat-cli

As well as script objects we can get insert clauses to get the data:

mssql-scripter --server ubuntu -d justdave --user sa --schema-and-data

We can also add headers before each object - this would make the output parseable!

We can also script logins for the server and by comparing with and without this option get just the information for logins

The mssql-scripter tool depends upon the SqlToolsService which sits on top of the SqlScriptPublishModel and SMO which is also used by the Scripting Wizard in SSMS!

The SqlToolsService is used by other cross platform SQL tools, such as the Microsoft/vscode-mssql extension.

The SqlToolsService is an open source project and hosted on github at Microsoft/sqltoolsservice.

The architecture is described at mssql-scripter Architecture

We can see that the cmdline application is being run for the SqlTools Service

Some interesting options are --target-server-version and --target-server-edition:

ISSUE: This is including the compression options even if we specify version 2014 and standard edition!

If we do not specify the version/edition and skip the --include-objects we do not get the compression clause!

mssql-scripter --server ubuntu -d justdave --user sa

ISSUE: If we try to display progress we get a error!

ISSUE: If we script the whole database we get an assert failed message in /var/log/syslog!

mssql-scripter --server ubuntu -d justdave --user sa

3. dbfs

dbfs is an experimental tool for accessing DMVs as files via a virtual mutned filesystem

This tool is available on github at Microsoft/dbfs

There is no need to login to sql server, the data is available via the UNIX philosophy that everything is a file!

We can then use scripting tools to parse the files.

We first create a configuration file

more dmvtool.config 

This actually works to connect to a linux server on the same host!

We then create a folder and run the dbfs tool to mount the DMV files under that folder

mkdir dmv
dbfs -c ./dmvtool.config -m ./dmv

When we run the command the dbfs process goes into the background at dettachs from the terminal

Under the dmv folder a new folder server appears which contains all the dmv files

We can view the output as text

Also we have .json file where we can view the output as JSON and make it human readable using python and json.tool

To stop the dbfs tool we kill it with -2 (Interrupt)!

NOTE: The dbfs tool will not run as root as this would "open unwanted security holes"!

ISSUE: SQL Server 2017 CTP 2.1 Some of the files for the DMVs currently return Operation not supported

SQL Server 2017 the issue with DMV's dm_xe_session_targets and dm_xe_session_targets.json not been supported has been fixed

However with RC2 the DMV dm_xe_session_targets contains new lines within the callstack column!

With RC2 we also see that DMV dm_xe_session_targets.json produces output where newlines appear in the json output e.g. in the middle of the address of an entry in the callstack

This means that python with json.tool refuses to display the json data!