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.
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.
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.
2017-01-03 22:33:48.55 spid34s [INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: . 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.
. 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 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
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.