SQL 2016 on Linux OS

Since SQL version 2016, customer can decide platform other than windows OS. SQL 2016 can be installed and configured on Linux OS and it’s completely supported. In my opinion the decision of allowing Linux platform is to compete with open source database engine such as MySQL, PostgreSQL and ORACLE those provide similar capabilities. The licensing & editions are still not clear but the platform provide most of features a DBA will look for.

Supported Linux Operating System

Red Hat Enterprise Linux 7.3 Workstation, Server, and Desktop
SUSE Enterprise Linux Server v12 SP2
Ubuntu 16.04LTS and 16.10
Docker Engine 1.8+ on Windows, Mac, or Linux
Operating system should minimum 3.25 GB of RAM and it’s tested up to 1 TB.

UN-Supported Features

Poly-base, stretched DB
System extended stored procedures (XP_CMDShell, etc.)
Windows/AD authentication
User certificate SSL/TLS
Agent service / Brower / Reporting service
More Details

Installation demo of SQL on Ubuntu 16.04 Server

Configure prerequisite

Step 1 Download Ubuntu server ISO

Step 2 Create a new Linux OS in your virtual environment. You should have atleast 4 GB RAM &  20 GB disk space (Recommend  Test environment). Supply ‘User Name’ & ‘Password’ that will be used to login in terminal (command prompt of Linux), start the installation.

Step 3. Installation will continue without any user intervention.

Step 4. Once installed successfully, supply user name / password (Refer Step 2) to login in terminal.

Step 5. Get yourself familiarize with Linux Ubuntu commands.
Get the OS details.
IP address of machine
Hostname of machine
Check connectivity with internet using ‘ping’

lsb_release –a
ping help.ubuntu.com

If you wish to change IP address then refer

Step 6. Install the Open SSH client applications on Ubuntu server so  you will be able to take terminal access remotely using putty  and it will help to insert commands easily.

sudo apt-get install openssh-server openssh-client


(All above steps are shown in following slide show)

This slideshow requires JavaScript.

Installation & configuration of SQL Server

Step 1 Ensure your system is fully updated before installing SQL.

sudo apt-get update

Step 2. Install curl (curl is a tool to transfer data from OR to a server using supported protocol (http,imap, ldap…etc)

sudo su
sudo apt-get install curl

STEP 3 Add ‘SQL Server’ information to the repository on ‘/etc/apt/sources.list.d/’

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list >/etc/apt/sources.list.d/mssql-server.list
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add

STEP 4 Install SQL server.

sudo apt-get install mssql-server

STEP 5 Run the configuration script to accept the license agreement and provide the System Administrator (SA) password.

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

STEP 6 SQL service will be enable/start by default. If it does not then enable and start the service. Also verify if SQL is listening on port 1433

sudo systemctl enable mssql-server
sudo systemctl start mssql-server
sudo netstat -peant | grep ":1433"

STEP 7 The SQL server is ready, DBA now can access SQL server using ADO.NET client OR SQL Management Studio installed on Windows machine for Admin purpose.

(All above steps are shown in following slide show)

This slideshow requires JavaScript.

Installation of SQL Server command-line tools (SQLCMD)

Step1 Download SQLCMD tool and ODBC Drivers..

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list>/etc/apt/sources.list.d/ms-prod.list
sudo apt-get update

Step 2 Install SQLCMD tool and ODBC Drivers.

sudo apt-get install mssql-tools

Accept license agreement.
Step 3 Create symlink for SQLCMD and

ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd

Step 4 Connect SQL server using SQLCMD then create database, table & inserts few rows.

sqlcmd -S -U SA -P Password123#

Use [master]
create database LinuxDBTest
use LinuxDBTest
create table t1 (c1 int, c2 varchar(100))
insert into t1 values (1, 'pradeep')
select * from t1

Step 5 Verify DB, Tables & row using SSMS.

This slideshow requires JavaScript.

#linux, #sqlonlinux