SAP on SQL Server on Azure – How to Bypass Proxy Server During Backup to URL
This short blog discusses how to avoid overloading an on-premises or Azure Proxy server with Backup to URL. Creating a typical .Net configuration file to disable a proxy server will allow BackupToURL.exe to communicate via https directly to Azure blog storage.
1. SQL Server Backup to URL
Modern releases of SQL Server support backups to Azure Blob storage. This method is convenient and popular for SAP on SQL customers running on Azure for full database backups and/or transaction log backups.
Customers running SQL Server Backup to URL are strongly recommended to update to the latest support pack and cumulative update, especially if the databases are running Transparent Database Encryption.
SAP always support the latest Service Pack and Cumulative Update for SQL Server https://launchpad.support.sap.com/#/notes/62988
2. Proxy Server Configuration & Backup to URL
The Backup to URL feature is implemented as a separate executable called BackupToURL.exe. This executable will call a standard Windows API when sending HTTPS traffic. By default this API will read the Windows Proxy Server configuration from Control Panel -> Internet Options -> Connections -> LAN Settings
Customers running SAP on SQL Server on Azure generally have one of two possible scenarios for the proxy server:
1. Azure is leveraging the central corporate proxy server that is kept in an existing on-premises data center.
2. A separate proxy server has been setup in Azure for Azure VMs/services to leverage.
In either case the proxy is unnecessary and will slow down the backup performance considerably. If the proxy server is on-premises the https call to the proxy server involves a two way transit of the ExpressRoute link. This adds to data costs for the link.
Modern versions of SQL Server support backup to many URL targets simultaneously and the traffic volume can be considerable. Customers have noticed that the proxy server and/or ExpressRoute link can become saturated.
It is generally recommended to disable the proxy server for BackupToURL.exe only and allow SQL Server Backup to URL to communicate directly with the target storage account. There are several ways to do this, but the recommended procedure is documented below
3. How to Disable Bypass Proxy Server for SQL Server Backup to URL
To disable the BackupToURL.exe from using the default proxy server create a file in the following path:
C:\Program Files\Microsoft SQL Server\MSSQL13.\<InstanceName>\MSSQL\Binn
The actual requirement is that the file be in the same directory as BackupToURL.exe for a particular version of SQL Server and Instance Name
The filename must be:
BackuptoURL.exe.config
The file contents should be:
<?xml version ="1.0"?>
<configuration>
<system.net>
<defaultProxy enabled="false" useDefaultCredentials="true">
<proxy usesystemdefault="true" />
</defaultProxy>
</system.net>
</configuration>
Additional information can be found here: /en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-best-practices-and-troubleshooting?view=sql-server-2017
Depending on the exact customer configuration it is possible that SQL Server executable does require a valid proxy server to do certain activities such as reading/writing to the Azure Key Vault for TDE. Care must be taken when completely disabling the proxy server via Control Panel
Additional options for controlling routing could include configurating a UDR. Another option is to create a private local endpoint for the blob storage account on the vnet of the SQL VM. Since the IP address is now local to the VM the proxy server will not be used.
Additional Links & Information
See point #5 for Backup to URL tuning https://blogs.msdn.microsoft.com/saponsqlserver/2017/04/04/sap-on-sql-general-update-for-customers-partners-march-2017/ /en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017 /en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017 /en-us/sql/relational-databases/backup-restore/deleting-backup-blob-files-with-active-leases?view=sql-server-2017
Managed Backups to Azure Blob Storage /en-us/sql/relational-databases/backup-restore/sql-server-managed-backup-to-microsoft-azure?view=sql-server-2017