1

SQL 2016 AOAG. 3 node cluster. Primary and 1 secondary on-prem configured with synchronous availability mode. 1 node in Azure using asynchronous availability mode. We are using AOAGs to move data to the Azure node. The data and log drives on the Azure node were configured with 64k block size per best practices. Older on-prem nodes disk and log drives were configured with 512 blocks.

Soon after the Azure node was added to the AOAGs we started seeing multiple entries in the Azure SQL Error log like... "There have been ###### misaligned log IOs which required falling back to synchronous IO. The current IO is on file F:\mssql\Log\xxxxxxxxxxxxx_log.ldf. "

I now understand this is a result of the physical sector mismatch between the on-prem and Azure nodes (512 vs 64k)... We then followed the recommendations of enabling trace flag 1800 and expected those log entries to diminish. However, they have not.

log_send_queue_size and redo_queue_size values on the Azure node are very low.

Since the Azure node will eventually become the primary when applications are moved to Azure... Should we keep the 64k block size, or re-format and start over with AG synchronization ?

1 Answer 1

0

In my experience, I needed to add Trace Flag 1800 to all instances involved in the Always On Availability Group.

I enabled the trace flag using the DBCC TRACEON(1800, -1) to enable it globally immediately, and then added it to the instance startup trace flags via the -T1800 command-line option.

Once I had enabled the trace flag on all instances, the "misaligned log IOs which required falling back to synchronous IO" message went away.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.