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

digoal 2018-08-19

479

作者

digoal

日期

2018-08-19

背景

本文介绍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 ''

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