Sector Size Shenanigans: NVMe Drivers and MSSQL on Windows 11

The story begins with the fact that I was trying to install Microsoft SQL on one of the shiny new test PCs at my workplace. Somehow, installation after installation failed. Always when the instance of the MSSQL Server should be started it failed.

If you look for the short and fast answer:
1. MSSQL does not support sector sizes above 4KB.
2. With Windows 11 they moved the NVMe drivers to stornvme.sys. This changes the sector size to an incompatible size.
3. Microsoft says that it could be fixed by forcing the sector size to be emulated. (We had no luck with that.)
4. Buy a SSD that is known to be working on Windows 11 and MSSQL.

We also had a few tests PCs from the first batch, where we still were running on Windows 10 Enterprise LTSC, so we said let’s try and upgrade those systems to Windows 11 Enterprise IoT LTSC. One PC could still start the MSSQL Server, the next one not. Did we brick the instance? We tried to reinstall MSSQL after the upgrade, but on that one system we could never get MSSQL running on Windows 11 again.

The problem we pinned down was the 4KB limit in sector size for MSSQL. You can check your systems sector size under Windows with a command line running as an administrator for your C drive, for example:

fsutil fsinfo sectorInfo C:

In this case where you see a size above 4096 MSSQL installation will fail. While Microsoft says you could „emulate“ the sector size with the following registry entry:

REG ADD "HKLM\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" /v "ForcedPhysicalSectorSizeInBytes" /t   REG_MULTI_SZ /d "* 4095" /f

We were wondering if the registry was a typo, and it should have been 4096, but had no success. So why did it on one PC running Windows 10? That is actually explained by Microsoft, and they show a comparison between the systems running 10 and 11.

Source: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/troubleshoot-os-4kb-disk-sector-size

In my opinion, this sound like:
We noticed that the SQL server was working by accident. We fixed the behavior in the improved Windows 11 drivers and hence the bug has been fixed, and the system remains in a non-functional state, which is the correct state.

Compatibility Matrix MS SQL Disks in Windows 11

Right now, we have only tested a few discs on Windows 11. In general, we did not find a good way to find the sector size before buying a NMVe drive, and all we did was plug it into a system and test it with the fsutil command mentioned above.

This list is far from comprehensive and complete, and these were the only discs we had lying around. For now, we don’t know how to detect „good“ SSDs other than buying, plugging them in and returning them if they do not work. Even I am unsure if this might become a more serious problem if the next batches of SSDs would increase the sector size, rendering MSSQL server almost useless.

Microsoft and >4KB sector size a general problem?

As we faced problems with MSSQL, but did not encounter issues with running Windows 11 IoT Enterprise, strangely there exists an article by Microsoft claiming, that they generally do not support sector sizes above 4KB.
See: https://learn.microsoft.com/en-us/troubleshoot/windows-server/backup-and-storage/support-policy-4k-sector-hard-drives

And at least Microsoft is definitely aware of this issue as seen in the MSSQL support article or even in this Blog article:
https://techcommunity.microsoft.com/blog/sqlserversupport/sql-server—new-drives-use-4k-sector-size/316277

Technical details and points that are open for discussion

In the beginning, we had thought that this problem might come of our new platform we were testing. These included AMD Threadripper Pro 5000 series running on a Supermicro WRX80 board, an AMD Threadripper 7000 series running on a TRX50 board from Supermicro and a DELL Precision 7875 Tower running an AMD Threadripper Pro 7000 series on a custom DELL WRX90 board. It would be interesting if this also happens on Intel based systems.

Also, untested was the possibility of other Windows 11 versions, as we only tested Pro and IoT Enterprise, it would still surprise me if there is a difference or even a pending update we did not install, because all systems had been installed offline.

Last but not least, I had once an issue with software due to an error in a NVMe firmware. Up until now, we did not check the Datacenter discs with a more recent Firmware. That said, it is also hard to tell „what“ is the most recent Firmware on Samsung Datacenter NVMe’s. While the consumer ones are easy to spot, we could not find an official website for downloading them. There is one website I did not check if it contains malicious software, but the amount of different firmware versions was big, but because they use an alphanumeric code, which I could not tell which systematic approach it followed, I did not try any of those versions.

If we find more clues, I will update this article. But right now we got some systems that are working and are even considering to change to another database or even removing it entirely.