Preface
In an earlier post we spoke about discovering that we can change our SQL Server Instance by restarting SQL Server with the /q option.
That post is here.
Real life experience
If truth be told, our first use-case was a recently installed SQL Server instance without any user databases.
In this post we will cover our experience with another newly built SQL Instance, but on which we will be restoring user database as part of a robust response to a server crash.
Our platform team raised up a new VM Instance, assigned server names and IP Addresses to it, and added it to our Active Directory team.
We installed SQL Server and applied the latest Service Packs.
During SQL Server install, upon being asked for the new server’s collation, we cheated and entered the Collation of another server from the same user community.
We went on to restore databases from the Production DB. And, we knew that we had to re-align the SQL Server Logins.
We assessed each restored databases and ran a script that transverses the contextual database sysusers table and link each user to the login.
BTW, the SP applied against each user is the sp_change_users_login.
Applying that script unearth our misguidedness.
Error
Error Image
Error Textual
Msg 468, Level 16, State 9L Cannot resolve the collation conflict between Latin1_General_BIN and Latin1_General_100_BIN2 in the equal to operation.
Little Traps
Knew right away that I had chosen the wrong collation during the install.
And, will have to transition over to the right collation.
There are a couple of little traps that one needs to look out for.
Read Only Databases
If we have read only databases on the SQL Instance, we can not continue.
Here is the error message.
Error
Error Image
Error Text
2017-01-03 22:33:48.55 spid34s [INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [22]. Starting offline checkpoint worker thread on a hidden SOS scheduler. 2017-01-03 22:33:48.55 spid34s CHECKDB for database 'ppsivr_20161214' finished without errors on 2016-12-28 03:04:32.197 (local time). This is an informational message only; no user action is required. 2017-01-03 22:33:48.55 spid8s Warning ****************** 2017-01-03 22:33:48.55 spid8s Attempting to change default collation to Latin1_General_100_BIN2. 2017-01-03 22:33:48.55 spid8s Error: 5804, Severity: 16, State: 1. 2017-01-03 22:33:48.55 spid8s Character set, sort order, or collation cannot be changed at the server level because at least one database is not writable. Make the database writable, and retry the operation. 2017-01-03 22:33:48.55 spid8s Error: 3416, Severity: 20, State: 1. 2017-01-03 22:33:48.55 spid8s The server contains read-only files that must be made writable before the server can be recollated. 2017-01-03 22:33:48.55 spid8s SQL Server shutdown has been initiated 2017-01-03 22:33:48.56 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
In Memory Databases
If we have in-memory databases on the SQL Instance, we also can not continue.
Here is the error message.
Error
Error Image
Error Text
. 2017-01-03 23:07:47.03 spid8s index restored for rbpivr1.gen_edi_stats_loc_time. 2017-01-03 23:07:47.09 spid8s index restored for rbpivr1.br_logon. 2017-01-03 23:07:47.79 spid8s index restored for rbpivr1.it_prp. 2017-01-03 23:10:04.93 spid8s index restored for rbpivr1.event_log_bkp_chs. 2017-01-03 23:10:06.57 spid8s index restored for rbpivr1.it_retire_election. 2017-01-03 23:10:06.61 spid8s index restored for rbpivr1.gen_ua_appgroup. 2017-01-03 23:10:07.46 spid8s Error: 41317, Severity: 16, State: 4. 2017-01-03 23:10:07.46 spid8s A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user dat abase or databases model and msdb, and it cannot write to master. 2017-01-03 23:10:09.54 spid8s Error: 3434, Severity: 20, State: 1. 2017-01-03 23:10:09.54 spid8s 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. 2017-01-03 23:10:09.54 spid8s SQL Server shutdown has been initiated 2017-01-03 23:10:09.55 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Validate
Code
Validate that in-memory tables exists on the cited database, please issue a query against the sys.tables table and look for cases where the is_memory_optimized is 1.
use [--database=--] go select tblSS.[name] , tblSO.[name] , tblSO.is_memory_optimized from sys.tables tblSO inner join sys.schemas tblSS on tblSO.[schema_id] = tblSS.[schema_id] where tblSO.[type] = 'U' and tblSO.is_memory_optimized = 1
Output
Remediate
Make a list of the identified databases, their and log files, detach the databases, change the collation, restart the SQL Instance, re-attach the detached databases.
Please keep in mind that you really need to write down the database names and make a good list of the datafiles, as you will need that information when trying to re-attach the databases.
[…] SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps Date Added :- 2017-Jan-8th Link […]
[…] SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps ( 2017-01-08 ; follow-up to post linked directly above) […]
Daniel, I ran into the In-Memory OLTP issue that you mention here and figured out what was causing it: auto-created statistics! If you delete all auto-created statistics on the in-memory tables, then this “-q” option will work on the in-memory tables :).
I updated my post on the “sqlservr -q” method (the “Changing the Collation of the Instance…” link in the comment just above this one) to include this info. I also provide some T-SQL that will find and remove these auto-created statistics. Just search for “Fix for In-Memory OLTP Errors” in that post.
Solomon:
Thanks, Bro,
How in the world did you figure that out?
All the best.
Keep it save, very save.
Daniel
Yer quite welcome. I can’t remember everything that lead me to the auto-stats, but I think I was checking to see if I could use this approach to bypass the restriction on using the new UTF-8 collations with memory-optimized tables ( https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/#UpdateCTP21 ), and it let me change the collation a few times (I had forgotten about the potential to get this error). But then I got the error another time while it said it was rebuilding indexes on a table that I had removed the indexes from, so I started looking into what could be considered an index or at least updated, something that wasn’t really an index, or something that was different between the tables. The auto-stats were the only things on the table that had no indexes, and the other tables didn’t have any auto-stats, so I figured it couldn’t hurt to try removing them. Then I did a few more tests, making sure that when it broke, at least one table had at least one auto-statistic, and then removing it/them (and making no other changes) would get it working again.