天天看點
傳回

MSSQL(SQL Server) on Linux 簡明部署與使用

标簽

PostgreSQL , ms sql , SQL Server

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#%E8%83%8C%E6%99%AF 背景

本文介紹MS SQL on Linux的簡單部署，使用。

https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017

後面簡單介紹如何将MS SQL遷移到PostgreSQL。

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#1-%E7%8E%AF%E5%A2%83 1 環境

CentOS 7.x x64 ECS

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#2-%E7%8E%AF%E5%A2%83%E9%85%8D%E7%BD%AE 2 環境配置

參考MS SQL 如下連結

https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-performance-best-practices?view=sql-server-2017

或參考PostgreSQL OS部分的配置

《PostgreSQL 10 on ECS 實施 流複制備庫鏡像+自動快照備份+自動備份驗證+自動清理備份與歸檔 - 珍藏級》 《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新使用者) - 珍藏級》 《PostgreSQL on Linux 最佳部署手冊 - 珍藏級》 
vi /etc/sysctl.conf  
  
kernel.sched_min_granularity_ns = 10000000  
kernel.sched_wakeup_granularity_ns = 15000000  
vm.swappiness=0  
kernel.numa_balancing=0  
vm.max_map_count=262144  
  
sysctl -p

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#3-%E5%AE%89%E8%A3%85mssql%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BD%AF%E4%BB%B6 3 安裝mssql資料庫軟體 

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo  
  
cat /etc/yum.repos.d/mssql-server.repo   
  
yum install -y mssql-server

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#4-%E5%AE%89%E8%A3%85mssql%E5%AE%A2%E6%88%B7%E7%AB%AF%E8%BD%AF%E4%BB%B6 4 安裝mssql用戶端軟體 

curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo  
  
yum remove unixODBC-utf16 unixODBC-utf16-devel  
  
yum install -y mssql-tools unixODBC-devel

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#5-%E6%9F%A5%E7%9C%8B%E5%AE%89%E8%A3%85%E4%BD%8D%E7%BD%AE 5 檢視安裝位置

檢視rpm包的安裝位置，友善後面配置檔案的設定。

rpm -ql mssql-tools  
rpm -ql mssql-server

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#6-%E9%85%8D%E7%BD%AE%E7%8E%AF%E5%A2%83%E5%8F%98%E9%87%8F 6 配置環境變量

可以将配置放到mssql user shell預設配置中。

su - mssql  
  
vi .bash_profile  
  
export PATH="/opt/mssql-tools/bin:/opt/mssql/bin:$PATH"  
  
export LD_LIBRARY_PATH="/opt/mssql/lib:$LD_LIBRARY_PATH"

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#7-%E9%85%8D%E7%BD%AEsystemd%E6%9C%8D%E5%8A%A1 7 配置systemd服務 

/opt/mssql/bin/mssql-conf setup

輸入MS SQL超級使用者SA密碼

密碼輸入

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#8-%E6%9F%A5%E7%9C%8Bmssql-server%E6%9C%8D%E5%8A%A1%E7%8A%B6%E6%80%81 8 檢視mssql-server服務狀态 

systemctl status mssql-server

目前資料庫未啟動

mssql-server.service - Microsoft SQL Server Database Engine  
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled)  
   Active: inactive (dead)  
     Docs: https://docs.microsoft.com/en-us/sql/linux  
  
Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.81 spid29s     The Service Broker endpoint is in disabled or stopped state.  
Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.81 spid29s     The Database Mirroring endpoint is in disabled or stopped state.  
Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.83 spid29s     Service Broker manager has started.  
Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.84 spid10s     Recovery is complete. This is an informational message only. No user action is required.  
Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z systemd[1]: Stopping Microsoft SQL Server Database Engine...  
Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.43 spid10s     Always On: The availability replica manager is going offline because SQL Server is shutting down. This is an informational mess...ion is required.  
Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.43 spid10s     SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. ...ion is required.  
Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.53 spid29s     Service Broker manager has shut down.  
Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.63 spid10s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.  
Aug 19 10:20:31 iZbp13nu0s9j3x3op4zpd4Z systemd[1]: Stopped Microsoft SQL Server Database Engine.  
Hint: Some lines were ellipsized, use -l to show in full.

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#9-%E6%9F%A5%E7%9C%8Bsystemd%E6%9C%8D%E5%8A%A1%E9%85%8D%E7%BD%AE 9 檢視systemd服務配置 

cat /usr/lib/systemd/system/mssql-server.service  
  
  
[Unit]  
Description=Microsoft SQL Server Database Engine  
After=network.target auditd.service  
Documentation=https://docs.microsoft.com/en-us/sql/linux  
  
[Service]  
ExecStart=/opt/mssql/bin/sqlservr  
User=mssql  
WorkingDirectory=/var/opt/mssql  
  
# Kill root process  
KillMode=process  
  
# Wait up to 30 seconds for service to start/stop  
TimeoutSec=30min  
  
# Remove process, file, thread limits  
#  
LimitNPROC=infinity  
LimitNOFILE=infinity  
TasksMax=infinity  
UMask=007  
  
# Restart on non-successful exits.  
Restart=on-failure  
  
# Don't restart if we've restarted more than 3 times in 2 minutes.  
StartLimitInterval=120  
StartLimitBurst=3  
  
[Install]  
WantedBy=multi-user.target

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#10-systemd%E6%9C%8D%E5%8A%A1%E9%85%8D%E7%BD%AE---%E9%BB%98%E8%AE%A4mssql%E6%95%B0%E6%8D%AE%E6%97%A5%E5%BF%97%E7%9B%AE%E5%BD%95 10 systemd服務配置 - 預設MSSQL資料，日志目錄 

cat /usr/lib/systemd/system/mssql-server.service  
  
  
WorkingDirectory=/var/opt/mssql

工作目錄内容

ll -la /var/opt/mssql  
  
ll -la /data02/mssql/mssql  
total 40  
drwxrwx--- 6 mssql mssql 4096 Aug 19 12:04 .  
drwxr-xr-x 3 mssql mssql 4096 Aug 19 12:06 ..  
-rw------- 1 mssql mssql   64 Aug 19 12:04 .bash_history  
-rw-rw-r-- 1 mssql mssql  114 Aug 19 12:04 .bash_profile  
drwxr-xr-x 2 mssql mssql 4096 Aug 19 10:00 data  
drwxr-xr-x 2 mssql mssql 4096 Aug 19 11:50 log  
-rw-rw-r-- 1 mssql mssql   51 Aug 19 10:51 mssql.conf  
drwxr-xr-x 2 mssql mssql 4096 Aug 19 09:53 secrets  
drwxr-xr-x 5 mssql mssql 4096 Aug 19 09:53 .system  
-rw------- 1 mssql mssql  727 Aug 19 12:04 .viminfo

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#11-%E9%85%8D%E7%BD%AE%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F%E9%98%B2%E7%81%AB%E5%A2%99 11 配置作業系統防火牆

開放端口，允許其他伺服器連接配接MS SQL。

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent  
sudo firewall-cmd --reload

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#12-%E5%85%B3%E9%97%ADsystemd%E8%87%AA%E5%8A%A8%E5%90%AF%E5%8A%A8%E6%9C%8D%E5%8A%A1 12 關閉systemd自動啟動服務 

systemctl disable mssql-server

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#13-%E5%90%AF%E5%8A%A8mssql%E6%95%B0%E6%8D%AE%E5%BA%93 13 啟動MSSQL資料庫 

systemctl start mssql-server

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#14-%E5%85%B3%E9%97%ADmssql%E6%95%B0%E6%8D%AE%E5%BA%93 14 關閉MSSQL資料庫 

systemctl stop mssql-server

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#15-%E8%BD%AC%E7%A7%BBmssql-data-log%E7%9B%AE%E5%BD%95-workingdirectory 15 轉移mssql data, log目錄 (WorkingDirectory)

1、停庫

systemctl stop mssql-server

2、确認已停庫

systemctl status mssql-server

3、轉移WorkingDirectory目錄

# df -h  
Filesystem      Size  Used Avail Use% Mounted on  
/dev/vdc1       1.5T  517G  940G  36% /data02  
/dev/vdb1       1.5T  955G  501G  66% /data01

轉移到資料目錄

# mkdir /data02/mssql  
# chown mssql:mssql /data02/mssql  
# mv /var/opt/mssql /data02/mssql/  
# ln -s /data02/mssql/mssql /var/opt/mssql

4、重新開機mssql

systemctl start mssql-server

5、檢視啟動狀态

systemctl status mssql-server  
  
mssql-server.service - Microsoft SQL Server Database Engine  
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled)  
   Active: active (running) since Sun 2018-08-19 12:17:54 CST; 3s ago  
     Docs: https://docs.microsoft.com/en-us/sql/linux  
 Main PID: 7145 (sqlservr)  
   CGroup: /system.slice/mssql-server.service  
           ├─7145 /opt/mssql/bin/sqlservr  
           └─7147 /opt/mssql/bin/sqlservr  
  
Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.64 Server      Node configuration: node 3: CPU mask: 0x00aaaaaaa0000000:0 Active CPU mask: 0x00aaaaaaa0000000:0. This message provides a descr...ion is required.  
Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.94 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informa...ion is required.  
Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.94 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.  
Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.96 Server      In-Memory OLTP initialized on highend machine.  
Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.01 Server      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File...ion is required.  
Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: ForceFlush is enabled for this instance.  
Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.02 Server      Query Store settings initialized with enabled = 1,  
Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.03 Server      Software Usage Metrics is disabled.  
Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.04 spid11s     Starting up database 'master'.  
Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: ForceFlush feature is enabled for log durability.  
Hint: Some lines were ellipsized, use -l to show in full.

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#16-%E4%BD%BF%E7%94%A8-sqlcmd%E5%AE%A2%E6%88%B7%E7%AB%AF%E8%BF%9E%E6%8E%A5-mssql 16 使用 sqlcmd用戶端連接配接 mssql

使用sqlcmd cli，連接配接mssql，測試

sqlcmd -S localhost -U SA -P '<YourPassword>'  
  
CREATE DATABASE TestDB  
SELECT Name from sys.Databases  
GO  
  
  
USE TestDB  
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)  
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);  
GO  
  
  
SELECT * FROM Inventory WHERE quantity > 152;  
GO  
  
  
QUIT

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#17-mssql-%E7%AE%80%E5%8D%95%E6%80%A7%E8%83%BD%E6%B5%8B%E8%AF%95 17 mssql 簡單性能測試

https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-performance-get-started?view=sql-server-2017

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#18-%E9%85%8D%E7%BD%AE%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8F%82%E6%95%B0 18 配置資料庫參數

https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-2017

例如

vi /var/opt/mssql/mssql.conf 

[sqlagent]
enabled = false

[EULA]
accepteula = Y

[network]
ipaddress = 10.31.124.69
tcpport = 1433

[memory]
memorylimitmb = 16384
  


重新開機執行個體
systemctl restart mssql-server

https://github.com/digoal/blog/blob/master/201808/20180819_01.md#%E5%8F%82%E8%80%83 參考

man sqlservr

man mssql-conf

https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-overview?view=sql-server-2017
sql 關系型資料庫 Linux 資料庫 postgresql linux連接配接sql server linux使用end server Linux系統server linux報錯end server SQL Server事務訂閱
上一篇: PostgreSQL 12 preview - plan_cache_mode參數控制強制使用plan cache或強制custom plan (force_custom_plan and force_generic_plan)
下一篇: Greenplum csvlog(日志資料)檢索、釋義(gp_toolkit.gp_log*)

繼續閱讀