The task at hand was to install SQL Server 2022 Developer Edition on a Windows 11 system ... shouldn't be that hard. My intended installation was the database engine, analysis service in multidimensional mode and integration services, with only the latter two succeeding, but the database engine failed.
Repair of the installation failed too, stating a message that it had never been properly configured and there was nothing to repair yet. Next option, uninstall the feature, reboot and reinstall it again. Failed installation… again. So it was time to start looking for clues in the logs and there was a clue in the ERRORLOG;
Warning ******************
SQL Server started in single-user mode. This an informational message only. No user action is required.
Starting up database 'master'.
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\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf.
Unable to create stack dump file due to stack shortage (ex_terminator - Last chance exception handling)
Stack Signature for the dump is 0x0000000000000000
So I started searching for the most descriptive part about the 256 misaligned log IOs, which brought up several results trying to get availability groups set up, mainly between on prem and Azure VM’s and a case of a bricked instance after upgrade to Windows 11.
The basic trend in the answers I found:
- Misaligned log IO’s are common and the circumstances are documented, but this gives no pointers towards fixing the installation other than wipe the system, go for matching sector sizes as my system was 512 per sector and 4096 per physical sector and per cluster. Not a very attractive approach. https://techcommunity.microsoft.com/t5/running-sap-applications-on-the/message-misaligned-log-ios-which-required-falling-back-to/ba-p/367796
- Trace flag 1800 can help you out. https://www.mssqltips.com/sqlservertip/5942/sql-server-misaligned-log-ios-which-required-falling-back-to-synchronous-io/
- Installations on Windows 11 can fail (also 2017 and 2019, no solution). https://stackoverflow.com/questions/73961399/cant-install-sql-server-2017-or-2019-on-a-new-windows-11
- Machine upgrades to Windows 11 may cause issues too: https://blog.nimblepros.com/blogs/sql-server-windows-11-fiasco/, https://borncity.com/win/2022/02/07/windows-11-may-bricks-mssql-server-instances-no-longer-executable/
Based on the options, I liked Trace flag 1800 best for a first attempt and the first attempt was successful right away. So here are the steps I took to make the installation succeed:
- From the failed install, find the registry key where the startup parameters are stored. For my default instance of SQL Server 2022, it is, but you may have to adjust for your SQL Server version and instance name; Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\Parameters
- Uninstall your failed SQL Server Database Engine installation, during the uninstallation, the registry path will be removed. After uninstallation, reboot your machine.
- Before starting the installation again, create the above registry path (don’t forget to adjust the MSSQL16.MSSQLSERVER part for your version and instance name) and add the REG_SZ value SQLArg3 and populate it with the string -T1800
- Start the installation. For me, this attempt succeeded right away.
Furthermore, you need to keep -T1800 in your startup parameters, or your instance will fail to start the next time.
In my case, it’s about installing a development machine, so I wasn’t too much concerned about performance implications. But on a general note, I did wonder if -T1800 would have an impact on performance and the one article I could find suggest significant performance gains;
https://blogs.vmware.com/apps/2021/12/enhancing-performance-vmc-on-aws-sql-server-trace-flag-1800.html