fredbourdelier
Dabbler
- Joined
- Sep 11, 2022
- Messages
- 27
I'm having a failure during installation of MS SQL Server 2022 (Developer free edition) on Windows Server Standard 2022 as a VM on TNAS CORE 13.0-U5.3.
Process:
1. Installed Win Server Standard from ISO (en-us, 16 core) as 1 vCPU/8core/2thread with 48GB RAM, 1TB vdisk partitioned as 3 user partitions (200/200/600 GB). Boot type UEFI. (VNC still enabled from setup, but using RDP to get in now).
Install notes:
1a. NIC driver needs to be INTEL E1000 in order to work. The VirtIO driver was never detected by the OS
1b. Disk driver was set to VirtIO, but Windows installer ISO contained no valid drivers. I had to download the VIRTIO-WIN from https://github.com/virtio-win, version 0.1.240, create a 2nd virtual CD and mount the image before starting install
1c. On powerup, there is a text-mode boot menu where it counts down (the Windows setup doesn't appear). Anything you type or press in there does nothing - just wait for the countdown, then type EXIT, press ENTER, and the UEFI boot menu apears
1d. The UEFI boot menu appeared scrambled and unreadable. I had to go into the VNC details of the VM (from TN CORE main panel menu: Virtual Machines / Select VM / Devices / VNC / 3 dot menu on the right / Edit / Resolution) and change resolution to 640x480. Others reported 800x600 working for them. It appears to be a try-it-until-it-works type of thing depending on your browser/screen setup
1e. Once you see the UEFI boot menu, select Boot from device, pick the virtual CDROM with the Windows image loaded on it using arrow keys. Press ENTER, then be ready to hit any key to boot from the Windows ISO CD. You have about 1-2 seconds before it times out and fails to boot
1f. Pick setup option 2 "manual" for new Windows installation, then you'll have to load the drivers from the VirtIO disk. Just selecting the CD is enough. Drives will appear from the virual disk(s) you selected in the initial VM setup. partition/format your drives and run through Windows setup as usual. You'll need to VNC to complete the setup, install RDP and then you can RDP in. I set mine up joining an AD domain (AD version 2016) which worked fine. Windows server updates, runs, participates in all domain activities just as a server running on physical box or VMware - I haven't seen any performance degradation, issues, or errors
1g. There have been occasional lockups of the VM on shutdown during updates, but "normal" reboots have always worked. During those failures to shut down I used the VM control panel in TNAS and hit the power Off slider, that always did the trick. Anyway servers shouldn't be rebooted that often during normal operation
After installing all OS updates, I tried to set up SQL Server (2022 Dev Free edition, but I've seen reports of this same problem as far back as SQL 2012). The basic issue, the help websites say, is the reported sector size from the disk driver. The core SQL engine is the failing component. It will run through the installation process, perform all checks with perfect results, run the installers, get what appears to be about 99% of the way through (green bar is full), then attempt to start SQL server "running action: SqlEngineConfigAction_Install_confignonrc_cpu64" to finish installing. Things stop here for a long time.
This is where things go sideways. SQL installer returns
TITLE: Microsoft SQL Server 2022 Setup
------------------------------
The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
For help, click: https://go.microsoft.com/fwlink?Lin...6&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026
------------------------------
BUTTONS:
OK
------------------------------
The error data is in the SQL Server INSTALLER error log, which is in <instance root>\MSSQL16.MSSQLSERVER\MSSQL\Log.
Looking into the log, the interesting item is this:
There have been 256 misaligned log IOs which required falling back to synchronous IO. The current IO is on file S:\SQL_DATA\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf.
There are a lot of articles on fixing this, here's the MS version (which is a lot less interesting than some, and much less to the point, but has a reason for reference)
...Other solutions:
blog.nimblepros.com
stackoverflow.com
The solution consists of forcing Windows to fake 4k sectors when reporting to applications (Win10 emulation within a W11 file driver core). This requires a registry change and reboot...which doesn't work. I've PowerShelled the command in, checked it, it's there. Rebooted. Many times. And that's where I am currently stuck. The MS article suggests that the physical sector value (which remains 64k) is no longer relevant when the "ForcedPhysicalSectorSizeInBytes" value is specified (see screenshot 1), even if FSutil still sees it as 64k (see screenshot 2).
However the SQL error persists, indicating that the initial physical sector size maintains some relevance in the SQL scheme of things.
The command confirmed in the registry:
FSUTIL still reports 64k physical bytes per sector, but changes the "effective physical bytes per sector" to a 4k sector size-presumably the result of the emulator layer.
Does anyone have experience with resolving this issue?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSTEM CONFIG HW:
DELL R730 gen 3 chassis 3.5", dual 1100wPSU,iDrac 9,4 onboard NIC 2@10/2@1
2@Xeon E5-2690v4@2.6GHz/14 core
768GB DDR4@2400/Reg/ECC
2@Nytro 960GB SSD SAS Boot mirror (rear drive mountpoints), 8@Exos x18/18TB SAS as RAIDZ2/1 pool
DELL HBA330 mini mono SAS/Flashed to IT mode
Process:
1. Installed Win Server Standard from ISO (en-us, 16 core) as 1 vCPU/8core/2thread with 48GB RAM, 1TB vdisk partitioned as 3 user partitions (200/200/600 GB). Boot type UEFI. (VNC still enabled from setup, but using RDP to get in now).
Install notes:
1a. NIC driver needs to be INTEL E1000 in order to work. The VirtIO driver was never detected by the OS
1b. Disk driver was set to VirtIO, but Windows installer ISO contained no valid drivers. I had to download the VIRTIO-WIN from https://github.com/virtio-win, version 0.1.240, create a 2nd virtual CD and mount the image before starting install
1c. On powerup, there is a text-mode boot menu where it counts down (the Windows setup doesn't appear). Anything you type or press in there does nothing - just wait for the countdown, then type EXIT, press ENTER, and the UEFI boot menu apears
1d. The UEFI boot menu appeared scrambled and unreadable. I had to go into the VNC details of the VM (from TN CORE main panel menu: Virtual Machines / Select VM / Devices / VNC / 3 dot menu on the right / Edit / Resolution) and change resolution to 640x480. Others reported 800x600 working for them. It appears to be a try-it-until-it-works type of thing depending on your browser/screen setup
1e. Once you see the UEFI boot menu, select Boot from device, pick the virtual CDROM with the Windows image loaded on it using arrow keys. Press ENTER, then be ready to hit any key to boot from the Windows ISO CD. You have about 1-2 seconds before it times out and fails to boot
1f. Pick setup option 2 "manual" for new Windows installation, then you'll have to load the drivers from the VirtIO disk. Just selecting the CD is enough. Drives will appear from the virual disk(s) you selected in the initial VM setup. partition/format your drives and run through Windows setup as usual. You'll need to VNC to complete the setup, install RDP and then you can RDP in. I set mine up joining an AD domain (AD version 2016) which worked fine. Windows server updates, runs, participates in all domain activities just as a server running on physical box or VMware - I haven't seen any performance degradation, issues, or errors
1g. There have been occasional lockups of the VM on shutdown during updates, but "normal" reboots have always worked. During those failures to shut down I used the VM control panel in TNAS and hit the power Off slider, that always did the trick. Anyway servers shouldn't be rebooted that often during normal operation
After installing all OS updates, I tried to set up SQL Server (2022 Dev Free edition, but I've seen reports of this same problem as far back as SQL 2012). The basic issue, the help websites say, is the reported sector size from the disk driver. The core SQL engine is the failing component. It will run through the installation process, perform all checks with perfect results, run the installers, get what appears to be about 99% of the way through (green bar is full), then attempt to start SQL server "running action: SqlEngineConfigAction_Install_confignonrc_cpu64" to finish installing. Things stop here for a long time.
This is where things go sideways. SQL installer returns
TITLE: Microsoft SQL Server 2022 Setup
------------------------------
The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
For help, click: https://go.microsoft.com/fwlink?Lin...6&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026
------------------------------
BUTTONS:
OK
------------------------------
The error data is in the SQL Server INSTALLER error log, which is in <instance root>\MSSQL16.MSSQLSERVER\MSSQL\Log.
Looking into the log, the interesting item is this:
There have been 256 misaligned log IOs which required falling back to synchronous IO. The current IO is on file S:\SQL_DATA\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf.
There are a lot of articles on fixing this, here's the MS version (which is a lot less interesting than some, and much less to the point, but has a reason for reference)
There have been 256 misaligned log IOs which required falling back to synchronous IO. - Microsoft Q&A
I checked the SQL configuration manager the sql server services are stopped but i restarted but it did not worked. i found the root cause is "There have been 256 misaligned log IOs which required falling back to synchronous IO. The current IO…
learn.microsoft.com
Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log
First published on MSDN on Oct 01, 2014 Summary Disk Manufacturers introduced a new specification for disk storage formats in 2011.
techcommunity.microsoft.com
...Other solutions:
Beware of SQL Server on Windows 11 with a modern SSD
What do you get when you mix a modern SSD, SQL Server and Windows 11. A grumpy Phil, that's what.
SQL Server : installation fails with error code 0x851A001A – Wait on the Database Engine recovery handle failed
Details: SQL Server 2017 (Developer or Express edition) Windows 2011 OS I have followed this article already but no avail https://blog.sqlauthority.com/2017/01/27/sql-server-sql-installation-fails-
stackoverflow.com
The solution consists of forcing Windows to fake 4k sectors when reporting to applications (Win10 emulation within a W11 file driver core). This requires a registry change and reboot...which doesn't work. I've PowerShelled the command in, checked it, it's there. Rebooted. Many times. And that's where I am currently stuck. The MS article suggests that the physical sector value (which remains 64k) is no longer relevant when the "ForcedPhysicalSectorSizeInBytes" value is specified (see screenshot 1), even if FSutil still sees it as 64k (see screenshot 2).
However the SQL error persists, indicating that the initial physical sector size maintains some relevance in the SQL scheme of things.
The command confirmed in the registry:
FSUTIL still reports 64k physical bytes per sector, but changes the "effective physical bytes per sector" to a 4k sector size-presumably the result of the emulator layer.
Does anyone have experience with resolving this issue?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSTEM CONFIG HW:
DELL R730 gen 3 chassis 3.5", dual 1100wPSU,iDrac 9,4 onboard NIC 2@10/2@1
2@Xeon E5-2690v4@2.6GHz/14 core
768GB DDR4@2400/Reg/ECC
2@Nytro 960GB SSD SAS Boot mirror (rear drive mountpoints), 8@Exos x18/18TB SAS as RAIDZ2/1 pool
DELL HBA330 mini mono SAS/Flashed to IT mode
