个性化配置你的SQL Server on Linux

问题引入

这天老鸟满面春风找到菜鸟：“鸟儿啊，看你最近研究SQL Server On Linux如鱼得水，干得不错啊。不过呢，这是一个张扬个性的年代，要创新，要与众不同，那你怎么在Linux平台对SQL Server做个性化的配置吧？比如，我们数据库文件存放的路径，备份文件存放的路径，排序规则等等”。
“鸟哥，今天心情不错啊，捡到金子啦？得了，这就按您的指示办去”。

问题分析

老鸟这个需求还是很有现实意义的，比如在Windows平台，我们会标准化SQL Server数据库文件存放的路径，定制化排序规则等。比如：mdf文件会存放在D:DATADBNameSQL目录，ldf文件会存放在D:DATADBNameLOG目录下。让我们看看SQL Server On Linux是怎么张扬它的个性化配置的。

个性化配置

在个性化配置之前，让我们来看SQL Server On Linux的默认配置是什么样子。

数据存放位置和排序规则

查看数据库物理文件的存放位置，只需要访问视图sys.master_files，默认排序规则访问视图sys.databases。脚本如下：

USE master
GO

SELECT 
    database_name = db_name(database_id) 
    ,file_name = name
    ,physical_name 
FROM sys.master_files

SELECT 
    database_name = name
    , collation_name 
FROM sys.databases;  

查询结果截图如下：

这里必须要吐槽，不吐不快，从SSMS查看数据库的物理位置，有两点很难理解，当然不知道微软后续的发版会不会改善：
Windows是使用反斜线“”来划分目录结构，而Linux系统使用的是正斜线“/”。
查询出来的结果集目录中会强制添加C：盘地址。
最终的对应关系是：Linux上的/var/opt/mssql/data对应于Windows上的目录C:varoptmssqldata。吐槽截图如下：

个性化配置脚本

在最开始做个性化配置脚本时候，我所有的这些目录是放在/var/opt/mssql/data/这个根目录下，后来发现这种做法有一个很大的坑，后面的“一个深坑”错误处理时会讲到。后来，我把根目录放到/data/下，算是踩过了这个坑。这么大的一个深坑，微软的攻城狮，你出来，我们聊聊，我保证不打残你。

# vim reconfMSSQLOnLinux.sh
#!/bin/sh

# --------------------------------------------
#
# purpose: 
# this script is for MSSQL on Linux
# reconfiguration.
#
# --------------------------------------------

# change mssql-server listen on 1435 from 1433

echo "change mssql-server listen port to 1435"
/opt/mssql/bin/mssql-conf set tcpport 1435

# Change the default data directory location
echo "change mssql-server default data directory location"
mkdir /data/SQL
chown mssql /data/SQL && chgrp mssql /data/SQL
/opt/mssql/bin/mssql-conf set defaultdatadir /data/SQL

# Change the default log directory location
echo "change mssql-server default log directory location"
mkdir /data/LOG
chown mssql /data/LOG && chgrp mssql /data/LOG
/opt/mssql/bin/mssql-conf set defaultlogdir /data/LOG

# Change the default dump directory location
echo "change mssql-server default dump directory location"
mkdir /data/DUMP
chown mssql /data/DUMP && chgrp mssql /data/DUMP
/opt/mssql/bin/mssql-conf set defaultdumpdir /data/DUMP

# Change the default backup directory location
echo "change mssql-server default backup direcotry location"
mkdir /data/BACKUP
chown mssql /data/BACKUP && chgrp mssql /data/BACKUP
/opt/mssql/bin/mssql-conf set defaultbackupdir /data/BACKUP

# Enable/Disable traceflags
echo "mssql-server trace flag 1222 & 1204 enabled"
/opt/mssql/bin/mssql-conf traceflag 1222 1204 on

# Change the SQL Server collation
echo "change mssql-server default collation"
/opt/mssql/bin/mssql-conf set-collation

[root@localhost ~]# chmod +x reconfMSSQLOnLinux.sh

执行配置脚本

仔细看这个返回结果，也是在根目录/data/前加了盘符C：，始终觉得怪怪的。最后，输入新的排序规则确认后，一会儿SQL Server就会完成个性化的设置并重启。

# sh reconfMSSQLOnLinux.sh
change mssql-server listen port to 1435
Applying value '1435' to 'tcpport'.
Restart of SQL Server is required to apply this setting. Please run 'systemctl restart mssql-server.service' when ready.
change mssql-server default data directory location
Applying value 'C:\data\SQL' to 'defaultdatadir'.
Restart of SQL Server is required to apply this setting. Please run 'systemctl restart mssql-server.service' when ready.
change mssql-server default log directory location
Applying value 'C:\data\LOG' to 'defaultlogdir'.
Restart of SQL Server is required to apply this setting. Please run 'systemctl restart mssql-server.service' when ready.
change mssql-server default dump directory location
Applying value 'C:\data\DUMP' to 'defaultdumpdir'.
Restart of SQL Server is required to apply this setting. Please run 'systemctl restart mssql-server.service' when ready.
change mssql-server default backup direcotry location
Applying value 'C:\data\BACKUP' to 'defaultbackupdir'.
Restart of SQL Server is required to apply this setting. Please run 'systemctl restart mssql-server.service' when ready.
mssql-server trace flag 1222 & 1204 enabled
Applying traceflag '1222'.
Restart of SQL Server is required to apply this setting. Please run 'systemctl restart mssql-server.service' when ready.
Applying traceflag '1204'.
Restart of SQL Server is required to apply this setting. Please run 'systemctl restart mssql-server.service' when ready.
change mssql-server default collation
This option will change the collation for SQL Server and RESTART the server. Continue [Yes/No]: Yes
Enter the collation: Chinese_PRC_CI_AS

一个深坑

在最开始测试过程中，所有目录放到根目录/var/opt/mssql/data/下，执行会出现以下异常信息，无法创建tempdb，错误的原因是在C:dataSQL目录下不存在tempdb.mdf文件。第一反应是，什么东西啊？C:dataSQLtempdb.mdf？我没有配置任何文件放到这个目录啊，还C盘呢？你以为是你的Windows操作系统吗？
最后聪明伶俐，人见人爱，车间车爆胎的菜鸟，大胆猜测，小心求证，终于搞明白了。反过来猜想，估计微软这帮攻城狮只截取了最后两层目录，比如/var/opt/mssql/data/SQL，他们会把/data/SQL做为数据库数据文件的存放目录，实在是令人着急。来看看错误信息:

2016-12-14 09:24:35.40 spid6s      Error: 5123, Severity: 16, State: 1.
2016-12-14 09:24:35.40 spid6s      CREATE FILE encountered operating system error 31(A device attached to the system is not functioning.) while attempting to open or create the physical file 'C:\data\SQL\tempdb.mdf'.
2016-12-14 09:24:35.44 spid6s      Error: 17204, Severity: 16, State: 1.
2016-12-14 09:24:35.44 spid6s      FCB::Open failed: Could not open file C:\data\SQL\tempdb.mdf for file number 1.  OS error: 2(The system cannot find the file specified.).
2016-12-14 09:24:35.44 spid6s      Error: 5120, Severity: 16, State: 101.
2016-12-14 09:24:35.44 spid6s      Unable to open the physical file "C:\data\SQL\tempdb.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
2016-12-14 09:24:35.45 spid6s      Error: 1802, Severity: 16, State: 4.
2016-12-14 09:24:35.45 spid6s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2016-12-14 09:24:35.45 spid6s      Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the operating system error log that may indicate why the tempdb files could not be initialized.

截个图留恋，证明下：

有了这层意思的精神领会以后，解决问题的方法就So easy了。

# mkdir -p /data/SQL
# mkdir -p /data/LOG
# chown mssql -R /data
# chgrp mssql -R /data

再次重试个性化配置脚本sh reconfMSSQLOnLinux.sh，这次总算成功了，谢天谢地。

打开防火墙

为了让外部客户端SSMS连接重新配置后的实例，请打开防火墙1435端口

[root@localhost ~]# firewall-cmd --zone=public --add-port=1435/tcp --permanent
success
[root@localhost ~]# firewall-cmd --reload
success

再次检查数据存放位置

重新配置完成后，接下来做如下测试：
创建数据：看看数据库mdf和ldf文件会不会放到/data/SQL和/data/LOG目录下。
备份数据库：看看数据库备份文件会不会放到/data/BACKUP目录。
排序规则检查：检查数据库是否是新的排序规则Chinese_PRC_CI_AS

create database TestDB2;
GO

BACKUP DATABASE [TestDB2] TO DISK = N'TestDB2_full_20161213.bak' 
WITH NOFORMAT, NOINIT, NAME = 'TestDB2-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10


BACKUP LOG [TestDB2] TO  
DISK = N'TestDB2_LogBackup_20161213.bak' WITH NOFORMAT
, NOINIT,  NAME = N'TestDB2_LogBackup_2016-12-13'
, NOSKIP, NOREWIND, NOUNLOAD ,  STATS = 5
GO

USE master
GO
SELECT 
    database_name = db_name(database_id) 
    ,file_name = name
    ,physical_name 
FROM sys.master_files

SELECT 
    database_name = name
    , collation_name 
FROM sys.databases;  

查询结果展示如下：

使用Linux的tree命令看看/data目录文件结构，如下图展示：

从SSMS查询结果和Linux目录结构来看，所有现象都满足预期，菜鸟对SQL Server On Linux进行了个性化配置。

写在最后

过程历经艰辛，但最终我们到达了成功的彼岸，总算对SQL Server On Linux进行了个性化的配置。就像我们最开始研究Linux版SQL Server说的那样，他还是只个刚出生的孩子，还有很多不完善的地方，甚至看起来还有点怪异。但是，让我们充满期望，给予宽容和友善让他茁壮成长。