Intermittent failures of SQL Server backups to URL
SQL Server backups of huge in size to URL might get failed due to insufficinet TIMEOUT value of I/O operation to Azure Blob.
MS released fix for the problem for SQL Server 2012, 2014 and 2016 versions - https://support.microsoft.com/en-us/help/4023679/fix-timeout-when-you-back-up-a-large-database-to-url-in-sql-server-201
Problem
The backups to URL had been failing intermittently.
- We have the Native SQL Server backups to URL of 150 DBs in SQL Servers on Virtual Machines configured to Azure Blob Storage.
- The data of 150 DBs together is more than 2 TB.
- We did enable COMPRESSION for SQL Server Backup.
- We are using GENERAL PURPOSE Storage Account.
- SQL Server Version is 2016 on Azure VM.
Work Done
- Enabled trace flag - 3051 on SQL Server to track the detailed error message during the failure of backup job to the ERROR LOG.
- The idea is to validate whether Proxy is the issue or not.
- My Customer doesn't want to bypass the proxy due to security reasons.
- Applied the above mentioned fix in TEST environment and verified that the backup job runs fine consistently.