Table of contents
Preamble
For some reasons I have never ever dig into SQL Server mainly because the only possible operating system was Windows. But starting with SQL Server 2017 Microsoft has started to embrace Linux and has released SQL Server on Linux with a limited set of functionalities but release after release this list is collapsing.
One cool thing that I really appreciate with SQL Server is that you get almost all cool features (compression, in-memory, security) in enterprise edition without being obliged to buy an option (like with Oracle Enterprise edition options). And for most of them they are even accessible with the standard edition like Columnstore and In-Memory Database: In-Memory OLTP.
We have even recently discovered (yes we are late) that with SQL Server Developer edition we can even have a free edition to be used in Q&A, Test and Dev only and only if you do not import into your production figures.
Microsoft is also much more flexible than Oracle on virtualization (not too hard you would say). In short you can licensed only a single virtual Machine for SQL Server or with SQL Server Enterprise edition you can license your physical host and apply for maximum virtualization (unlimited number of virtual machine running SQL Server).
According to Microsoft 30-40% of their SQL Server customers are doing Linux, most probably with the help of cloud vendors.
This first blog post is a raw installation of SQL Server 15.0.4178.1-3 on Red Hat Enterprise Linux Server release 7.9 (Maipo) but more to come…
SQL Server on Linux installation
Configure the Linux repository with:
[root@server1 ~]# cd /etc/yum.repos.d [root@server1 yum.repos.d]# wget https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo --2021-11-10 11:54:38-- https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo Resolving proxy.domain.com (proxy.domain.com)... 10.11.12.13 Connecting to proxy.domain.com (proxy.domain.com)|10.11.12.13|:8080... connected. Proxy request sent, awaiting response... 200 OK Length: 231 [application/octet-stream] Saving to: ‘mssql-server-2019.repo’ 100%[======================================================================>] 231 --.-K/s in 0s 2021-11-10 11:54:39 (39.3 MB/s) - ‘mssql-server-2019.repo’ saved [231/231] [root@server1 yum.repos.d]# ll mssql-server-2019.repo -rw-r--r-- 1 root root 231 Nov 10 11:26 mssql-server-2019.repo [root@server1 yum.repos.d]# cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.9 (Maipo) |
Once the repository is configured the installation is as simple as:
[root@server1 ~]# dnf install -y mssql-server |
Use the suggested command to configure SQL Server for Linux. My advice is to choose the Developer edition that has all Enterprise feature but that must not be used in production (must also not contains any production figures for testing):
[root@server1 ~]# /opt/mssql/bin/mssql-conf setup usermod: no changes Choose an edition of SQL Server: 1) Evaluation (free, no production use rights, 180-day limit) 2) Developer (free, no production use rights) 3) Express (free) 4) Web (PAID) 5) Standard (PAID) 6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded 7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum 8) I bought a license through a retail sales channel and have a product key to enter. Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409 Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software. Enter your edition(1-8): 2 The license terms for this product can be found in /usr/share/doc/mssql-server or downloaded from: https://go.microsoft.com/fwlink/?LinkId=2104294&clcid=0x409 The privacy statement can be viewed at: https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409 Do you accept the license terms? [Yes/No]:Yes 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 from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service. Setup has completed successfully. SQL Server is now starting. |
I have decided to create a dedicated Veritas FileSystem to store all my database file (much better than using the system disks):
[root@server1 ~]# vxassist -g vgperp maxsize Maximum volume size: 136239104 (66523Mb) [root@server1 ~]# vxassist -g vgperp make lvol4 1g [root@server1 ~]# vxassist -g vgperp maxsize Maximum volume size: 134141952 (65499Mb) [root@server1 ~]# mkfs -t vxfs -o bsize=8192,largefiles /dev/vx/rdsk/vgperp/lvol4 version 15 layout 2097152 sectors, 131072 blocks of size 8192, log size 2048 blocks rcq size 128 blocks largefiles supported maxlink supported WORM not supported [root@server1 ~]# grep mssql /etc/fstab /dev/vx/dsk/vgperp/lvol4 /mssql vxfs defaults,_netdev 1 2 [root@server1 ~]# mount -a [root@server1 ~]# df -h /mssql Filesystem Size Used Avail Use% Mounted on /dev/vx/dsk/vgperp/lvol4 1.0G 19M 998M 2% /mssql [root@server1 ~]# chown mssql:mssql /mssql |
A mssql-server service is created and you can check it works with these two running processes:
[root@server1 ~]# ps -ef | grep mssql | grep -v grep mssql 22256 1 0 12:10 ? 00:00:00 /opt/mssql/bin/sqlservr mssql 22283 22256 8 12:10 ? 00:00:06 /opt/mssql/bin/sqlservr |
If you do not want to have SQL Server database files spread everywhere like in my first trail you have to configure few environment variables:
[root@server1 mssql]# ll ~mssql total 12 drwxr-xr-x 2 mssql mssql 4096 Nov 10 12:10 data drwxr-xr-x 2 mssql mssql 4096 Nov 10 12:20 log -rw-r--r-- 1 mssql mssql 51 Nov 10 12:09 mssql.conf drwxr-xr-x 2 mssql mssql 25 Nov 10 12:10 secrets |
I have decided to change few parameters (listening port) and change first the location of the master database:
[root@server1 ~]# /opt/mssql/bin/mssql-conf set network.tcpport 1443 SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@server1 ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /mssql/data SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@server1 ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /mssql/log SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@server1 ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /mssql/dump [root@server1 ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /mssql/backup/ SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@server1 ~]# /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /mssql/masterdatabasedir/master.mdf SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@server1 ~]# /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /mssql/masterdatabasedir/mastlog.ldf SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@server1 ~]# /opt/mssql/bin/mssql-conf set memory.memorylimitmb 1024 SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@server1 ~]# cat ~mssql/mssql.conf [sqlagent] enabled = false [EULA] accepteula = Y [network] tcpport = 1443 [filelocation] defaultdatadir = /mssql/data defaultlogdir = /mssql/log defaultdumpdir = /mssql/dump defaultbackupdir = /mssql/backup/ masterdatafile = /mssql/masterdatabasedir/master.mdf masterlogfile = /mssql/masterdatabasedir/mastlog.ldf [memory] memorylimitmb = 1024 |
To validate the master database file directory change you need to stop the service, move the files from OS standpoint and finally restart the service:
[root@server1 ~]# systemctl stop mssql-server [mssql@server1 data]$ mv master.mdf /mssql/masterdatabasedir/ [mssql@server1 data]$ mv mastlog.ldf /mssql/masterdatabasedir/ [root@server1 mssql]# systemctl start mssql-server |
To go fast I have used SQL Server Management Studio (SSMS) that I have installed on my Windows desktop and I have to say that it worked seamlessly:
once you supplied the credentials:
SQL Server on Linux command-line tools installation
Configure the repository:
[root@server1 ~]# cd /etc/yum.repos.d/ [root@server1 yum.repos.d]# wget https://packages.microsoft.com/config/rhel/7/prod.repo --2021-11-10 15:23:58-- https://packages.microsoft.com/config/rhel/7/prod.repo Resolving proxy.domain.com (proxy.domain.com)... 10.11.12.13 Connecting to proxy.domain.com (proxy.domain.com)|10.11.12.13|:8080... connected. Proxy request sent, awaiting response... 200 OK Length: 193 [application/octet-stream] Saving to: ‘prod.repo’ 100%[======================================================================>] 193 --.-K/s in 0s 2021-11-10 15:23:58 (33.5 MB/s) - ‘prod.repo’ saved [193/193] |
Install the tools with:
[root@server1 ~]# dnf install -y mssql-tools unixODBC-devel |
You can connect to your local instance with command line (add /opt/mssql-tools/bin directory in your path):
[mssql@server1 ~]$ sqlcmd -S localhost,1443 -U SA Password: 1> 2> CREATE DATABASE testdb 3> go 1> SELECT Name from sys.Databases 2> go Name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb testdb (5 rows affected) 1> |
Final cleaning to have all database files in good directories
get the filenames list with:
1> :setvar SQLCMDMAXFIXEDTYPEWIDTH 40 2> select name, physical_name from sys.master_files 3> go name physical_name ---------------------------------------- ---------------------------------------- master /mssql/masterdatabasedir/master.mdf mastlog /mssql/masterdatabasedir/mastlog.ldf tempdev /var/opt/mssql/data/tempdb.mdf templog /var/opt/mssql/data/templog.ldf tempdev2 /var/opt/mssql/data/tempdb2.ndf tempdev3 /var/opt/mssql/data/tempdb3.ndf tempdev4 /var/opt/mssql/data/tempdb4.ndf tempdev5 /var/opt/mssql/data/tempdb5.ndf tempdev6 /var/opt/mssql/data/tempdb6.ndf tempdev7 /var/opt/mssql/data/tempdb7.ndf tempdev8 /var/opt/mssql/data/tempdb8.ndf modeldev /var/opt/mssql/data/model.mdf modellog /var/opt/mssql/data/modellog.ldf MSDBData /var/opt/mssql/data/MSDBData.mdf MSDBLog /var/opt/mssql/data/MSDBLog.ldf |
For msdb and model files you can use the below four simple command (you can create a SQL file and use -i option of sqlcmd to execute the file):
--MSDB database file and log file alter database msdb modify file (Name = mSDBData, FILENAME = '/mssql/systemdatabasedir/MSDBData.mdf') go alter database msdb modify file (name = msdblog, filename = '/mssql/systemdatabasedir/MSDBLog.ldf'); go --MODEL database file and log file alter database model modify file (name = modeldev, filename = '/mssql/systemdatabasedir/Model.mdf') go alter database model modify file (name = modellog, filename = '/mssql/systemdatabasedir/Modellog.ldf'); go |
For tempdb you can auto-generate the modification script with:
1> SELECT concat('alter database tempdb modify file(name=',name,',filename=''', physical_name, ''')') AS command FROM sys.master_files WHERE database_id = DB_ID('TempDB'); 2> go command -------------------------------------------------------------------------------------------------------------------------------------------------------- alter database tempdb modify file(name=tempdev,filename='/var/opt/mssql/data/tempdb.mdf') alter database tempdb modify file(name=templog,filename='/var/opt/mssql/data/templog.ldf') alter database tempdb modify file(name=tempdev2,filename='/var/opt/mssql/data/tempdb2.ndf') alter database tempdb modify file(name=tempdev3,filename='/var/opt/mssql/data/tempdb3.ndf') alter database tempdb modify file(name=tempdev4,filename='/var/opt/mssql/data/tempdb4.ndf') alter database tempdb modify file(name=tempdev5,filename='/var/opt/mssql/data/tempdb5.ndf') alter database tempdb modify file(name=tempdev6,filename='/var/opt/mssql/data/tempdb6.ndf') alter database tempdb modify file(name=tempdev7,filename='/var/opt/mssql/data/tempdb7.ndf') alter database tempdb modify file(name=tempdev8,filename='/var/opt/mssql/data/tempdb8.ndf') (9 rows affected) |
Then push the result in a SQL file modify the path to match you desired target directory (/mssql/systemdatabasedir/ for me) and execute the script. A final stop of the instance, a move of files at OS level and an instance start will finalize the modification…
AdventureWorks example database installation
To complete this initial post of SQL Server on Linux trial I have decided to import the AdventureWorks2019 Microsoft famous example database:
[mssql@server1 ~]$ cd /msql/backup [mssql@server1 backup]$ wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak |
Even if I love command line I have to admit that SSMS is a great piece of software and you can restore the backup file in really few mouse click. Choose the backup file:
File mapping is done automatically with value of defaultdatadir and defaultlogdir:
Not much to choose here, but useful for other personnal backups:
You can also generate a script that could be helpfull if you want to script or if, like me, trying to go further in your SQL Server understanding. Those below command could help you to generatete the right TSQL restore command:
RESTORE FILELISTONLY FROM DISK = N'/mssql/backup/AdventureWorks2019.bak' RESTORE HEADERONLY FROM DISK = N'/mssql/backup/AdventureWorks2019.bak' |
Finally the T-SQL restore command:
1> RESTORE DATABASE [AdventureWorks2019] FROM DISK = N'/mssql/backup/AdventureWorks2019.bak' with file = 1, 2> MOVE N'AdventureWorks2017' TO N'/mssql/data/AdventureWorks2019.mdf', 3> MOVE N'AdventureWorks2017_log' TO N'/mssql/log/AdventureWorks2019_log.ldf', 4> NOUNLOAD, 5> stats = 5 6> go 5 percent processed. 10 percent processed. 15 percent processed. 20 percent processed. 25 percent processed. 30 percent processed. 35 percent processed. 40 percent processed. 45 percent processed. 50 percent processed. 55 percent processed. 60 percent processed. 65 percent processed. 70 percent processed. 75 percent processed. 80 percent processed. 85 percent processed. 90 percent processed. 95 percent processed. 100 percent processed. Processed 26344 pages for database 'AdventureWorks2019', file 'AdventureWorks2017' on file 1. Processed 2 pages for database 'AdventureWorks2019', file 'AdventureWorks2017_log' on file 1. RESTORE DATABASE successfully processed 26346 pages in 8.017 seconds (25.673 MB/sec). |
A dummy select on a default example table:
1> select top(10) firstname, lastname from adventureworks2019.person.person 2> go firstname lastname -------------------------------------------------- -------------------------------------------------- Syed Abbas Catherine Abel Kim Abercrombie Kim Abercrombie Kim Abercrombie Hazem Abolrous Sam Abolrous Humberto Acevedo Gustavo Achong Pilar Ackerman (10 rows affected) |
Conclusion and next steps
Frankly this testing has really been seamless and all has really worked perfectly. Kudos to Microsoft. It worked so well that I clearly plan to test soon advanced features like:
- Replication
- Log Shipping
- Always On failover cluster instance
- Always On availability groups
References
