Orion 2019.4 how to change database collation?

Question

Hi all,

I have installed orion modules version 2019.4-HF1 using SQL_Latin1_General_CP1_CI_AS as database collation. And i found the Chinese characters in database cannot correctly displayed. So i need to change database collation from SQL_Latin1_General_CP1_CI_AS to Chinese_PRC_CI_AS.

I have searched in SuccessCenter, found a related article as below link.

https://support.solarwinds.com/SuccessCenter/s/article/Supported-SolarWinds-Orion-database-collations-for-Orion-Platform-2015-1-or-later

I executed below command to change database collation:

sqlservr -m -T4022 -T3659 -q"Chinese_PRC_CI_AS"

At the end of command output information, i see some error as below. It seemed the database collation change failed.

2020-01-27 17:43:33.81 spid5s       index restored for SolarWindsOrion.UDT_Port.

2020-01-27 17:43:33.84 spid5s      Error: 1934, Severity: 16, State: 1.

2020-01-27 17:43:33.84 spid5s      UNKNOWN TOKEN failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

2020-01-27 17:43:34.04 spid5s      Error: 3434, Severity: 20, State: 1.

2020-01-27 17:43:34.04 spid5s      Cannot change sort order or locale. An unexpected failure occurred while trying to reindex the server to a new collation. SQL Server is shutting down. Restart SQL Server to continue with the sort order unchanged. Diagnose and correct previous errors and then retry the operation.

2020-01-27 17:43:34.04 spid5s      SQL Server shutdown has been initiated

2020-01-27 17:43:34.04 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Attached file is the command running full log.

Could somebody help me?

ERRORLOG.1

blue0o0 · Answer

Hi Mark,

Thanks for your reply. The first method's problem is how to re-create tables and import data in a batch scripts. I am not a skilled DBA. I have no idea.

And i found someone said can migrate data using api script in this post:

Alter SQL Collation of Solarwinds Database

Could someone give me some information about this api script?

m_roberts · Answer

This is a known problematic function and you will find a number of posts on Thwack from users attempting to change the collation. Going from Latin to Chinese I would say is even less likely to succeed, due to the significant changes inherent in the character sets and order.

A skilled DBA may be able to work through each encountered issue and resolve manually, but my experience would indicate this is likely to be the first method in the KB I am afraid to say.