StarWind is a hyperconverged (HCI) vendor with focus on Enterprise ROBO, SMB & Edge

SQL Server on Linux

  • March 7, 2017
  • 12 min read
Toronto VMUG Co-Leader, VCP4/5, VCAP5-DCA, vExpert 2011-2014, Top 50 Viirtualization Blogger

There has certainly been no lack of surprises coming from Redmond over the past year or so. As soon as Satya Nadella took the reins of Microsoft it has seemed like a barrage of Microsoft news hitting the wires – but this news does not fall within the traditional line of Microsoft. Open sourcing .net, open sourcing PowerShell, and finally, providing a means to run MS SQL Server on Linux. Don’t get me wrong – this is great news for the IT world. Cross compatibility, platform-independent – these words are words that excite me. Microsoft has taken notice of the dominance of the cloud, more so, the dominance of Linux within the cloud. Certainly, releasing the ability to take a Linux instance within the cloud and run our SQL Server on it is a step in the right direction – for Microsoft, and for us as IT professionals.
Just like PowerShell, the MS SQL Linux deployment supports a limited number of distributions – more specifically Red Hat Enterprise Linux 7 and Ubuntu Server 16.04.

Let’s dive in!

Unlike PowerShell on Linux, where Microsoft delivers the “goods” as an individual package, they took the repository route for getting and installing SQL Server. In order to get our SQL Server binaries, we first need to add Microsoft’s repositories to our sources.list file and instruct Ubuntu to trust the packages coming from it. First, let’s import the GPG keys into apt to establish the trust. The following command should handle that for us…

With our trust established we can now add the 2 repositories needed from Microsoft to install SQL Server to our sources.list of the following commands

With our repositories setup, we are ready to begin our install.  In true Ubuntu fashion, we need to first update our package cache and then install two packages; mssql-server and mssql-tools.

As you can see below, depending on what packages you already have installed on your Ubuntu image there may be a lot of prerequisites and dependency packages that may need to be pulled down.  That said the apt package manager does the heavy lifting for us here – and if you have ever installed SQL Server on a Windows machine you know that it can take quite a while and also has a number of perquisites.  Just accept the defaults and continue on.

SQL Server installation on a Windows Ubuntu command view

Once installed we simply need to run a configuration script to setup some of those common parameters we are used to when installing an instance of SQL Server.  Running the MySQL-conf script with the setup switch, as shown below will provide the means for just that…

Running the MySQL-conf script with the setup switch

And with that we are finished.  As we can see above our SQL Server instance has successfully started on our Ubuntu server.  That said if this was all we had to do within our day jobs then we would be a much happier group of sysadmins.  In the real world, we need to manage these SQL Servers, creating databases, running queries, etc…

SQL Server Management

We can manage our SQL Server instances using SQL Server Management Studio like you always have – simply point it to the host name or IP of the Ubuntu Server hosting mssql and everything will be exactly the same.  But we need Windows for this and hey, we went through the trouble of installing this on Linux, why not take a stab at doing some management on Linux.  This is where the second package we installed, mssql-tools comes into play.

The two main commands that come with mssql-tools are sqlcmd and bcp (sound familiar?).  These are located in /opt/mssql-tools/bin, which is not part of our exported path within Linux by default.  We could continue to type in the complete path to these commands but I find it easier to simply include this directory into our PATH environment using the following command.

After this, we can simply enter our command line interface with the following command

Once in the sqlcmd context, we can issue queries as we always have; selecting items, creating databases, modifying data – basically anything TSQL related can be done within the sqlcmd utility on Linux.  Below we can see that I’ve simply selected the Version Information from the master database, showing we are in fact running on Ubuntu!

SQL Server on Ubuntu

And with that, we have successfully installed, configured, and used SQL Server on Linux.  At this point it’s not quite at feature parity with the Windows version, however, Microsoft’s vision and the claim is that over time the two will be.  With the evolution of SQL Server over the years, I can understand how it may take a little while to provide some of that deeper integration they have deployed within the Windows instances.  That said, if you are just looking for a relational database, SQL Server for Linux is providing just that!  We are in some interesting times right now, and Microsoft is adapting to the ever-changing landscape of IT environments very well.  Only time will tell how successful adoption will be of SQL Server on Linux and how it will affect the long-time Linux database systems such as MySQL and Postgres, which have long matured on the Linux platform – Microsoft, at this point, is the new kid in town and has a lot of work to do.  The first few iterations of SQL Server on Linux though have certainly impressed me.

Back to blog