Home > Workload Solutions > SQL Server > White Papers > Dell PowerMax 2500 and 8500 Best Practices for Mission Critical SQL Server Databases > Storage considerations for SQL Server on Linux
When using Linux, there are many native tools that are not required for the deployment of SQL Server on Linux but that can be helpful. For example, use Linux UDEV rules to create user-friendly names (aliases) to each PowerMax volume for ease of identification. You can also use UDEV rules to set volume permissions, change the IO scheduler, and so on.
SQL Server supports both EXT4 and XFS file system types to host the database, transaction logs, and additional files such as checkpoint files for in-memory OLTP in SQL Server. Microsoft recommends using the XFS file system type for hosting the SQL Server database and transaction log files.
Use Linux Logical Volume Manager (LVM) to create a striped logical volume and place a single file system where all the SQL Server database files can reside. Their performance is supported through all the PowerMax volumes behind that file system.
As with Windows, it is recommended to use a multipathing software such as PowerMax or Linux native multipath software. The following examples demonstrate the use of these software tools.
Use Linux UDEV rules to create user-friendly names (aliases) based on the volume WWN (add 3 before the WWN to signify that the host finds the volume WWN in SCSI INQUIRY page 0x83), set volume permissions, IO scheduler, and other parameters. For example:
# view /etc/udev/rules.d/85-sqlsvr.rules
KERNEL=="emcpower*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id -g -d /dev/$name", RESULT=="360000970000120200304533030313031", SYMLINK+="sqldisks/sysdb1"
KERNEL=="emcpower*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id -g -d /dev/$name", RESULT=="360000970000120200304533030313032", SYMLINK+="sqldisks/userdb1_data1"
KERNEL=="emcpower*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id -g -d /dev/$name", RESULT=="360000970000120200304533030313033", SYMLINK+="sqldisks/userdb1_data2"
…
# set global attributes to PowerPath devices (can be done per device if not all devices should have the same)
ACTION=="add|change", KERNEL=="emcpower*", OWNER:="mssql", GROUP:="mssql", MODE="0660", ATTR{queue/scheduler}="deadline", ATTR{queue/nr_requests}="128"
# After changes use: udevadm control --reload-rules && udevadm trigger
Based on rules file, view the aliases and their relationship to the volumes in the /dev/sqldisks/ directory:
[root@dsib1004 ~]# ls -l /dev/sqldisks/
drwxr-xr-x 2 root root 540 Jun 25 13:13 ./
drwxr-xr-x 25 root root 27640 Jun 29 17:30 ../
lrwxrwxrwx 1 root root 13 Jun 25 13:13 sysdb1 -> ../emcpowerab
lrwxrwxrwx 1 root root 13 Jun 25 13:13 userdb1_data1 -> ../emcpoweral
lrwxrwxrwx 1 root root 13 Jun 25 13:13 userdb1_data10 -> ../emcpowerbe
lrwxrwxrwx 1 root root 13 Jun 25 13:13 userdb1_data11 -> ../emcpowerah
lrwxrwxrwx 1 root root 13 Jun 25 13:13 userdb1_data12 -> ../emcpowerbc
lrwxrwxrwx 1 root root 13 Jun 25 13:13 userdb1_data13 -> ../emcpoweraz
...
The following example shows how to create a striped logical volume and place an XFS file system on it:
# pvcreate -f /dev/emcpoweri
# pvcreate -f /dev/emcpowerg
# pvcreate -f …
# vgcreate sqldata --physicalextentsize 4MiB --force --yes /dev/emcpoweri
# vgextend sqldata /dev/emcpowerg
# vgextend sqldata /dev/emcpower…
# lvcreate --name sqldata_lv --stripes 16 --stripesize 128 --size 1599G sqldata
# mkfs.xfs -n size=64k -f /dev/sqldata/sqldata_lv
# mount -t xfs -o noatime /dev/sqldata/sqldata_lv /sql/sqldata
# chown mssql.mssql /sql/sqldata
For more information about Microsoft performance best practices, see Performance best practices and configuration guidelines for SQL Server on Linux.
The Linux operating system makes scripting easy. Scripts can be added to run database tasks, collect performance metrics, and so on. The following example shows the creation of a SQL Server user database from T-SQL using a shell script:
$ cat ./sql_run.sh
sqlcmd -S localhost -U SA -P $MSSQL_PWD -i $1
$ cat ./sql_create_userdb1.sql
USE master;
GO
CREATE DATABASE userdb1 ON
( NAME = 'data1', FILENAME = '/sql/sqldata/userdb1_data1.mdf' ),
( NAME = 'log1', FILENAME = '/sql/sqllog/userdb1_log1.ldf' ),
( NAME = 'data2', FILENAME = '/sql/sqldata/userdb1_data2.ndf' ),
( NAME = 'data3', FILENAME = '/sql/sqldata/userdb1_data3.ndf' ),
( NAME = 'data4', FILENAME = '/sql/sqldata/userdb1_data4.ndf' ),
( NAME = 'data5', FILENAME = '/sql/sqldata/userdb1_data5.ndf' ),
( NAME = 'data6', FILENAME = '/sql/sqldata/userdb1_data6.ndf' ),
( NAME = 'data7', FILENAME = '/sql/sqldata/userdb1_data7.ndf' ),
( NAME = 'data8', FILENAME = '/sql/sqldata/userdb1_data8.ndf' );
GO
ALTER DATABASE [userdb1] SET RECOVERY SIMPLE
GO
$ ./sql_run.sh sql_create_userdb1.sql