Question SQL Server 2014 and NVMe drive incompatibility.

[RickVS]

I have a legacy business application called Act!. The version I use is no longer supported. When I install it, it requires SQL Server 2014 which is part of its installer. I recently switched from a SATA SSD to an NVMe SSD. I kept getting errors upon installation and discovered that there is an incompatibility with the newer drives. While the primary issues is that SQL Server can't deal with standard native sector sizes larger than 512 bytes and 4 KB, at the command prompt I confirmed that this new drive does indeed NOT exceed this parameter. SQL Server still would not install without errors. So I gave up and returned the NVMe drive and replaced it with a different (and much slower) SATA SSD (the Samsung 2 Tb 870 EVO) and the install completed without incident.

However, this means that going forward I will never be able to upgrade to an NVMe drive because Act! is critical to my business unless someone on this forum can offer me a solution. Any thoughts on how to navigate this? Thanks.

 

[rgd1101]

get 2 ssd. one for Act! and one for everything else

 

[ex_bubblehead]

The real answer here is to dump that obsolete and long outdated software. There are plenty of tools that will convert the database to something more modern.

 

[cruisetung]

It's MS SQL problem, not Act! 's problem

See if you can install or store SQL database on a drive with 4k size sector partition or volume

Or format the nvme drive with 4K size sector

https://help.act.com/s/article/Unab...e-devices-with-Windows-11-prem?language=en_US

https://learn.microsoft.com/en-us/t...-os-4kb-disk-sector-size?tabs=registry-editor

There's no released version of SQL Server compatible with sector sizes greater than 4 KB. For more information, see the Hard disk drive sector-size support boundaries in SQL Server article.

Resolutions​

• Currently, the ForcedPhysicalSectorSizeInBytes registry key is required to successfully install SQL Server when using modern storage platforms, such as NVMe, that provide a sector size larger than 4 KB. This Windows operating system registry key forces the sector size to be emulated as 4 KB.
  
  To add the ForcedPhysicalSectorSizeInBytes registry key, use Registry Editor or run commands as described in the Command Prompt or PowerShell section. There's no need to add Trace Flag 1800 for this scenario.

You can modify the registry with 1. Registry editor 2. command prompt 3. Poweshell
detailed in the article

Only registry mehod displayed here

1. Run Registry Editor as an administrator.
2. Navigate to Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device.
3. Select Edit > New > Multi-String value and name it as ForcedPhysicalSectorSizeInBytes.
4. Right-click the name, select Modify, and type * 4095 in the Value data field.
5. Select OK and close Registry Editor.

Windows 10 and Windows 11 behave (or report) differently for nvme drives.

You don't have problem installing and running any version of SQL Server on Win10, but once you upgrade to Win11 you get errors, described in Scenarios 4 & 5 in the article.

Windows 10	Windows 11
LogicalBytesPerSector : 512	LogicalBytesPerSector : 512
PhysicalBytesPerSectorForAtomicity : 4096	PhysicalBytesPerSectorForAtomicity : 16384
PhysicalBytesPerSectorForPerformance : 4096	PhysicalBytesPerSectorForPerformance : 16384
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096	FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096

 

[RickVS]

I was able to confirm that the drive did indeed hav a 4K sector size however I still had the same problems. Also, I'm not able to install SQL on a drive that isn't the primary Windows drive; C: drive. I can install Act on another drive but can't choose the path for SQL.

 

[rgd1101]

if you have sql server install already on the system do Act use that?