Console Tools for the SQL Server for Linux CTP2.1 Docker Image

In my ‘lab’ I have my last three laptops and a Beaglebone Black. The laptops all came with a retail Windows OS (10-8-7). All have long been running Linux (openSUSE, linuxMint and CentOS in the moment, but after 18 months I’m ready to admit openSUSE is not among my preference for desktop Linux purposes – it’s just such a pain to .). The Beaglebone is on Debian Jesse for ARM. Not a Windows box to be found other than a lingering dual-boot that hasn’t been accessed – or updated! – in more than a year now on the most recent machine.

I was easily tempted when the SQL Server for Linux hit the Internet. But playing around with the earlier CTPs without a Windows box was not very exciting. All I had was sqlcmd and bcp. Eventually I scrounged up a few more tools.

This post talks about some of the command line tools I found usable. If you have SQL Server for Linux in your future, this may be interesting and useful to you. It occurs to me that being able to query and configure a SQL Server from it’s console is a fundamental and essential requirement: just in case you cannot connect from anywhere else and your data is valuable to you.

/~ bash

The out of the box console query tools for SQL Server on Linux is the empty set. The server-side components of something M$ is calling ODBC are there, but the client side ODBC mssql-tools package that provides sqlcmd and bcp must usually be installed separately to get to your data. The official Docker SQL Server image (from CTP2) comes with mssql-tools pre-installed. (found in /opt/mssql-tools/bin inside the container).

The mssql-conf utility comes with every SQL for Linux. Whether or not the tools are installed. Indeed mssql-conf can come in handy to get things set up in the file system. It must also be good for start-up trace flag configurations and tls?

One of the first things you may want to do upon installation is look at the SQL Server errorlog suggesting cat, tail and head could also be counted among the out of the box tools at your disposal as well as what-ever text editors are available on the server (e.g. EMACS, Gedit, Nano, Kwrite, LibreOffice, Bluefish, Atom, vi, etc.) and sudo/root level access to the SQL Server files. Bluefish and Atom are IDEs with some OK support for SQL syntax giving you an ability to review and modify your library of SQL scripts with autocomplete color with formatting to highlight SQL syntax. In slight conflict with my earlier mild disdain for openSUSE’s KDE desktop, even Kwrite is not unusable as a sql editor. One side effect for authors of ad hoc queries is that you can end up flopping between apps to toggle between building the queries, running the queries and seeing the results. Even to correct a silly typo…

When using the SQL Server on Linux image from Dockerhub.com, you will want to include the Docker CLI in your tool set. The Docker CLI is used to start and stop the SQL Server Container rather than stopping and starting the SQL Server. It is still possible to stop only the SQL Server service of course, albeit a bit convoluted. You open an interactive shell inside the running container to get to the mssql-tools included in the image. All mssql-conf work for that image is done inside the running container (that is, local to the SQL instance).

> docker exec –interactive –tty <container-id> /bin/bash

The more stable and deploy-able way to create SQL Server meta-data or move data in bulk has always been by script. SQL meta-data as well as routine and repetitive tasks expressed as sqlcmd batch scripts are powerful and generally portable – from one SQL Server to the next anyway. For the most part anyway, a T-SQL script that works with SQL Server on a Windows server also works with SQL Server on Linux. Few things could so discouragingly encourage scripted work more than a sqlcmd prompt staring back at you.

With the CTP there remain a few unseen obstructions in the port and probably will always be a potential for annoying cross-platform inconsistencies. SQL Trace and Extended Event data, for example, are written to the file system at /var/opt/mssql/log by the SQL Server on Linux but are so useless from the Linux console. Extended Store Procedures (xp_) that touch the file system are generally not working (yet?). Also, when script file archives are transferred to the Linux system weird things can happen like symmetric uni-code glyphs replacing ASCII quotes causing working and tested scripts to fail upon db engine parse because of the tilt direction of the quotation marks. The fix is usually to not copy scripts in to Linux in that way that introduced the inconsistencies.

Writing ad hoc queries when debugging or researching an issue with sqlcmd can be exasperating. Especially when the query doesn’t fit nicely on one line. Sure sqlcmd understands line feeds, but once you push enter to create a line, there is no changing it. A batch that can be edited regardless the number lines in that batch would be preferable. As a compromise, use the -input (-i) sqlcmd command line switch option and a collection of scripts. As previously mentioned, an editor like Atom or Bluefish provide enhanced query author support. Note that script libraries stored remotely from the SQL Server create a remote dependency that may be better avoided for improved availability at critical times. Better to keep essential scripts at the server and maintain the master copy in a source control.

sql errorlog

When the Docker container for a SQL Server instance is created including a persisted volume – or volumes if only to project hopefulness – there are different choices for reviewing the SQL errorlog than when you need to get in the container in order to see any of SQL Server’s output files. Fortunately, everything is still in the familiar mssql/logs subdirectory by default regardless in which case you find yourself. One difference is, that you will need to add the step of opening an interactive shell to work with the files inside the container. You also need to be vigilant of the volume being unexpectedly dropped from sync with the container during some ‘maintenance’ operation or another. And in either scenario, you can also look at the Docker logs to query all the log records available to Docker Engine. Turns out the Docker log IS the collection SQL error logs all in one view.

> docker container logs <container-id>

The command includes a tail like set of options to filter the log records returned either by file position or timestamp. As mentioned previously, several tools are included with Linux that will vary somewhat by builder. I don’t think you will find one that does not include cat. The ‘official’ SQL Server is built on Ubuntu 16.04 indicating that the GNU coreutils head and tail commands are in /usr/bin. I believe that may be true for all three supported Linux flavors. You are root when you open an interactive bash shell inside a container. For a package installed SQL Server, you will likely need root or sudo rights to access programs in /usr/bin

> docker container logs --help
Usage: docker container logs [OPTIONS] CONTAINER

Fetch the logs of a container

Options:
 --details Show extra details provided to logs
 -f, --follow Follow log output
 --help Print usage
 --since string Show logs since timestamp
 --tail string Number of lines to show from the end of the logs (default "all")
 -t, --timestamps Show timestamps

Mssql-conf:

The mssql-conf utility exposes settings comparable to editable settings in the service start-up information of a SQL Server instance running on Windows. Various database file locations and properties can be set and SQL Server trace flags can be enabled/disabled. mssql-conf is a work in progress and documented with the most recently released changes at https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf.

root@63fe05e40911:/# /opt/mssql/bin/mssql-conf
 usage: mssql-conf [-h] [-n]  ...

positional arguments:

setup Initialize and setup Microsoft SQL Server
 set Set the value of a setting
 unset Unset the value of a setting
 list List the supported settings
 traceflag Enable/disable one or more traceflags
 set-sa-password Set the system administrator (SA) password
 set-collation Set the collation of system databases
 validate Validate the configuration file

optional arguments:
 -h, --help show this help message and exit
 -n, --noprompt Does not prompt the user and uses environment variables only
 defaults.
root@63fe05e40911:/# mssql-conf list   
 network.tcpport TCP port for incoming connections
 network.ipaddress IP address for incoming connections
 filelocation.defaultbackupdir Default directory for backup files
 filelocation.defaultdumpdir Default directory for crash dump files
 traceflag.traceflag Trace flag settings
 filelocation.defaultlogdir Default directory for error log files
 filelocation.defaultdatadir Default directory for data files
 hadr.hadrenabled Allow SQL Server to use availability group...
 coredump.coredumptype Core dump type to capture: mini, miniplus,...
 coredump.captureminiandfull Capture both mini and full core dumps
 network.forceencryption Force encryption of incoming client connec...
 network.tlscert Path to certificate file for encrypting in...
 network.tlskey Path to private key file for encrypting in...

network.tlsprotocols TLS protocol versions allowed for encrypte...
 network.tlsciphers TLS ciphers allowed for encrypted incoming...
 language.lcid Locale identifier for SQL Server to use (e...
 sqlagent.errorlogginglevel 1=Errors, 2=Warnings, 4=Info
 sqlagent.errorlogfile SQL Agent log file path
 sqlagent.databasemailprofile SQL Agent Database Mail profile name

When using the Docker Image, use the Docker interactive prompt command previously described to get to the utility from a shell on the host. When the mssql volume is persisted on the Docker host, the mssql.conf configuration file will be viewable directly from the host, but access to the utility to make any changes must be done using the mssql-conf tools found only within the container.

NPM

The name ‘mssql’ is somewhat ambiguous. It is the name of the sub-folder where SQL errorlogs, data files and the mssql.conf configuration settings file are located, (/var/opt/mssql) as well as the /opt/mssql sub-folder where the sqlserver executable lives, This latter folder also includes a library of python files that comprise mssql-conf. Other than to mention them now to help compare the Linux and Windows contexts, none of these are what is meant in this post by references to mssql. Instead our references apply specifically to the open source Microsoft maintained node-mssql npm driver for SQL Server (https://github.com/patriksimek/node-mssql) that uses the Microsoft maintained tedious javascript TDS implementation (https://tediousjs.github.io/tedious/) from a Linux client machine to query any SQL Server.

mssql is the package name at npmjs.org and in common usage as well, but the package source follows a deprecated naming convention with a ‘node’ prefix for the github.com repository name. The truth is, the user never has to explicitly invoke node.js when this package is installed globally and used at the bash prompt. I acts pretty much like any other command-line binary.

node-mssql

When the MIT licensed mssql npm package is installed globally and an appropriate /.mssql.json’ configuration file has been created, queries can be piped through tedious to the SQL Server directly – if a bit awkwardly – from the bash prompt. To get this set up, assuming node.js is already on the machine:

1. Install mssql globally:

> sudo npm install -g mssql

2. Create an ‘/.mssql.json’ configuration file somewhere in your path (or type this connection information object as an argument to mssql every time you use it, your choice).

> echo '{ "user": "sa", "password": "<YourStrongPassw0rd>", "server": "localhost", "database": "AdventureWorks2014"}` > .mssql.json

Or, for a Docker SQL Server container from the Docker host (just like any other SQL Server available on the network at port 1433):

> echo '{ "user": "sa", "password": "<YourStrongPassw0rd>", "server": "172.17.0.1", "database": "AdventureWorks2014"}` > .mssql.json

3. Pipe a query in, get a consistently formatted JSON array of query result out

> echo "select name from sysdatabases"|mssql /home/bwunder/.mssql.json

or – if the .json file’s path is in $PATH or the file is in the current directory use the shorthand:

> echo "select * from sys.databases where name=master" | mssql

But double quotes wrapped in single quotes will fail:

> echo ‘select * from sysdatabases where name=”master"’ | mssql

And multil-line queries break when the author enters that first line-feed.

*Important Note: Installing both the sql-cli package mentioned below and the node-mssql package mentioned above globally creates a global name conflict – the last one installed becomes known as mssql globally at the bash prompt. The last package (re)installed always wins: Installing either package with the -g switch breaks the other even when that other is already installed and working but it is easy enough (thought not a good practice I suspect) to toggle to and fro explicitly with another install of the one you desire now. Internally, sql-cli requires mssql. An npm uninstall of mssql when sql-cli was the last of the two to be installed produces the insightful warning:

bwunder@linux-niun:~> sudo npm uninstall -g mssql
 root's password:
 npm WARN gentlyRm not removing /usr/local/bin/mssql as it wasn't installed by /usr/local/lib/node_modules/mssql

sql-cli

When, instead*, the Apache licensed sql-cli NPM package is installed globally you get an interactive query engine client out of the box. With this package, when you hit enter – each and every time you hit enter – what you typed goes to the SQL Server. Like mssql, you only get one line of typed query text per round trip to the SQL Server. And as with sqlcmd, however, submission by script file, does not carry a limitation on line feeds allowed within the script. It is only the ad hoc command line usage that is limited to query tweets.

~> sudo npm install -g sql-cli
~> mssql -s 172.17.0.1 -u sa -p '<yourstrong!passw0rd>' -d AdventureWorks2014</yourstrong!passw0rd>
 Connecting to 172.17.0.1...done 

sql-cli version 0.6.2
 Enter ".help" for usage hints.
 mssql> .help 
 command             description
 ------------------  ------------------------------------------------
 .help               Shows this message
 .databases          Lists all the databases
 .tables             Lists all the tables
 .sprocs             Lists all the stored procedures
 .search TYPE VALUE  Searches for a value of specific type (col|text)
 .indexes TABLE      Lists all the indexes of a table
 .read FILENAME      Execute commands in a file
 .run FILENAME       Execute the file as a sql script
 .schema TABLE       Shows the schema of a table
 .analyze            Analyzes the database for missing indexes.
 .quit               Exit the cli
mssql> select name from sysdatabases

name
 ------------------
 master
 tempdb
 model
 msdb
 sqlpal
 AdventureWorks2014

6 row(s) returned

Executed in 1 ms
 mssql> .quit

sql-cli also:

  • has a helpful, but limited set of command options for running a script or browsing the catalog with comfortably formatted tabular query results.
  • does indeed depend on mssql but probably not on the latest release of mssql.
  • Includes a few catalog CLI dot commands (e.g., .help) useful to the query developer.
  • works like an sqlcmd -q interactive session except that you never have to type GO in order to send a batch to the SQL Server like you must when using sqlcmd interactively.

The big loss when using sql-cli is bash. That is, the user must leave the sqlcli process in order to work at the bash prompt. With mssql the user is always at the bash prompt.

~

Multi-line ad hoc T-SQL queries are mostly not meant to happen with mssql or sql-cli. sqlcmd will at least let you enter them, even if only in an awkward and not editable buffered line reader kind of way. You will have to decide which you prefer else toggle as needed. When properly configured in an Active Directory Domain, mssql, sql-cli, sqlcmd and bcp on a Linux client should be able to connect to and query a SQL Server on any Windows or Linux Server available to it in that Domain.

Chances are quite good that your node.js oriented tools will be using mssql elsewhere. However, sql-cli feels like a somewhat more useful console command line given the handful of catalog and query tools that come with the interface.

http://

sqlpad

If there is a Chrome browser on the server, sqlpad is a great query tool built for and upon the mssql package. Firefox and sqlpad don’t seem to get along. The sqlpad 2.2.0 NPM package is using mssql v “^3.0.0.0” according to its package.json. Some newer stuff may be missing but the query tool is a pleasure compared to the command prompt and tabular results are oh so much nicer in the DOM in a browser than when echo’d to a terminal. From the drop-down intelli-sence style autocomplete presenting database catalog objects/property selections in the query window hints , a query UI can’t get much easier to work with than sqlpad.

~> sqlpad --dir ./sqlpaddata --ip 127.0.0.1 --port 3000 --passphrase secr3t admin bwunder@yahoo.com --save
 Saving your configuration.
 Next time just run 'sqlpad' and this config will be loaded.
 Config Values:
 { ip: '127.0.0.1',
  port: 3000,
  httpsPort: 443,
  dbPath: '/home/bwunder/sqlpaddata',
  baseUrl: '',
  passphrase: 'secr3t',
  certPassphrase: 'No cert',
  keyPath: '',
  certPath: '',
  admin: 'bwunder@yahoo.com',
  debug: true,
  googleClientId: '',
  googleClientSecret: '',
  disableUserpassAuth: false,
  allowCsvDownload: true,
  editorWordWrap: false,
  queryResultMaxRows: 50000,
  slackWebhook: '',
  showSchemaCopyButton: false,
  tableChartLinksRequireAuth: true,
  publicUrl: '',
  smtpFrom: '',
  smtpHost: '',
  smtpPort: '',
  smtpSecure: true,
  smtpUser: '',
  smtpPassword: '',
  whitelistedDomains: '' }
 Loading users..
 Loading connections..
 Loading queries..
 Loading cache..
 Loading config..
 Migrating schema to v1
 Migrating schema to v2
 Migrating schema to v3 

bwunder@yahoo.com has been whitelisted with admin access.

Please visit http://localhost:3000/signup/ to complete registration.
 Launching server WITHOUT SSL

Welcome to SQLPad!. Visit http://127.0.0.1:3000 to get started

The browser page opens to a login screen. You must create a sqlpad user (sign up) before you can sign in. SQL password entry will come after you sign in when you create connections.

You get some charting abilities with sqlpad too. Nothing fancy, but useful for an aggregate IO, CPU or storage time-series visualization to be shared with colleagues.

sqlpad does seem to loose it’s mind now and again – I don’t know, maybe it’s just me -, but a restart of the app seems to make everybody happy again. SOP

~

It may behoove anyone responsible for a SQL Server for Linux instance to get familiar with sqlpad, mssql and sql-cli as well as rediscover SQLCMD and BCP at the command line, master mssql-conf and find all the tools you can like sqlpad. This is true even if/when the intention is to always use the Microsoft supported Windows client UI (SSMS, Visual Data Tools, etc.,) for all development, testing and database administration. The thing is, we can never know when access at the server console will suddenly become the most – perhaps only – expedient option for an urgent – perhaps essential – access to any given SQL Server instance until that moment is at hand.

Personally, I like sqlpad but don’t really care much for the terminal query experience in any flavor I have tried should no browser be handy. In truth, I had built my own console tool that would at least accept a multi-line query that can be edited, then executed either with sqlcmd over ODBC to get a tabular result or with mssql v4.x over tedious to get JSON results before I ‘found’ sqlpad. I affectionately named my tool sqlpal long ago. That was supposed to represent it’s foundations in SQL Server and the Vorpal node.js CLI from NPM. Get it? sql+pal? Then I started seeing references to SQL-PAL and PAL in the Microsoft vendor documentation concerning the CTP. Turns out SQL-PAL is the name for the interop layer between SQL Server and the Linux kernel or some such thing. Sorry for any confusion.

Sqlpal also includes some core Docker management automation plus the Vantage wrapper for Vorpal that enables a remote ssh like encrypted peer to peer connectivity and locally managed IP firewall among other things. Feel free to check it out if you want your SQL Server CTP to run in a Container: https://www.github.com/bwunder/sqlpal. Docker unfolds a SQL Server for Linux development platform for folks that already know SQL Server but may not necessarily have worked with Linux or Docker much… yet.

Given a bit more refinement of the CTP, SQL Server for Linux in a Container could even perform adequately behind many (most?) apps when released for production use in the near future. In the mean time, I’m going to investigate any possibility to combine sqlpad for the browser and it’s authentication protocols with sqlpal’s Batch caching, query object and script folder hooks for the bash shell because I need something to do.

Advertisements
This entry was posted in node.js. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s