SQL Guest Processing Windows 2016 AG without WFCS on VMWare

Availability for the Always-On Enterprise

SQL Guest Processing Windows 2016 AG without WFCS on VMWare

Veeam Logoby andyman3000 » Tue Jul 31, 2018 9:38 pm

Referencing Case # 03119599

Hello I am working with our DBA to test a POC for enabling SQL processing on our Veeam Backups so we do not need to use LiteSpeed for SQL database backups.

We have enabled Periodic Backup Log Shipping every 15 minute on the 4 servers we are testing on (2 Virtual and 2 Physical). Backups seem to be going smoothly except for one VMWare/Virtual SQL server.

The Server backups are succesful, but the SQL Server Transaction logs for the one server are warning/failing with this error message:

Failed to call RPC function 'Vss.IsPrefferedSqlReplicaForDb': Error code: 0x80004005. Failed to invoke func [IsPrefferedSqlReplicaForDb]: Unspecified error. Code = 0x80040e14. Code meaning = IDispatch error #3092. Source = Microsoft OLE DB Provider for SQL Server. Description = Could not process the operation. Always On Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster. Disable and re-enable Always On Availability Groups by using the SQL Server C

The support engineer on my case wanted me to have the DBA disable and re-enable Always On Availability Groups by using the SQL Server Configuration Manager as a first step, but he said he cannot do that as There is no Windows cluster services installed on it. This one uses windows OS 2016 AG setup without having to install WFCS.

Support then referred me to this KB http://veeam.com/kb2463 to verify if this is a Veeam supported SQL configuration.

Since I'm not a DBA I'm not sure from the article if I am attempting to backup a non-supported SQL Configuration with Veeam, or if I'm not asking our DBA the right questions :D

Is anyone familiar with Windows 2016 Availability groups compared to Windows Failover Clustering? Is this unsupported with Veeam? DBA seems reluctant to make any changes currently as we are in a POC phase.
andyman3000
Novice
 
Posts: 3
Liked: never
Joined: Thu Nov 30, 2017 5:46 pm
Full Name: Andy Perkins

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby Mike Resseler » Wed Aug 01, 2018 5:37 am

Hi Andy,

Welcome to our forums!

I might be wrong her but AG requires WFSC. See here: https://docs.microsoft.com/en-us/sql/da ... erver-2017

Also, in that link you will see that you might need indeed to disable and re-enable the AG group.
"Always On availability groups registry keys are subkeys of the WSFC cluster. If you delete and re-create a WSFC cluster, you must disable and re-enable the Always On availability groups feature on each instance of SQL Server that hosted an availability replica on the original WSFC cluster. "

I think it would be best to take this information to your DBA and look together for performing this

My 2 cents
Mike
Mike Resseler
Veeam Software
 
Posts: 4527
Liked: 487 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby andyman3000 » Thu Aug 02, 2018 4:43 pm

Thanks for the response Mike!

Turns out the SQL Availability Group is spread out on a Windows and a Linux server. The Windows server provided was the passive node, and this behavior was expected according to the DBA as even his LiteSpeed tools would not be able to back it up. So the POC is about to get interesting, as we will be able to dive into Veeam Agent for Linux as well, which is our goal to unify all tools into Veeam.

Thanks,
Andy
andyman3000
Novice
 
Posts: 3
Liked: never
Joined: Thu Nov 30, 2017 5:46 pm
Full Name: Andy Perkins

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby Mike Resseler » Thu Aug 02, 2018 5:29 pm

Now you actually have my interest. Let me know the results!
Mike Resseler
Veeam Software
 
Posts: 4527
Liked: 487 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby benmac » Thu Aug 02, 2018 7:53 pm

Did you ever find the solution?

I am trying to perform SQL processing for SQL Server 2017 set up as an AG on two Ubuntu VMs. Getting Pacemaker failovers when the backup fires off though, and am curious if your topic is related or if maybe I need to open a ticket.
benmac
Novice
 
Posts: 3
Liked: never
Joined: Thu Aug 02, 2018 7:44 pm
Full Name: Ben

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby andyman3000 » Thu Aug 09, 2018 9:42 pm

I am curious how did you set up SQL Processing on the Ubuntu VMs? We have the active DB on the RHEL server but it is looking like we will have to run pre/post scripts which is what they are doing anyway. Only problem is that their native/scripted backup is going through an NFS mount point to another location. I'm not sure if Veeam Agent backup will grab the database backups saved to another location.

Sounds like it should not failover the database to the other node when the backup starts, but from what it seems speaking with our DBA is that database has to be offline for the SQL backup to happen.

So just am curious are you running pre/post scripts for the DB backup?

The Window Agent AG backs up fine, just with the caveat of the Availability group databases being skipped (which is expected according to the DBA since they are active elsewhere)
andyman3000
Novice
 
Posts: 3
Liked: never
Joined: Thu Nov 30, 2017 5:46 pm
Full Name: Andy Perkins

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby Mike Resseler » Fri Aug 10, 2018 4:56 am

Ben,

In the UG I can find that SQL server 2017 is only supported on windows!
@andy What version are you using?
Mike Resseler
Veeam Software
 
Posts: 4527
Liked: 487 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby benmac » Fri Aug 10, 2018 4:56 pm

andyman3000 wrote:I am curious how did you set up SQL Processing on the Ubuntu VMs?


I have an AG replicating the primary server data to a secondary as well as a config-only monitoring server. The AG is set up to take backups from the primary for log truncation purposes, etc. and I wanted Veeam to do the full system backups, with 15-min log backups.

andyman3000 wrote:We have the active DB on the RHEL server but it is looking like we will have to run pre/post scripts which is what they are doing anyway. Only problem is that their native/scripted backup is going through an NFS mount point to another location. I'm not sure if Veeam Agent backup will grab the database backups saved to another location.


This is similar. We have a share set up on our Veeam server that I'm connecting to via CIFS mount point. These are for the 15-min transaction logs, full, and diff backups initiated from within SQL. However, I'm still not getting full system backups from Veeam without a pacemaker takeover.

andyman3000 wrote:Sounds like it should not failover the database to the other node when the backup starts, but from what it seems speaking with our DBA is that database has to be offline for the SQL backup to happen.


Hmm... does he/she (the DBA) mean for the database to actually be offline, or that the data needs to be backed up from the secondary replica? I don't have a way for the dB to be offline and maintain business continuity. If they mean from the secondary - that's what I keep reading, but apparently that method isn't supported with how I have the AG set up (SQL Server Enterprise may allow this, but we only have Standard).

andyman3000 wrote:So just am curious are you running pre/post scripts for the DB backup?

The Window Agent AG backs up fine, just with the caveat of the Availability group databases being skipped (which is expected according to the DBA since they are active elsewhere)


Honestly, I don't have any pre/post scripts, yet. I'm still trying to get backups to be successful so I haven't done anything in this realm. Do you have something in mind that might make it work?

Here's an example of the e-mail I get every morning when the Veeam backup fires off:
Code: Select all
Pacemaker Alert Takeover in progress at Fri Aug 10 03:01:03 CDT 2018 on <primary server name>
 
        Command line was:
        /usr/lib/ocf/resource.d/heartbeat/MailTo start

This is sent from root@<machine name> via the MailTo heartbeat within Pacemaker. I don't have much detail on top of the time it occurs, which the daily backups start at 3am every day.
benmac
Novice
 
Posts: 3
Liked: never
Joined: Thu Aug 02, 2018 7:44 pm
Full Name: Ben

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby Mike Resseler » Mon Aug 13, 2018 5:37 am

Ben,

As said, it seems that SQL server 2017 on linux is not supported (see the user guide) so I doubt we can get it working at this point in time. I don't know what the plans are for the next version to support this to be honest
Mike Resseler
Veeam Software
 
Posts: 4527
Liked: 487 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby benmac » Mon Aug 13, 2018 1:12 pm

Thanks Mike. Does Veeam support Pacemaker on Linux in general? I can handle SQL backups internally using maintenance plans, but I'd like to stop failovers from occurring at backup time. The two resources that lead me to think no are this from Reddit (https://www.reddit.com/r/sysadmin/comments/5371js/backing_up_linux_with_veeam/), and this from Veeam forums (https://forums.veeam.com/veeam-agent-for-linux-f41/veeam-agent-backup-failed-t48306.html).
benmac
Novice
 
Posts: 3
Liked: never
Joined: Thu Aug 02, 2018 7:44 pm
Full Name: Ben

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby Mike Resseler » Tue Aug 14, 2018 5:30 am

Ben,

Unfortunately I have no idea whether this is supported or not (and I don't have the possibility to test this at the moment). I will sent this thread to our Linux specialist and ask to come back to you. Stay tuned (I believe he is OOO at this moment...)
Mike Resseler
Veeam Software
 
Posts: 4527
Liked: 487 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: SQL Guest Processing Windows 2016 AG without WFCS on VMW

Veeam Logoby PTide » Thu Aug 16, 2018 4:22 pm

Hi,

Does Veeam support Pacemaker on Linux in general? I can handle SQL backups internally using maintenance plans, but I'd like to stop failovers from occurring at backup time.

Since you use Pacemaker on VMs, I think the Linux Agent forum thread that you've mentioned might be not very relevant to the subject here. The failover problem you're seeing is likely to be related to the way how the VMs react to VMware snapshot. But I digress...

We have not conducted any Pacemaker-specific tests. The failover might occur due to cluster nodes losing network connectivity for a short period of time (from milliseconds to seconds) during VMWare snapshot creation process. That, depending on your cluster settings, might be enough for the cluster to perform a failover or even declare the other node to be dead although it is clearly not (that can lead to a "brain-split" situation). If my memory serves me well, there is a migration-threshold parameter in Pacemaker which defines how many failures should occur before a service gets migrated to another node. You should be able to rise the parameter right before a snapshot is triggered (so that the first failure won't be considered to be a "real failure") and revert the changes right after the snapshot has been taken. That can be done with the usage of pre-freeze/post-thaw scripts, for example. Another option would be to increase a timeout before failover, or to disable failover completely for the duration of your backup window for that particular cluster. I hope that makes sense.

Thanks
PTide
Veeam Software
 
Posts: 4144
Liked: 342 times
Joined: Tue May 19, 2015 1:46 pm


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google [Bot], tsightler and 37 guests