SQL Server on Linux – NFS share between two or more Linux Servers

We might need a network shared directory between two or more Linux Servers to move databases or log backups while configuring Log-shipping, Replication, AlwaysOn or simply restoring the database backup to another Linux server.

The Linux NFS is the easiest way to share files between one or more Linux Servers. In the following example we have three Servers, where Linux01 is the primary (or nfs) server, and the secondary (or client) servers are Linux03 and Linux05.

NFS Server: linux01 (192.168.0.155) - CentOS 7.4

NFS Client: linux03 (192.168.0.157) - CentOS 7.4

NFS Client: linux05 (192.168.0.159) - CentOS 7.4

Set-by-step guide:

On the linux01 Server, perform the following:

1.      Install the NFS library:

sudo yum -y install nfs-utils

2.      Create a directory:

sudo mkdir /sqlcommon

3.      Grant permission to the directory:

sudo chmod -R a+w /sqlcommon

4.      Edit the expots configuration file and add the shared folder information:

vi /etc/exports

/sqlcommon 192.168.0.157(rw,sync)

/sqlcommon 192.168.0.159(rw,sync)

           

Note that any one of the following entries can also be used to provide access to everyone:

/sqlcommon *(rw,sync)

/sqlscommon 192.168.0.0/24(rw,sync)

5.      Enable and start the NFS service:

sudo systemctl enable nfs-server

sudo systemctl start nfs-server

6.      Refresh the export tables:

sudo exportfs -avr

7.      Configure the firewall to allow NFS service:

sudo firewall-cmd --permanent --zone=public --add-service=nfs

On the secondary (NFS Client) servers Linux02 and linux05:

1.      Install the NFS library:

sudo yum -y install nfs-utils

2.      Create a mount point:

sudo mkdir /mnt/sqlmount

3.      (a). For a temporary mount, execute the following on both servers:

sudo mount -t nfs 192.168.0.155:/sqlcommon /mnt/sqlmount

(b)   To mount permanently, add an entry to the fstab file:

            sudo vi /etc/fstab

mount 192.168.0.155:/sqlcommon /mnt/sqlmount defaults  0 0

sudo mount –a

df -h

Testing the NFS share on linux03 or linux05:

Use the following command to access the network shared directory.

cd /mnt/sqlmount

ls -a

Browsing the NFS Share from linux05:

 

SQL Server on Linux - Active Directory authentication with Kerberos

Before using the Windows Authentication mechanism in SQL Server on Linux, the following steps need to be configured:

1.      An Active Directory domain account for the SQL Server on Linux.

2.      SPN needs to be set for the Linux host and for that AD account.

3.      Installation of Kerberos and Samba on Linux.

4.      Joining the Linux Server to the Windows domain.

5.      Verifying that the AD account is able to acquire the Kerberos Ticket.

6.      Attaching the AD account with the SQL Server process on Linux.

7.      Configuring and securing SQL Server.

Configuration steps:

1.      Create an Windows domain account on the AD Server with "User never expires" and "User cannot change password" options.

2.      On the AD Server, create a SPN for this account. The general syntax is the following:

setspn -A MSSQLSvc/[FQDN of Linux Host]:[tcp port]  [AD-Account Name]

3.      On the Linux Server, join the domain with the following command with the AD account, which has sufficient privileges in AD to join a new machine to the domain:

sudo realm join myhome.org -U '[AD Admin Account]@[UPPERCASE DOMAIN NAME]' -v

Or

sudo realm join --user=[AD Admin Account]  [lowercase domain name]

4.      Verify and take note of kvno:

id [AD Account for Linux]@[lowercase domain name]

kinit [AD Account for Linux]@[UPPERCASE DOMAIN NAME]

kvno MSSQLSvc/[lowercase fqdn of linux server]:1433 

klist

5.      Create a keytab file with ktutil.

sudo ktutil

ktutil> addent -password -p MSSQLSvc/[lowercase fqdn of linux server]:[tcp port]@[UPPERCASE DOMAIN NAME] -k [kvno] -e aes256-cts-hmac-sha1-96

ktutil> addent -password -p MSSQLSvc/[fully qualified domain name of host machine]:[tcp port]@[UPPERCASE DOMAIN NAME] -k [kvno] -e rc4-hmac

ktutil> wkt /var/opt/mssql/secrets/mssql.keytab

ktutil> quit

6.      Restrict access to the keytab file and ensure only the mssql user can read the file.

sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab

sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab

7.      Configure the SQL Server to use this keytab file for the Kerberos authentication:

sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab

8.      Restart the SQL Server service on Linux server.

sudo systemctl restart mssql-server

Step by step joining to a domain:

We will be adding the linux03 (CentOS 7.4) to use Kerberos authentication to join the domain myhome.org for the AD user linuxsqlsvc:

For this task, we have the following environment information:

Linux Host/IP: linux03/192.168.0.156

FQDN:             linux03.myhome.org (lowercase fqdn of linux server)

Domain:          myhome.org (192.168.0.100)

Realm:            MYHOME.ORG (uppercase domain name)

AD Admin:     sqladmin (is an Admin account in Windows AD)

1.      Create the new AD account linuxsqlsvc with "User never expires" and "User cannot change password" options:

AD account:     linuxsqlsvc

Password:        Very$ecret

2.      Create SPN for Linux03 and linuxsqlsvc on the admin command prompt of the AD Server:

setspn -A MSSQLSvc/linux03.myhome.org:1433 linuxsqlsvc

3.      Install both the realmd and Kerberos client packages on the SQL Server host:

sudo yum -y install realmd krb5-workstation

4.      Join to the domain myhome.org; choose a command from the following. Here the sqladmin has sufficient privileges in AD to join a new machine to the domain:

sudo realm join myhome.org -U 'sqladmin@MYHOME.ORG' -v

or

sudo realm join --user=sqladmin myhome.org

5.      Verify and take note of kvno:

id linuxsqlsvc@myhome.org

kinit linuxsqlsvc@MYHOME.ORG (domain name in upper case)

kvno MSSQLSvc/linux03.myhome.org:1433 

klist

6.      Create a keytab file with ktutil:

sudo ktutil

> addent -password -p MSSQLSvc/linux03.MYHOME.ORG:1433@MYHOME.ORG -k 2 -e aes256-cts-hmac-sha1-96

> addent -password -p MSSQLSvc/linux03.MYHOME.ORG:1433@MYHOME.ORG -k 2 -e rc4-hmac

> wkt /var/opt/mssql/secrets/mssql.keytab

> quit

7.      Restrict access to the keytab file and ensure only the mssql user can read the file.

sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab

sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab

8.      Configure the SQL Server to use this keytab file for the Kerberos authentication:

sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab

9.      Restart the SQL Server service on the Linux server:

sudo systemctl restart mssql-server

Configuring Windows Authentication for SQL Server on Linux:

Reference: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication

SQL Server on Linux - Database backup directly to a Windows Network shared folder using samba

We may want to use a Windows Network shared location to redirect SQL Server database backup files, either permanently or temporarily, from the SQL Server Linux instance. Whenever a database happens, the backup files will go directly to the Network shared folder which is \\192.168.0.50\linuxshare, where the Network shared username and password are the following:

username=megauser

password=verysecret

Step-by-step guide:

1.      If samba and cifs packages have not been installed, then install them:

sudo yum -y install samba-client samba-common cifs-utils

2.      Create a directory on the linux Server:

sudo mkdir /var/opt/mssql/sqlbackup

3.      Assign permission to mssql user:

sudo chown mssql:mssql /var/opt/mssql/sqlbackup

4.      Review the permission:

sudo ls -la /var/opt/mssql/sqlbackup

5.      Retrieve the mssql user id (uid):

id mssql

If we need to use the Windows network backup location permanently, then follow the following steps:

1.      Create a hidden file such as .sharedcrd. The file name can be anything:

sudo vi /etc/samba/.sharedcrd

2.      Add the following to the .sharedcrd:

            username=megauser

            password=verysecret

3.      Assign permission to the root user only and make it secured:

sudo chown roor:root /etc/samba/.sharedcrd

sudo chmod 600 /etc/samba/.sharedcrd

4.      Edit the fstab file:

vi /etc/fstab

5.      Add the following lines to the newly created file:

//192.168.0.50/linuxshare /var/opt/mssql/sqlbackup cifs defaults,credentials=/etc/samba/.sharedcrd,uid=991 0 0

6.      Reload the fastab file:

sudo mount -a

7.      Reconfigure the default location of the backup files for SQL Server.

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/sqlbackup

8.      Restart the SQL Server Service:

sudo service mssql-server restart

If we would like to use Windows Network shared temporarily, then execute the following line on the bash shell:

sudo mount.cifs //192.168.0.50/linuxshare /var/opt/mssql/sqlbackup -o user=megauser userid=991

Once the database backup is done, it can be dismounted with the umount command:

umount /var/opt/mssql/sqlbackup

SQL Server on Linux - Adding dedicated backup disk for SQL Server

The default backup location of SQL Server during installation on Linux is /var/opt/mssql/data. This is also the default location of all data and log files. We should add a new disk and reconfigure the SQL Server default backup location. Additionally, we also need three separate directories for Full, Differential and Transaction Log backups for edge of administration.

The SQL Server configuration file is mssql.conf and is located in the following directory /var/opt/mssql/mssql.conf which needs to be adjusted after adding the disk.

In this task, we will perform the following:

1.      Add a 50GB dedicated backup disk.

2.      Prepare (partition, format, mount) the disk.

3.      Create multiple directories.

4.      Assign read/write permission to the SQL Server process.

5.      Reconfigure SQL Server configuration (mssql.conf file).

6.      Test the database backup using SSMS.

Environment: VMWare Esxi 6.5

Linux Edition: CentOS 7.4

SQL Server: linux05 (192.168.0.159)

Linux Terminal Emulator: Putty

Step-by-step guide:

Part one: Adding a 50GB new disk to SQL Server Box.

1.      From the Windows desktop, logon to the Linux server as a root user.

2.      Check the current disk status with Linux lsblk command.

3.      Add a 50GB disk to Linux Server and check the status again. The newly added disk name is sdb.

4.      Create partition on the disk sdb as follows:

sudo parted -s -a optimal /dev/sdb mklabel gpt

sudo parted -s -a optimal /dev/sdb mkpart primary 1M 100%

5.      Create the physical volume sdb1:

sudo pvcreate /dev/sdb1

6.      Create a volume group as vg_sqlbackup:

sudo vgcreate vg_sqlbackup /dev/sdb1

7.      Create a logical volume as lv_sqlbackup:

sudo lvcreate -n lv_sqlbackup --extents 100%FREE vg_sqlbackup

8.      Format the drive:

sudo mkfs.xfs /dev/vg_sqlbackup/lv_sqlbackup

9.      Create the mount directory in /var/opt/mssql:

sudo mkdir /var/opt/mssql/sqlbackup

10. Change the user and group ownership:

sudo chown mssql:mssql /var/opt/mssql/sqlbackup

11. Edit the fstab file, create a permanent mount point and add the mount point entry:

vi /etc/fstab

/dev/vg_sqlbackup/lv_sqlbackup  /var/opt/mssql/sqlbackup xfs   defaults 0 0  

12. Reload the fstab:

sudo mount -a

13. Check the disk status:

sudo df -h

14. Edit or reconfigure the SQL Server configuration file:

Method one – directly edit the conf file:

sudo vi /var/opt/mssql/mssql.conf

Add the following parameter:

[filelocation]

defaultbackupdir = /var/opt/mssql/sqlbackup

Alternatively, method two:

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/sqlbackup

15. Restart the SQL Server Service:

sudo service mssql-server restart

Part two: Creating directory for Full, Differential and Transaction Log backup:

1.      Check the newly created default backup location:

ll /var/opt/mssql/sqlbackup/

2.      Create three directories: full, diff and tlog respectively:

sudo mkdir /var/opt/mssql/sqlbackup/full

sudo mkdir /var/opt/mssql/sqlbackup/diff

sudo mkdir /var/opt/mssql/sqlbackup/tlog

3.      Assign permission to the mssql user and review the change:

sudo chown mssql:mssql /var/opt/mssql/sqlbackup/full

sudo chown mssql:mssql /var/opt/mssql/sqlbackup/diff

sudo chown mssql:mssql /var/opt/mssql/sqlbackup/tlog

ll /var/opt/mssql/sqlbackup/

Testing the backup in SSMS:

1.      Connect to the SQL Server on Linux using SSMS and execute the following commands:

BACKUP DATABASE TestDB TO DISK='/var/opt/mssql/sqlbackup/full/testdb_full_backup.bak'

BACKUP DATABASE TestDB TO DISK='/var/opt/mssql/sqlbackup/diff/testdb_diff_backup.bak'

WITH DIFFERENTIAL

BACKUP LOG TestDB TO DISK='/var/opt/mssql/sqlbackup/tlog/testdb_transaction_01.trn'

2.      Review the backup locations in Linux server:

ll -RL /var/opt/mssql/sqlbackup/

Some Screenshots:

Default database backup location:

Single disk on SQL Server:

A new 50 GB disk has been added:

Steps performed on Linux to add the new disk:

Added mount point entry to fstab:

New database backup location:

Three directories for different types of backup:

Verifying existence of database backup files:

Using SQL Monitoring Tool: