Prerequisites

• Microsoft Azure account;
• SSH terminal.

How to Deploy Azure Linux Virtual Machine with SQL Server

The following is a step-by-step procedure.

Step 0. Virtual Machine Selection

Get into your Microsoft Azure account.

Click on “New” as shown above, then see a menu as shown below.

As shown above click on “Compute” section and look for “SQL Server Linux”.

You should see a list of available SQL servers, as shown below (it may be a long list, so be patient).

Click on “Free SQL Server License: SQL Server 2017 Developer on Ubuntu Server 16.04 LTS“.

You should see something like the following:

Change “Resource Manager” to “Classic” (see the red arrow that points out to the place on the picture above), this is just to make your life a little bit easier.

To finish the preparations click on the “Create” button meaning “Create Azure SQL Server 2017 Developer on Ubuntu” and start the following procedure of creation.

Step 1. Starting the Creation Procedure with Basics

Fill in the fields Name, User name, Password, etc.

Step 2. Choosing the Size of Your Linux Virtual Machine

Choose the size of your SQL server on Ubuntu.

Pricing here is not for the case, it is for other cases.

Click on “Select” button.

Step 3. Configure Optional Features

See the parameters there and change them, if you want, then click on “OK” button.

Step 4. See the Summary and check the options, names, etc

Click on “OK” button.

Note: refreshing the screen sometimes helps when you wait too long for finishing the procedure

Now you should have Microsoft Azure SQL server on Ubuntu at your disposal (see the Overview of the SQL server on Ubuntu below).

How to Connect to Azure SQL Server on Linux (Ubuntu)

Connect SSH Terminal to Ubuntu Server (Ubuntu Virtual Machine)

Take parameters for the SSH terminal connection to Linux (Ubuntu) server from the parameters list of your Ubuntu virtual machine (Ubuntu server) created according to the creation procedure described above.

For example, take “DNS name” from your Azure “SQL Server on Ubuntu” screen shown at the very last picture of the creation procedure and copy it to the “Host” field in the Xshell New Session Properties window, if you use Xshell terminal.

 Configure SQL Server on Ubuntu using the SSH Terminal

Type the following Microsoft instructions into your SSH terminal window:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

sudo apt-get update

After the last command (“update”) there should be a long list like the following:

Continue typing:

sudo apt-get install -y mssql-server

sudo /opt/mssql/bin/mssql-conf setup

systemctl status mssql-server

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"

sudo apt-get update

After entering the last command (“update”) there should be a long list similar to one from the above black-and-white output window.

Continue typing:

sudo apt-get install -y mssql-tools unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

sudo /opt/mssql/bin/mssql-conf setup

After entering the last (“setup”) command from the list you will be asked for two things.

The first thing: choose an edition of SQL server.

Choose your option (Developer for this case).

The second thing: enter the SQL Server system administrator password.

Type in the password (you will use it later).

Note: although it doesn’t say it explicitly, the password here must contain both uppercase and lowercase letters, numbers, and other (non-alphanumeric) symbols, 8 characters is the minimal length for the password. 

Now the connection to the SQL server on Ubuntu should work.

Check its version just in case to make sure that the server is really up and running.

SELECT @@VERSION

As a result, you should see a text like this:

From now on the SQL server on Linux (Ubuntu) should be ready to use. 

Test the SQL Server on Linux (Ubuntu)

Use sqlcmd command-line tool to use Microsoft Azure SQL Server on Linux (Ubuntu).

Note: sqlcmd tool has been installed during configuring the SQL Server. 

sqlcmd -S localhost -U SA -P '<SQL Server system administrator password>'

Note: <SQL Server system administrator password> in the line above is the same “SQL Server system administrator password” that you defined above when answering about “the second thing”. 

If successful, you should get to a sqlcmd command prompt from the Microsoft Azure SQL Server on Ubuntu:

1>

Note: the prompt changes (incrementing) each time after entering a command, and the counting (incrementing) stops after entering “GO” (after that the counting starts from “1>” again). 

Microsoft site contains a set of instructions any user can use for testing purposes, let’s use it:

CREATE DATABASE TestDB

SELECT Name from sys.Databases

GO

After this you should see a text like this:

Let’s proceed further:

USE TestDB

CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)

INSERT INTO Inventory VALUES (1, 'banana', 150);

INSERT INTO Inventory VALUES (2, 'orange', 154);

SELECT * FROM Inventory WHERE quantity > 152;

GO

Note: DON’T FORGET ABOUT “GO” AT THE END.

As a result, you should see a text like this:

That’s it

Have a good time using the Microsoft Azure SQL Server on Linux (Ubuntu)

At the beginning of 2016, the IT giant Microsoft announced their plans to introduce MS SQL Server on Linux.

Currently, the company is taking full advantage of the spheres where Linux is among the top of the industry, including the technologies that power the cloud. So making SQL Server available in Linux is perfectly in line with the company’s current strategy.

The thing is that the preview version packages are already available for Red Hat Enterprise Linux 7 and Ubuntu Server 16.04 64 bits (unfortunately no 32-bit version is available), so at this point, Linux system administrators might want to start learning how to install, maintain, and use MS SQL Server on Linux.

The only thing to consider before installing MS SQL Server on Linux is that the preview version requires having at least 3.25 GB of RAM  and if you have less then the installation will simply issue an error.

How to Install MS SQL Server on Linux

RHEL / CentOS

For starters, you need to add two repositories to software sources list.

1. Paste the following lines into /etc/yum.repos.d/sql-server.repo:

[packages-microsoft-com-mssql-server] 
name=packages-microsoft-com-mssql-server 
baseurl=https://packages.microsoft.com/rhel/7/mssql-server/ 
enabled=1 
gpgcheck=1 
gpgkey=https://packages.microsoft.com/keys/microsoft.asc

To install the MS SQL Server command-line tools, create /etc/yum.repos.d/msprod.repo with the following script:

[packages-microsoft-com-prod] 
name=packages-microsoft-com-prod 
baseurl=https://packages.microsoft.com/rhel/7/prod/ 
enabled=1 
gpgcheck=1 
gpgkey=https://packages.microsoft.com/keys/microsoft.asc

2. Once you’ve completed step 1 above, install the packages using yum package manager:

# yum install -y mssql-server mssql-tools

Once the installation is complete, you will be notified to run the configuration script (/opt/mssql/bin/sqlservr-setup), accept the license terms, set the password for the SA user, and start the service. Moreover, there’s an option to enable it to start automatically on boot.

3. Now, open port 1433/tcp on your firewall to allow external clients to communicate with the database server:

For those who use firewalld:

# firewall-cmd --add-port=1433/tcp --permanent
# firewall-cmd --reload

For those who use iptables:

# iptables -A INPUT -p tcp --dport 1433 -j ACCEPT
# iptables-save > /etc/sysconfig/iptables

Ubuntu

You need to have at least Ubuntu 16.04 or you will face unmet dependencies problems.

1. To make Ubuntu trust the packages from the MS SQL Server repositories, import the GPG keys:

$ sudo sh -c "curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -"

make sure that you have curl installed, you can install it by running

sudo apt-get install curl

2. Then add the repositories to /etc/apt/sources.list.d/sql-server.list:

$ sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial main > /etc/apt/sources.list.d/sql-server.list"
$ sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main >> /etc/apt/sources.list.d/sql-server.list"

3. After that resynchronize the package index files and update the core package and additional tools:

$ sudo apt-get update
$ sudo apt-get install mssql-server mssql-tools -y --allow-unauthenticated

During the process of installation, you will be asked for accepting license terms, answer “Yes” if you have no problems with it

4. After the installation is finished, you need to run the configuration script:

$ sudo /opt/mssql/bin/mssql-conf setup

When you are prompted to accept the license terms choose “Yes”.

Then you will be prompted to create the new SQL Server system administrator password. It should be at least 8 characters long and contain uppercase and lowercase letters, numbers and non-alphanumeric characters.

5. Now it’s time to verify that the service is running:

systemctl status mssql-server

if everything is ok you’ll get something like this:

● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor pre
Active: active (running) since Wed 2017-03-01 14:59:15 GMT; 3min 29s ago
Main PID: 827 (sqlservr)
Tasks: 102
CGroup: /system.slice/mssql-server.service
├─ 827 /opt/mssql/bin/sqlservr
└─1284 /opt/mssql/bin/sqlservr

How to Connect to MS SQL Server on Linux

In order to execute SQL commands, you need to run the sqlcmd client.  You can do it using the following command (replace YOUR_PASSWORD with the one you specified during the package installation):

$ sqlcmd -S localhost -U SA -P 'YOUR_PASSWORD'

If you got ‘command not found’ error read this. Otherwise, you should see the sqlcmd’s prompt. Let’s execute a simple command that will tell us the SQL Server’s version:

1> SELECT @@VERSION
2> GO

If you see something like this:

Microsoft SQL Server vNext (CTP1.3) - 14.0.304.138 (X64) 
 Feb 13 2017 16:49:12 
 Copyright (C) 2016 Microsoft Corporation. All rights reserved.
 on Linux (Ubuntu 16.10)

then you have successfully installed and connected to you SQL Server on Linux!

Bottom Line

In this ‘how to’ article we have explored Microsoft SQL Server installation process on RHEL / CentOS and Ubuntu Server.

Tip for Linux system administrators: now that Microsoft and Linux are becoming closer you will need to learn more about MS SQL Server to make sure that you are well informed and aware of all the nuances.

It was also announced that by mid-2017 Linux will get the same SQL Server editions as are offered today on Windows: Enterprise, Standard, Web, Express, and Developer. Despite the fact that Express and Developer are free, only the Express edition will be licensed for production use with resource limitations.

We appreciate your feedback! If you have any questions or concerns, or just want to share some info with us, please feel free to comment on this article.