SQL Server Azure VM with 512e SSD Storage - Error 9012



  • I have been given a SQL Server Azure VM that has 512e storage attached for data and log files, according to fsutil fsinfo ntfsinfo [drive letter]:\.

    It is a DR replication node from an on-premises server with 512 storage drives.

    The VM is reporting 9012 error, which could be due to the storage or due to the difference between the replication server and the primary server.

    I am trying to assess if this is a cause for concern for both Availability Groups delay and for optimal SQL Server setup, if I were to turn it into a primary.

    Everything I am reading seems to say that https://docs.microsoft.com/en-us/archive/blogs/psssql/sql-server-new-drives-use-4k-sector-size But the articles are from 2011 - hasn't anything changed in 11 years? Or is 512e still not the way to go.

    Azure documentation does not mention any of this and I can only gather that 512e may be optional for the new SSD disks.

    Note: Enabling trace flag 1800 did not resolve the error.

    Microsoft don't even recommend HDD, listing them as recommended for Dev only now. I am waiting to see if I have ability to open a support ticket, but wanted to know people's experience here too.



  • Probably nothing has changed in the last 11 years and 512e is still not the way to go. However is horrifying that Microsoft is proposing such HD on SQL Server Azure VM.

    On https://www.sqlservercentral.com/forums/topic/512e-advanced-format-drives I found a person that was experiencing the same around 2015. There is apparently an Hot Fix that was released by Microsoft https://support.microsoft.com/en-us/topic/kb3009974-fix-slow-synchronization-when-disks-have-different-sector-sizes-for-primary-and-secondary-replica-log-files-in-sql-server-ag-and-logshipping-environments-ed181bf3-ce80-b6d0-f268-34135711043c .

    This makes me think that you are probably on SQL Server 2012-2014 and you might have done Lift & Shift to Azure VM. Maybe that's why Azure is providing you the 512e SSD storage. (Old OS might need the same old compatible hardware? Cannot tell...)

    Run these commands to check if the Hot Fix has been applied to your computer:

    dism /online /get-packages | findstr KB3009974
    

    or

    SYSTEMINFO.exe | findstr KB3009974
    

    Let me know if this helps.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2