Restore into AG with automatic seeding

[johannesk]

hi,

When Veeam restores SQL server 2016+ database it seem it automagically starts Automatic Seeding from the SQL node restored to, to the passive node. That is very nice.

But when Veeam has done restoring to the first node, it taks a tsql backup to the local disk on the SQL server. This is probably a requirement of SQL server, since if the database does not have a backup stamp, it's not allowed to be added to AG at all.

But it seem it got changed in some SQL server update, at least on SQL server 2019, that when a database is restored, it keeps the backup stamp of the database from the backup it is being restored from.

So I wonder - is it somehow possible to tell Veeam or Restore-VESQLDatabase with some undocumented parameter to skip doing the tsql backup before the command starts the automatic seeding?

My workaround for this now, is to skip using the -AvailabilityGroupName parameter, and run the Automatic Seeding command right after the restore command is finished. But it would be a nice step to optimize the process if it was built into the command.

Regards,
Jóhannes

[PetrM]

Hi Johannes,

It's an interesting idea from the technical perspective but could you please explain how this tlog backup impacts you and why do you need to skip it? I don't argue that it might be a good optimization but just want to assess the value of such a change.

Thanks!

[johannesk]

The tsql backup command that is started as part of the Restore-VESQLDatabase process (if -AvailabilityGroupName is used) will add more time to the restore process, only to get the backup stamp on the database. The backup is not used at all and is fortunatelly deleted when the whole restore is done.

This requires the target SQL server to have enough diskspace for the tsql backup and takes addiditonal time. Thus if this could be skipped, it would optimize alot. Hence, we work around this by just not using the -AvailabilityGroupName parameter.

In our case we are scheduling Restore-VESQLDatabase command for a very big database. The tsql command requires the target SQL server to always have a 1TB diskdrive available, and adds 2 hours to the whole restore process.

Regards,.
Jóhannes

[PetrM]

Hi Johannes,

The tsql command requires the target SQL server to always have a 1TB diskdrive available, and adds 2 hours to the whole restore process.

To be honest, I'm very surprised to hear that such an amount of free space and time is needed for the tlog backup. I think we should discuss it internally to decide on ETA but anyway it seems to be a valuable improvement. Your feedback is highly appreciated!

Thanks!

[johannesk]

We now have VBR version 12.1.1.56. The workflow for SQL database restore seems to be the same as 1 year ago when restoring into AvailabilityGroup on SQL server 2016 and up.

It's still doing the unnecessary step of doing tsql backup of the database when the restore process has finished restoring to the first node. Then when that tsql backup is done, it starts the automatic seeding.

Before SQL Server 2016, the database would not have a backup stamp on it when it's restored to the first node, thus the automatic seeding would not work since it requires a backup stamp to exist before it can start. This is not necessary with SQL server 2016 and up, thus the Veeam restore process could skip the step to do a tsql backup on the first node before starting the automatic seeding to the secondary node.

This is how it shows in the restore log.

Code: Select all

[21.02.2024 11:06:51.891] 17 (44460) SQL query: BACKUP DATABASE [dbname] TO DISK = N'G:\Tmp\Veeam\VESQLff27668f78226e4a86f0d05152eb8dac\dbname.bak' WITH COMPRESSION
[21.02.2024 11:07:14.934]  13 (6840)  sending session keeping notification...
.
.
.
[21.02.2024 11:54:17.591]  13 (6840)  sending session keeping notification...
[21.02.2024 11:54:43.195] 17 (44460) SQL server message: Processed 150739256 pages for database 'dbname', file 'dbname' on file 1.
Processed 5 pages for database 'dbname', file 'dbname_log' on file 1.
BACKUP DATABASE successfully processed 150739261 pages in 2870.307 seconds (410.287 MB/sec).
[21.02.2024 11:54:43.197] 17 (44460)   database backup created.
[21.02.2024 11:54:43.199] 17 (44460)  Automatic seeding on the following replicas:

[PetrM]

Hi Johannes,

We did not make any changes in this workflow and it's expected that the behavior is still the same. But I cannot recall the details of my discussion with the team based on your previous post one year ago, I remember only that we noted your idea as a potential improvement. Let me double check the status of your request.

Thanks!

[PetrM]

Hello,

Normally, we perform this log backup only if the automatic seeding feature is disabled. @johannesk could you please check that automatic seeding is enabled in your SQL server deployment? If yes, the described behavior is not by design and we're not talking about the feature request, we're discussing the technical issue which must be fixed in one of our upcoming patches/updates.

Thanks!

[johannesk]

The SQL server AvailabilityGroup system I use as an example, and take the logs from (in above post) has got the Automatic Seeding enabled. And indeed, the Veeam restore process does initiate the automatic seeding and that works fine. What I'm pointing out here, is that the Veeam restore process starts a tsql full backup (not log backup) on the first node before it starts the automatic seeding. Thus increase the time of the restore process and requires diskspace on the SQL server node for that tsql full backup. This was a requirement in SQL server versions prior 2016.

@PetrM, are you saying that the restore process should not do the tsql full backup to disk before it starts the automatic seeding successfully? I'd be happy to work with support to show this in more detail so you can get the details.

[PetrM]

Hi Johannes,

Ok, if automatic seeding is enabled, it looks like a technical issue. It would be very helpful for us if you could open a support request so that my colleagues can analyze logs. Please share a support case ID so that I can escalate it to the development team.

Thanks!

[johannesk]

I have created case #07221189 for this @petrM

[PetrM]

Hi Johannes,

I already asked our support team leaders to pay attention to your case.

Thanks!

[johannesk]

I have received a fix for the issue. It has been applied and tested. Now when we restore SQL database into AG, the process goes directly into Automatic Seeding when the restore is done on the first node.

[PetrM]

Hi Johannes,

I'm glad to hear that the issue is fixed, many thanks for the information!

Thanks!

[johannesk]

Those that were also getting the fix so that SQL database restore into Availability Group won't do TSQL backup before the automatic seeding, it turns out that the fix is not included in newer builds. When we upgraded to 12.1.2.172 the fix was gone and we had to get it reissued for that VBR build. Veeam Support told me that this fix won't be included in VBR until version 13 thus it will need to be requested after every upgrade.

For those interested then it's fix 'Fix_697693_60dcb667c2' that fixes this for VBR 12.1.2.172.

Regards,
Jóhannes