I was shocked when Microsoft first announced in July last year that they were releasing SQL Server for Linux, and my first question was – why? But after my recent visit to SQLBits SQL Server conference, I think I now have a better understanding.
Microsoft’s Drawbridge Research Project
Firstly it seems it wasn’t that difficult. Microsoft ran a research project called Drawbridge to figure out how to create sandboxes/containers to have high-density applications on Windows for the cloud. (More on the Drawbridge project can be found here: https://www.microsoft.com/en-us/research/project/drawbridge/).
SQL Server’s operating system – the SQLOS
The reason they had great success with SQL Server was due to work done in SQL Server 2005 when they introduced the SQLOS layer, a user mode operating system, SQL Server’s operating system on top of an operating system. This abstract layer was required as SQL Server has a highly specialised workload and it was needed to achieve the required levels of performance and scalability, whereas Windows Server is a general operating system which caters for many different types of applications. So this means the SQL Server core engine doesn’t heavily depend on the Windows operating system and the set of API calls that interact directly to Windows is narrowed down to about 50 or so. SQLOS and parts of Drawbridge were combined to make SQLOSv2 or what we now call SQLPAL, the SQL Platform Abstract Layer.
Beneath the SQLPAL is a layer called a host extension which provides the bridge to the underlying platform. Everything above the host extension is common code. Hence Microsoft are able to use the same code base for both versions and why most features just work. SQL Server thinks it is running on Windows regardless of the underlying operating system. This is why you can backup a database on a version of SQL Server on Linux, copy to a Windows hosted SQL Server and restore it, or vice versa. Of course, there are some features such as Filetable which are too dependent on the Windows filesystem to work. And by the sound of it, there won’t be any support for xp_cmdshell any time soon.
The plan in the future
SQL Server on Windows in the vNext release cycle will continue the way that it always has. Microsoft’s plan in the future is to have SQL Server run on SQLPAL with a Windows host extension so that it mirrors the architecture on Linux. (For those who like to get into the detail take a look at this blog post: https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-on-linux-how-introduction/).
Stack Overflow Developer Survey results are in
Secondly, Microsoft has been taking notice of the Stack Overflow Developer surveys which are showing a growing trend of developers who develop on Apple Macs using Linux and open source databases and who have probably never used a commercial database. Porting SQL Server to Linux offers them more choice including free editions (such as Developer and Express).
Let’s get started and install SQL Server using Docker in a Linux container
So back from SQLBits SQL Server conference I was eager to get started with SQL Server on Linux on my laptop and wanted a way of doing so for free. Below I show you how I did it so you can get up and running too. You will need Windows 10 Pro or Enterprise Edition (it doesn’t work on Home edition as I discovered but I was able to get a product key using my MSDN subscription and then upgrade by going into settings => Update & Security => Activation, and then using Change Product Key). You will also, according to the documentation, need at least 3.5 GB of free RAM (I haven’t tried running it on less).
Before we get started, you will first need to check your laptop is enabled for hardware virtualization, open up Task Manager and click on the Performance tab and then CPU. In the bottom right-hand corner if you don’t see Virtualization as enabled, as shown below, you will need to turn this on in the BIOS settings of your laptop.
Next check if Hyper-V is installed (this is why at least Pro edition of Windows is needed). Click in the “Cortana Ask Me Anything” window on the task bar and type “turn windows features on or off” and select that item. Then select and enable Hyper-V.
Now we are ready to install Docker. Click on the following link https://www.docker.com/docker-windows and then click Download from Docker Store. For this demo, I used “Get Docker CE for Windows (stable)” version shown below. Installation is fairly straightforward just double-click the InstallDocker.msi to run the installer. When the installation finishes, Docker will start automatically. The whale in the notification area will indicate when Docker is up and running and accessible from the command prompt.
To check Docker was installed successfully open a command prompt and run
From the output above you can see Docker uses a Linux VM. If you open Hyper-V Manager, you will see that Docker has created a MobyLinuxVM VM.
Before going ahead and downloading and installing the container for SQL Server, we need to increase the amount of memory for the VM. Right-click the whale icon in the notification area and select the settings item from the menu. I increased the memory to 4096 MB in the Advance tab although you might get away with slightly less, then click apply. Docker will now restart. You will know when Docker is ready by hovering the mouse pointer over the whale icon.
To start a SQL Server instance run the following command in a command prompt. There is no need to pull the image separately as Docker will detect the image is missing and start to pull it as shown below. The command below is slightly different from the documentation on the Docker page https://hub.docker.com/r/microsoft/mssql-server-linux/ as in the Windows Docker CLI you must use double quotes (at least when using CMD.exe) for the –e parameters.
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 -d microsoft/mssql-server-linux
Once completed we have a fully operational version of a SQL Server instance running on Linux in a Docker container. Use the connection string localhost, 1433 using the ‘sa’ credentials with your regular set of SQL Server tools to connect and explore.