Mssql 2017修改master默认排序规则
将sqlserver数据库从Windows服务器迁移到了linux服务器，经过一番折腾终于是安装好了。数据库也正常运行，但下午的时候同事告诉我说，软件在做操作的时候提示排序规则有问题。部分中文乱码，显示为问号，如下图所示
经过对比迁移前后的数据库master，发现：Windows下Sqlserver排序规则为：Chinese_PRC_CI_AS,Linux系统下则为SQL_Latin1_General_CP1_CI_AS。于是就想到了修改排序规则，很快查到一些资料。
1.网上所说的方法，在sqlcmd命令下执行
ALTER DATABASE [数据库名] COLLATE Chinese_PRC_CI_AS;
这种方法对于master这类系统级别的表没有任何作用，该命令只适用于系统表除外的表。
修改失败后，继续查找能修改系统表排序规则的方法。问过很多人，查找过很多资料，没啥用。
无意间发现之前自己安装mssql时写了一篇博客：里面有用到mssql-conf这个工具，然后就查看了一下该命令用法。发现里面有个参数(set-collation)可以直接修改系统表排序规则
具体参数如下
root@newings:/opt/mssql/bin# sudo /opt/mssql/bin/mssql-conf usage: mssql-conf [-h] [-n] ... positional arguments: setup 初始化并设置 Microsoft SQL Server。 set 设置某个设置的值 unset 取消设置某个设置的值 list 列出受支持的设置 traceflag 启用/禁用一个或多个跟踪标志 set-sa-password 设置系统管理员(SA)密码 set-collation 设置系统数据库的排序规则 validate 验证配置文件 set-edition 设置 SQL Server 实例的版本 optional arguments: -h, --help show this help message and exit -n, --noprompt 不提示用户并使用环境变量或默认值。
第一次没成功。如下所示
root@newings:/opt/mssql/bin# sudo /opt/mssql/bin/mssql-conf set-collation Enter the collation: Chinese_PRC_CI_AS 正在配置 SQL Server... 2019-04-19 13:07:06.51 Server Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Nov 30 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) 2019-04-19 13:07:06.52 Server UTC adjustment: 8:00 2019-04-19 13:07:06.52 Server (c) Microsoft Corporation. 2019-04-19 13:07:06.52 Server All rights reserved. 2019-04-19 13:07:06.52 Server Server process ID is 4124. 2019-04-19 13:07:06.52 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'. 2019-04-19 13:07:06.52 Server Registry startup parameters: -d /var/opt/mssql/data/master.mdf -l /var/opt/mssql/data/mastlog.ldf -e /var/opt/mssql/log/errorlog 2019-04-19 13:07:06.52 Server Command Line Startup Parameters: -q "Chinese_PRC_CI_AS" 2019-04-19 13:07:06.52 Server Error: 17113, Severity: 16, State: 1. 2019-04-19 13:07:06.52 Server Error 5(Access is denied.) occurred while opening file '/var/opt/mssql/data/master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
看到这个东西也是一头雾水啊，完全不知道从哪里下手，又折腾了好半天。还是没搞定，这时候想到了，如果我重新装一个数据库直接修改应该成功吧。
果然，装完数据库，啥都没干我就直接执行了下面的命令。成功了
sudo /opt/mssql/bin/mssql-conf set-collation
结合第二次和第三次操作过程，发现。执行该命令，数据库必须处于停止状态，如果在运行状态是会报错的！！！
第三次执行如下所示，命令显示不全，但不影响。
-e /var/opt/mssql/log/errorlog 2019-04-19 12:41:20.81 Server Command Line Startup Parameters: -q "Chinese_PRC_CI_AS" 2019-04-19 12:41:20.81 Server SQL Server detected 1 sockets with 4 cores per socket and 8 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required. 2019-04-19 12:41:20.81 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 2019-04-19 12:41:20.81 Server Detected 25706 MB of RAM. This is an informational message; no user action is required. 2019-04-19 12:41:20.81 Server Using conventional memory in the memory manager. 2019-04-19 12:41:20.81 Server Large Page Allocated: 32MB 2019-04-19 12:41:20.90 Server Buffer pool extension is already disabled. No action is necessary. 2019-04-19 12:41:20.95 Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled. 2019-04-19 12:41:20.96 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled. 2019-04-19 12:41:20.96 Server Implied authentication manager initialization failed. Implied authentication will be disabled. 2019-04-19 12:41:20.97 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['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-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA']. 2019-04-19 12:41:20.98 Server The maximum number of dedicated administrator connections for this instance is '1' 2019-04-19 12:41:20.98 Server Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 2019-04-19 12:41:20.99 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required. 2019-04-19 12:41:20.99 Server In-Memory OLTP initialized on standard machine. 2019-04-19 12:41:21.09 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required. ForceFlush is enabled for this instance. 2019-04-19 12:41:21.09 Server Database Mirroring Transport is disabled in the endpoint configuration. 2019-04-19 12:41:21.09 spid5s Warning ****************** 2019-04-19 12:41:21.09 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required. 2019-04-19 12:41:21.10 Server Query Store settings initialized with enabled = 1, 2019-04-19 12:41:21.10 spid5s Starting up database 'master'. 2019-04-19 12:41:21.10 Server Software Usage Metrics is disabled. ForceFlush feature is enabled for log durability. 2019-04-19 12:41:21.44 spid5s SQL Server Audit is starting the audits. This is an informational message. No user action is required. 2019-04-19 12:41:21.44 spid5s SQL Server Audit has started the audits. This is an informational message. No user action is required. 2019-04-19 12:41:21.61 spid5s SQL Trace ID 1 was started by login "sa". 2019-04-19 12:41:21.72 spid5s Server name is 'newings'. This is an informational message only. No user action is required. 2019-04-19 12:41:21.72 spid5s Always On Availability Groups was not started because the SQL Server instance is running in single-user mode. This is an informational message. No user action is required. 2019-04-19 12:41:21.72 spid23s Starting up database 'jsswak-jinhua'. 2019-04-19 12:41:21.72 spid22s Starting up database 'jsswak-hn'. 2019-04-19 12:41:21.72 spid9s Starting up database 'mssqlsystemresource'. 2019-04-19 12:41:21.72 spid21s Starting up database 'jsswak-hz'. 2019-04-19 12:41:21.73 spid27s Starting up database 'jsswak-jx'. 2019-04-19 12:41:21.72 spid20s Starting up database 'msdb'. 2019-04-19 12:41:21.72 spid25s Starting up database 'jsswak-zh'. 2019-04-19 12:41:21.73 spid26s Starting up database 'KSSTHLYDB'. 2019-04-19 12:41:21.73 spid9s The resource database build version is 14.00.3048. This is an informational message only. No user action is required. 2019-04-19 12:41:21.72 spid24s Starting up database 'jsswak-sjs'. 2019-04-19 12:41:21.74 spid9s Starting up database 'model'. 2019-04-19 12:41:21.85 spid18s A self-generated certificate was successfully loaded for encryption. 2019-04-19 12:41:21.85 spid18s Server is listening on [ 'any' <ipv6> 1433]. 2019-04-19 12:41:21.85 spid18s Server is listening on [ 'any' <ipv4> 1433]. 2019-04-19 12:41:21.86 Server Server is listening on [ ::1 <ipv6> 1434]. 2019-04-19 12:41:21.86 Server Server is listening on [ 127.0.0.1 <ipv4> 1434]. 2019-04-19 12:41:21.86 Server Dedicated admin connection support was established for listening locally on port 1434. 2019-04-19 12:41:21.87 spid18s SQL Server is now ready for client connections. This is an informational message; no user action is required. 2019-04-19 12:41:22.18 spid22s Parallel redo is started for database 'jsswak-hn' with worker pool size [4]. 2019-04-19 12:41:22.77 spid23s Parallel redo is started for database 'jsswak-jinhua' with worker pool size [4]. 2019-04-19 12:41:22.84 spid5s Parallel redo is shutdown for database 'jsswak-hn' with worker pool size [4]. 2019-04-19 12:41:22.87 spid24s Parallel redo is started for database 'jsswak-sjs' with worker pool size [4]. 2019-04-19 12:41:23.24 spid21s Parallel redo is started for database 'jsswak-hz' with worker pool size [4]. 2019-04-19 12:41:23.27 spid26s Parallel redo is started for database 'KSSTHLYDB' with worker pool size [4]. 2019-04-19 12:41:23.28 spid5s Parallel redo is shutdown for database 'jsswak-jinhua' with worker pool size [4]. 2019-04-19 12:41:23.49 spid5s Parallel redo is shutdown for database 'jsswak-sjs' with worker pool size [4]. 2019-04-19 12:41:23.70 spid5s Parallel redo is shutdown for database 'jsswak-hz' with worker pool size [4]. 2019-04-19 12:41:23.70 spid27s Parallel redo is started for database 'jsswak-jx' with worker pool size [4]. 2019-04-19 12:41:23.90 spid9s Polybase feature disabled. 2019-04-19 12:41:23.90 spid9s Clearing tempdb database. 2019-04-19 12:41:24.07 spid24s Parallel redo is shutdown for database 'KSSTHLYDB' with worker pool size [4]. 2019-04-19 12:41:24.17 spid25s Parallel redo is started for database 'jsswak-zh' with worker pool size [4]. 2019-04-19 12:41:24.21 spid5s Parallel redo is shutdown for database 'jsswak-jx' with worker pool size [4]. 2019-04-19 12:41:24.64 spid20s Parallel redo is shutdown for database 'jsswak-zh' with worker pool size [4]. 2019-04-19 12:41:25.41 spid9s Starting up database 'tempdb'. 2019-04-19 12:41:25.67 spid9s The tempdb database has 1 data file(s). 2019-04-19 12:41:25.77 spid5s Error: 49958, Severity: 21, State: 1. 2019-04-19 12:41:25.77 spid5s The server collation cannot be changed with user databases attached. Please detach user databases before changing server collation. 2019-04-19 12:41:25.77 spid5s Error: 2745, Severity: 16, State: 2. 2019-04-19 12:41:25.77 spid5s Process ID 5 has raised user error 49958, severity 21. SQL Server is terminating this process. 2019-04-19 12:41:25.77 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
执行到tempdb的时候，就报错了！请留这句话
The server collation cannot be changed with user databases attached. Please detach user databases before changing server collation.
这句话，大概意思就是，在修改系统库排序规则的时候，请将将数据库（系统库除外的库）分离后再执行操作！！！
最后一次，在做完数据库分离后执行命令果然是成功了。
2019-04-19 12:50:15.62 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required. 服务器排序规则已更改。 请运行 "sudo systemctl start mssql-server" 以启用 SQL Server。
执行成功提示。
mssql 2017 ubuntu安装篇：https://www.cnblogs.com/Roobbin/p/9626508.html
