Question Need a hard drive (platter or SSD) w/ a physical sector size larger than 4k

[Trey22]

Software tester here, and seems we run into an issue when installing SQL Server on Win 11 on drives w/ a larger than 4k physical sector size.

I've checked (using fsutil fsinfo sectorinfo c) 5 of my work machine drives and they are all either 512 or 4096. So of course SQL Server installs fine.

 

[zir_blazer]

As far that I know there are no drives with higher than Advanced Format (4 KiB Physical Sector Size), and in the case of SSDs, the actual Flash block size is abstracted. So what on earth are you using on the first place? Perhaps you are confusing 4 KiB physical with 512 emulation and 4 KiB native. I think some enterprise SSDs uses non-standard Sector Sizes so you get say, 520 instead of 512, but no idea if this also happens with 4 KiB SAS disks or such.
Alternative: Using virtualization Software and edit the IDE/SATA Controller to present whatever topology you want. Some QEMU devices may already do this.

Edit: It seems than some HDs, mainly SAS ones, suports multiple different Sector Sizes including above 4096 Bytes, most likely the difference being disk Firmware or how you format it:. Check these Seagate Exos X14.

 

[aigomorla]

There are SAS 512e / 4Kn Drives which use emulation like zir states.
This for example.

Amazon.com

But i have not seen a nvme 512e, except some no name.

Amazon.com

But i would not trust that drive unless you absolutely need the nVME speed.

 

[Koharski]

Wow, I have some experience with this exact problem. There are some drives with 8k physical sectors, but these are normally specialty products sold to the enterprise market. The ones I have are the Micron 5100 Eco - a 3.84TB drive. Micron also offered a pro model, but only up to 2TB. The pro drives have a lot of unprovisioned flash for endurance. They are designed to be fully written to several times per day. The eco drives trade spare flash for raw capacity. In this case, exposing an 8K sector size to the OS can allow a filesystem like ZFS to align writes to the NAND's page size, and reduce write amplification. Normally the controller would handle this, but with a larger array it makes more sense to do this in software.

Funny enough, I managed to pick these drives up cheap from work because they do not work well in windows and they are not supported by MS SQL server. From what I understand SQL does check the physical specs of the drive in order to optimize performance a bit. If the drive is emulating a 512b disk, it will try to force native 4k - which is not an available configuration on this drive. This is all second hand knowledge, but I do know that several techs spent hours with our vendors trying to sort this out. And the ultimate call was just to get a supported drive.

Here are some links that may be relevant. I'll include a smart report below as well:

Troubleshooting operating system disk sector size greater than 4 KB - SQL Server

This article troubleshoots SQL Server installation or startup failures related to some new storage devices and device drivers exposing a disk sector size greater than the supported 4-KB sector size.

support.microsoft.com

Troubleshooting operating system disk sector size greater than 4 KB - SQL Server

This article troubleshoots SQL Server installation or startup failures related to some new storage devices and device drivers exposing a disk sector size greater than the supported 4-KB sector size.

learn.microsoft.com

Support policy for 4K sector hard drives - Windows Server

Provides support information for the large-sector (4K) drives when they're used with Windows and other Microsoft products. Compatibility information is included.

learn.microsoft.com

https://www.micron.com/-/media/client/global/documents/products/product-flyer/5100_product_brief.pdf

Micron 5100 MAX & ECO Enterprise SSD Review (960GB/1920GB) - The SSD Review

By now, if you are an avid reader or haven't been stuck under a rock for the past 7-8 years, you kno

www.thessdreview.com

smartctl 7.2 2020-12-30 r5155 [x86_64-linux-5.15.107-1-pve] (local build)
Copyright (C) 2002-20, Bruce Allen, Christian Franke, www.smartmontools.org

=== START OF INFORMATION SECTION ===
Device Model: MTFDDAK3T8TBY-1AR1ZA 01PE064D7A09453LEN
Serial Number: 1CAB4DCA
LU WWN Device Id: 5 00a075 11cab4dca
Firmware Version: ME51
User Capacity: 3,840,755,982,336 bytes [3.84 TB]
Sector Sizes: 512 bytes logical, 8192 bytes physical
Rotation Rate: Solid State Device
Form Factor: 2.5 inches
TRIM Command: Available, deterministic, zeroed
Device is: Not in smartctl database [for details use: -P showall]
ATA Version is: ACS-3 T13/2161-D revision 5
SATA Version is: SATA 3.2, 6.0 Gb/s (current: 3.0 Gb/s)
Local Time is: Sun May 21 02:06:34 2023 PDT
SMART support is: Available - device has SMART capability.
SMART support is: Enabled
AAM feature is: Unavailable
APM level is: 254 (maximum performance)
Rd look-ahead is: Enabled
Write cache is: Enabled
DSN feature is: Unavailable
ATA Security is: Disabled, frozen [SEC2]
Wt Cache Reorder: Enabled

=== START OF READ SMART DATA SECTION ===
SMART overall-health self-assessment test result: PASSED

General SMART Values:
Offline data collection status: (0x04) Offline data collection activity
was suspended by an interrupting command from host.
Auto Offline Data Collection: Disabled.
Self-test execution status: ( 0) The previous self-test routine completed
without error or no self-test has ever
been run.
Total time to complete Offline
data collection: ( 2671) seconds.
Offline data collection
capabilities: (0x7b) SMART execute Offline immediate.
Auto Offline data collection on/off support.
Suspend Offline collection upon new
command.
Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities: (0x0003) Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability: (0x01) Error logging supported.
General Purpose Logging supported.
Short self-test routine
recommended polling time: ( 2) minutes.
Extended self-test routine
recommended polling time: ( 72) minutes.
Conveyance self-test routine
recommended polling time: ( 3) minutes.
SCT capabilities: (0x0035) SCT Status supported.
SCT Feature Control supported.
SCT Data Table supported.

SMART Attributes Data Structure revision number: 16
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME FLAGS VALUE WORST THRESH FAIL RAW_VALUE
1 Raw_Read_Error_Rate -O--CK 100 100 000 - 0
5 Reallocated_Sector_Ct PO--CK 100 100 010 - 0
9 Power_On_Hours -O--CK 100 100 000 - 21412
103 Unknown_Attribute -O--CK 100 100 000 - 275
12 Power_Cycle_Count -O--CK 100 100 000 - 313
170 Unknown_Attribute -O--CK 100 100 000 - 0
171 Unknown_Attribute -O--CK 100 100 000 - 0
172 Unknown_Attribute -O--CK 100 100 000 - 0
174 Unknown_Attribute -O--CK 100 100 000 - 275
192 Power-Off_Retract_Count -O--CK 100 100 000 - 275
100 Unknown_Attribute -O--CK 100 100 000 - 68
184 End-to-End_Error -O--CK 100 100 097 - 0
187 Reported_Uncorrect -O--CK 100 100 000 - 0
188 Command_Timeout -O--CK 100 100 000 - 1064
190 Airflow_Temperature_Cel -O---K 072 043 011 - 28 (Min/Max 11/57)
195 Hardware_ECC_Recovered -O--CK 100 100 000 - 0
196 Reallocated_Event_Count -O--CK 100 100 001 - 0
198 Offline_Uncorrectable -O--CK 100 100 000 - 0
199 UDMA_CRC_Error_Count -O--CK 100 100 000 - 0
173 Unknown_Attribute -O--CK 100 100 000 - 0
241 Total_LBAs_Written -O--CK 100 100 000 - 398080812481
242 Total_LBAs_Read -O--CK 100 100 000 - 4106755592
232 Available_Reservd_Space -O--CK 000 000 000 - 43603
231 Unknown_SSD_Attribute PO--CK 100 100 001 - 0
197 Current_Pending_Sector -O--CK 100 100 000 - 0
177 Wear_Leveling_Count -O--CK 100 100 000 - 67
194 Temperature_Celsius -O--CK 028 057 000 - 28 (Min/Max 11/57)
||||||_ K auto-keep
|||||__ C event count
||||___ R error rate
|||____ S speed/performance
||_____ O updated online
|______ P prefailure warning

General Purpose Log Directory Version 1
SMART Log Directory Version 1 [multi-sector log support]
Address Access R/W Size Description
0x00 GPL,SL R/O 1 Log Directory
0x01 SL R/O 1 Summary SMART error log
0x02 SL R/O 51 Comprehensive SMART error log
0x03 GPL R/O 16383 Ext. Comprehensive SMART error log
0x04 GPL,SL R/O 8 Device Statistics log
0x06 SL R/O 1 SMART self-test log
0x07 GPL R/O 1 Extended self-test log
0x09 SL R/W 1 Selective self-test log
0x10 GPL R/O 1 NCQ Command Error log
0x11 GPL R/O 1 SATA Phy Event Counters log
0x13 GPL R/O 1 SATA NCQ Send and Receive log
0x24 GPL R/O 8000 Current Device Internal Status Data log
0x25 GPL R/O 8000 Saved Device Internal Status Data log
0x30 GPL,SL R/O 9 IDENTIFY DEVICE data log
0x80-0x9f GPL,SL R/W 16 Host vendor specific log
0xa0 GPL VS 35328 Device vendor specific log
0xa1 GPL,SL VS 4 Device vendor specific log
0xa3 GPL VS 13824 Device vendor specific log
0xa4 GPL VS 354 Device vendor specific log
0xa4 SL VS 98 Device vendor specific log
0xa5 GPL VS 8000 Device vendor specific log
0xa5 SL VS 64 Device vendor specific log
0xa6 GPL VS 8000 Device vendor specific log
0xa6 SL VS 64 Device vendor specific log
0xb0 GPL,SL VS 1 Device vendor specific log
0xe0 GPL,SL R/W 1 SCT Command/Status
0xe1 GPL,SL R/W 1 SCT Data Transfer

SMART Extended Comprehensive Error Log Version: 1 (16383 sectors)
No Errors Logged

SMART Extended Self-test Log Version: 1 (1 sectors)
Num Test_Description Status Remaining LifeTime(hours) LBA_of_first_error
# 1 Vendor (0xff) Completed without error 00% 21096 -
# 2 Vendor (0xff) Completed without error 00% 20947 -
# 3 Vendor (0xff) Completed without error 00% 20542 -
# 4 Vendor (0xff) Completed without error 00% 20350 -
# 5 Vendor (0xff) Completed without error 00% 19969 -
# 6 Vendor (0xff) Completed without error 00% 19934 -
# 7 Vendor (0xff) Completed without error 00% 19807 -
# 8 Vendor (0xff) Completed without error 00% 19627 -
# 9 Vendor (0xff) Completed without error 00% 19572 -
#10 Vendor (0xff) Completed without error 00% 19447 -
#11 Vendor (0xff) Completed without error 00% 18986 -
#12 Vendor (0xff) Completed without error 00% 18877 -
#13 Vendor (0xff) Completed without error 00% 18804 -
#14 Vendor (0xff) Completed without error 00% 18642 -
#15 Vendor (0xff) Completed without error 00% 17869 -
#16 Vendor (0xff) Completed without error 00% 17481 -
#17 Vendor (0xff) Completed without error 00% 16969 -
#18 Vendor (0xff) Completed without error 00% 16272 -
#19 Vendor (0xff) Completed without error 00% 15950 -

SCT Status Version: 3
SCT Version (vendor specific): 1 (0x0001)
Device State: SMART Off-line Data Collection executing in background (4)
Current Temperature: 28 Celsius
Power Cycle Min/Max Temperature: 22/33 Celsius
Lifetime Min/Max Temperature: 11/57 Celsius
Under/Over Temperature Limit Count: 0/0

SCT Temperature History Version: 2
Temperature Sampling Period: 1 minute
Temperature Logging Interval: 1 minute
Min/Max recommended Temperature: 0/70 Celsius
Min/Max Temperature Limit: -5/75 Celsius
Temperature History Size (Index): 478 (43)

Index Estimated Time Temperature Celsius
44 2023-05-20 18:09 26 *******
... ..( 49 skipped). .. *******
94 2023-05-20 18:59 26 *******
95 2023-05-20 19:00 27 ********
... ..( 35 skipped). .. ********
131 2023-05-20 19:36 27 ********
132 2023-05-20 19:37 28 *********
... ..(149 skipped). .. *********
282 2023-05-20 22:07 28 *********
283 2023-05-20 22:08 29 **********
284 2023-05-20 22:09 29 **********
285 2023-05-20 22:10 28 *********
... ..( 29 skipped). .. *********
315 2023-05-20 22:40 28 *********
316 2023-05-20 22:41 29 **********
317 2023-05-20 22:42 28 *********
318 2023-05-20 22:43 29 **********
... ..( 3 skipped). .. **********
322 2023-05-20 22:47 29 **********
323 2023-05-20 22:48 28 *********
324 2023-05-20 22:49 29 **********
... ..( 12 skipped). .. **********
337 2023-05-20 23:02 29 **********
338 2023-05-20 23:03 28 *********
339 2023-05-20 23:04 29 **********
... ..( 24 skipped). .. **********
364 2023-05-20 23:29 29 **********
365 2023-05-20 23:30 28 *********
... ..(111 skipped). .. *********
477 2023-05-21 01:22 28 *********
0 2023-05-21 01:23 26 *******
... ..( 42 skipped). .. *******
43 2023-05-21 02:06 26 *******

SCT Error Recovery Control command not supported

Device Statistics (GP Log 0x04)
Page Offset Size Value Flags Description
0x01 ===== = = === == General Statistics (rev 1) ==
0x01 0x008 4 313 --- Lifetime Power-On Resets
0x01 0x010 4 21412 --- Power-on Hours
0x01 0x018 6 398080812481 --- Logical Sectors Written
0x01 0x020 6 971047984 --- Number of Write Commands
0x01 0x028 6 130372176710 --- Logical Sectors Read
0x01 0x030 6 746761669 --- Number of Read Commands
0x01 0x038 6 77083620000 --- Date and Time TimeStamp
0x04 ===== = = === == General Errors Statistics (rev 1) ==
0x04 0x008 4 0 --- Number of Reported Uncorrectable Errors
0x04 0x010 4 1064 --- Resets Between Cmd Acceptance and Completion
0x05 ===== = = === == Temperature Statistics (rev 1) ==
0x05 0x008 1 28 --- Current Temperature
0x05 0x010 1 28 --- Average Short Term Temperature
0x05 0x018 1 25 --- Average Long Term Temperature
0x05 0x020 1 57 --- Highest Temperature
0x05 0x028 1 11 --- Lowest Temperature
0x05 0x030 1 46 --- Highest Average Short Term Temperature
0x05 0x038 1 16 --- Lowest Average Short Term Temperature
0x05 0x040 1 37 --- Highest Average Long Term Temperature
0x05 0x048 1 16 --- Lowest Average Long Term Temperature
0x05 0x050 4 0 --- Time in Over-Temperature
0x05 0x058 1 70 --- Specified Maximum Operating Temperature
0x05 0x060 4 0 --- Time in Under-Temperature
0x05 0x068 1 0 --- Specified Minimum Operating Temperature
0x06 ===== = = === == Transport Statistics (rev 1) ==
0x06 0x008 4 3313 --- Number of Hardware Resets
0x06 0x010 4 941 --- Number of ASR Events
0x06 0x018 4 0 --- Number of Interface CRC Errors
0x07 ===== = = === == Solid State Device Statistics (rev 1) ==
0x07 0x008 1 0 N-- Percentage Used Endurance Indicator
|||_ C monitored condition met
||__ D supports DSN
|___ N normalized value

Pending Defects log (GP Log 0x0c) not supported

 

[zir_blazer]

Wow, I have some experience with this exact problem. There are some drives with 8k physical sectors, but these are normally specialty products sold to the enterprise market. The ones I have are the Micron 5100 Eco - a 3.84TB drive. Micron also offered a pro model, but only up to 2TB. The pro drives have a lot of unprovisioned flash for endurance. They are designed to be fully written to several times per day. The eco drives trade spare flash for raw capacity. In this case, exposing an 8K sector size to the OS can allow a filesystem like ZFS to align writes to the NAND's page size, and reduce write amplification. Normally the controller would handle this, but with a larger array it makes more sense to do this in software.

Funny enough, I managed to pick these drives up cheap from work because they do not work well in windows and they are not supported by MS SQL server. From what I understand SQL does check the physical specs of the drive in order to optimize performance a bit. If the drive is emulating a 512b disk, it will try to force native 4k - which is not an available configuration on this drive. This is all second hand knowledge, but I do know that several techs spent hours with our vendors trying to sort this out. And the ultimate call was just to get a supported drive.

According to the Micron 5100 ECO datasheet, it states 512 Bytes per Sector, and on the Identify Device tables it has one (Word 106) that says "1 = Device has multiple logical sectors per physical sector" and "8 logical sectors per physical sector", which makes it 4 KiB physical sector size. Thus is a standard 512e drive (4 KiB internal, exposes 512 logical). The 8 KiB you're talking about is most likely Cluster Size at the File System level. Logical Sector and Physical Sector sizes are below that.

On ServeTheHome there are people that usually purchases HDs from enterprise grade OEM systems that uses Sector Sizes bigger than 512 Bytes or 4 KiB, so you have guides like this one. I'm not sure if these formats actually changes the Physical Sector Size to slighty bigger than expected (Like 520 or 528 Bytes) or just the Logical one. There is also a NVMe SSD that seems to include end user toolkit to change Sector Size, but reading the Sabrent Sector Size Converter manual it is just to switch between 512e or native 4 KiB and none of the odd ones.

I have no idea of SQL requeriments because user Software shouldn't deal with File System shenanigans directly, much less disk drive internal topology.

 

[Trey22]

Wow, I have some experience with this exact problem. There are some drives with 8k physical sectors, but these are normally specialty products sold to the enterprise market. The ones I have are the Micron 5100 Eco - a 3.84TB drive. Micron also offered a pro model, but only up to 2TB. The pro drives have a lot of unprovisioned flash for endurance. They are designed to be fully written to several times per day. The eco drives trade spare flash for raw capacity. In this case, exposing an 8K sector size to the OS can allow a filesystem like ZFS to align writes to the NAND's page size, and reduce write amplification. Normally the controller would handle this, but with a larger array it makes more sense to do this in software.

Funny enough, I managed to pick these drives up cheap from work because they do not work well in windows and they are not supported by MS SQL server. From what I understand SQL does check the physical specs of the drive in order to optimize performance a bit. If the drive is emulating a 512b disk, it will try to force native 4k - which is not an available configuration on this drive. This is all second hand knowledge, but I do know that several techs spent hours with our vendors trying to sort this out. And the ultimate call was just to get a supported drive.

Here are some links that may be relevant. I'll include a smart report below as well:

Troubleshooting operating system disk sector size greater than 4 KB - SQL Server

This article troubleshoots SQL Server installation or startup failures related to some new storage devices and device drivers exposing a disk sector size greater than the supported 4-KB sector size.

support.microsoft.com

Troubleshooting operating system disk sector size greater than 4 KB - SQL Server

This article troubleshoots SQL Server installation or startup failures related to some new storage devices and device drivers exposing a disk sector size greater than the supported 4-KB sector size.

learn.microsoft.com

Support policy for 4K sector hard drives - Windows Server

Provides support information for the large-sector (4K) drives when they're used with Windows and other Microsoft products. Compatibility information is included.

learn.microsoft.com

https://www.micron.com/-/media/client/global/documents/products/product-flyer/5100_product_brief.pdf

Micron 5100 MAX & ECO Enterprise SSD Review (960GB/1920GB) - The SSD Review

By now, if you are an avid reader or haven't been stuck under a rock for the past 7-8 years, you kno

www.thessdreview.com

Appreciate that! At least gives me a place to start, before... had no idea what to look for.