Comprehensive data protection for all workloads
sebastian.mair
Influencer
Posts: 12
Liked: never
Joined: Apr 11, 2013 12:24 pm
Contact:

Backup SQL Always ON 2016 Basic Availiblity Group

Post by sebastian.mair »

hello, i have a 2 SQL Server 2016 Standard with Basic Availiblity Group. i have Databases on both hosts, but have only 2 which are on high availiblity state. so i want to backup both nodes, but the backup on the secondary replica fails, because this is a limitation of BAG. but in fact i have normal databases on the secondary node, so my question is, is it possible to exclude the database from backup on the second node? i can only exclude the complete database from veeam over the registry tweak, but i want to backup the HA Database which is active on the primary node.


any ideas?

kind regards,

sebastian
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

Hi Sebastian, indeed, Basic Availability Group doesn't allow backup of databases from secondary node (there's no read access on secondary replica). We already have a requirement to detect and exclude such databases from VSS processing and will address this in one of the future updates. Thanks.
leigh.brewster
Lurker
Posts: 2
Liked: never
Joined: Nov 28, 2016 1:37 am
Full Name: Leigh Brewster
Contact:

[MERGED] SQL 2016 Backup Failes

Post by leigh.brewster »

Hi, I have a SQL2016 AlwaysOn setup with a primary and a secondary replica running on Server 2012R2

When trying to backup the secondary replica SQL server I get the following error:

Code: Select all

16/05/2017 11:05:17 AM :: Unable to release guest. Error: Failed to call RPC function 'Vss.Unfreeze': Error code: 0x80004005. Failed to invoke func [Unfreeze]: Unspecified error. Unfreeze error: [Backup job failed. Cannot create a shadow copy of the volumes containing writer's data. A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{83df507e-cc79-44fb-979c-0ac04025ad51}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4].].  
Unable to release guest. Error: Failed to call RPC function 'Vss.Unfreeze': Error code: 0x80004005. Failed to invoke func [Unfreeze]: Unspecified error. Unfreeze error: [Backup job failed. Cannot create a shadow copy of the volumes containing writer's data. A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{83df507e-cc79-44fb-979c-0ac04025ad51}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4].].
Error: Failed to prepare VM for processing: Failed to call RPC function 'Vss.Unfreeze': Error code: 0x80004005. Failed to invoke func [Unfreeze]: Unspecified error. Unfreeze error: [Backup job failed. Cannot create a shadow copy of the volumes containing writer's data. A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{83df507e-cc79-44fb-979c-0ac04025ad51}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x8
Any help would be great

Thanks

Leigh
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: SQL 2016 Backup Failes

Post by Dima P. »

Hi Leigh,

Please reach our support team and let them investigate the application logs. Additionally, here is the thread with a similar error, take a look at suggested workaround. Thank you.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL 2016 Backup Failes

Post by foggy »

Any chance this is a Standard Edition of SQL Server 2016?
leigh.brewster
Lurker
Posts: 2
Liked: never
Joined: Nov 28, 2016 1:37 am
Full Name: Leigh Brewster
Contact:

Re: SQL 2016 Backup Failes

Post by leigh.brewster »

Yes STD SQL2016 why?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL 2016 Backup Failes

Post by foggy »

Then it is a known MS limitation, please see above.
nmdange
Veteran
Posts: 527
Liked: 142 times
Joined: Aug 20, 2015 9:30 pm
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by nmdange »

According to the original post Veeam should have an update at some point to detect these databases and exclude them to stop the error from happening. Is there any ETA on when this fix might be made available? This is still an issue on 9.5 update 2.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

This is planned for v10.
james-w
Novice
Posts: 3
Liked: never
Joined: Sep 06, 2017 6:18 am
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by james-w »

Hi,

Is this still planned for v10?

Thanks.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

Yes.
nmdange
Veteran
Posts: 527
Liked: 142 times
Joined: Aug 20, 2015 9:30 pm
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by nmdange »

Any ETA on when this might be fixed? I've noticed that the backup job our SQ 2016 Std AlwaysOn members are in is not merging/removing old restore points because the backup job always fails.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

It is planned for the next Veeam B&R update, but I cannot provide any ETA at the moment.
sdv
Influencer
Posts: 14
Liked: 2 times
Joined: Jan 09, 2018 8:12 am
Full Name: Stefan
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by sdv »

Just a ping to keep this in track.
Hoping for release of 9.5v4 instead of v10.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

Hi Stefan, yes, high chances it will be included in U4.
nixoidas
Service Provider
Posts: 15
Liked: never
Joined: May 24, 2017 12:29 pm
Full Name: Mantas
Contact:

[MERGED] AlwaysOn SQL backup

Post by nixoidas »

Hi,

We have 2 nodes SQL cluster. SQL licence Standard. There is one AlwaysOn db. When veeam backups these 2 nodes, backup fails on that VM which is not active. Error is something like sqlwriter is at failed state. If we exclud AlwaysOn db from the second node, backups is success. Is there a way for veeam to check on which node the AlwaysOn db is during backup and not to try to backup this db on other node that does not have this db during backup time? Otherwise, veeam is not very smart with Standard SQL edition and there is no way to backup AlwaysOn on Standard... I am aware of the SQL limitations for Standard but there are commands which Veeam could run to check where the alwayson db resides and backup this db only on the the active node and not on both of them.
nixoidas
Service Provider
Posts: 15
Liked: never
Joined: May 24, 2017 12:29 pm
Full Name: Mantas
Contact:

Re: AlwaysOn SQL backup

Post by nixoidas »

Any IDEAS?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: AlwaysOn SQL backup

Post by foggy »

Veeam B&R does actually check the node state and use this information during logs processing. The error you see is not Veeam B&R but rather SQL specific, please see above for details. Thanks.
nmdange
Veteran
Posts: 527
Liked: 142 times
Joined: Aug 20, 2015 9:30 pm
Contact:

Re: AlwaysOn SQL backup

Post by nmdange »

This is a known issue with Veeam and Basic Availability Groups that is supposed to be fixed in Update 4

https://forums.veeam.com/post241299.html
sdv
Influencer
Posts: 14
Liked: 2 times
Joined: Jan 09, 2018 8:12 am
Full Name: Stefan
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by sdv »

foggy wrote: Jul 30, 2018 4:47 pm Hi Stefan, yes, high chances it will be included in U4.
Hi Foggy, thanks for replying with an ETA.

Can we still expect this to be included in U4 ?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

Yes, it is there.
mkaaden
Enthusiast
Posts: 29
Liked: 2 times
Joined: Dec 11, 2017 12:35 pm
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by mkaaden »

Hi,

I've implemented Update4 in a new installation.
I still get truncation error's on secondairy databases
Is there a setting to filter out secondairy databases in Basic Availability Groups ?

Regards
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

Well, truncation is a different scenario and we were able to reproduce this internally. Though secondary database is correctly detected and excluded from backup (while the entire job failed in previous version), further logs truncation does indeed produce a warning. Will be fixed in future, thanks for the heads up!
mkaaden
Enthusiast
Posts: 29
Liked: 2 times
Joined: Dec 11, 2017 12:35 pm
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by mkaaden »

In this case one database sneaked over to the second node.
But you are assuming that these always run active-standby ?
What if they are running active-active ?
Then there should be some other mechanism to determine the primairy node of the database and include that with the backup.
I'll guess you do the same with Exchange DAG ?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

We're talking about Basic AG, where the secondary replica is always inactive, since there's no read access on it.
nmdange
Veteran
Posts: 527
Liked: 142 times
Joined: Aug 20, 2015 9:30 pm
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by nmdange »

If you have multiple databases each in their own Basic AG, the active node may be different per database, so if the exclusion is at the server level and not the database level, it won't work properly.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

Exclusions are database level, so will work correctly in the described use case.
mkaaden
Enthusiast
Posts: 29
Liked: 2 times
Joined: Dec 11, 2017 12:35 pm
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by mkaaden »

Will there be a hotfix for the Log truncation issue on the Secondary node ?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by foggy »

Hotfix no, it will be included in one of the future releases.
nmdange
Veteran
Posts: 527
Liked: 142 times
Joined: Aug 20, 2015 9:30 pm
Contact:

Re: Backup SQL Always ON 2016 Basic Availiblity Group

Post by nmdange »

I'm a bit disappointed it took 2 years from when this issue was first posted for Veeam to get some kind of fix in, and it's still not complete. Seems like that should have been enough time for Veeam to fully test this functionality and see that there are still errors with log truncation.
Post Reply

Who is online

Users browsing this forum: Bing [Bot], FelixW, Google [Bot], Semrush [Bot] and 153 guests