Microsoft SQL Server 2019 on Linux Ubuntu 18.04, installation and configuration

Logo

Introduction

Who would have believed in 2009, ten years ago, Microsoft SQL Server 2019 on Linux Platforms. Definitely not the author of this article, but Microsoft did it !

SQL Server 2017 was already supported on Linux platforms, but many features were missing compared to Windows version like replication, high avalaibility and mirroring, distributed transaction coordinator (DTC), some active directory authentication mechanisms and SQL Server agent functionalities… List of SQL Server 2017 missing features on Linux.

SQL Server 2019 on Linux platforms now include the major missing features in SQL Server 2017. So let’s explore SQL Server 2019 on Linux.

SQL Server 2019 is installed on Ubuntu 18.04 in this article : only Ubuntu 16.04 is supported, but it works on Ubuntu 18.04 for test purposes.

Architecture SQL Server 2019 Ubuntu

Multiples instances and named instances are not (yet ?) supported on Linux platforms, third party architectures like Docker must be used to do so.

In this article, a standalone SQL Server instance is installed. As almost companies implement firewall rules that prevent servers from communicating with the outside world, offline installations are performed with the packages *.deb provided by Microsoft.

  • The SQL Server engine listens on port 1433 and runs with the user mssql.
  • Database files are not installed in the default directory.
  • SQL Server agent, SSL and active directory authentication are not covered here.
Architecture SQL Server 2019 Linux Ubuntu

After the installation, the 2 first tests performed will be :

  • Restore a database on Linux SQL Server 2019 from a windows SQL Server 2016 database backup.
  • Export/import a table with bcp in native mode from SQL Server 2016 Windows to SQL Server 2019 Linux.

These 2 tests will show if it is easy to migrate from SQL Server 2016 on Windows to SQL Server 2019 on Linux.

Pre-requisites

The system requirements are the following for SQL Server 2019 on Linux platforms :

  • Memory : 2GB
  • File system type : XFS or EXT4
  • Disk space : 6 GB
  • Processor speed : 2 GHz
  • Processor cores : 2
  • Processor type : x64

Use the command fsck or mount to check file system types where SQL Server will be installed :

fsck -N /dev/sda1
[/sbin/fsck.ext4 (1) -- /] fsck.ext4 /dev/sda1
mount | grep '^/dev/sda1'
/dev/sda1 on / type ext4 (rw,relatime,data=ordered)

The SQL Server 2019 setup will create an account mssql if it does not exist. If you work in environments with normalized id for accounts, prepare in advance the account mssql, that’s the case here : 

root@vps$ useradd mssql -g dba -d /home/mssql -m -s /bin/bash -u 10006

root@vps$ passwd mssql

Packages installations : server and client tools

Server engine installation

Download first the package mssql-server from Microsoft web site. For this package choose Ubuntu 16.04 even if the target system is Ubuntu 18.04 : https://packages.microsoft.com/ubuntu/16.04/. The package SQL Server 2019 CTP 3 (mssql-server_15.0.1600.8-1_amd64.deb) is in the subdirectory ./mssql-server-preview/pool/main/m/mssql-server

Upload the package to the Ubuntu server and check first the dependencies :

root@vps$ dpkg -I mssql-server_15.0.1600.8-1_amd64.deb | grep 'Depends:'

Depends: libunwind8, libnuma1, libc6, adduser, libc++1, gdb, debconf,
hostname, openssl (>= 1.0.1g), python (>= 2.7.0), libgssapi-krb5-2,
libsss-nss-idmap0, gawk, sed, libpam0g, libldap-2.4-2,
libsasl2-2, libsasl2-modules-gssapi-mit, tzdata

Few dependencies, but some are very important : openssl >= 1.0.1g and python >= 2.7.0

Install the missing packages (4 here) : 

root@vps$ apt install libc++1 gdb libsss-nss-idmap0 libsasl2-modules-gssapi-mit

then install the package mssql-server, installation is quite easy :

root@vps$ dpkg -i mssql-server_15.0.1600.8-1_amd64.deb

Selecting previously unselected package mssql-server.
(Reading database ... 133143 files and directories currently installed.)
Preparing to unpack mssql-server_15.0.1600.8-1_amd64.deb ...
Unpacking mssql-server (15.0.1600.8-1) ...
Setting up mssql-server (15.0.1600.8-1) ...

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

Processing triggers for libc-bin (2.27-3ubuntu1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...

Configuration will be done later after client tools packages installation.

About the installation, all binaries (sqlservr…) and librairies are installed in /opt/mssql (owner : root). Maybe SQL Server could be installed elsewhere, but it’s better here to avoid complicated methods (fakeroot…) for a technology preview on a non supported Ubuntu version, especially if we plan to upgrade to a newer version in the next weeks.

  • /opt/mssql/bin
  • /opt/mssql/lib

A directory /var/opt/mssql is also created (owner mssql). This directory contains the configuration file mssql.conf of the future instance. At this stage, only one entry in this file : sqlagent disabled.

/var/opt/mssql/mssql.conf
[sqlagent]
enabled = false

Client tools installation

About client tools, install the packages in the following order below due to the dependencies :

  • unixodbc, if not already installed. Version >= 2.3.1 is required.
  • msodbcsql17 (version 17.3) : Microsoft ODBC Driver.
  • mssql-tools (version 17.3) : sqlcmd and bcp tools.

Packages are downloaded from Microsoft web site : https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/. For client tools, the right Ubuntu version is selected (18.04).

  • msodbcsql17 : ./msodbcsql17/msodbcsql17_17.3.1.1-1_amd64.deb.
  • mssql-tools : ./mssql-tools/mssql-tools_17.3.0.1-1_amd64.deb.

The dependencies are the following :

root@vps$ dpkg -I msodbcsql17_17.3.1.1-1_amd64.deb | grep 'Depends:'
           Depends: libc6 (>= 2.21), libstdc++6 (>= 4.9), libkrb5-3, openssl,
 debconf (>= 0.5), unixodbc (>= 2.3.1)
root@vps$ dpkg -I mssql-tools_17.3.0.1-1_amd64.deb | grep 'Depends:'
           Depends: libc6 (>= 2.21), libstdc++6 (>= 4.9), libkrb5-3, openssl,
 debconf (>= 0.5),  msodbcsql17 (>= 17.3.0.0), msodbcsql17 (<< 17.4.0.0)

Install UnixOdbc >= 2.3.1 

root@vps$ apt install unixodbc

then install MS SQL Server ODBC Driver and MS SQL Server client tools packages :

root@vps$ dpkg -i msodbcsql17_17.3.1.1-1_amd64.deb
          
root@vps$ dpkg -i mssql-tools_17.3.0.1-1_amd64.deb

Microsoft ODBC SQL driver 17 is then installed in the directory /opt/microsoft/msodbcsql17 and MS SQL Server tools (sqlcmd and bcp) in the directory /opt/mssql-tools/bin.

For an ease of use of sqlcmd and bcp, add the directory /opt/mssql-tools/bin in the environment variable $PATH for the user mssql. This can be done in the files $HOME/.bashrc and $HOME/.profile.

$HOME/.bashrc, $HOME/.profile
export PATH="$PATH:/opt/mssql-tools/bin"

Building and running the MS SQL Server instance

Configuring the SQL Server instance

Before building the instance, many options can be prepared (for example the database locations…).

For the full list of the options, run mssql-conf list :

root@vps$ /opt/mssql/bin/mssql-conf list
          
...
filelocation.defaultdatadir                             Default directory for data files
...
filelocation.defaultlogdir                              Default directory for log files
filelocation.errorlogfile                               Error log file location
filelocation.masterdatafile                             Master database data file location
filelocation.masterlogfile                              Master database log file location
...

Without any custom configuration before running mssql-conf setup, database files are created by default in the directory /var/opt/mssql. That’s not what we may want, especially if we split data files and transaction log files on separate devices, or simply store these ones on LUN SAN.

So configuration must be done before, especially database file locations. Obviously for every custom directory, the owner must be mssql.

root@vps$ /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /sqlpac/mssql/system/master.mdf

root@vps$ /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /sqlpac/mssql/system/mastlog.ldf

root@vps$ /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /sqlpac/mssql/dbfiles
          
root@vps$ /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /sqlpac/mssql/tlogfiles

root@vps$ /opt/mssql/bin/mssql-conf set filelocation.errorlogfile  /opt/mssql/dba/srvmssql/log/errorlog

root@vps$ /opt/mssql/bin/mssql-conf set errorlog.numerrorlogs  10

root@vps$ /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir  /opt/mssql/dba/srvmssql/backup

root@vps$ /opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir  /opt/mssql/dba/srvmssql/crashdump

Each custom parameter is written in the configuration file /var/opt/mssql/mssql.conf. This configuration file replaces the Windows registry database for most of the parameters (default datadir…)

/var/opt/mssql/mssql.conf
[sqlagent]
enabled = false

[filelocation]
defaultdatadir = /sqlpac/mssql/dbfiles
defaultlogdir = /sqlpac/mssql/tlogfiles
masterdatafile = /sqlpac/mssql/system/master.mdf
masterlogfile = /sqlpac/mssql/system/mastlog.ldf
errorlogfile = /opt/mssql/dba/srvmssql/log/errorlog
defaultbackupdir = /opt/mssql/dba/srvmssql/backup
defaultdumpdir = /opt/mssql/dba/srvmssql/crashdump

[errorlog]
numerrorlogs = 10

Running mssql-conf setup

Everything is ready : server, client packages and configuration. Let’s build the instance with mssql-conf and the option setup.

root@vps$  /opt/mssql/bin/mssql-conf setup

The developer edition is selected and the license agreement is accepted.

A password is defined for the sa SQL Server login :

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

Connecting to the SQL Server engine with sqlcmd

The first connection is now performed with sa login : 

mssql@vps$ sqlcmd -Usa
          
select @@version
go
Microsoft SQL Server 2019 (CTP3.0) - 15.0.1600.8 (X64)
        May 17 2019 00:56:19
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Ubuntu 18.04.2 LTS) <X64>

Default settings : collation, port

The Ubuntu server is installed in English, but what is the default collation setting then ? The collation applied is SQL_Latin1_General_CP1_CI_AS.

select name, collation_name from sys.databases where name='master'
go

name           collation_name
------------   -----------------------------------------------
master         SQL_Latin1_General_CP1_CI_AS
exec sp_helpsort
go
          
Server default collation
-------------------------
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Use /opt/mssql/bin/mssql-conf set-collation to change the collation for system databases if necessary.

The SQL Server service runs with no surprise on the default port 1433 with the account mssql: 

netstat -tulpn | grep LISTEN | grep 'sqlservr' | grep '1433'

tcp        0      0 0.0.0.0:1433            0.0.0.0:*               LISTEN      7023/sqlservr
tcp6       0      0 :::1433                 :::*                    LISTEN      7023/sqlservr

By default, the number of SQL Server processes (sqlservr) depends on the number of cores, here 2 cores : 

ps -ef | grep 'sqlservr'

mssql     7012     1  0 14:54 ?        00:00:01 /opt/mssql/bin/sqlservr
mssql     7023  7012 22 14:54 ?        00:01:30 /opt/mssql/bin/sqlservr

Service mssql-server : start, stop, status

A service mssql-server is automatically created and enabled, the usual commands are available to start, stop, restart and view the status of the SQL Server service : 

root@vps$ systemctl start mssql-server

root@vps$ systemctl stop mssql-server
          
root@vps$ systemctl restart mssql-server
          
root@vps$ systemctl status mssql-server
          
● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since wed 2019-05-29 16:27:35 CEST; 7s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 8502 (sqlservr)
    Tasks: 129
   CGroup: /system.slice/mssql-server.service
           ├─8502 /opt/mssql/bin/sqlservr
           └─8528 /opt/mssql/bin/sqlservr

Obviously, enabling or disabling the SQL Server service is available like any other service.

root@vps$ systemctl enable mssql-server
root@vps$ systemctl disable mssql-server

Connecting from a Windows client

If the Ubuntu firewall ufw is configured to deny incoming connexions by default, don’t forget to open SQL Server port 1433 : 

root@vps$ ufw allow 1433
           
Rule added
Rule added (v6)

Tests have been performed with SQL Server Management Studio 17.2 (SSMS 17.2) :

Ubuntu Linux SQL Server Management Studio

No immediate issues, but errors occur while trying a reverse engineering (for example, right click on a table : Script Table as CREATE To New Query Query Editor).

Error SQL Server Management Studio 17, invalid version 15

Use SSMS 18, now available (GA), the first fully aware of SQL Server 2019 (compatibility level 150). As usual, setup very long, many issues encountered and many depending applications installed (.NET Framework 4.7.2, Visual Studio Isolated Shell 2017 for SSMS, Microsoft Visual Studio Tools for Applications 2017…).

Cross platforms data transfers Windows / Linux (backup, restore, bcp)

Backup / Restore

Test has been done to restore the backup of the demo database AdventureworksDW2016CTP3 coming from Windows SQL Server 2016. Everything is OK ! No extra steps to perform in this cross-platform context, dbcc checkdb command did not raise any exception. Further more, migration to SQL Server 2019 is done normally as if we were working in Windows platform.

restore database AdventureworksDW
from disk='/opt/mssql/dba/srvmssql/backup/win/AdventureworksDW2016CTP3.bak'
with move 'AdventureWorksDW2014_Data' to '/sqlpac/mssql/dbfiles/AdventureworksDW.mdf',
move 'AdventureWorksDW2014_Log' to '/sqlpac/mssql/tlogfiles/AdventureworksDW.ldf',
replace
go
Processed 186680 pages for database 'AdventureworksDW', file 'AdventureWorksDW2014_Data' on file 1.
Processed 3 pages for database 'AdventureworksDW', file 'AdventureWorksDW2014_Log' on file 1.
Converting database 'AdventureworksDW' from version 835 to the current version 902.
Database 'AdventureworksDW' running the upgrade step from version 835 to version 836.
...
Database 'AdventureworksDW' running the upgrade step from version 901 to version 902.
RESTORE DATABASE successfully processed 186683 pages in 15.777 seconds (92.442 MB/sec).
dbcc checkdb('AdventureworksDW')
go
DBCC results for 'AdventureworksDW'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureworksDW'.

CREATE DATABASE FOR ATTACH

About attaching a database with data and transaction log files coming from Windows SQL Server 2016, same results : no issue and the migration is completed.

create database AdventureworksDW
on (filename = '/sqlpac/mssql/dbfiles/AdventureworksDW.mdf'), (filename = '/sqlpac/mssql/tlogfiles/AdventureworksDW.ldf') 
for attach
go

Converting database 'AdventureworksDW' from version 852 to the current version 902.
...
Database 'AdventureworksDW' running the upgrade step from version 901 to version 902.

bcp out / in with Unicode data from Windows to Linux

In a windows SQL Server 2016, some unicode data are inserted in a table named t_bcp_unicode in order to test a transfer with bcp out / in in native mode.

create table t_bcp_unicode 
( lang varchar(20) not null,
  comment nvarchar(200) not null)
go

insert into t_bcp_unicode values ('FR','Une rue française')
go
insert into t_bcp_unicode values ('DE','eine Straße')
go
insert into t_bcp_unicode values ('CH',N'一条街')
go
insert into t_bcp_unicode values ('RU',N'улица')
go
insert into t_bcp_unicode values ('GR',N'ένα δρόμο')
go

Data are exported in a file with bcp SQL Server 2016 in native mode : 

SRVWIN> bcp sqlpac..t_bcp_unicode out t_bcp_unicode.bcpc -Slocalhost -T -n

The import in SQL Server 2019 / Linux is then performed without any issue : 

mssql@vps$ bcp sqlpac..t_bcp_unicode in t_bcp_unicode.bcpn -Svps -Usa -n 

mssql@vps$ sqlcmd -Usa 

use sqlpac
go
select * from t_bcp_unicode
go
lang                 comment             
-------------------- -----------------------------------
FR                   Une rue française         
DE                   eine Straße                     
CH                   一条街               
RU                   улица      
GR                   ένα δρόμο

Conclusion

Named instances and multiple instances are not (yet ?) possible, but it is quite easy to build SQL Server on Linux. Further more, SQL Server databases can be loaded or attached from windows environments with no extra steps, this is not really surprising as Windows and Linux are both "Little Endian" type platforms (right-to-left byte numbering).

Unfortunately, benchmarks could not be performed, machines with the same hardware characteristics were not available, but there is a great article about it (in French) : Microsoft SQL Server 2017 plus rapide sous Linux que Windows ? Pas si sûr…