Best practice for Micrsoft SQL with HA

Availability for the Always-On Enterprise

Best practice for Micrsoft SQL with HA

Veeam Logoby nm56 » Thu Nov 24, 2016 3:49 pm

I'm setting up a new environment and installed a Microsoft SQL Failover cluster cause I'm familiar with it, I like the HA it provides and VMware now officially supports it.

Unfortunately I'm now realizing the iSCSI Bus Sharing issue, not being able to take snapshot which prevents Veeam B&R from making backups of the SQL nodes. I thought I could handle SQL backups within SQL and backup the VM excluding the RDM drives. But I'm not seeing how I can do this. I've done a lot of forum and kb reading on this and haven't found a good answer yet.

So my question is: Does anyone use SQL Failover cluster and how do you handle backups of the guest OS (Windows backup, Veeam Endpoint). Are you satisfied with this.

Is there an alternative approach I should use instead of Failover cluster? SQL AlwaysOn Availability group looks more expensive and elaborate for what we need.

Other notes:
- I like the High Availability a Failover clusters provide, able to patch and reboot servers without taking them offline.
- I'm okay handling SQL backups within native SQL itself, might even prefer this.
- VMs are stored on a SAN, SQL cluster is connecting to separate drives on SAN using RDM.

Any help and advice is appreciated.
nm56
Lurker
 
Posts: 2
Liked: never
Joined: Wed Nov 23, 2016 7:13 pm

Re: Best practice for Micrsoft SQL with HA

Veeam Logoby Mike Resseler » Fri Nov 25, 2016 6:19 am

Hi,

You are right that snapshotting is not supported in this case. While RDM virtual disks in physical mode, Independent disks, and disks connected via in-guest iSCSI initiator are also not supported, these are skipped from processing automatically, With VMs with disks engaged in SCSI bus sharing in this case we can't even snapshot the VM.

I am not that familiar with this but somebody else might be... Is there a way to support SQL failover cluster without using that SCSI bus sharing?

If not... I personally would use Veeam Endpoint (soon Veeam Agent for Windows) and backup the VM entirely and straight to a B&R repository so you can afterwards have all the goodies of Veeam B&R :-).

A few things you can keep in mind:
* You can do a full backup of the VM, including log truncation of SQL by VEB, giving you (in combination with the above B&R repository) Veeam SQL explorer functionality
* You can do the SQL backup yourself and then take a backup of the entire C-volume and on the DATA volume only the .bak files from the SQL backup.
* BMR is perfectly possible, but create a new recovery media every time there are major changes to the OS (like VM upgrade or OS upgrade or similar

Hope it helps
Mike
Mike Resseler
Veeam Software
 
Posts: 3340
Liked: 379 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: Best practice for Micrsoft SQL with HA

Veeam Logoby nm56 » Wed Nov 30, 2016 6:44 pm

This was very helpful and I'm trying to test using Endpoint and BareMetal restore. But another question came up:

- How do I backup, and restore, the RDM drive mapping for the VM?

I made a Endpoint backup of the C volume and system state and skipped the Data drive (RDM). However, when I create the VM to restore onto, I need to add the RDM drive but can't cause it is associated with the original VM.

In a cluster, I would point the second node VM to the first node's RDM drives vmdk file. However what if the datastore was lost?

Also another question that may deserve it own topic. Is there a way to setup Microsoft SQL to use shared storage (SAN) for databases without using RDM? Is setting up a 'regular' VMFS volume for databases supported and recommended?
nm56
Lurker
 
Posts: 2
Liked: never
Joined: Wed Nov 23, 2016 7:13 pm

Re: Best practice for Micrsoft SQL with HA

Veeam Logoby nmdange » Wed Nov 30, 2016 9:33 pm

It is possible with Hyper-V to backup shared vhdx, but it is only crash-consistent so you probably would not want to use it with a SQL cluster anyway.

With SQL Server 2012 or higher running Enterprise Edition, or SQL Server 2016 with Standard Edition, you can use an Always-On Availability Group which does not require shared storage. Instead the database data is replicated between the nodes. It's definitely a bit more involved in the setup than a traditional failover cluster but since there is no shared storage you can use Veeam to backup the VMs like normal.

Here's some info on backups in this scenario:
https://helpcenter.veeam.com/docs/backu ... tml?ver=95
nmdange
Expert
 
Posts: 213
Liked: 59 times
Joined: Thu Aug 20, 2015 9:30 pm

Re: Best practice for Micrsoft SQL with HA

Veeam Logoby WRS2200 » Thu Dec 01, 2016 3:45 am

Thanks for this post. This was very helpful.
WRS2200
Enthusiast
 
Posts: 28
Liked: 3 times
Joined: Thu Aug 06, 2015 8:21 pm
Full Name: Weston Strom


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google Feedfetcher and 64 guests