First steps with SQL Server on Linux

As you might know, a few weeks ago Microsoft released the first public CTP version of SQL Server that runs directly on Linux! Therefore it was time for me to cross the bridge and install my first ever Linux installation so that I could play with SQL Server on it. In this blog post I want to give you an overview of what the first steps with SQL Server on Linux look like.

Architecture

Before we get into the details about SQL Server on Linux, I want to spend a few minutes to talk about how Microsoft achieved being able to run SQL Server on Linux. When Microsoft first talked publically about their intention to run SQL Server on Linux, everyone thought that SQL Server would just be ported natively to Linux. SQL Server already abstracts some of the underlying OS functionality through the SQLOS, so this could have worked, but would have meant that Microsoft had to maintain different code bases (one for Windows, one for Linux). We can all agree that that would not be really practicable.

To overcome this problem Microsoft now just runs the unmodified Windows-based SQL Server on Linux. What Microsoft is doing is the following:

“We are running the traditional Windows SQL Server embedded into a Linux process on top of SQLPAL, which evolved from Drawbridge” – according to Slava Oks:

SQLPAL is the “SQL Server Platform Abstraction Layer”. Drawbridge itself was a Microsoft Research project established back in September 2011 with the goal of providing a new form of virtualization for application sandboxing. There is not yet that much public information out about how the whole architecture of SQL Server on Linux works, therefore I can’t elaborate that much here. But the most important thing to remember is that SQL Server on Linux is not a port, it’s just the traditional Windows-based SQL Server!

Therefore you can do (almost) everything on SQL Server on Linux that you can do with your SQL Server on Windows. It just works without any side-effects. Of course not everything is yet working with SQL Server on Linux. There is currently no support for SQL Server Agent, and there is nothing there about HA/DR technologies like Always On Availability Group. But I assume that Microsoft will even provide us in that area new functionalities and features on Linux. How awesome would it be to create an Availability Group between a Windows-based and a Linux-based Replica?

Let’s work with SQL Server on Linux!

To be able to run SQL Server on Linux, you need an installation of a Linux distribution. Microsoft currently supports Ubuntu 16.04. and RHEL 7.2 (Red Hat Enterprise Linux). In my case I decided to install a simple Ubuntu VM and install SQL Server on it. The Microsoft documentation shows you step by step how to install SQL Server on Linux. SQL Server is just a few commands away from you:

sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
sudo apt-get update
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/sqlservr-setup

As soon as you have installed SQL Server on Linux, you can verify that SQL Server is running with the sytemctl command.

systemctl status mssql-server

In addition you can also install the SQL Server Client Tools on Linux, which give you sqlcmd executed on Linux – quite nice.

After the installation of SQL Server, you can just connect with the traditional Windows-based SQL Server Management Studio to your SQL Server that runs on Linux. You just have to provide the IP address of the Ubuntu VM and the sa user account with the correct password. It’s so easy – it’s just a traditional SQL Server instance – running somewhere else – in our case on Linux! After your connection has succeeded, now let’s execute the famous SELECT @@VERSION:

And yes, we are running on Linux! But that’s not all. You can take a *regular* Windows-based SQL Server Backup and restore it on Linux. It simply works. First of all you have to copy the SQL Server Database Backup onto your Ubuntu VM. By default Ubuntu doesn’t install SMB which you need if you want to configure file shares for file exchange. Let’s fix that in the first step by installing the SMB feature on Ubuntu:

sudo apt-get install samba
smbpasswd -a klaus

After you have SMB installed, you have to configure a Remote File Share by adding the following entry into the /etc/samba/smb.conf file:

[Documents]
path = /home/klaus/Documents
available = yes
valid users = klaus
read only = no
browsable = yes
public = yes
writable = yes

In my case I’m just sharing the folder /home/klaus/Documents as a file share. And then you have to restart the SMB service to make the remote file share accessible from your Windows system.

sudo service smbd restart

And finally you copy your SQL Server Database Backup over to your Ubuntu VM. When you have your Database Backup on your Ubuntu VM, you finally have to copy it into the /var/opt/mssql/data folder so that SQL Server can find and access it.

sudo cp AdventureWorks2014.bak /var/opt/mssql/data/AdventureWorks2014.bak

When you now navigate to the Restore Database UI within SQL Server Management Studio, you can see that SQL Server can see your backup.

And then you just do a regular RESTORE DATABASE command by providing the backup file.

-- Restore AdventureWorks2014 on Linux
RESTORE DATABASE [AdventureWorks2014] FROM DISK = N'C:\var\opt\mssql\data\AdventureWorks2014.bak' WITH FILE = 1,
MOVE N'AdventureWorks2014_Data' TO N'C:\var\opt\mssql\data\AdventureWorks2014_Data.mdf',  
MOVE N'AdventureWorks2014_Log' TO N'C:\var\opt\mssql\data\AdventureWorks2014_Log.ldf', STATS = 1
GO

To be honest: this is *awesome*!!!

What can you expect from SQL Server on Linux? As I have already said previously – almost everything! I have already tested some edge-case scenarios like Memory-Optimized Tables, ColumnStore Indexes, Parallel Execution Plans, Wait Stats, etc. – IT JUST WORKS!

It makes sense that all these features are just working, because is the regular Windows-based SQL Server – just hosted together with a stripped down version of the Windows OS within a Linux process.

Why SQL Server on Linux?

From a technical perspective it’s quite amazing what Microsoft has achieved here. Nobody would have ever dreamed about all these things back in the area of Steve Ballmer. Microsoft has really done a 180 degree about-turn – well done! But what is the business reason for running SQL Server on Linux? Let’s think about it a minute:

• No license costs for the underlying OS (Linux is free)
• Microsoft provides a *mature* RDMBS technology for the Linux stack (there are other databases available like MySQL, but …)
• …

To be honest: at first the whole idea of running SQL Server on Linux looked crazy , but the more you are think about that approach, the more it makes sense – for some scenarios. We all have to agree that SQL Server on Linux will not be a primary deployment model for everyone on earth. But it will make sense for some specific scenarios.

Summary

The last few weeks have been just overwhelming: first we have all development features available in the Standard Edition of SQL Server, and now we have an unmodified Windows-based SQL Server running directly on Linux. It’s a great time to be part of the SQL Server community and see what happens over the next months and years. We have an awesome time ahead of us.

What do you think about these future directions? Please feel free to leave a comment.

Thanks for your time,

-Klaus