By: Manvendra Singh | Comments (1) | Related: > SQL Server on Linux
Problem
Microsoft announced support for SQL Server on Linux. In a previous tip I explained the step by step method to install SQL Server vNext on Red Hat Linux 7.2. In this tip I will explain how to install SQL Server on an Ubuntu server 16.04 version.
Solution
Microsoft released its first public preview of SQL Server vNext for Linux on 16th November 2016. The next release of SQL Server brings the power of SQL Server to Linux. Now you can develop applications with SQL Server on Linux, Windows, Ubuntu, or Docker and deploy them as well on these platforms.
SQL Server on Linux includes all the standard SQL Server management features that come with the Windows version. These include advanced features like always-encrypted, row-level security and columnstore indexes, but there are a lot of features that are not supported yet in this preview like SQL Server Agent, Replication, AlwaysON, etc. You can get the full list of supported features in this Microsoft link SQL Server on Linux Release Notes. You can also get a list of known issues of this version of SQL Server in this link.
SQL Server Installation on Ubuntu Server
Before moving forward, you need to have a box running with Ubuntu server on which you will install SQL Server. I have downloaded the free version of Ubuntu server from their official website to install it on virtual machine on my lab server which is running Windows Server 2012 R2. I have not mentioned the steps of Ubuntu server installation as it is not in scope of this article.
I followed the step by step method of the Microsoft online documentation to understand this installation. Now let's start the SQL Server installation on Ubuntu.
Step 1: First, connect to the Ubuntu server either using PuTTY or directly by logging into the Ubuntu server using a valid username and password. I used PuTTY to connect to this server. I got some network errors while making the connection through PuTTY to the Ubuntu server. After spending some time on Google, I found out that we need to install the secure shell application openssh on the Ubuntu server for making a remote connection. I kept all of the default configurations for this application and ran the below command to get it installed on the Ubuntu server.
#Install openssh-server application on Ubuntu for remote connection.susudo apt-get install openssh-server
Once you execute this command, a series of executions will start showing on your screen which was not possible to capture in a screenshot, so we can rerun the same command to check whether the openssh application was installed. We can see the output showing "openssh-server is already the newest version" which means the application was installed.
Now we can go ahead and make a remote connection using PuTTY. Make sure to have the IP address of the Ubuntu server to make a remote connection. If you don't know the IP address of your Ubuntu server you can run "ifconfig" to check the IP address as shown in the below screenshot.
Step 2: Now launch PuTTY and enter the IP address of the Ubuntu server and click on the open button to make a connection. Once a connection is established a black screen will appear on your screen asking you to enter your login name and password to get authentication. I have established a successful connection using my login name as shown in the below picture.
Step 3: Since the connection has been established to the Ubuntu server, now we will start the SQL Server installation process. Run the below command to import GPG keys which is used for users to communicate securely. GPG keys also provides cryptographic privacy and authentication. Now we will run a curl command to import the keys from the Microsoft website to this Ubuntu server.
#import the GPG keyscurl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
We can see that curl command is not installed on this machine so we need to run the suggested command to install the curl command first.
Run below command which is suggested above to install curl.
#install curlsusudo apt install curl
Once you press the enter button it will ask you to confirm whether to install this command or not by typing Y or n. I entered Y to proceed and the download process of the package has started. You can see the progress bar percentage below.
Once the download is completed, installation of curl will start automatically. We can see curl is installed on this machine in the below picture.
Again we will run the following curl command to import the keys from the Microsoft website to this Ubuntu server.
#import the GPG keyscurl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
We can see the command executed successfully in the below picture, so now let's go ahead with next step to download the SQL Server Ubuntu Repository.
Step 4: Run the below curl command to download the SQL Server Ubuntu Repository which will be used to access packages while installing SQL Server.
#download 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
Once the above command executes we can check the location /etc/apt/sources.list.d/ to verify whether the above file is saved in this location. Run the ls -lrt command to display all the files present in the folder as shown below.
You can see our target file mssql-server.list file is in this location in the above picture. Now our next step is to install SQL Server on this box.
Step 5: First we will run the below command to fetch a list of available updates for this Ubuntu server.
sudo apt-get update
The command apt-get update downloads the package lists from the repositories and "updates" them to get information on the newest versions of packages and their dependencies.
Once the above command executes successfully, the next step is to install SQL Server on Ubuntu. Run the below command to kick off the SQL Server installation on this machine.
#Install SQL Server vNext sudo apt-get install -y mssql-server
Once you press enter to execute the above command, the execution process will start with downloading a series of required packages as shown below.
You can see the progress bar is increasing as compared to the above picture. The package count is also increasing.
Installation of these packages will start automatically after downloading completes. You can see installation is complete in the below screenshot.
You can see there is an instruction shown in the dashed-rectangle in the above screenshot saying "Please run /opt/mssql/bin/sqlservr-setup to complete the setup of Microsoft(R) SQL Server(R)". So our next step is to run this file to complete the installation.
Before executing the suggested file to complete the installation, let's check the status of the SQL Server service installed on this machine. Run the below command to check the status of the mssql-server service.
#check the status of mssql-server service. systemctl status mssql-server
We can see that mssql-server service is installed, but it is not active and its status is showing disabled in the below picture. We will run this command again to check the mssql-server service status post installing the configuration files.
Step 6: This is the last step to install and configure SQL Server on Ubuntu. Here we will run the above suggested file in this step. This is kind of post install activity to configure our SQL Server like setting the sa password, accepting the license agreement and starting the SQL Server service.
sudo /opt/mssql/bin/sqlservr-setup
Once you execute the above command, it will ask you to accept the license terms and conditions by typing YES on the below prompt.
Once you type YES to accept the license terms and press Enter to proceed, it will ask you to enter strong password for the sa account. Enter the sa password and then re-enter again.
Once the sa password is set the configuration file will ask you to start the SQL Server service by choosing Y to start or N to not start the service. Type y and press Enter to start the SQL Server service.
Step 7: Now check the SQL Server service mssql-server to verify whether SQL Server is up and running.
#check the status of mssql-server service. systemctl status mssql-server
The SQL Server service status is showing enabled and active (running) which is highlighted in green below.
Now you are done with your SQL Server installation on your Ubuntu server. You need the SQLCMD utility to connect to SQL Server from your Ubuntu-Linux server, but you can make successful database connection using SSMS from your Windows machine. I will explain how to install SQL Server tools like SQLCMD to access databases from Ubuntu in my next tip.
Step 8: We will connect to this SQL Server instance using SQL Server Management Studio installed on a Windows machine. First we need to get the IP address of this server through which we will use to connect to SQL Server. The IP address of this machine is 192.168.209.129 as we get see step number 1. Now launch SSMS on your Windows machine and click on the Connect button to get the window where we will put in the connection details. Enter the details like instance name as the IP address of the Ubuntu server, choose SQL Server authentication and enter the sa login and the password then press Connect.
We have been connected using the sa account to this instance as Windows authentication is not supported in this version of SQL Server on Ubuntu. Now open a new query window to execute a few commands to check the accessibility of this instance. I ran the below commands to check the SQL Server version and created a database named Manvendra.
--check SQL Server Version. SELECT @@VERSION --Create a db named Manvendra CREATE DATABASE Manvendra --Verify the newly created db Manvendra SELECT name FROM sysdatabases
We can also check the accessibility of database Manvendra by running a general query. I ran a SELECT command to get the name of all users for this database.
--Users in this db. USE Manvendra GO SELECT name FROM sysusers where gid=0
Next Steps
- The next step is to install SQL Server tools on the Ubuntu server to enable us to make database connections from Ubuntu. I will explain the step by step process of this installation in my next tip.
- Explore more knowledge on SQL Server Database Administration Tips.
- Read more tips about SQL Server on Linux
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
