SQL Avaibility Groups / ESXi

[aniros]

Hi,

I'm about building an SQL Lab with availability groups option to ensure high availability , and I'm thinking about the adequate architecture to adopt.

A saw many tutos about solutions about iSCSI, TureNAS, shared drives, vSAN .... but in each case, there is something missing to ensure a real and effective availability of SQL app.

To simplify things, I have to machines, so 2 hosts, on witch I want to install  SQL instances: A primary one, and a secondary that runs when the first one fails. I believe I can do that with iSCSI drives and Windows failover feature, but the problem are the drives witch are "connected to one of the 2 hosts (for example host 1). Those drives will not be available for the host 2, so even if it runs, it will not get access to logs, data, databases, ...

Is there a solution to put drives in a "independent" state, that the two hosts can access and if one of them fails, the second one continue working automatically?

I tried to be breif and I hope I explained well my probnlem.

Thanks.

[Lalegre]

,

I am getting a lit confused with your scenario.

If you decide to use iSCSI Storage, this should be coming from a storage outside of the two ESXi and mount the volumes directly inside the Guest OSes that form the cluster. That way if one ESXi goes down, the secondary ESXi will still have a node running.

 

[aniros]

Hi Lalegre,

Thanks fot taking time to reply.

Ok, If I understand, using an external iSCSI (from a FreeNAS VM for example) will allow that the 2 hosts access to the same drives simultaneously?

If yes, is there a way to ensure redundancy for the iSCSI VM, so if it shuts down, a second VM can run immediately and drives still available for the 2 hosts?

Other question: Is iSCSI the best solution to go with in my case?

Thanks in advance.

[Duncan Epping]

Let's start with the basics:

• You have 2 vSphere hosts?
• Do you have some kind of shared storage?
• Is this for production workloads or a lab environment?

[aniros]

Hi depping,

1- I have 2 vSphere hosts (ESXi 6.5) and I can add a third host (I read somewhere that I may need 3 hosts for deploying a particular HA architecture). Hosts are equipped with SFP+ network cards.

2- Shared storage? I have drives plugged to hosts, and I can make some drives shared. I don't know if this answers your question.

3- It's a lab environment.

Thanks a lot.

[Lalegre]

,

So the point is the following, if you decide to go with SQL Always On using AG (Availability Groups) you do not need any type of storage access from the Guest OS at the same time as replication happens between the Databases Individually on each of the nodes.

However, if you decide to go with SQL Always On with FCI (Windows Failover Clustering) you will need a volume that is accessible at the same time from both SQL Servers as the Databases are shared between them, this could be by using SCSI Bus Sharing, iSCSI, RDMs, etc)

 

[aniros]

Hi,

Thanks for your reply.

So I created a cluster to prepare SQL AG with a shared folder as witness. This folder is located on the Domain controller.

So, it will be ok for SQL nodes. But what if the DC machine fails? The shared folder will not be accessible, and the both SQL nodes will fail.

I think about a way to create a second cluster on 2 DC machines (1 DC per Host). What can be your recommendations?

Thanks in advance.

[Dayton01]

I can address a small part of this subject.  I have two ESXi hosts running v7.0 and a TrueNAS Core server running iSCSI.  I installed a 10Gb switch and each host and the TrueNAS attached to the switch.  On both ESXi hosts I configured ISCSI and setup a datastore, so they are sharing the same volume.  I uploaded all my Windows and Linux iso's to the NAS into an ISO folder and either host can use them to create a new VM.  In addition the hosts have local SAS storage where all their VM's were installed but I moved a few VM's to the NAS.  I can shutdown the VM and then start it on either host, but would not ever try to start it on both at the same time.

All the servers are Supermicro with dual CPU, dual power supply, RAID 10 SAS storage, NAS is using zfs RAID-z2.  I have dual UPS's that go to seperate circujit breakers so the dual power supplies are protected.  In addition there is a natural gas AC generator protecting the breaker panel.  The servers can run on UPS until the generator is ready, which is about 10-20 seconds.

TrueNAS Core (FreeNAS) does not include support for iSCSI multipath so you cannot provide network failover unless you purchase their enterprise devices.