Coffalyser.Net uses Microsoft SQL Server to store data. Microsoft SQL Server currently has a limitation that causes it to fail to work on certain modern SSDs or hard drives with large sector sizes in combination with Windows 11 or Windows Server 2022. This article describes how this issue may be detected, and how it is possible to work around the issue.
The instructions in this article are intended for an SQL Server instance preconfigured for use with Coffalyser.Net. If you have a custom installation of SQL Server, especially if this installation is also used for other applications, we strongly recommend contacting your IT department for support instead. Find out if you have a preconfigured or custom SQL Server.
This article is relatively technical and may require more advanced computer knowledge. Please contact us when unsure about anything. If you are an IT professional, you may also refer directly to Microsoft's recommendations related to this issue.
Problem background
All versions of Microsoft SQL Server currently only support disk drives with native sector sizes of 512 bytes or 4 KB, as specified in Microsoft's system requirements. Some modern drives use larger sector sizes. This was not a problem when using Windows 10, as the storage device drivers ignored these larger sector sizes. However, Windows 11 and Windows Server 2022 support these larger sector sizes, causing SQL Server to fail to start as it detects an unsupported storage device. Microsoft is aware of this issue but has not released a fix at the time of writing.
In practice, this just means that SQL Server may not run when you have Windows 11 or Windows Server 2022 in combination with a modern storage device (i.e. a modern computer).
If you already use Coffalyser.Net on Windows 11 or Windows Server 2022 or if you do not use a storage device with sector sizes of more than 4 KB, then you do not need to take any action.
Symptoms
When you try to install a (preconfigured) SQL Server on Windows 11 or Windows Server 2022, setup may seemingly complete but any attempts to continue with configuration of Coffalyser.Net may result in an error that SQL Server could not be reached.
When you upgrade an older version of Windows to Windows 11 or Windows Server 2022, an existing installation of Coffalyser.Net may cease to function, showing an orange server with an invalid or unknown database status message.
Both things may have various causes. For general troubleshooting of SQL Server installation issues, see this article. For general troubleshooting of the invalid or unknown database message, see this article.
If you follow the steps described in the article about an invalid or unknown database status, you will try to start the SQL Server (COFFALYSER) service at some point. If you suffer from the issue described here, this will fail. When you examine the ERRORLOG file, as also described in the article about the invalid or unknown database status, you will find this message:
There have been 256 misaligned log IOs which required falling back to synchronous IO.If you do not find the message above, you have a different problem than the problem this article is about. Contact us and send us the ERRORLOG file for personalized support.
Once you have established that the message above appears in your log files, you can further confirm the sector size of your disk as follows:
-
Click start and type cmd. This should bring up the Windows Command Prompt app (or an equivalent name in your language).
-
Right-click the app and choose Run as administrator (or your language equivalent).
-
Execute the following command, replacing C: with the appropriate drive letter if necessary:
fsutil fsinfo sectorinfo C: -
The command should return values for PhysicalBytesPerSectorForAtomicity and PhysicalBytesPerSectorForPerformance. If either value is larger than 4096, your drive indeed uses a larger sector size, and you would be expected to have this issue.
Solutions
At the time of writing, Microsoft does not offer a real solution, but there are workarounds. Trivial workarounds include using a different storage drive, a different computer, or a computer that still uses an older version of Windows. We understand that this is often not possible or desirable. Therefore, we prefer a workaround that sets a flag that tells SQL Server to use a different sector size, which circumvents the issue.
If you are an IT professional, we recommend choosing the option most suitable for your situation based on the information provided by Microsoft.
Microsoft also suggests altering the registry to force Windows 11 and Windows Server 2022 to behave in a similar way as Windows 10 did and pretend that the sector size is 4 KB. We do not offer support for this option as we cannot predict what consequences this may have for your system. We only recommend taking this approach if you have sufficient expertise on this matter and the possible consequences. Similarly, do not adjust the properties of a custom SQL Server installation used for other applications without first fully understanding the consequences.
New installation of SQL Server
The workaround that we recommend for a new installation of an SQL Server preconfigured for use by Coffalyser.Net is too complicated to describe here. If you need help getting a new installation functional, contact us. Please specify that you are facing this problem and include any relevant log files to speed up your support request.
Existing installation of SQL Server
For an SQL Server preconfigured for use by Coffalyser.Net that has previously worked but failed when you upgraded Windows, we recommend the workaround described below.
-
On the computer that runs the preconfigured SQL Server instance, click the start button and search for SQL Server 2022 Configuration Manager. Open this.
-
Click on SQL Server Services on the left-hand side or central part of the window.
-
Right-click the SQL Server (COFFALYSER) service and choose Properties.
-
Click on the Startup Parameters tab. In the text box, enter:
-T1800(Exactly as shown, without spaces.)
-
Press Add. The dialog should now look something like this (paths in the existing startup parameters may vary):
-
Click OK, and OK again.
-
Right-click the SQL Server (COFFALYSER) service again and choose Start.
-
The service should now start and should display Running as its state.
-
Go back to Coffalyser.Net and try to log in. If necessary, step through the configuration wizard to reestablish the connection with the database.