How to Install and Configure MS SQL (Beta) on Ubuntu 16.04

WebsiteInternetWeb Services

In this article, we will learn how to Install and Configure MS SQL on Ubuntu, Microsoft has recently announced that they are planning to release the MS SQL for Linux. Specially beta versions of Red Hat Enterprises Linux, Centos &, Ubuntu but only with 64-bit editions.

Prerequisites

  • A Machine with a min of 4 GB RAM and 30 GB Hard disk space.
  • A Machine installed with Ubuntu 16.04.
  • A user with root privileges or root user.

MS SQL Server installation on Ubuntu

As the MS SQL is not available in the default Ubuntu repository we needed to add the MS SSQL repository details to the local machine repository.

We needed to add the GPG Key to the local machine

$ sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 983 100 983 0 0 253 0 0:00:03 0:00:03 --:--:-- 253
OK

Then we will add the Microsoft repository to the local Ubuntu machine

$ sudo curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 87 100 87 0 0 95 0 --:--:-- --:--:-- --:--:-- 95
deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial main

We needed to run the update to get the repository to the local machine

$ sudo apt-get update

Once the repository is updated, we will run the below command to install the MS-SQL server

$ sudo apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
gcc-5-base gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6 libc6-dbg
libcc1-0 libcurl3 libjemalloc1 libpython-stdlib libpython2.7-minimal
libpython2.7-stdlib libstdc++6 libunwind8 python python-minimal python2.7
python2.7-minimal
Suggested packages:
..
..
Need to get 158 MB of archives.
After this operation, 734 MB of additional disk space will be used.
Get:1 http://in.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libpython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [339 kB]
Get:2 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd64 mssql-server
amd64 14.0.1.246-6 [144 MB]
..
..
..
14 not fully installed or removed.
Need to get 0 B/144 MB of archives.
After this operation, 681 MB of additional disk space will be used.
Preconfiguring packages ...
(Reading database ... 60605 files and directories currently installed.)
Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
Unpacking mssql-server (14.0.1.246-6) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
…
..
Setting up libc++1:amd64 (3.7.0-1) ...
Setting up libc6-dbg:amd64 (2.23-0ubuntu4) ...
Setting up libjemalloc1 (3.6.0-9ubuntu1) ...
Setting up mssql-server (14.0.1.246-6) ...
+-------------------------------------------------------------------+
| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
| Microsoft(R) SQL Server(R). |
+-------------------------------------------------------------------+
Processing triggers for libc-bin (2.23-0ubuntu3) ...

Configuring the MS SQL SA Account

Once the installation is completed, we will run sqlservr-setup script to setup password of the SA; we needed to give a strong password for the SA account (with a min of 8 characters, lower and upper case letters with numbers from 1 to 10 or any non-alpha numeric characters)

# sudo /opt/mssql/bin/sqlservr-setup
Microsoft(R) SQL Server(R) Setup
You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended mode.
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388 and found
in /usr/share/doc/mssql-server/LICENSE.TXT.
Do you accept the license terms? If so, please type "YES": YES
Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account: The passwords specified do not match. Please try again.
Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:
Setting system administrator (SA) account password...
Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /lib/systemd/system/mssql-server-telemetry.service.

Setup completed Successfully

Once the configuration is done, we will restart the MS SQL services and will see the status of the services with the below command

Restart the services

# systemctl start mssql-server

See the status of the services

# systemctl status mssql-server
mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2016-12-06 23:13:01 IST; 52s ago
Main PID: 6173 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─6173 /opt/mssql/bin/sqlservr
└─6190 /opt/mssql/bin/sqlservr
Dec 06 23:13:28 ubuntu16 sqlservr[6173]: [45B blob data]
..
..
Dec 06 23:13:28 ubuntu16 sqlservr[6173]: [124B blob data]
Dec 06 23:13:36 ubuntu16 systemd[1]: Started Microsoft(R) SQL Server(R) Database Engine.

Installing MS SQL Client Tools

Install and update the GPG key and we will update the repository to the local machine.

# curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 983 100 983 0 0 282 0 0:00:03 0:00:03 --:--:-- 282
OK

Once GPG key is updated we will update the local repository with the Microsoft repository.

$ sudo curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- 0:00:01 --:--:-- 0
100 79 100 79 0 0 67 0 0:00:01 0:00:01 --:--:-- 67
deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main$

Once the repository is installed on the local machine, we can use the below command to install the MS SQL tools with the below command

# sudo apt-get install mssql-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
..
..
Selecting previously unselected package mssql-tools.
(Reading database ... 66292 files and directories currently installed.)
Preparing to unpack .../mssql-tools_14.0.1.246-1_amd64.deb ...
Unpacking mssql-tools (14.0.1.246-1) ...
Setting up mssql-tools (14.0.1.246-1) ...

Connecting to SQL Server on Linux

Once the tools installed on the machine, we will test, below is the command and general syntax to connect to the MS SQL server

Syntax:

# sqlcmd –S IP-ADDRESS –U SA –P ‘<YOUR SYSTEM PASSWORD’
# sqlcmd sqlcmd -S localhost -U SA -P Test@1234
1>CREATE DATABASE TEST1;
2>GO;
3>quit

For example, we will execute some of these query returns the name of all of the databases.

SELECT Name from sys.Databases;
GO

Creating a database using the SQL Server default settings.

CREATE DATABASE inventdb;
GO

In the above article we have learned how to install MS SQL Server on Ubuntu 16.04, MS SQL command line tools to connect to the server also we have learned how to change or setup the SA account password for the MS SQL and install the client tools and execute the quries.

raja
Published on 23-Jan-2020 12:25:00
Advertisements