SQL restore into AlwaysOn - Feature request

[johannesk]

hi,

One of the really cool features already in Veeam is, when you restore a DB to a SQL node belonging to AlwaysOn, the wizard offers you to do the restore also to the secondary node.

BUT

When the restore has completed to the primary node - the Veeam restore wizard sends a backup command to the primary node to create a .bak file and restore it to the secondary node. As of now, this .bak file is created on the C: drive. For big databases that can easlily be problematic and fill up the C: drive.

I suggest that when the .bak file is created for the secondary node that the .bak file is created on the already configured 'Database Default Locations' backup folder. Generally that is configured on other drive than drive C:

I my scenario today, I got this error.

[Window Title]
VEEAM EXPLORER FOR MICROSOFT SQL SERVER

[Content]
Database restore failed: DBNAME.
Write on "C:\Windows\TEMP\VESQL_17e91ae2-6152-47d5-a0b6-41110dd94947\DBNAME1.bak" failed: 112(There is not enough space on the disk.)
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.

[OK]

[foggy]

Hi Jóhannes, starting from Veeam B&R v9.5 U3 you can specify the folder for storing temporary files on the SQL server via the following parameter in explorer's configuration file (ProgramData\Veeam\Backup\SQLExplorer\Config.xml):

Code: Select all

<Veeam>
   <SQLExplorer TempFolderPath="C:\tmp"/>     
</Veeam>

[johannesk]

I configured this option on the config.xml on the Veeam Server, where I'm doing the SQL restore from with the VBR console. Also I read about this configuration option on the help center.

"...You can configure a path to the temporary location on the staging SQL Server..."
https://helpcenter.veeam.com/docs/backu ... tml?ver=95

So I configured the Backup folder to be used on the SQL staging server, but alas, the .bak file for the AlwaysOn secondary node is still created on the C: drive on the AlwaysOn primary node. Thus filling up the C: drive.

Is that I bug I'm running into or os this option (SQLExplorer TempFolderPath) not supposed to work for this scenario?

regards,
Johannes

[johannesk]

This is the step that would be nice to be able to configure. From DBA standpoint, it would be ideal if the path would be the already configured default location for SQL backups.

Code: Select all

15.2.2018 10:08:47   10 (18516) Checking SQL Server permissions on \\targetSQLserver1N1\ADMIN$\TEMP\VESQL_a75aef21-5bae-4a9b-9dbf-093bcbef034b (account: ADName\serviceuser~sq, SID: S-1-5-21-2106337540-114255811-1274951907-113927)...
15.2.2018 10:08:47   10 (18516) Granting access to directory: \\targetSQLserver1N1\ADMIN$\TEMP\VESQL_a75aef21-5bae-4a9b-9dbf-093bcbef034b (account SID: S-1-5-21-2106337540-114255811-1274951907-113927, agent: targetSQLserver1N1)...

[foggy]

I configured this option on the config.xml on the Veeam Server, where I'm doing the SQL restore from with the VBR console.

Have you restarted Veeam Explorer for SQL Server after that? Please also make sure you're running Veeam B&R v9.5 U3.

[johannesk]

I added this config clause to the existing config.xml and made the format wrong. Thats why it did not work yesterday.

I confirm, that this parameter is working now. The value configured in the Config.xml is picked up and used on the first AlwaysOn node. Thus after the database has been restored to the first AlwaysOn node it does a backup to the path configured in the Config.xml, locally to that node. Then it copies the .bak file to the same path on the secondary node and does the restore and AlwaysOn Join.

I thougth maybe it would use this path on the staging SQL server, but it does not.

This solves my issue. Thanks alot for this information.

But I still stand by my RFE regarding that it would be great if the restore would check the SQL server location for backups. That way we don't need to configure this XML file for every AlwaysOn restore. Please at least consider it.

[foggy]

Will do, thanks for the feedback.