Microsoft SQLServer on Linux
Introduction
These days Microsoft announced the preview of the first release of SQLServer on Red Hat Enterprise Linux 7.2 and Ubuntu Linux 16.04 (coming soon on SUSE Linux Enterprise Server v12 SP2.
It was in the air for a while, since Microsoft announced to work on porting and it’s clearly a move to undermine the Oracle market within the framework that rules.
In particular, let’s see in detail how to install SQLServer on Linux, to be exact on Red Hat distribution.
To do this test, I configured a virtual machine CentOS 7 with Virtual Box (deliberately omit to illustrate this step, as it is closely linked to the virtualizer used and, primarily, is not relevant to the subject matter of the article).
[root@sqlserver ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE=NAME="cpe:/o:centos:centos:/"
HOME_URL="https://www.centos.org"
BUG_REPORT_URL="https://bug.centos.org"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
[root@sqlserver ~]#Installing Microsoft SQLServer
To install and configure it, just folow steps you can read in the tutorial from Microsoft, except that one relating to the firewall, because I don’t want to log on from Windows clients using Microsoft Management Studio, which is free and can be downloaded here.
For beginners with Unix and Linux environments, I will try to clearly describe the steps, showing the entire output from the executed commands.
Repository
First of all, you have to configure the repository of Microsoft SQLServer.
[root@sqlserver ~]# curl https://packages.microsoft.com/config/rhel/7/mssql-serv
er.repo > /etc/yum.repos.d/mssql-server.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 220 100 220 0 0 96 0 0:00:02 0:00:02 --:--:-- 96
[root@sqlserver ~]#Packages install
Once run the above, we can download and install the packages.
[root@sqlserver ~]# yum install -y mssql-server
Loaded plugins: fastestmirror
packages-microsoft-com-mssql-server | 2.9 kB 00:00
packages-microsoft-com-mssql-server/primary_db | 2.4 kB 00:00
Loading mirror speeds from cached hostfile
* base: mi.mirror.gar.it
* extras: mi.mirror.gar.it
* updates: mi.mirror.gar.it
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.1.246-6 will be installed
--> Processing Dependency: bzip2 for package: mssql-server-14.0.1.246-6.x86_64
--> Processing Dependency: gdb for package: mssql-server-14.0.1.246-6.x86_64
--> Running transaction check
---> Package bzip2.x86_64 0:1.0.6-13.el7 will be installed
---> Package gdb.x86_64 0:7.6.1-8.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mssql-server x86_64 14.0.1.246-6 packages-microsoft-com-mssql-server 138 M
Installing for dependencies:
bzip2 x86_64 1.0.6-13.el7 base 52 k
gdb x86_64 7.6.1-80.el7 base 2.4 M
Transaction Summary
================================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 140 M
Installed size: 145 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/base/packages/bzip2-1.0.6-13.el7.x86_64.rpm: He
ader V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for bzip2-1.0.6-13.el7.x86_64.rpm is not installed
(1/3): bzip2-1.0.6-13.el7.x86_64.rpm | 52 kB 00:00
(2/3): gdb-7.6.1-80.el7.x86_64.rpm | 2.4 MB 00:03
warning: /var/cache/yum/x86_64/7/packages-microsoft-com-mssql-server/packages/ms
sql-server-14.0.1.246-6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be122
9cf: NOKEY
Public key for mssql-server-14.0.1.246-6.x86_64.rpm is not installed
(3/3): mssql-server-14.0.1.246-6.x86_64.rpm | 138 MB 03:04
--------------------------------------------------------------------------------
Total 778 kB/s | 140 MB 03:04
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
Userid : "Microsoft (Release signing) "
Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
From : https://packages.microsoft.com/keys/microsoft.asc
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) "
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
Package : centos-release-7-2.1511.el7.centos.2.10.x86_64 (@anaconda)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : bzip2-1.0.6-13.el7.x86_64 1/3
Installing : gdb-7.6.1-80.el7.x86_64 2/3
Installing : mssql-server-14.0.1.246-6.x86_64 3/3
+-------------------------------------------------------------------+
| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
| Microsoft(R) SQL Server(R). |
+-------------------------------------------------------------------+
Verifying : mssql-server-14.0.1.246-6.x86_64 1/3
Verifying : gdb-7.6.1-80.el7.x86_64 2/3
Verifying : bzip2-1.0.6-13.el7.x86_64 3/3
Installed:
mssql-server.x86_64 0:14.0.1.246-6
Dependency Installed:
bzip2.x86_64 0:1.0.6-13.el7 gdb.x86_64 0:7.6.1-80.el7
Complete!
[root@sqlserver ~]# Please note how the installation perform a dependency check and provides for downloading the necessary packages.
SQLServer setup
After installation, proceed to configure Microsoft SQL Server.
Just accept the license agreement and passwords setup .
[root@sqlserver ~]# /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) acount:
Settimg 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.se
rvice to /usr/lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-te
lemetry.service to /usr/lib/systemd/system/mssql-server-telemetry.service.
Setup completed successfully.
[root@sqlserver ~]#Installation check
Once all packages have been installed, go and check that Microsoft SQLServer runs properly.
[root@sqlserver ~]# systemctl status mssql-server -l
mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service: enabled: vendor
preset: disabled)
Active: active (running) since Fri 2916-12-01 00:50:54 CET: 21min ago
Main PID: 2172 (sqlserver)
CGroup: /system.slice/mysl-sqlserver,service
˫- 837 /opt/mssql/bin/sqlserver
˪-2180 /ops/mysql/bin/sqlserver
Dec 01 00:51:22 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:22.86 spid
6s Polybase feature disabled.
Dec 01 00:51:25 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:22.86 spid
6s Cleaning tempdb database.
Dec 01 00:51:25 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:24.18 spid
6s Starting up database 'tempdb'.
Dec 01 00:51:25 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:25.52 spid
6s The tempdb database has 1 data ile(s).
Dec 01 00:51:25 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:25.55 spid
20s The Service Broker endpoint is in disabled or stopped state.
Dec 01 00:51:25 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:25.55 spid
20s The Database Mirroring endpoint is in disabled orstopped state.
Dec 01 00:51:25 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:25.80 spid
20s Service Broker manager has started.
Dec 01 00:51:25 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:51:25.81 spid
5s Recovery is complete. This is an informational message only. No user act
ion is required
Dec 01 00:58:08 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:58:08.26 spid
51 Attempting to load library 'xplog70.dll' into memory. This is an informa
tional message only. No user action is required.
Dec 01 00:58:08 sqlserver.localdomain sqlservr[837]: 2016-11-30 23:58:08.26 spid
51 Using 'xplog70.dll' version '2016.140.01' to execute extended stored pro
cedure 'xp_msver'. This is an informational message only: no user action is requ
ired.
[root@sqlserver ~]#A .dll on Linux? Sounds strange.
Just for explanation, the .dll dynamic libraries are typical of Windows platforms, while on Linux you can find “.so” extention (or “.so.n”, where “n” is a number, like “.so.5” ).
While trying to perform a simple search
[root@sqlserver ~]# strings /opt/mssql/bin/sqlservr > results.txt
[root@sqlserver ~]#we find no trace of that name, however, we can find some interesting strings:
/binn/sqlservr.exe
/var/opt/mssql/.system
/var/opt/mssql/log
/proc/sys/kernel/random/uuid
/proc/self/coredump_filterDuring Microsoft SQLServer setup there is no need to change some settings because they are written inside the executable.
Please note the wrong path (“/bin” is the right one) and the typical extension of programs running on Windows.
Installing tools
The client needed for connection, called sqlcmd, is located in another repository.
Of course, I’m talking about the command line client, but remember that you can still use the Manamenet Studio, following the steps that Microsoft provides in this page.
[root@sqlserver ~]# curl https://packages.microsoft.com/config/rhel/7/prod.repo
> /etc/yum.repos.d/msprod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 185 0 0:00:01 0:00:01 --:--:-- 185
[root@sqlserver ~]# yum install mssql-tools
Loaded plugins: fastestmirror
base | 3.6 kB 00:00
extras | 3.4 kB 00:00
packages-microsoft-com-mssql-server | 2.9 kB 00:00
packages-microsoft-com-prod | 2.9 kB 00:00
updates | 3.4 kB 00:00
packages-microsoft-com-prod/primary_db | 4.6 kB 00:05
Loading mirror speeds from cached hostfile
* base: mi.mirror.garr.it
* extras: mi.mirror.garr.it
* updates: mi.mirror.garr.it
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:14.0.1.246-1 will be installed
--> Processing Dependency: msodbcsql for package: mssql-tools-14.0.1.246-1.x86_6
4
--> Running transaction check
---> Package msodbcsql.x86_64 0:13:0.1.0-1 will be installed
--> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql-13.0
.1.0-1.x86_64
--> Running transaction check
---> Package unixODBC-utf16.x86_64 0:2.3.1-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mssql-tools x86_64 14.0.1.246-1 packages-microsoft-com-prod 249 k
Installing for dependencies:
msodbcsql x86_64 13.0.1.0-1 packages-microsoft-com-prod 3.8 M
unixODBC-utf16 x86_64 2.3.1-1 packages-microsoft-com-prod 329 k
Transaction Summary
================================================================================
Install 1 Package (+2 Dependent packages)
Total dowload size: 4.4 M
Installed size: 4.4 M
Is this ok [y/d/N]: y
Dowloading packages:
(1/3): mssql-tools-14.0.1.246-1.x86_64.rpm | 249 kB 00:01
(2/3): unixODBC-utf16-2.3.1-1.x86_64.rpm | 329 kB 00:00
(3/3): msodbcsql-13.0.1.0-1.x86_64.rpm | 3.8 MB 00:07
--------------------------------------------------------------------------------
Total 639 kB/s | 4.4 MB 00:07
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : unixODBC-utf16-2.3.1-1.x86_64 1/3
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746838 and frond in
/usr/share/doc/msodbcsql/LICENSE.TXT . By entering 'YES',
you indicate that you accept the license terms.
Do you accept the licenseterms? (Enter YES or NO)
YES
Installing : msodbcsql-13.0.1.0-1.x86_64 2/3
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and frond in
/usr/share/doc/mssql-tools/LICENSE.TXT . By entering 'YES',
you indicate that you accept the license terms.
Do you accept the licenseterms? (Enter YES or NO)
YES
Installing : mssql-tools-14.0.1.246-1.x86_64 3/3
Verifying : msodbcsql-13.0.1.0-1.x86_64 1/3
Verifying : unixODBC-utf16-2.3.1-1.x86_64 2/3
Verifying : mssql-tools-14.0.1.246-1.x86_64 3/3
Installed:
mssql-tools.x86_64 0:14.0.1.246-1
Dependency Installed:
msodbcsql.x86_64 0:13.0.1.0-1 unixODBC-utf16.x86_64 0:2.3.1-1
Complete!
[root@sqlserver ~]#Operational test
Now you have all tools you need, so just log on and move your first steps with Microsoft SQL Server on Linux.
[root@sqlserver ~]# sqlcmd -S localhost -U sa
Password:
1> select @@version
2> go
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (CentOS Linux 7 (Core))
(1 rows affected)
1> select name from sysdatabases order by name
2> go
name
--------------------------------------------------------------------------------
------------------------------------------------
master
model
msdb
tempdb
(4 rows affected)
1> CREATE DATABASE test;
2> GO
1> USE test;
2> GO
Changed database context to 'test'.
1> CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);
2> GO
1> INSERT INTO inventory VALUES (1, 'banana', 150);
2> INSERT INTO inventory VALUES (2, 'orange', 154);
3> GO
(1 rows affected)
(1 rows affected)
1> SELECT * FROM inventory WHERE quantity > 152;
2> GO
id name quantity
----------- -------------------------------------------------- -----------
2 orange 154
(1 rows affected)
1> After this first exercise suggested by Microsoft, let’s explore for a few seconds your database you’ve just created.
1> USE master;
2> GO
Changed database context to 'master'.
1> select [Type] = CONVERT(VARCHAR(10), type_desc),
2> [Path] = CONVERT(VARCHAR(40), physical_name),
3> [MB] = (size*8)/1024
4> FROM sys.master_files
5> WHERE DB_NAME(database_id) = 'test';
6> GO
Type Path MB
---------- ---------------------------------------- -----------
ROWS C:\var\opt\mssql\data\test.mdf 8
LOG C:\var\opt\mssql\data\test_log.ldf 8
(2 rows affected)
1> Please note the “Windows style” path.
For those who are not accostumed to *nix, the path doesn’t include the name of the device drive (“C:” in this case) and shows the slash (“/”) instead of the back slash (“\”).
1> sp_detach_db 'test';
2> GO
1> select [Name] = CONVERT(VARCHAR(20), name) from sysdatabases order by name;
2> GO
Name
--------------------
master
model
msdb
tempdb
(4 rows affected)
1> select [Type] = CONVERT(VARCHAR(10), type_desc),
2> [Path] = CONVERT(VARCHAR(40), physical_name),
3> [MB] = (size*8)/1024
4> FROM sys.master_files
5> WHERE DB_NAME(database_id) = 'test';
6> GO
Type Path MB
---------- ---------------------------------------- -----------
ROWS /var/opt/mssql/data/test.mdf 8
LOG /var/opt/mssql/data/test_log.ldf 8
(2 rows affected)
1> sp_detach_db 'test';
2> go
1> sp_attach_db 'test','C:\var\opt\mssql\data\test.mdf','C:\var\opt\mssql\data\test_log.ldf';
2> go
1> select [Type] = CONVERT(VARCHAR(10), type_desc),
2> [Path] = CONVERT(VARCHAR(40), physical_name),
3> [MB] = (size*8)/1024
4> FROM sys.master_files
5> WHERE DB_NAME(database_id) = 'test';
6> GO
Type Path MB
---------- ---------------------------------------- -----------
ROWS C:\var\opt\mssql\data\test.mdf 8
LOG C:\var\opt\mssql\data\test_log.ldf 8
(2 rows affected)
1> quit
[root@sqlserver ~]#In order to execute the database attach, you can use paths in both formats.
Let’s do the last exercise, before drawing conclusions.
[root@sqlserver ~]# sqlcmd -S localhost -E
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SSPI Provider: No Kerbe
ros credentials available.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot generate SSPI co
ntext.
[root@sqlserver ~]#For the moment, Microsoft has not yet published the documentation on how to configure this type of access.
Just for you to know, the installed ODBC driver appears 2 versions after the one you can download from the Microsoft site.
My opinion
This is the first release Microsoft SQLServer for Linux, so it’s normal that you may experience several limits in managing the database.
As experienced during this test, this is just a “Wine style” porting: it means there is a layer between executable file and o.s. (like Wine) that translates basic commands from Windows environment to Linux environment.
The major defect is the lack of documentation, but I feel that this deficiency is remedied in time.
Who wish not to leave graphical interfaces from Microsoft can use the mssql extention for Visual Studio Code on Linux.
Enjoy.
