查看: 352|回复: 0

[SQLServer] 配置SQL Server on Linux(2)

发表于 2018-1-8 08:00:02
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工具

  1. /opt/mssql/bin/mssql-conf set memory.memorylimitmb 3500
复制代码

重启数据库实例

  1. systemctl restart mssql-server
复制代码

疑问

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

  1. EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
  2. GO
  3. EXEC sys.sp_configure N'max server memory (MB)', N'3500'
  4. GO
  5. RECONFIGURE WITH OVERRIDE
  6. GO
  7. EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
  8. GO
复制代码

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

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

  1. [root@134test ~]# mkdir -p /data/mssql_data/
  2. [root@134test ~]# chown -R mssql:mssql /data/mssql_data/
复制代码

使用mssql-conf工具

  1. [root@134test ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql_data/
  2. SQL Server needs to be restarted in order to apply this setting. Please run
  3. 'systemctl restart mssql-server.service'.
复制代码

重启数据库实例生效

  1. systemctl restart mssql-server
复制代码

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

  1. 4> create database db2;
  2. 5> go
复制代码

  1. [root@134test ~]# cd /data/mssql_data/
  2. [root@134test /data/mssql_data]# ll db*
  3. -rw-rw---- 1 mssql mssql 3932160 2017-12-27 12:31 db1_log.ldf
  4. -rw-rw---- 1 mssql mssql 12582912 2017-12-27 12:31 db1.mdf
  5. -rw-rw---- 1 mssql mssql 8388608 2017-12-27 12:37 db2_log.ldf
  6. -rw-rw---- 1 mssql mssql 8388608 2017-12-27 12:33 db2.mdf
复制代码

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

  1. /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /tmp/
复制代码

3.3 更改 TCP 端口

使用mssql-conf工具

  1. /opt/mssql/bin/mssql-conf set network.tcpport <new_tcp_port>
复制代码

重启实例生效

  1. [root@134test ~]# systemctl restart mssql-server
复制代码

测试

  1. [root@134test ~# /opt/mssql/bin/mssql-conf set network.tcpport 1444
  2. SQL Server needs to be restarted in order to apply this setting. Please run
  3. 'systemctl restart mssql-server.service'.
  4. [root@134test ~]# systemctl restart mssql-server
复制代码

  1. [root@134test ~]# sqlcmd -S localhost -U sa
  2. Password:
  3. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
  4. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
  5. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred <br>while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct <br>and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
复制代码

  1. [root@134test ~]# sqlcmd -S localhost,1444 -U sa
  2. Password:
  3. 1>
复制代码

3.4 删除设置

使用mssql-conf工具的unset命令

  1. /opt/mssql/bin/mssql-conf unset network.tcpport
  2. /opt/mssql/bin/mssql-conf unset memory.memorylimitmb
  3. ……
复制代码

重启实例生效

  1. [root@134test ~]# systemctl restart mssql-server
复制代码

3.5 查看当前设置及配置文件
  1. cat /var/opt/mssql/mssql.conf
复制代码

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

  1. [EULA]
  2. accepteula = Y
  3. [coredump]
  4. captureminiandfull = true
  5. coredumptype = full
  6. [filelocation]
  7. defaultbackupdir = /var/opt/mssql/data/
  8. defaultdatadir = /var/opt/mssql/data/
  9. defaultdumpdir = /var/opt/mssql/data/
  10. defaultlogdir = /var/opt/mssql/data/
  11. [hadr]
  12. hadrenabled = 0
  13. [language]
  14. lcid = 1033
  15. [memory]
  16. memorylimitmb = 4096
  17. [network]
  18. forceencryption = 0
  19. ipaddress = 10.192.0.0
  20. kerberoskeytabfile = /var/opt/mssql/secrets/mssql.keytab
  21. tcpport = 1401
  22. tlscert = /etc/ssl/certs/mssql.pem
  23. 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
  24. tlskey = /etc/ssl/private/mssql.key
  25. tlsprotocols = 1.2,1.1,1.0
  26. [sqlagent]
  27. databasemailprofile = default
  28. errorlogfile = /var/opt/mssql/log/sqlagentlog.log
  29. errorlogginglevel = 7
  30. [telemetry]
  31. customerfeedback = true
  32. userrequestedlocalauditdirectory = /tmp/audit
  33. [traceflag]
  34. traceflag0 = 1204
  35. traceflag1 = 2345
  36. traceflag = 3456
复制代码

4. 参考

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




上一篇:小刘同学的第七篇博文
下一篇:sql
回复

使用道具 举报