1. 前言 SQL Server一般是在安裝過程中進行相關的配置，安裝完成之後，再去修改有一些配置就比較麻煩，比如更改SQL Server實例級別的排序規則。但在Linux下，安裝過程並沒有很多可以配置的步驟，安裝步驟變得很簡單，更多的配置可以通過/opt/mssql/bin/mssql-conf工 ...
1. 前言
SQL Server一般是在安裝過程中進行相關的配置，安裝完成之後，再去修改有一些配置就比較麻煩，比如更改SQL Server實例級別的排序規則。但在Linux下，安裝過程並沒有很多可以配置的步驟，安裝步驟變得很簡單，更多的配置可以通過/opt/mssql/bin/mssql-conf工具進行。
2. 環境
Linux: CentOS 7.4，SQL Server 2017 (RC2) - 14.0.900.75 (X64)
3. 更改SQL Server排序規則
步驟如下：
- 備份所有用戶資料庫。（如果是剛剛安裝好的實例沒有用戶資料庫可以跳過這一步。）
- 停止資料庫實例
- 運行/opt/mssql/bin/mssql-conf set-collation命令修改排序規則
- 啟動資料庫實例
- 還原用戶資料庫（沒有用戶資料庫可跳過）
下麵就來演示下。
查詢當前資料庫實例的排序規則
1> SELECT CONVERT(NVARCHAR(50),SERVERPROPERTY('Collation')); 2> GO -------------------------------------------------- SQL_Latin1_General_CP1_CI_AS
Code-1： 更改前的資料庫實例排序規則
使用sp_detach_db存儲過程來分離資料庫。如果提示資料庫正在使用，可以設置資料庫為單用戶模式，或者乾脆就把資料庫實例重啟。
1> exec sp_detach_db db1; 2> go 1> exec sp_detach_db AdventureWorks2014; 2> go 1> exec sp_detach_db AdventureWorks2017; 2> go
Code-2：分離用戶資料庫
在shell 下：
[root@134test ~]# systemctl stop mssql-server [root@134test ~]# /opt/mssql/bin/mssql-conf set-collation Enter the collation: Chinese_PRC_CI_AS Configuring SQL Server... This is an evaluation version. There are [27] days left in the evaluation period. 2017-12-27 10:31:21.81 Server Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64) Jul 27 2017 08:53:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core)) 2017-12-27 10:31:21.82 Server UTC adjustment: 8:00 2017-12-27 10:31:21.82 Server (c) Microsoft Corporation. 2017-12-27 10:31:21.82 Server All rights reserved. 2017-12-27 10:31:21.82 Server Server process ID is 4120. 2017-12-27 10:31:21.82 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'. 2017-12-27 10:31:21.83 Server Registry startup parameters: -d /var/opt/mssql/data/master.mdf -l /var/opt/mssql/data/mastlog.ldf -e /var/opt/mssql/log/errorlog 2017-12-27 10:31:21.83 Server Command Line Startup Parameters: -q "Chinese_PRC_CI_AS" 2017-12-27 10:31:21.83 Server SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket,
4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required. 2017-12-27 10:31:21.84 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 2017-12-27 10:31:21.84 Server Detected 3027 MB of RAM. This is an informational message; no user action is required. 2017-12-27 10:31:21.84 Server Using conventional memory in the memory manager. 2017-12-27 10:31:22.36 Server Buffer pool extension is already disabled. No action is necessary. 2017-12-27 10:31:22.63 Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled. 2017-12-27 10:31:22.67 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled. 2017-12-27 10:31:22.67 Server Implied authentication manager initialization failed. Implied authentication will be disabled. 2017-12-27 10:31:22.69 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']. 2017-12-27 10:31:22.76 Server The maximum number of dedicated administrator connections for this instance is '1' 2017-12-27 10:31:22.76 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0.
This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 2017-12-27 10:31:22.77 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. 2017-12-27 10:31:22.79 Server In-Memory OLTP initialized on lowend machine. 2017-12-27 10:31:22.84 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. 2017-12-27 10:31:22.85 Server Database Mirroring Transport is disabled in the endpoint configuration. 2017-12-27 10:31:22.85 spid5s Warning ****************** 2017-12-27 10:31:22.86 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required. 2017-12-27 10:31:22.86 Server Query Store settings initialized with enabled = 1, 2017-12-27 10:31:22.86 spid5s Starting up database 'master'. 2017-12-27 10:31:22.87 Server Software Usage Metrics is disabled. 2017-12-27 10:31:23.47 spid5s Buffer pool extension is already disabled. No action is necessary. 2017-12-27 10:31:23.47 spid5s SQL Server Audit is starting the audits. This is an informational message. No user action is required. 2017-12-27 10:31:23.49 spid5s SQL Server Audit has started the audits. This is an informational message. No user action is required. 2017-12-27 10:31:23.68 spid5s SQL Trace ID 1 was started by login "sa". 2017-12-27 10:31:23.68 spid5s Server name is '134test'. This is an informational message only. No user action is required. 2017-12-27 10:31:23.70 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. 2017-12-27 10:31:23.71 spid5s Starting up database 'msdb'. 2017-12-27 10:31:23.72 spid9s Starting up database 'mssqlsystemresource'. 2017-12-27 10:31:23.75 spid9s The resource database build version is 14.00.900. This is an informational message only. No user action is required. 2017-12-27 10:31:23.79 spid9s Starting up database 'model'. 2017-12-27 10:31:24.04 spid5s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required. 2017-12-27 10:31:24.12 spid9s Polybase feature disabled. 2017-12-27 10:31:24.12 spid9s Clearing tempdb database. 2017-12-27 10:31:24.33 spid17s A self-generated certificate was successfully loaded for encryption. 2017-12-27 10:31:24.34 spid17s Server is listening on [ 'any' <ipv6> 1433]. 2017-12-27 10:31:24.35 spid17s Server is listening on [ 'any' <ipv4> 1433]. 2017-12-27 10:31:24.35 Server Server is listening on [ ::1 <ipv6> 1434]. 2017-12-27 10:31:24.36 Server Server is listening on [ 127.0.0.1 <ipv4> 1434]. 2017-12-27 10:31:24.36 Server Dedicated admin connection support was established for listening locally on port 1434. 2017-12-27 10:31:24.39 spid17s SQL Server is now ready for client connections. This is an informational message; no user action is required. 2017-12-27 10:31:24.99 spid9s Starting up database 'tempdb'. 2017-12-27 10:31:25.12 spid9s The tempdb database has 1 data file(s). 2017-12-27 10:31:25.18 spid5s Warning ****************** 2017-12-27 10:31:25.18 spid5s Attempting to change default collation to Chinese_PRC_CI_AS. 2017-12-27 10:31:25.22 spid5s index restored for master.syspriorities. 2017-12-27 10:31:25.23 spid5s index restored for master.sysbrickfiles. 2017-12-27 10:31:25.23 spid5s index restored for master.sysprufiles. 2017-12-27 10:31:25.23 spid5s index restored for master.sysowners. 2017-12-27 10:31:25.23 spid5s index restored for master.sysdbreg. 2017-12-27 10:31:25.24 spid5s index restored for master.sysschobjs. 2017-12-27 10:31:25.25 spid5s index restored for master.sysextsources. 2017-12-27 10:31:25.25 spid5s index restored for master.sysextfileformats. 2017-12-27 10:31:25.25 spid5s index restored for master.syscolpars. 2017-12-27 10:31:25.25 spid5s index restored for master.sysxlgns. 2017-12-27 10:31:25.25 spid5s index restored for master.sysxsrvs. 2017-12-27 10:31:25.26 spid5s index restored for master.sysnsobjs. 2017-12-27 10:31:25.26 spid5s index restored for master.syscerts. 2017-12-27 10:31:25.26 spid5s index restored for master.sysrmtlgns. 2017-12-27 10:31:25.26 spid5s index restored for master.sysxprops. 2017-12-27 10:31:25.26 spid5s index restored for master.sysscalartypes. 2017-12-27 10:31:25.26 spid5s index restored for master.sysidxstats. 2017-12-27 10:31:25.27 spid5s index restored for master.sysendpts. 2017-12-27 10:31:25.27 spid5s index restored for master.sysclsobjs. 2017-12-27 10:31:25.27 spid5s index restored for master.sysremsvcbinds. 2017-12-27 10:31:25.27 spid5s index restored for master.sysrts. 2017-12-27 10:31:25.27 spid5s index restored for master.sysasymkeys. 2017-12-27 10:31:25.27 spid5s index restored for master.syssqlguides. 2017-12-27 10:31:25.27 spid5s index restored for master.syssoftobjrefs. 2017-12-27 10:31:25.28 spid5s index restored for master.MSreplication_options. 2017-12-27 10:31:25.31 spid5s index restored for tempdb.syspriorities. 2017-12-27 10:31:25.32 spid5s index restored for tempdb.sysowners. 2017-12-27 10:31:25.32 spid5s index restored for tempdb.sysdbreg. 2017-12-27 10:31:25.33 spid5s index restored for tempdb.sysschobjs. 2017-12-27 10:31:25.33 spid5s index restored for tempdb.sysextsources. 2017-12-27 10:31:25.33 spid5s index restored for tempdb.sysextfileformats. 2017-12-27 10:31:25.33 spid5s index restored for tempdb.syscolpars. 2017-12-27 10:31:25.33 spid5s index restored for tempdb.sysxlgns. 2017-12-27 10:31:25.33 spid5s index restored for tempdb.sysxsrvs. 2017-12-27 10:31:25.33 spid5s index restored for tempdb.sysnsobjs. 2017-12-27 10:31:25.34 spid5s index restored for tempdb.syscerts. 2017-12-27 10:31:25.34 spid5s index restored for tempdb.sysrmtlgns. 2017-12-27 10:31:
