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…
|
1 |
sudo sh -c "curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -" |
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
|
1 2 3 |
sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial main > /etc/apt/sources.list.d/sql-server.list" sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main >> /etc/apt/sources.list.d/sql-server.list" |
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.
|
1 2 3 |
sudo apt-get update sudo apt-get install mssql-server 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.
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…
|
1 |
/opt/mssql/bin/mssql-conf setup |
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.
|
1 |
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile |
After this, we can simply enter our command line interface with the following command
|
1 |
sqlcmd –S localhost –U SA |
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!
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.
