Resolving SQL Server Instance and Database Collation Discrepancies: Step-by-Step Guide

Introduction

Recently I experienced a situation while migrating a database to a new SQL Server instance where the instance collation was different than the database collation, causing tempdb to become unusable. This discrepancy rendered tempdb unusable and caused JOIN, ORDER BY, GROUP BY, and other operations to fail. Facing a strict deadline to get these applications migrated, I decided to rebuild the instance collation to match the new application databases.

Preparations

Run the following queries and copy the results to notepad or an Excel spreadsheet.

-- View system configuration
SELECT * FROM sys.configurations;

-- View server properties
SELECT SERVERPROPERTY('ProductVersion ') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;

-- View database files locations
SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

Before moving forward, I backed up everything that would be lost due to the rebuild, including SQL logins, jobs, alerts, linked servers, and permissions. Ensuring recent backups of all databases is important—future you will thank past you for this step.

SQL Server Configuration

To save the current SQL Server settings, I ran the following script and copied the results to a file:

-- Enable advanced options
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

DROP TABLE IF EXISTS #ConfigSettings;

-- Create a temp table to store the configuration settings
CREATE TABLE #ConfigSettings (
    name NVARCHAR(35),
    minimum INT,
    maximum INT,
    config_value INT,
    run_value INT,
    config_script NVARCHAR(MAX)
);

-- Insert the configuration settings into the temp table
INSERT INTO #ConfigSettings (name, minimum, maximum, config_value, run_value)
EXEC sp_configure;

-- Update the temp table to add the script column
UPDATE #ConfigSettings
SET config_script = 'EXEC sp_configure ''' + name + ''', ' + CAST(config_value AS NVARCHAR(10)) + ';';

-- Select the results
SELECT * FROM #ConfigSettings;

This script captures the SQL Server settings that will be wiped out during the rebuild. Save the results to a folder dedicated to the rebuild process.

Script Logins and Permissions

SQL Logins need to be extracted, along with users and roles. To accomplish this, I used the following script to create two stored procedures that will help me export the logins. I got these stored procedures from Microsoft’s official website which walks you through this step.

EXEC sp_help_revlogin

I then executed the stored procedure to generate a script to create SQL Logins, which I saved to my REBUILD folder:

Script Msdb Objects

All of the steps outlined in this section are optional, so only complete the steps you need. In my case, I needed to copy the SQL Jobs, Alerts, and Operators. However, if you have Linked Servers, Endpoints, Extended Events, or db_mail. Now would be the time to copy those configurations. I did this by expanding the SQL Server Agent in the Object Explorer, selecting the Jobs folder, then hitting the F7 key to bring up the Object Explorer Details. From here I could highlight the jobs I wanted and script the CREATE TO to a file in my REBUILD folder. I then repeated these steps for the Alerts and Operators.

Script Master Objects

Again, all of the following steps in the section are optional and depend on how the SQL Server is used. I always install sp_WhoIsActive and similar stored procedures to the master database. Just right click on the objects you need to keep and script them, individually or all together, to the REBUILD folder.

Rebuilding the System Databases

Once I completed those steps, I moved on to rebuilding the system databases. This step will require downtime. Once situated, login to the database server and find your setup.exe file for SQL Server. I found mine in C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\SQL2022. I opened a CMD prompt and changed the directory to the one listed above. I then ran the following CMD

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<ListOfAdminAccounts> /SAPWD=<YourStrongPassword> /SQLCOLLATION=<NewInstanceCollation>

Optionally, Microsoft has a list of additional parameters you can specify in your command.

After the command completed, I logged back into SQL Server via SSMS and verified the database collation with:

SELECT name, collation_name FROM sys.databases WHERE database_id < 5

With that behind me, I was able to run the CREATE scripts I generated earlier, as well as the configuration script.

I needed to add the following to the top of my sp_configure script to enable Advanced Options.

EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO

After that was finished, I disabled Advanced Options again.

EXEC sp_configure 'show advanced options', '0'
GO
RECONFIGURE
GO

Finally, I restored all databases from the most recent backup, completing the migration.