Windows 11 may bricks MSSQL server instances; no longer executable

[German]Windows 11 seems to be becoming a challenge for many people because of problems with drivers and software at all corners. A blog reader alerted me to a particular problem on a customer's machine. There, an MSSQL Server instance can no longer be started because the sector size has changed to an unsupported value. It's an issue related to Windows 11 and Samsung SSD 980.

Microsoft SQL Server

The Microsoft SQL Server (MSSQL) is a relational database management system from Microsoft, which is available on Windows in various versions. Currently, Microsoft SQL Server 2019 and a 2022 edition is in preview. In addition, there are also the free Express editions of the MSSQL server.

The Technical Support Policy for Microsoft SQL Server also states, as of Jan. 25, 2022, that SQL Server 2017 should run on Linux (all editions), SQL Server 2017 should run on Windows (all editions). I'm going to assume that this also applies to more recent SQL Server versions. 

Windows 11: MSSQL server no longer starts

German blog reader Philipp K. Schießl has asked me in a private message about a problem that occurred with one of his customers. Philipp writes that he sends me as a reader a problem description, which might be interesting and describes the problem:

I have the phenomenon on a customer machine that after upgrading to Windows 11, the MSSQL server instance is no longer running.

A quick Google search for keywords like "MSSQL server won't start" threw up some hits for me from January 2022 at Microsoft. For example, the support post SQL Server service may not start after patch installed was updated on 1/25/2022. Also, the post Using SQL Server in Windows was updated in January 2022. However, the documents in question do not take me any further. I had still asked the reader what the errorlog.txt file says about this. There he meant that a line:

There have been 256 misaligned log IOs which required falling back to synchronous IO. 
The current IO is on file C:\Program Files\Microsoft SQL Server\MSSQL15.XXX\MSSQL\DATA\master.mdf.

could be relevant. However, the log entries did not really reveal much.

Sector size is not supported

Philipp then looked into the matter and came across a rather crude issue, which he assumes is related to the upgrade to Windows 11. He wrote:

The reason for this, as far as my evaluations of an attempted reinstallation, is the consistency check of the SQL server instance when it is started.

Obviously the upgrade to Windows 11 changed the sector size to a value (in this case: 32768 = 32KB) which is not supported by any current SQL server version.

The solution that Philipp finally realized is: The only remedy was to install the SQL server on a secondary disk with a standard 4KB cluster sector size. I still don't quite understand why the sector size changes with the Windows 11 upgrade. But maybe this will help someone. Anyway, thanks to Philipp for the hint.

Microsoft's Explanation

Well, after I published the German edition of this post here and linked the blog article in various admin groups on Facebook, Lars L. pointed me to the post Troubleshoot errors related to system disk sector size greater than 4 KB. The article deals with SQL Server and Windows 11 and was last updated on Jan 25, 2022. Here are the relevant sections. 

Troubleshoot errors related to system disk sector size greater than 4 KB

This article provides solutions for troubleshooting errors during installation or starting SQL Server on Windows 11 related to system disk sector size greater than 4 KB.

Cause

During service startup, SQL Server begins the database recovery process to ensure database consistency. Part of this database recovery process involves consistency checks on the underlying filesystem before attempting the activity of opening system and user database files.

On systems running Windows 11, some new storage devices and device drivers will expose a disk sector size greater than the supported 4 KB sector size.

When this occurs, SQL Server will be unable to start due to the unsupported file system as SQL Server currently supports sector storage sizes of 512 bytes and 4 KB.

The support article it is described how to use:

fsutil fsinfo sectorinfo <volume pathname>

to check this condition. So the observation of the German blog reader has been confirmed by Microsoft.

Another discussion: It's Samsung SSD 980

And another reader pointed me to this Microsoft discussion, started in Sept. 2021 from a reader also affected by the behavior discussed above:

Windows 11 – Unable to start MSSQLSERVER service

Hi,
I have upgraded my windows 10 Pro to Windows 11, these are the current specs:

Device name DESKTOP-M331UEJ
Processor AMD Ryzen 7 5800X 8-Core Processor 3.80 GHz
Installed RAM 32.0 GB
System type 64-bit operating system, x64-based processor
Edition Windows 11 Pro Insider Preview
Version Dev
Installed on ‎ 2021-‎09-‎05
OS build 22449.1000
Experience Windows Feature Experience Pack 1000.22449.1000.0

After the update, the MSSQL server 2019 can't start.
This is the error from the Event Viewer:

1. MSSQLSERVER
2. Error: 1067. The process terminated unexpectedly.
4. Faulting application name: sqlservr.exe, version: 2019.150.4153.1, time stamp: 0x60f610ce
5. Faulting module name: ntdll.dll, version: 10.0.22449.1000, time stamp: 0x05321977
6. Exception code: 0xc0000005
7. Fault offset: 0x0000000000035f8e
8. Faulting process id: 0x2ad4
9. Faulting application start time: 0x01d7a2f27a6bc3c3
10. Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
11. Faulting module path: C:\WINDOWS\SYSTEM32\ntdll.dll
12. Report Id: 1796a874-3c3f-467f-a923-4cfe5b7ae754
13. Faulting package full name:
14. Faulting package-relative application ID:

This is my personal development machine with single SSD, of disk size 4096 KB! The the IOs are misaligned, and the only fix for this problem is to run the MSSQL service with the 1800 Trace Flag. (Enables SQL Server optimization when disks of different sector sizes are used for primary and secondary replica log files, in SQL Server Always On and Log Shipping environments. This trace flag is only required to be enabled on SQL Server instances with transaction log file residing on disk with sector size of 512 bytes. ) Is this a bug on Windows 11 side? Sql server side? And can we expect a fix for this?

Other users confirmed this issue. Some reader confirmed the issue in connection with Samsung's SSD 980 (the Samsung 980 Pro SSD seems to work). The solution proposed within this discussion ist either: Create a virtual disk (VHD) and and use it to install MS SQL-Server. Or install MS SQL-Server on a separate drive.