配置 sql server linux,配置SQL Server on Linux（2）

1. 前言

前一篇配置SQL Server on Linux(1)，地址：http://www.cnblogs.com/fishparadise/p/8125203.html ，是关于更改数据库排序规则的。实现的原理跟在Windows平台差不多，都是需要备份用户数据库，重建系统数据库来实现的，不过操作过程简化了。下面的配置是一些常规的设置，比如最大内存，默认数据文件位置，等。

2. 环境

Linux: CentOS 7.4，SQL Server 2017 (RC2) - 14.0.900.75 (X64)

3. 更改设置

3.1 内存限制

使用mssql-conf工具

/opt/mssql/bin/mssql-conf set memory.memorylimitmb 3500

重启数据库实例

systemctl restart mssql-server

疑问

以上方法是官方的步骤，但使用SSMS去查看实例的内存设置，发现最大内存限制还是没有改变。不知道为什么。使用sys.sp_configure去配置最大内存则生效了。

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'max server memory (MB)', N'3500'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

GO

3.2 设置默认数据或日志目录位置

创建自定义目录及更改目录权限

[root@134test ~]# mkdir -p /data/mssql_data/

[root@134test ~]# chown -R mssql:mssql /data/mssql_data/

使用mssql-conf工具

[root@134test ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql_data/

SQL Server needs to be restarted in order to apply this setting. Please run

'systemctl restart mssql-server.service'.

重启数据库实例生效

systemctl restart mssql-server

测试(数据和日志文件都在这个目录下)

4> create database db2;

5> go

[root@134test ~]# cd /data/mssql_data/

[root@134test /data/mssql_data]# ll db*

-rw-rw---- 1 mssql mssql 3932160 2017-12-27 12:31 db1_log.ldf

-rw-rw---- 1 mssql mssql 12582912 2017-12-27 12:31 db1.mdf

-rw-rw---- 1 mssql mssql 8388608 2017-12-27 12:37 db2_log.ldf

-rw-rw---- 1 mssql mssql 8388608 2017-12-27 12:33 db2.mdf

如果需要单独更改日志的目录(如/tmp)

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /tmp/

3.3 更改 TCP 端口

使用mssql-conf工具

/opt/mssql/bin/mssql-conf set network.tcpport

重启实例生效

[root@134test ~]# systemctl restart mssql-server

测试

[root@134test ~# /opt/mssql/bin/mssql-conf set network.tcpport 1444

SQL Server needs to be restarted in order to apply this setting. Please run

'systemctl restart mssql-server.service'.

[root@134test ~]# systemctl restart mssql-server

[root@134test ~]# sqlcmd -S localhost -U sa

Password:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred

while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct

and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

[root@134test ~]# sqlcmd -S localhost,1444 -U sa

Password:

1>

3.4 删除设置

使用mssql-conf工具的unset命令

/opt/mssql/bin/mssql-conf unset network.tcpport

/opt/mssql/bin/mssql-conf unset memory.memorylimitmb

……

重启实例生效

[root@134test ~]# systemctl restart mssql-server

3.5 查看当前设置及配置文件

cat /var/opt/mssql/mssql.conf

以下是官方提供的一个示例配置。可根据实际需要直接修改/var/opt/mssql/mssql.conf，最后重启数据库实例生效。未在此文件中显示的所有设置均使用其默认值。

[EULA]

accepteula = Y

[coredump]

captureminiandfull = true

coredumptype = full

[filelocation]

defaultbackupdir = /var/opt/mssql/data/

defaultdatadir = /var/opt/mssql/data/

defaultdumpdir = /var/opt/mssql/data/

defaultlogdir = /var/opt/mssql/data/

[hadr]

hadrenabled = 0

[language]

lcid = 1033

[memory]

memorylimitmb = 4096

[network]

forceencryption = 0

ipaddress = 10.192.0.0

kerberoskeytabfile = /var/opt/mssql/secrets/mssql.keytab

tcpport = 1401

tlscert = /etc/ssl/certs/mssql.pem

tlsciphers = ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA

tlskey = /etc/ssl/private/mssql.key

tlsprotocols = 1.2,1.1,1.0

[sqlagent]

databasemailprofile = default

errorlogfile = /var/opt/mssql/log/sqlagentlog.log

errorlogginglevel = 7

[telemetry]

customerfeedback = true

userrequestedlocalauditdirectory = /tmp/audit

[traceflag]

traceflag0 = 1204

traceflag1 = 2345

traceflag = 3456

4. 参考

更多的配置，请参考微软的官方文档：https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-configure-mssql-conf