Skip to content

install SQL Server 2019 on linux

SQL Server 2019 – Ubuntu 20.04 repository – Developer version

Starting with SQL Server 2019 CU10 , Ubuntu 20.04 is supported.

install ms sql server 2019

zie https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-linux-ver15&preserve-view=true

# Import the public repository GPG keys
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - 
# Register the Microsoft SQL Server Ubuntu repository for SQL Server 2019:
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

# install SQL Server
sudo apt-get update 
sudo apt-get install -y mssql-server

# run mssql-conf setup 
sudo /opt/mssql/bin/mssql-conf setup
# pswd  see KP   
#  (server =  localhost or 192.168.10.184) 
#  dev edition , english 

# verify
clear && systemctl status mssql-server --no-pager
ok

uninstall

sudo apt-get remove mssql-server
sudo rm -rf /var/opt/mssql/

disable autostart

sudo systemctl list-unit-files --type=service | grep 'mssql' (zie systemctl )
=> mssql-server.service enabled enabled

sudo systemctl disable mssql-server.service
dit de-activeert enkel de boot startup
je kan stoppen en starten met sudo systemctl start mssql-server sudo systemctl status mssql-server

stop manueel mssql-service vooraleer LXM te restarten ( “normale” afsluiten mssql duurt 30 minuten !)

locations

binaries are in /opt/mssql/
master databases are in : /var/opt/mssql/data (root permission required, since owner is mssql user, not ward)
mssql user has the file and group ownership for both MDF and LDF files.

I do not want databases & log files on the SDD , so move db :
sysdb -> /mnt/backup/SQLSYSDB
userdb -> /mnt/backup/SQLDATA

relocate system databases

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15#masterdatabasedir
https://www.mssqltips.com/sqlservertip/7087/move-sql-server-system-databases-linux/

#   1. create database directories , if not exist already 
    mkdir ...
#   2. set permissions
    sudo chown mssql  /mnt/backup/SQLSYSDB   &&    sudo chgrp mssql  /mnt/backup/SQLSYSDB  
    sudo  chown mssql /mnt/backup/SQLDATA    &&    sudo chgrp mssql  /mnt/backup/SQLDATA
#   3. change location of master , sql service must be running 
    sudo /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /mnt/backup/SQLSYSDB/master.mdf
    sudo /opt/mssql/bin/mssql-conf set filelocation.masterlogfile  /mnt/backup/SQLSYSDB/mastlog.ldf

    #! do NOT restart ms sql server as suggested  restart service is indeed ok for most option changes
    # but NOT for moving master files 
    #  **stop** server instead ! 
#   4. stop SQL Server
    systemctl stop mssql-server 
    # verify status: 
    clear && systemctl status mssql-server --no-pager
#   5. move database files  (copy first, delete later) 
    sudo cp /var/opt/mssql/data/master.mdf  /mnt/backup/SQLSYSDB
    sudo cp /var/opt/mssql/data/mastlog.ldf  /mnt/backup/SQLSYSDB
#   6. start SQL Server
    sudo systemctl start mssql-server 
#   7. validate 


#   8. validate 
exec sp_helpfile

ALTER DATABASE msdb   MODIFY FILE ( NAME = MSDBData, FILENAME = '/mnt/backup/SQLSYSDB/MSDBData.mdf');  
GO
ALTER DATABASE msdb   MODIFY FILE ( NAME = MSDBLog, FILENAME = '/mnt/backup/SQLSYSDB/MSDBLog.ldf');  
GO

ALTER DATABASE model  MODIFY FILE ( NAME = modeldev, FILENAME = '/mnt/backup/SQLSYSDB/model.mdf');  
GO
ALTER DATABASE model  MODIFY FILE ( NAME = modellog, FILENAME = '/mnt/backup/SQLSYSDB/modellog.ldf');  
GO

ALTER DATABASE tempDB  MODIFY FILE ( NAME = tempdev,FILENAME = '/mnt/backup/SQLSYSDB/tempdb.mdf');  
GO
ALTER DATABASE tempDB  MODIFY FILE ( NAME = templog, FILENAME = '/mnt/backup/SQLSYSDB/templog.ldf');  
GO
ALTER DATABASE tempDB  MODIFY FILE ( NAME = tempdev2,FILENAME = '/mnt/backup/SQLSYSDB/tempdb2.ndf');  
GO
ALTER DATABASE tempDB  MODIFY FILE ( NAME = tempdev3,FILENAME = '/mnt/backup/SQLSYSDB/tempdb3.mnf');  
GO
ALTER DATABASE tempDB  MODIFY FILE ( NAME = tempdev4,FILENAME = '/mnt/backup/SQLSYSDB/tempdb4.ndf');  
GO

sudo systemctl stop mssql-server.service
move other systemdb files to

aa

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir   /mnt/backup/SQLBACKUP
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir     /mnt/backup/SQLDATA 
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir     /mnt/backup/SQLLOGGING
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir      /mnt/backup/SQLDATA 
sudo /opt/mssql/bin/mssql-conf set filelocation.errorlogfile       /mnt/backup/SQLLOGGING

issues

debuggen met :
journalctl -xe
systemctl status mssql-server

  • stoppen ms sql service kan tot 60 minuten duren !
    was ook zo in LXM19 , ik had er een oplossing voor ( genoteerd in onenotes ? )

  • sudo /opt/mssql/bin/mssql-conf set
    werkt niet => “shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory”

na uninstall , re-install
nu is het wel een tekst bestand !?
root@lapwm:/var/opt/mssql# cat mssql.conf

[sqlagent]
enabled = false
[EULA]
accepteula = Y
[language]
lcid = 1033
[filelocation]
masterdatafile = /mnt/backup/SQLSYSDB/master.mdf
masterlogfile = /mnt/backup/SQLSYSDB/mastlog.ldf

zeer interessant :
https://github.com/MicrosoftDocs/WSL/issues/815