- Windows 10 Fall Creators Update installation and features - Thu, Nov 2 2017
- Install Microsoft SQL Server on Ubuntu Linux - Thu, Jan 5 2017
- Use PowerShell with Google Cloud Platform - Thu, Dec 8 2016
According to Microsoft’s vNext team, they support the CTP 1.1 release of SQL Server vNext for Linux on Red Hat Enterprise 7.3 or Ubuntu 16.04LTS/16.10, and Docker Engine 1.8 on Windows, Mac, or Linux. The minimum requirements are an EXT4 file system and at least 3.25GB of memory. The full list of requirements and downloads is available here.
For now, the preview release is free. Final releases will include Enterprise, Standard, and Web versions, available for purchase. The Developer and Express editions, while limited in scope, will remain free to use.
The vNext SQL Server preview release still has a few limitations and unsupported features, such as AD Authentication, SQL Server Agent, full-text search, and user-provided SSL/TLS certificates. Because of the SQL Server Agent limitation, the SQL Server Management Studio (SSMS) currently does not support maintenance plans and the Management Data Warehouse. Microsoft is working on these features and should resolve them in the near future.
vNext SQL Server setup
We’re starting with a fresh install of Ubuntu 16.04LTS server, and we’ve included the OpenSSH server package in our OS. First, we want to make sure we have an SSH key and verify the server is up to date. We’ll generate an SSH key with the following command:
ssh-keygen
Next, perform an update to Ubuntu with the following commands at a bash prompt:
sudo apt-get update sudo apt-get upgrade sudo reboot
Next, we’ll import the public repository GPG keys.
sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
Your result should look like the following:
Import GPG keys
If you receive an error similar to “gpg: can't open `–': No such file or directory” the hyphen/dash at the end of the last command may be Unicode and not ASCII. It may help to type in the last character by hand instead of using copy/paste.
Now 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
Your result should look similar to the following:
Register the mssql repository
Update the sources list and then install SQL Server:
sudo apt-get update sudo apt-get install -y mssql-server
Your results should display the selecting, unpacking, and preparing of several prerequisites and .deb packages culminating with the following message:
Please run /opt/mssql/bin/sqlservr-setup to complete the setup of Microsoft(R) SQL Server(R).
So we’ll go ahead and run the command:
sudo /opt/mssql/bin/sqlservr-setup
We accept the license terms, enter the system administrator (SA) account password, confirm that we want to start the SQL Server service, enable SQL Server to start on boot, and our setup should complete successfully.
SQL server setup
We can confirm the SQL server service is running by issuing the command:
systemctl status mssql-server
mssql server status
As a side note, it’s curious that the command service mssql-server status also produces the same result as systemctl, but issuing the service -status-all command does not list mssql-server.
Install MSSQL tools
Next, we want to install mssql-tools. Do so with curl and the following command:
sudo curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
Update the sources list and run the installation command:
sudo apt-get update sudo apt-get install mssql-tools
Accept the license terms by hitting <yes> a couple times at the prompts. The installation of Microsoft SQL Server on Ubuntu is now complete. You can find information and documentation on the setup and requirements, as well as how to uninstall the vNext mssql-server here.
With our mssql-server running and our mssql-tools installed, we can now connect to the server any number of different ways including:
- sqlcmd on Linux
- sql-cli on MacOS
- Windows PowerShell
- SQL Server Data Tools (SSDT)
- SQL Server Management Studio (SSMS)
We’ll use the last method, SSMS, to connect to our server. First, download the SQL Server Management Studio version 17.0 here.
After installing SSMS, we will find the program available in our start menu:
SSMS start menu
Go ahead and start SSMS. The management studio will open and ask us to connect to a service. Change the Authentication drop-down to SQL Server Authentication, enter your SA username and password, enter the IP address or server name of your Ubuntu box, and click Connect.
SSMS connect to mssql server on Ubuntu
Our connection will complete, and we can now browse our server and begin to import data and run queries.
SSMS connected to Ubuntu mssql server
Wrap–Up
The porting of SQL Server to Linux is a huge move for Microsoft, demonstrating that Linux is a crucial part of its future. While Microsoft SQL Server won’t be open source and will come with licensing, hopefully SQL Server on Linux will have less complex licensing than Oracle. Additionally, as more databases move to the cloud, the ability to run SQL Server on Linux will make Microsoft more relevant, as possibly other Microsoft technologies such as SharePoint and Exchange may soon follow.
