SQL Server on Linux之主體環境設定

2020-05-22

1360
0
[MSSQL]SQL Server on Linux
2020-05-24

本篇介紹SQL Server on Linux主體環境的設定，當我們安裝好mssql-server、mssql-tools等套件，也使用"/opt/mssql/bin/mssql-conf setup"指令來安裝、設定及啟動mssql-server服務後，後續可能還需要異動一些設定，如：啟動Agent Service服務、修改預設的備份路徑、變更SQL Server版本等，此時有三種方式可以參考 -- 使用參數指令、使用參數設定檔、使用環境變數

(1)使用參數指令

# /opt/mssql/bin/mssql-conf list    --列舉所有參數
# /opt/mssql/bin/mssql-conf list | grep -E '(sqlagent|filelocation|hadr|memory)'  --列舉特定參數
-------------------------------------------------------------------------------------------------
--以下為執行結果
filelocation.defaultbackupdir                           備份檔案的預設目錄
filelocation.defaultdatadir                             資料檔案的預設目錄
filelocation.defaultdumpdir                             損毀傾印檔案的預設目錄
filelocation.defaultlogdir                              記錄檔的預設目錄
filelocation.errorlogfile                               錯誤記錄檔位置
filelocation.masterdatafile                             master 資料庫的資料檔案位置
filelocation.masterlogfile                              master 資料庫的記錄檔位置
hadr.hadrenabled                                        允許 SQL Server 對高可用性和災害復原使用可用性群組
memory.memorylimitmb                                    SQL Server 記憶體限制 (MB)
sqlagent.databasemailprofile                            SQL Agent Database Mail 設定檔名稱
sqlagent.enabled                                        啟用或停用 SQLAgent
sqlagent.errorlogfile                                   SQL Agent 記錄檔路徑
sqlagent.errorlogginglevel                              SQL Agent 記錄層次位元遮罩 - 1=錯誤，2=警告，4=資訊
-------------------------------------------------------------------------------------------------

--以啟用Agent Service為例：
--Agent Service從SQL Server 2017 on Linux CU4版本 (2018年2月)後，已包含在主體套件中，不用另行安裝
--在SSMS(Windows環境)中，無法[啟動]/[停止]/[重新啟動]Agent Service，也無法得知其真正的執行狀態(SQL Server on Linux不支援[組態管理員])
--欲查詢Agent Service的執行狀態，請使用T-SQL查詢

# /opt/mssql/bin/mssql-conf set sqlagent.enabled true    --啟用Agent Service，SQL Server 必須重新啟動才可套用這項設定
# systemctl restart mssql-server

(2)使用參數設定檔

--透過修改設定檔方式，也可以變更設定
# cat /var/opt/mssql/mssql.conf
--------------------------------------------
--以下為設定檔部分內容
[sqlagent]
enabled = true

[language]
lcid = 1028

[filelocation]
defaultbackupdir = /var/opt/mssql/backup

[memory]
memorylimitmb = 2048
--------------------------------------------

接續前述的例子，我們將在設定檔直接修改Agent Service的設定，首先確認目前sqllnx5(192.168.56.135)的Agent Service狀態，目前是"Running"

--查詢Agent Service執行狀態
IF EXISTS(
 SELECT 1
 FROM MASTER.dbo.sysprocesses
 WHERE program_name = N'SQLAgent - Generic Refresher')
 BEGIN
 SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Running' AS 'SQLServerAgent Status'
 END
 ELSE
 BEGIN
 SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Stopped' AS 'SQLServerAgent Status'
 END
go
select @@servername
go

# vi /var/opt/mssql/mssql.conf    --修改設定檔
------------------------------
[sqlagent]
enabled = false    --將true改為false
------------------------------

# systemctl restart mssql-server    --重新啟動服務才能套用新的設定值

重新檢視Agent Service的狀態，已變成"Stopped"，所以直接修改設定檔也是可行的

(3)使用環境變數

--(a)使用環境變數來設定主體環境，以"啟用Agent Service"為例：
# MSSQL_AGENT_ENABLED=true /opt/mssql/bin/mssql-conf setup    --必須先停止SQL Server服務，否則會出現警告訊息
# systemctl stop mssql-server
# systemctl status mssql-server
# MSSQL_AGENT_ENABLED=true /opt/mssql/bin/mssql-conf setup    --執行完畢後，mssql-server服務會自動帶起來
# systemctl status mssql-server
# cat /var/opt/mssql/mssql.conf    --檢視[sqlagent]區塊的設定，此時enabled = true

--(b)使用環境變數來設定主體環境，以"修改TCP port"為例：
# systemctl stop mssql-server           --使用mssql-conf指令前，需先停止mssql-server服務
# MSSQL_TCP_PORT=1477 /opt/mssql/bin/mssql-conf setup    --將埠號由TCP 1433修改為TCP 1477

--比較指令參數做法 #/opt/mssql/bin/mssql-conf set network.tcpport 1477，兩者效果相同

# systemctl status mssql-server         --mssql-server服務會自動帶起來

# netstat -tulpn | grep sql    --連線埠號已改用1477 port
tcp        0      0 127.0.0.1:1434          0.0.0.0:*               LISTEN      4312/sqlservr
tcp        0      0 0.0.0.0:1477            0.0.0.0:*               LISTEN      4312/sqlservr
tcp6       0      0 ::1:1434                :::*                    LISTEN      4312/sqlservr
tcp6       0      0 :::1477                 :::*                    LISTEN      4312/sqlservr

--記得修改防火牆設定，否則使用SSMS重新連線至"192.168.56.135,1477"會失敗
# firewall-cmd --list-all
----------------------------------------------------------
internal (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3 enp0s8
  sources:
  services: dhcpv6-client mdns samba samba-client smtp ssh
  ports: 1433/tcp 1434/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
----------------------------------------------------------

# firewall-cmd --permanent --add-port=1477/tcp
# firewall-cmd --reload
# firewall-cmd --list-all
----------------------------------------------------------
internal (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3 enp0s8
  sources:
  services: dhcpv6-client mdns samba samba-client smtp ssh
  ports: 1433/tcp 1434/tcp 1477/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
----------------------------------------------------------

使用SSMS連線至"192.168.56.135,1477"成功

以下是常用的環境變數，先前介紹SQL Server on Container時，我們也有用到下方的部分環境變數，如：ACCEPT_EULA、MSSQL_SA_PASSWORD、MSSQL_PID、MSSQL_BACKUP_DIR...等，可參考【SQL Server on Container起手式】、【永久保存Container中SQL Server資料庫檔案的方式】這兩篇文章

ACCEPT_EULA            --設為任意值可確認您接受終端使用者授權合約
MSSQL_SA_PASSWORD
MSSQL_PID              --SQL Server版本，如Standard/Developer/Express/Evaluation...等
MSSQL_LCID             --SQL Server語言識別碼，如: 1028為繁體中文   --Language Certificate ID(LCID)
MSSQL_COLLATION        --SQL Server預設定序，會覆蓋掉MSSQL_LCID的設定
MSSQL_MEMORY_LIMIT_MB  --預設是總實體記憶體的80%
MSSQL_TCP_PORT
MSSQL_BACKUP_DIR
MSSQL_DATA_DIR
MSSQL_LOG_DIR
MSSQL_MASTER_DATA_FILE
MSSQL_MASTER_LOG_FILE
MSSQL_ENABLE_HADR      --啟用可用性群組 (1或0)
MSSQL_AGENT_ENABLED    --啟用Agent Service (true或false)

<範例練習> 以環境變數來設定主體環境

# mkdir /var/opt/mssql/backup               --建立放置備份檔案的目錄
# ll /var/opt/mssql/                        --檢視"backup"目錄的owner及group
# chown mssql:mssql /var/opt/mssql/backup   --修改目錄權限
# ll /var/opt/mssql
# systemctl stop mssql-server               --停用服務，並以SSMS連線至192.168.56.135看一下記憶體及備份路徑的設定
# MSSQL_LCID=1028 MSSQL_PID="Developer" MSSQL_MEMORY_LIMIT_MB=2048 MSSQL_BACKUP_DIR="/var/opt/mssql/backup" MSSQL_AGENT_ENABLED=true /opt/mssql/bin/mssql-conf setup
# systemctl status mssql-server             --服務會自動啟動

# set | grep MSSQL    --查不到剛剛設定的環境變數(MSSQL_LCID、MSSQL_PID, MSSQL_MEMORY_LIMIT_MB, MSSQL_BACKUP_DIR、MSSQL_AGENT_ENABLED)

--將環境變數改透過export指令來設定
# vi /etc/profile.d/mssqlpath.sh    --前面在安裝SQL Server工具套件(mssql-tools)及SSIS套件(mssql-is)時，有設定過PATH環境變數

--在mssqlpath.sh中，增加以下內容(注意: 若此處的環境變數有異動或增減，記得重新執行#/opt/mssql/bin/mssql-conf setup，如此才會重新套用新的設定)
-------------------------------------------------------------------
export MSSQL_LCID=1028
export MSSQL_PID="Developer"
export MSSQL_MEMORY_LIMIT_MB=2048
export MSSQL_BACKUP_DIR="/var/opt/mssql/backup"
export MSSQL_AGENT_ENABLED=true
-------------------------------------------------------------------

--記得登出root後，再重新登入(重要步驟)
# set | grep MSSQL                     --已經可以查到MSSQL相關的環境變數了

# /opt/mssql/bin/mssql-conf setup      --此時，我們重新執行mssql-conf指令來設定主體環境，會發現僅需要輸入sa密碼，其他的步驟都省略了
# systemctl status mssql-server        --服務自動啟動了

--最後使用SSMS工具來觀察備份路徑是否已改變、Agent Service是否已啟動...等

<補充說明>

1. 需留意，有些主體環境設定方式，必須先將mssql-server服務stop後才能設定，如：設定sa帳號的密碼，或以"環境變數"方式啟用Agent Service時

# /opt/mssql/bin/mssql-conf set-sa-password
# MSSQL_AGENT_ENABLED=true /opt/mssql/bin/mssql-conf setup

2. 另外有些設定方式，在變更設定時，雖然不用將mssql-server服務stop，但設定完畢後需要重新啟動mssql-server服務才能生效，如: 以"/opt/mssql/bin/mssql-conf set sqlagent.enabled true"來啟用Agent Service時

# /opt/mssql/bin/mssql-conf set sqlagent.enabled true

[參考資料]：

1. 使用 mssql-conf 工具在 Linux 上設定 SQL Server

2. 使用環境變數在 Linux 上設定 SQL Server 設定

Jay Huang

MSSQL

回首頁

橄欖葉子

Jay Huang的隨記二三事