SQL-Ex blog

Что такое коллация уровня сервера в SQL Server

Коллация определяет правила сортировки, свойства регистра и акцента данных. Это важная настройка, которая может влиять на ваши данные в базе данных, поэтому не рекомендуется менять её часто.

Коллация может конфигурироваться на 4 слоях в SQL Server:

• Уровень сервера


• Уровень базы данных


• Уровень столбца


• Уровень выражения

Замечание. Если вы используете SQL Server Express Edition, вы не можете поменять коллацию SQL_Latin1_General_CP1_CI_AS.

Изменение коллации уровня сервера для существующего экземпляра SQL Server

В этом разделе будут описаны предварительные действия и процесс изменения коллации уровня сервера. Изменения имени коллации связаны с перестройкой системных баз данных.

Предварительные действия

Выполните следующие пункты для изменения коллации на производственном экземпляре.

1. Сделайте полные резервные копии всех баз данных. Если возможно, сделайте снимок виртуальной машины прежде, чем приступить к дальнейшему.


2. Убедитесь, что у вас имеются все скрипты для создания пользовательских баз данных или объектов баз данных, например, таблиц, логинов, пользователей, заданий и т.п. Мы создадим все объекты баз данных после изменения коллации.


3. Экспортируйте все данные, используя bcp или сторонние инструменты.


4. Запишите конфигурационные значения сервера и номер сборки, включая исправления (hotfixes), примененные к экземпляру SQL Server.


5. Запишите места размещения файлов системных баз данных. Это может потребоваться, если вы перемещали ваши файлы системных баз данных в другое место.

Давайте рассмотрим несколько скриптов, которые помогут вам получить параметры системы. Скопируйте их вывод в блокнот или файл Excel, или просто сделайте скриншот и сохраните на будущее.

Выполните команду ниже, чтобы получить параметры конфигурации уровня сервера.

--Получить все параметры конфигурации уровня сервера

SELECT * FROM sys.configurations;

Получите версию сборки SQL Server, выполнив скрипт ниже.

--Вывести версию сборки SQL Server

SELECT @@VERSION

GO

SELECT  

SERVERPROPERTY('ProductVersion ') AS ProductVersion,  

SERVERPROPERTY('ProductLevel') AS ProductLevel,  

SERVERPROPERTY('ResourceVersion') AS ResourceVersion;

Подобным образом мы должны получить места размещения файлов системных баз данных с помощью следующего оператора T-SQL:

SELECT name, physical_name AS Database_file_location  

FROM sys.master_files  

WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

Затем выполните следующие скрипты T-SQL, чтобы собрать информацию о коллации экземпляра SQL Server и всех баз данных одновременно.

--Получить имеющуюся коллацию экземпляра SQL Server Instance 

--и всех баз данных

SELECT name, collation_name  

FROM sys.databases  

GO  

--Получить существующую коллацию уровня сервера

SELECT SERVERPROPERTY('Collation') As [Instance Level Collation]

Теперь сгенерируем скрипты Create для всех логинов и заданий, созданных в экземпляре SQL Server для будущих ссылок и проверки. Помимо этого, вы можете выполнить скрипт ниже для получения списка всех логинов и заданий в экземпляре SQL Server.

--Get list of logins

Use master

Go

Select * from syslogins

--Get list of Jobs

USE msdb

Go

Select * from sysjobs

Перестройка экземпляра

После завершения выполнения пунктов из предыдущего раздела следующий шаг - это запуск процесса изменения коллации. Т.к. мы сделали бэкапы или экспортировали все данные пользовательских баз, необходимо отсоединить (detach) все пользовательские базы данных от экземпляра SQL Server. Вы можете также удалить их, если хотите импортировать экспортированные данные в новую базу данных. Я предложил бы отсоединить их, чтобы сохранить копию ваших баз данных с тем, чтобы вы могли присоединить их после изменения коллации.

Процесс изменения коллации уровня сервера требует перестройки системных баз данных. Нам нужно передать имя новой коллации при перестройке системных баз данных. Операция перестройки системных баз данных требует запуска установки SQL Server. Давайте начнем операцию перестройки системных баз данных для изменения коллации уровня сервера.

Авторизуйтесь в вашей системе, в которой вы будете менять коллацию. Откройте командную строку Windows или терминальное окно PowerShell. Откроте PowerShell, затем наберите cmd, чтобы использовать использовать утилиту командной строки Windows, как показано на рисунке ниже. Измените местоположение вашего каталога, куда вы поместили установочные файлы вашего SQL Server.

--Открыть командную строку Windows
Cmd
--Изменить каталог, где размещены установочные файлы SQL Server
Cd <полный путь>
--Мое местоположение C:\Manvendra\SQL2019\Developer_ENU поэтому я выполняю
cd C:\Manvendra\SQL2019\Developer_ENU
Теперь я нахожусь там, где хранятся установочные файлы SQL Server.



Затем мы выполним приведенную ниже команду, чтобы перестроить системные базы данных. Кроме того, мы должны выполнить эту команду с параметром новой коллации, чтобы изменить ее для экземпляра SQL Server и всех системных баз данных.

--Перестроить системные базы данных с новой коллацией.
--Замените все значения в < > на ваши.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=<ИмяЭкземпляра>
/SQLSYSADMINACCOUNTS=<аккаунты> / SAPWD= <СильныйПароль>
/SQLCOLLATION=<ИмяКоллации>
--Я заменил все значения и выполнил команду ниже с коллацией SQL_Latin1_General_CP1_CS_AS
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=WIN-AFNHJ3L1D6E\Administrator /SAPWD=L@b12 /SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS
Вы можете увидеть вывод команды на скриншоте ниже. Вам следует оставить этот экран для продолжения, пока вы не получите рабочий каталог.



Здесь вы получили приглашение ко вводу. Это означает, что системные базы данных перестроились на новую коллацию для экземпляра SQL Server. Давайте проверим.



Убедитесь, что SQL Server запущен, и его службы загружены. Теперь запустите SQL Server Management Studio или выполните sqlcmd для подключения к экземпляру SQL Server. Вы можете видеть, что я успешно подключился к тому экземпляру, который был перестроен на предыдущих шагах. Мы видим только системные базы данных на изображении ниже.



Выполните следующие операторы T-SQL, чтобы собрать параметры коллации уровня сервера и уровня базы данных. Этот шаг проверит изменение коллации сервера и системных баз данных.

--Получить коллацию экземпляра SQL Server и всех бах данных

SELECT name, collation_name  

FROM sys.databases  

GO  

--Получить существующую коллацию уровня сервера

SELECT SERVERPROPERTY('Collation') As [Instance Level Collation]

Как показано на скриншоте ниже, серверная коллация и коллация всех системных баз данных использует SQL_Latin1_General_CP1_CS_AS. Мы проверили, что новая коллация была сконфигурирована в экземпляре SQL Server.



Коллация сервера сменилась новой. Следующий шаг - присоединить все пользовательские базы данных, которые ранее были отсоединены перед перестройкой системных баз данных. Помните, что все существующие базы данных, которые присоединяются или восстанавливаются будет иметь ту же коллацию, которую они имели до резервирования или отсоединения. Вам потребуется вручную изменить ее после изменения серверной коллации. С другой стороны, все вновь создаваемые базы данных будут иметь новую коллацию по умолчанию.

Я присоединил мои пользовательские базы данных, как показано на рисунке ниже.



После присоединения баз данных вы снова можете проверить коллацию этих баз данных. Если вы хотите поменять их коллацию, обратитесь к предыдущей статье , где описывается, как это выполнить.

Заключение

Если вы экспортировали данные из баз данных, вы можете выполнить все скрипты, сгенерированные на подготовительном этапе при создании объектов базы данных. Затем вы можете импортировать экспортированные данные в эти вновь созданные базы данных и объекты. Вы можете проверить и модифицировать конфигурационные параметры уровня сервера, которые были получены в разделе подготовки.

Также вы можете переместить файлы системных баз данных, если хотите хранить их специальном месте, т.к. перестройка системных баз данных будет создавать их в местоположении по умолчанию. Можно также выполнить скрипты для создания логинов и заданий с помощью ранее сгенерированных скриптов. Не восстанавливайте системные базы данных, т.к. это изменит вновь сконфигурированную коллацию на старую для этих баз данных.