This past Wednesday at Microsoft Connect(); Scott Guthrie announced the public preview of SQL Server on Linux. I was fortunate enough to have access to the early bits of SQL Server on Linux during the private preview phase, and was also given a tour of SQL Server vNext during the Microsoft MVP Summit last week. Today I wanted to help walk you through how to be up and running with SQL Server on Linux so that you can kick the tires on this version yourself.
First off, let me share with you a quick picture of how all the pieces fit:
SQL Server runs on Linux by using what is called the Platform Abstraction Layer (SQLPAL). Inside of SQLPAL is a new version of the SQLOS (named SQLOS v2), and this allows for SQL Server to run in the user process space. So if you get things up and running and are poking around to see how SQL Server is running in the Linux kernel, you are going to be disappointed. Or happy. Or meh. One of those. Anyway, I wanted to mention this and share the slide because I’ve seen the question more than a few times.
Now, to use SQL Server on Linux you will need a few things:
1. A linux box. If you have your own Linux server then download the v.Next bits here. Have fun with that because I’m lazy and will use an Azure VM instead.
2. A way to connect to the SQL Server instance, such as the latest version of SQL Server Management Studio (SSMS v17.0 RC1), or perhaps Visual Studio Code.
3. No fear of a command line. Maybe practice with some PowerShell or Server Core to get your feet wet first.
And that’s it, really. Let’s get started.
Creating SQL Server v.Next on Red Hat Enterprise Linux in Azure
Well, first things first, if you don’t have an Microsoft Azure account, go get one. Once you do then you will want to create a VM using the image that is already available. A quick search of ‘sql linux’ should return the following:
I will create a VM named ‘sqlonlinux’, set the size to be A3, and be up and running in less than five minutes. Here’s what the confirmation page looks like before I click OK:
It was that simple. Mostly.
Installing SQL Server vNext on Red Hat Enterprise Linux in Azure
OK, we have our Linux server, and I will assume you have either SSMS v17.0 RC1 (or some other client like VS Code) installed and working properly. We are ready to connect to the instance of SQL Server, right?
Wrong!
We need to complete the installation of SQL Server on the Linux server. That’s right, the current Azure image of Red Hat Enterprise Linux doesn’t come with SQL Server already running. So that means we need to put our hands on a command line.
I am going to open up Terminal on my iMac and use SSH to connect to the Linux server (yeah, that’s right, I’m doing all of my work with SQL Server using my iMac and somewhere Steve Jobs is smiling, I know it):
ssh sqlonlinux.cloudapp.net
Followed by the install command (the SQL Server bits are in the /opt/mssql/bin directory, and don’t forget sqlservr is missing the second ‘e’):
sudo ../../opt/mssql/bin/sqlservr-setup
We will accept the license terms, set a strong ‘sa’ password, and let the install complete. You should then see something like this:
Notice how we were prompted to start the service (well, it’s really a process, but whatevs) and also prompted to enable SQL Server to start upon a reboot. That’s a nice touch for folks that may not be Linux admins and aren’t aware that installing programs on Linux often requires 87 steps to make certain everything is up and running. I will then verify that the process is running:
ps aux | grep sql
Connecting to SQL Server vNext on Red Hat Enterprise Linux in Azure
With the instance running I can now use whatever client tool I choose and connect…
Oops. Looks like we got an error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
That’s because there’s just one more thing that we need to do here. We need to open up and endpoint for this VM in the Azure portal.
I’ve also chosen some bad things here. I would advise you don’t name your endpoint ‘MSSQL’, and you probably want a public port that isn’t 1433. But this is a trivial example I am doing for you (don’t do this for production, please). Also worth noting is that I could have configured the endpoint during the creation of the VM (by default we are given one for SSH as shown here):
Typically on a Windows VM here is another step to take and that is allowing the port to connect through the Windows Firewall rules. I will leave it as an exercise for the reader as to why we don’t need to worry about the Windows Firewall for our Linux server.
Once the endpoint is saved and applied to the VM we should be able to connect without any issue. This is what I get when I connect through Visual Studio Code:
And this is what it looks like when I connect with SSMS:
A few things worth noting in that image. First, the SQL Agent is shown as ‘disabled’, but there is no SQL Agent service for you to enable. Another thing is that Polybase is not available, and neither are Availability Groups. Here’s a link to the current release notes that talks about the features that are not supported. But what I think is most important to notice is this:
WE ARE RUNNING SQL SERVER ON FRAKKIN’ LINUX!
Summary
To me, the whole premise of SQL Server on Linux went from zero to Ludicrous Speed in just a few months in 2016. I’m not sure what more to say than that. I’ve shown you how easy it is to get started using it for yourself. Here are some useful links for you:
The (current) complete documentation for SQL Server on Linux
Here’s a lab for SQL admins to learn about Linux
Here’s a lab for Linux admins to know more about SQL
I will do my best to update this page as more SQL-Server-on-Linux goodness is announced. Until then, have fun exploring SQL Server on Linux!
I don’t see the option to add an endpoint from the Azure portal. Additionally, using the following commands shows me that port 1433 is already enabled:
sudo firewall-cmd –zone=public –add-port=1433/tcp –permanent
sudo firewall-cmd –reload
When I try to connect to my instance from my windows machine running SQL Server Data tools, I get the error message you show above. I am just using the ip address for my server name. Do I need to include the port number in the server name, and if so, what is the syntax?
I figured it out: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-azure-virtual-machine
You have to add an inbound rule in the Network Security Group which allows traffic on the port on which SQL Server listens (default of 1433)
Awesome, glad you got it working!