Resolving SQLCMD Timeout During the RESTORE HEADERONLY of SQL Server dSource SnapSync (KBA6590)

Issue

During a SQL Server SnapSync (or Validated Sync) on a Full database backup, the RESTORE HEADERONLY command might timeout with an error similar to the following:

Attempt to run sqlcmd command on remote host "Remote_Host_for_Shared_Backup_Location.Your_Company.com" timed out. 

This error can be seen as the failed message text from a SnapSync job on the SQL Server dSource. Delphix is running the RESTORE HEADERONLY command on the designated backup file to be ingested.

Note:

The remote host is where you stored your backup files to be accessible by Delphix, known as the "Share Backup Location". This is an SMB share and follows a UNC format. Even though it does say "remote host" this share can be located on the staging host/instance.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
4.3	4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0
4.2	4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1
4.1	4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

To Resolve the Timeout for the RESTORE HEADERONLY Command

We have seen three distinct scenarios where the RESTORE HEADERONLY command may take longer than 10 minutes and timeout.

1. In the first scenario, the staging databases were running on a SQL Server 2016 SP1 instance.  There is a known bug in some versions of SQL Server that causes the RESTORE HEADERONLY command to take a long time on databases where Transparent Data Encryption (TDE)  is enabled.

https://support.microsoft.com/en-us/help/4052908/sql-server-2016-service-pack-2-release-information

10698847

Fixes an issue in which restore of a compressed backup for a Transparent Data Encryption (TDE) enabled database through the Virtual Device Interface (VDI) interface may fail with the operating system error 38.

SQL Engine

https://support.microsoft.com/en-us/...erver-2016-sp1

10268790

4019893

FIX: Restore fails when you do backup by using compression and checksum on a TDE enabled database in SQL Server 2016

SQL service

Here's a blog mention on a fix found in 2016 SP1 CU4:
https://social.msdn.microsoft.com/Forums/vstudio/en-US/441eb035-7ef1-4c96-be84-a67242cf6c19/restore-headeronly-statement-for-a-tde-compressed-backup-slow-to-complete-in-sql-server-2014?forum=sqldatabaseengine
 

2. Using a fully qualified host name rather than an alias when referencing the network share has proved to be more performant. For example on the dSource's Configuration -> Data Management, tab specify the fully qualified domain name for the "Backup Path":

\\myhost.acme.com\backup_directory

 

3. In a few rare cases, we have seen the RESTORE HEADERONLY command take longer than 20 minutes when the source database's log file was very large. After shrinking the log file and then performing a backup, the RESTORE HEADERONLY command on the new backup completed substantially faster.

Troubleshooting

The best way to isolate the cause and resolution of the SQLCMD timeout is to test the backup file used for the SnapSync (sync) job.  In this case you know the backup location and you know the backup file name.  To aid in troubleshooting, log in as the Delphix OS User for this staging environment, and then run the following command from the same staging SQL Server instance used to stage your SQL Server dSource. 

RESTORE HEADERONLY FROM DISK=N'\\Remote_Host_for_Shared_Backup_Location.Your_Company.com\SQLBackups\Full_Backup_20200829.bak'

You can run this from a tool such as SSMS (SQL Server Management Studio). Normally the RESTORE HEADERONLY command should  take a few seconds or complete instantaneously. By default SSMS does not have a timeout set for SQL commands. If it takes more than 10 minutes (the default SQL command timeout in Delphix), you may be running into one of the issues described in this article. If it takes 20 minutes with no response and the source database is TDE enabled, it is reasonable to imply you are hitting the SQL Server bug mentioned and you might consider upgrading if you are running SQL Server 2016 SP1 (or perhaps earlier, like SQL Server 2014, in which case it is not known if there is resolution to the issue on that version of SQL Server).

If you decide to upgrade the staging instance, simply test the command again. In the case this issue is resolved you will likely receive a favorable response in seconds.  The RESTORE HEADERONLY reads the backup file and returns the backup header information SQL Server and applications can use to understand aspects of the backup.  In the case with Delphix, this information is compared to the source instance backupset information for the backup file.  If the information matches (LSNs, backup UUID, recovery fork GUID, etc) then the backup file has been validated and the SnapSync job proceeds to restore the backup file.

 

 

 

---