個性化配置你的SQLServeronLinux

風移發表於2016-12-27
SQLServerLinux

問題引入

這天老鳥滿面春風找到菜鳥：“鳥兒啊，看你最近研究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;

查詢結果截圖如下：
01.png
這裡必須要吐槽，不吐不快，從SSMS檢視資料庫的物理位置，有兩點很難理解，當然不知道微軟後續的發版會不會改善：
Windows是使用反斜線“”來劃分目錄結構，而Linux系統使用的是正斜線“/”。
查詢出來的結果集目錄中會強制新增C：盤地址。
最終的對應關係是：Linux上的/var/opt/mssql/data對應於Windows上的目錄C:varoptmssqldata。吐槽截圖如下：
02.png

個性化配置指令碼

在最開始做個性化配置指令碼時候，我所有的這些目錄是放在/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:dataSQL` 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:dataLOG` 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:dataDUMP` 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:dataBACKUP` 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:dataSQL	empdb.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:dataSQL	empdb.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:dataSQL	empdb.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.

截個圖留戀，證明下：
03.png

有了這層意思的精神領會以後，解決問題的方法就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;

查詢結果展示如下：
04.png
使用Linux的tree命令看看/data目錄檔案結構，如下圖展示：
05.png
從SSMS查詢結果和Linux目錄結構來看，所有現象都滿足預期，菜鳥對SQL Server On Linux進行了個性化配置。

寫在最後

過程歷經艱辛，但最終我們到達了成功的彼岸，總算對SQL Server On Linux進行了個性化的配置。就像我們最開始研究Linux版SQL Server說的那樣，他還是隻個剛出生的孩子，還有很多不完善的地方，甚至看起來還有點怪異。但是，讓我們充滿期望，給予寬容和友善讓他茁壯成長。


相關文章