MSSQL(SQL Server) on Linux 简明部署与使用

4 minute read

背景

本文介绍MS SQL on Linux的简单部署，使用。

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

后面简单介绍如何将MS SQL迁移到PostgreSQL。

1 环境

CentOS 7.x x64 ECS

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  

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  

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  

5 查看安装位置

查看rpm包的安装位置，方便后面配置文件的设置。

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

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"

7 配置systemd服务

/opt/mssql/bin/mssql-conf setup

输入MS SQL超级用户SA密码

密码输入

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.  

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  

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  

11 配置操作系统防火墙

开放端口，允许其他服务器连接MS SQL。

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

12 关闭systemd自动启动服务

systemctl disable mssql-server

13 启动MSSQL数据库

systemctl start mssql-server

14 关闭MSSQL数据库

systemctl stop mssql-server

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.  

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  

17 mssql 简单性能测试

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

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

参考

man sqlservr

man mssql-conf

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