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

Introduction

Recently I encountered a situation while migrating a database to a new SQL Server instance where the instance collation differed from the database collation, causing tempdb to become unusable. This discrepancy made tempdb unusable and caused JOIN, ORDER BY, GROUP BY, and other operations to fail. With a deadline to migrate these applications, 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 proceeding, 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 for this process.

SQL Server Configuration

To save the current SQL Server settings, I ran the following script and saved 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 removed 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. I used the following script to create two stored procedures that help export the logins. I found these stored procedures on Microsoft’s official website which explains this process.

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 steps in this section are optional, so only complete what you need. In my case, I needed to copy SQL Jobs, Alerts, and Operators. 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 Object Explorer, selecting the Jobs folder, then pressing F7 to open Object Explorer Details. From there I highlighted the jobs I wanted and scripted them to a file in my REBUILD folder. I repeated these steps for Alerts and Operators.

Script Master Objects

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

Rebuilding the System Databases

After completing those steps, I moved on to rebuilding the system databases. This step requires downtime. Log in 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 to that directory, then ran the following command:

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 completed, 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.