Tlp-sql-server-logo

SQL Server on Linux

Back in March, Microsoft announced that they would be bringing SQL Server to Linux. This was a very significant move for Microsoft, showing its clear commitment to developing cross-platform solutions. (They have also released Visual Studio for Mac and Linux).

This should be of interest to a couple of different groups of people. The first would be those with a large Linux infrastructure who are looking to move from either Oracle (which is considerably more expensive than SQL Server) or from open source database engines such as PostgreSQL or MySQL. Another group might be those who are looking for a more cost-effective way of running SQL Server. Current prices on Azure show that the equivalent Linux virtual machine costs are around 70% of Windows if using Red Hat and 55% if using Ubuntu Server. There is no pricing information yet on SQL Server for Linux, but certainly no reason to suspect that it will cost more than on Windows.

Although the private preview was made available at the time of the announcement, Microsoft have now released the first public preview which is available to install and try out.

There is plenty of documentation available on how to set up SQL Server on Linux, but I will walk through the process I used in the rest of this article.

Creating a Linux virtual machine

SQL Server for Linux is available on three different platforms: Red Hat, Ubuntu and the Docker image (which can run on MacOs for example). In order to try out the installation, I chose to set up an Ubuntu server in Azure. To do this, you need to go to https://portal.azure.com and select Virtual Machines on the left-hand menu, followed by the add button to create a new virtual machine. From the available servers, you can choose Ubuntu Server, then Ubuntu Server 16.04 LTS. This leads to the terms and conditions page on which you click the Create button to begin the creation process. At this point you will be presented with the following form to fill in.

Configure VM Basic Settings

At this point, the main decision you have to make is whether you want to use a password or an SSH public key to connect to the Ubuntu Server. If you choose a public key (the more secure option) you will need to install PuTTY, which you can get from the WinSCP download page. To find the installer scroll down the page and click on the PuTTY installation package. This will install PuTTY, Puttygen and Pageant. It is also worth installing WinSCP itself as we will use it later to connect to the Linux server and upload a backup file. To create the public/private key pair, you will need to run puttygen.exe and generate a new key.

PuTTY Key Generator

The public key can be copied and pasted into the form, but make sure you save the private key for later. Clicking OK takes you to the next screen where you can choose the size of your virtual machine.

Configure VM Size

Obviously the size you choose will depend on what you want to use the server for. You don’t have to select one of the recommended sizes, and can instead choose from the large selection of sizes available.

The next screen is for configuring optional settings, and can be skipped through by pressing OK again. You will then be presented with a summary page, where you review your settings before pressing OK to create the virtual machine. The deployment process will take a few minutes to complete, at which point you will be presented with a screen showing the essential machine information.
Configure VM Configuration

In order to connect to the new virtual machine you will need to make a note of the IP address. Before you can connect however, we need to set up the private SSH key we saved earlier. To do this you need to run pageant.exe, which will be found along with the other PuTTY programs you installed. On opening Pageant, you will see a window where you can click to add a new key. Select the private key (ppk) file and click Open. As long as Pageant is running in the background, it will enable you to connect to anything that uses this key.

Installing SQL Server

The next step is to run PuTTY itself.

PuTTY

Enter the IP address of your new server and click Open. PuTTY is configured by default to use Pageant where possible to match private and public SSH keys, so there shouldn’t be anything else you need to do here. If you do have problems, it’s worth checking under Connection > SSH > Auth where you should see a checkbox called “Attempt authentication using Pageant”. This needs to be checked for PuTTY to use the keys stored in Pageant.

Click Open to connect to your new server. Enter the login name you chose when configuring the machine and PuTTY should check Pageant for the matching private key and log you in.

PuTTY Connect

Now you can begin the installation of SQL Server. This is a remarkably simple procedure, involving only a few commands.

First you import the GPG (GNU Privacy Guard) keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

Then you register the Microsoft SQL Server Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

These commands then install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

After SQL Server installs (which should only take a couple of minutes), run this command to complete the configuration:

sudo /opt/mssql/bin/sqlservr-setup

You will be asked to accept the licence terms and prompted for an SA password. You can also choose to start the SQL Server service and whether it should start on boot.

Connecting using SQL Server Management Studio

In order to connect to the SQL Server from outside of the server itself you will need to open the SQL Server port in the server firewall. This is done back in the Azure portal. From the menu on the left select Network security group, then click on the group that appears.

Configure VM Network

Click on Inbound security rules to add the new rule for SQL Server. Give the rule a name and select MS SQL as the Service.

Configure VM Security

You should now be able to connect to the SQL Server instance using SQL Server Management Studio. Simply enter the IP address along with the SA username and password created when configuring the SQL instance. You may need to download the latest version of SSMS if you don’t already have it.

SSMS

Uploading backup files

Obviously, you’ll want to get some databases running on the server. The good news is that backups from Windows versions of SQL Server will restore just fine, so I downloaded the World Wide Importers sample database for testing with. You can upload a backup file using WinSCP – just login with your username and as long as Pageant is still running it will connect you to your server.

Win SCP Login

 

Then just find your backup file and drag from your local machine to the Ubuntu server. Once that is done, the backup file will be in your user’s home directory. Now, when referring to files in SQL Server on Linux, it appears that there are two ways of doing so. You can either use the Unix path – e.g. /home/duncan/WideWorldImporters-Full.bak or the Windows version of this – C:\home\duncan\WideWorldImporters-Full.bak. Both seem to work perfectly well, and you can even use both versions in the same statement:

SSMS Restore

Conclusion

So, you’ve got a machine running SQL Server on Linux – impressive! Most things appear so far to work the same way as with Windows. There are all the usual dynamic management views, the standard reports are there, Extended Events work just fine and so on. However, there are still a number of unsupported features at the moment, which is perhaps not surprising as the official release isn’t until next year. These include replication, full-text search, AlwaysOn, SQL Server Agent, Reporting Services, Analysis Services, Integration Services, Windows authentication and many others which can be found in the release notes. However, many if not most of these will be added gradually before the full release.

Overall I am impressed so far, and look forward to the official release next year.