SQL server woes on Windows 11

I was trying to install SQL Server 2012 mostly because of the DA:O toolset. For later versions of SQL Server, I know that there are some issues with older MDF files, so I was going to stick with proven defaults.

To my surprise, installing SQL Server 2012 onto Windows 11 Pro didn’t work. My first thought was that perhaps Microsoft had blocked it somehow since this software is 10 years old and I had a similar experience with Microsoft before. So I went to download a more recent version – SQL Server 2017.

To my even bigger surprise, this also had an issue – same error code – installation was almost completed and then error in the same “place”. Installation was almost complete, but service cannot be started, thus SQL Server was unusable. Here I started to suspect that something isn’t right here.

After some searching, I came to the following conclusions:

• I have system installed onto nvme drive.
• According to Microsoft Q&A, there is a known issue with certain disks where Windows 11 reports a sector size that SQL Server cannot cope with. However, there are workarounds for that issue such as installing SQL 2022 Express instead (but don’t know for toolset if it would operate wit sql 2022).
• Another source suggests that SQL Server has issues with Windows 11 and modern SSDs. The author suggests checking the SQL Server log files to see if there are any clues as to what might be causing the issue. If you notice a message stating something like “there have been 256 misaligned log IOs which required falling back to synchronous …”, then you can try fixing it by disabling write caching on your SSD.
• SQL Server currently supports sector storage sizes of 512 bytes and 4096 bytes. However, on systems running Windows 11, some storage devices and new device drivers will display a disk sector size larger than the supported 4 KB sector size. When this occurs, SQL Server cannot start because of an unsupported file system. To fix this issue, you can add a registry key which will cause the behavior of Windows 11 and later to be similar to Windows 10. This will force the sector size to be emulated as 4 KB. According to Microsoft, when you run the command “fsutil fsinfo sectorInfo c:” in Windows, you will receive an output that includes “PhysicalBytesPerSectorForAtomicity” and “PhysicalBytesPerSectorForPerformance” values. If these values are different, retain the largest one to ascertain the disk sector size. A value of 4096 indicates a sector storage size of 4 KB. Usually for nvme drives the value is 16KB.

In my case, it was the sector size of the system disk with a value of 16KB.

To fix this, I started the command prompt (cmd.exe) as an administrator and executed the following command:

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

You can find more information and instructions here.

After the system restart, I was able to install SQL Server normally.

However, I still have not installed the toolset onto my new computer, so that still needs to be validated. Not to mention Python and Lightmapper.

Hope you find it useful!