Infrastructure at your Service

David Barbarin

SQL Server on Linux and experimental Flexible Architecture

In this blog post let’s talk a little bit about SQL Server optimal installation on the Linux file system. This is probably one of the first considerations we will have as a database administrator. Actually basic installation of SQL Server can be resumed to use yum command and let the SQL Server installer place all related files on the file system. But in a production environment, the story will be different and we will have to follow best practices. In fact, Microsoft guidelines already exist in Windows world and concern mainly placement of system / user database files and tempdb as well.

But what about Linux? In my opinion, the game is not so different and existing database file placement rules also apply on Linux. We don’t work with drive letters on Linux but it doesn’t matter because in some cases we already use the same mount point concept.

How to start and how to achieve an optimal placement were the questions in my head since the last meetup I attended about MongoDB and organized by my colleagues Medhi Bada and Eric Dusquesnoy.

During the meeting, they talked about MongoDB installation according the OFA architecture initiated by Oracle and which has many similar aspects with FHS on Unix system. Another interesting thing I’ve realized after discussing with my colleagues is that we also propose the same based-model for other SGBDs like MySQL or PostgreSQL (with some adjustments in the respect of their respective context).

In a nutshell, OFA architecture provides the following interesting things:

  • A logical layout for the databases that helps DBAs to manage their system. The idea here is to provide a predictable and consistent file naming structure to manage and to deal easily with issues such as adding hardware, installing new instance etc…
  • Preventing performance bottlenecks by distributing I/O across enough disks. For instance, we may separate group of entities that will contend for disk resources. We may think here about separating data and log files, data files in different filegroups or dedicated tempdb to its own storage path as well.
  • Maximizing database reliability in case of disk failure

But you may wonder why to scale the existing SQL Server best practices to the OFA standard?

First of all, let’s say that existing Microsoft best practices, in many respects, resembles to OFA. Then providing a uniform approach to customers (Oracle, MySQL, PostgreSQL, MongoDB and SQL Server) about best practices may help to contribute to a true immersion of SQL Server on Linux. From a customer perspective, we may provide guidelines on an already widely architecture standard known in Linux world. This is, at least, the way I see it.

So just to be clear, the next part of this blog is purely experimental and it doesn’t pretend to replace any future guideline that will be provided by Microsoft.

Before getting to the heart of the matter, let’s get the files associated to the mssql-server package. I continue to use the CentOS distribution for my demo but you may easy apply the same with other distributions.

[root@sql mssqlserver]# rpm -qpl mssql-server-14.0.405.200-1.x86_64.rpm
/opt/mssql/bin
/opt/mssql/bin/compress-dump.sh
/opt/mssql/bin/generate-core.sh
/opt/mssql/bin/mssql-conf
/opt/mssql/bin/paldumper
/opt/mssql/bin/sqlpackage
/opt/mssql/bin/sqlservr
/opt/mssql/lib
/opt/mssql/lib/libc++.so.1
/opt/mssql/lib/libc++abi.so.1
/opt/mssql/lib/libjemalloc.so.1
/opt/mssql/lib/libsqlvdi.so
/opt/mssql/lib/libunwind.so.8
/opt/mssql/lib/mssql-conf
/opt/mssql/lib/mssql-conf/checkinstall.sh
/opt/mssql/lib/mssql-conf/checkrunninginstance.sh
/opt/mssql/lib/mssql-conf/collations.txt
/opt/mssql/lib/mssql-conf/disableservice.sh
/opt/mssql/lib/mssql-conf/enableservice.sh
/opt/mssql/lib/mssql-conf/mssql-conf.py
/opt/mssql/lib/mssql-conf/mssqlconfhelper.py
/opt/mssql/lib/mssql-conf/mssqlsettings.py
/opt/mssql/lib/mssql-conf/mssqlsettingsmanager.py
/opt/mssql/lib/mssql-conf/sapassword.sh
/opt/mssql/lib/mssql-conf/set-collation.sh
/opt/mssql/lib/mssql-conf/startservice.sh
/opt/mssql/lib/mssql-conf/stopservice.sh
/opt/mssql/lib/sqlpackage.sfp
/opt/mssql/lib/sqlservr.sfp
/opt/mssql/lib/system.certificates.sfp
/opt/mssql/lib/system.common.sfp
/opt/mssql/lib/system.netfx.sfp
/opt/mssql/lib/system.sfp
/usr/lib/systemd/system/mssql-server.service
/usr/share/doc/mssql-server/LICENSE.TXT
/usr/share/doc/mssql-server/THIRDPARTYNOTICES.TXT
/usr/share/man/man1/mssql-conf.1.gz
/usr/share/man/man1/sqlpackage.1.gz
/usr/share/man/man1/sqlservr.1.gz

From the above output we may find out a bunch of files that are part of the proper functioning of SQL Server. For example, we may notice different script files (either bash and python scripts files), libraries and sfp files (stands for Singe File Package – thanks @slava_oks), man page files and finally the sqlservr binary.

Most of these files are installed in the main hierarchy /opt/mssql/ with the following specific items

  • /opt/mssql/bin/ (binary files and SQL Server crash dump generation scripts)
  • /opt/mssql/lib (sqlserver libraries and sfp files)
  • /opt/mssql/lib/mssql-conf (python and bash scripts)

In addition, installing a SQL Server instance reveals an additional hierarchy /var/opt/mssql with the followings items :

  • data folder = default folder for database data and transaction log files. By the way, system and tempdb database files are located here by default. At the moment of writing this blog, SQL Server is in CTP 1.4 and moving system databases are not supported (by using mssql-conf utility) but we may expect to see this limit removed in the future
  • log folder = log files are stored here. We may retrieve logs related to SQL Server engine (errorlog files), to the SQL Server agent (SQLAGENT.OUT), to the default SQL Server trace and HK engine extended event.
  • secret folder = contains the machine.key file used by the SQL Server engine or potential other files that come with high-availability architectures to store pacemaker credentials for example.
  • conf file = Stores the default location files for user database files and dump files as well.

The last one – mssql.conf – has drawn my attention. According to my tests, it is possible to modify this file manually without getting sides effects but maybe to take with a pinch of salt at this stage.

So here my flexible architecture version for SQL Server:

  • Binary structure
mkdir -p /u00/app/sqlserver
mkdir -p /u00/app/sqlserver/product/14/mssqlserver
mkdir -p /u00/app/sqlserver/local/dmk
mkdir -p /u00/app/sqlserver/local/mssqltools
mkdir -p /u00/app/sqlserver/admin/mssqlserver/etc
mkdir -p /u00/app/sqlserver/admin/mssqlserver/log
mkdir -p /u00/app/sqlserver/admin/mssqlserver/dump

ln -s /opt/mssql/bin /u00/app/sqlserver/product/14/mssqlserver
ln -s /var/opt/mssql/mssql.conf /u00/app/sqlserver/admin/mssqlserver/etc/
ln -s /var/opt/mssql/log/ /u00/app/sqlserver/admin/mssqlserver/log 

Products file consists of SQL Server software that are supplied on the media / package provided by Microsoft.

Administrative files are files containing data about the instance, including SQL Server and SQL Agent error log files, default traces or HK engine extended event files server process diagnostic and finally instance parameter files like mssql.conf file.

  • Database structure
mkdir -p /u01/sqlserverdata/mssqlserver
mkdir -p /u02/sqlserverlog/mssqlserver
mkdir -p /u03/sqlservertempdb/mssqlserver
mkdir -p /u98/sqlserver/backup/mssqlserver

Then let’s configure the correct permissions on the new hierarchy folders

chown -R mssql:mssql /u01/sqlserverdata/mssqlserver
chmod -R 750 /u01/sqlserverdata/mssqlserver
chown -R mssql:mssql /u02/sqlserverlog/mssqlserver
chmod -R 750 /u02/sqlserverlog/mssqlserver
chown -R mssql:mssql /u03/sqlservertempdb/mssqlserver
chmod -R 750 /u03/sqlservertempdb/mssqlserver
chown -R mssql:mssql /u98/sqlserver/backup/mssqlserver
chmod -R 750 /u98/sqlserver/backup/mssqlserver

After configuring the permissions let’s change default path parameters from the mssql-config utility

/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /u01/sqlserverdata/mssqlserver 
/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /u02/sqlserverlog/mssqlserver 
/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /u98/sqlserver/backup/mssqlserver
/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /u00/app/sqlserver/admin/mssqlserver/dump

 

And let’s move system database files (except the master database) by connecting to SQL Server (moving tempdb is supported since the CTP 1.4).

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @path_name NVARCHAR(MAX) = N'/u03/sqlservertempdb/mssqlserver/';
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @path_name NVARCHAR(MAX) = N'/u03/sqlservertempdb/mssqlserver/';

SELECT 
	@sql += N'ALTER DATABASE [tempdb] MODIFY FILE (NAME = ' + name + ', FILENAME = ''' 
	        + @path_name + REVERSE(SUBSTRING(REVERSE(REPLACE(physical_name, 'C:\', '')), 1 , CHARINDEX('\', REVERSE(REPLACE(physical_name, 'C:\', ''))) - 1)) + ''
			+ ''')' + CHAR(13)
FROM sys.master_files
WHERE database_id = 2;

SET @path_name = N'/u01/sqlserverdata/mssqlserver/';

SELECT 
	@sql += N'ALTER DATABASE ' + QUOTENAME(DB_NAME(database_id)) + ' MODIFY FILE (NAME = ' + name + ', FILENAME = ''' 
	        + @path_name + REVERSE(SUBSTRING(REVERSE(REPLACE(physical_name, 'C:\', '')), 1 , CHARINDEX('\', REVERSE(REPLACE(physical_name, 'C:\', ''))) - 1)) + ''
			+ ''')' + CHAR(13)
FROM sys.master_files
WHERE database_id <= 4 
	AND database_id NOT IN (1, 2)
	AND type_desc = 'ROWS';

SET @path_name = N'/u02/sqlserverlog/mssqlserver/';

SELECT 
	@sql += N'ALTER DATABASE ' + QUOTENAME(DB_NAME(database_id)) + ' MODIFY FILE (NAME = ' + name + ', FILENAME = ''' 
	        + @path_name + REVERSE(SUBSTRING(REVERSE(REPLACE(physical_name, 'C:\', '')), 1 , CHARINDEX('\', REVERSE(REPLACE(physical_name, 'C:\', ''))) - 1)) + ''
			+ ''')' + CHAR(13)
FROM sys.master_files
WHERE database_id <= 4 
	AND database_id NOT IN (1, 2)
	AND type_desc = 'LOG'

PRINT @sql

EXEC sp_executesql @sql;
GO

After moving manually system database files by using move command we may finally restart the SQL Server instance:

systemctl restart mssql-server

Moreover, we may also add to the system path the binary structure in place and then change it when a new product is installed

echo 'PATH=”$PATH:/u00/app/sqlserver/product/14/mssqlserver/bin”' >> ~/.bash_profile
echo 'PATH=”$PATH:/u00/app/sqlserver/product/14/mssqlserver/bin”' >> ~/.bashrc
source ~/.bashrc

Invoking mssql-conf is pretty easy in this case as shown below:

[root@sql home]# mssql-conf [options]

Finally you may have have a look at the database files placement :

SELECT 
	DB_NAME(database_id) AS database_name,
	name AS logical_name,
	physical_name
FROM sys.master_files
ORDER BY database_id

Only master database files remain on the default location .. I probably may use symbol links here but to be honest I will wait on next releases to benefit from a more consistent way to move all of system databases by using mssql-conf during the installation process.

Bottom line

Keep in mind that OFA architecture is very flexible and provides only guidelines and best practices. Therefore, we will be able to make adjustments over time as the version evolves. Please feel free to comment. It always be appreciated!

See you

 

 

One Comment

Leave a Reply


5 × = five

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader