Best practice for Micrsoft SQL with HA

[nm56]

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.

[Mike Resseler]

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

[nm56]

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?

[nmdange]

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

[WRS2200]

Thanks for this post. This was very helpful.