VMware Cloud Community
Tungdil
Contributor
Contributor
Jump to solution

SQL 2012 always on using affinity rules

Hey guys,

I'm using SQL 2012 alwaya on in a virtual environment but so far it's not configured like best practice and I wanted to change that.

At the moment I don't have any DRS rule in place so it can happen that both virtual machines run on the same host and that's something I want to avoid.

My big problem though is that I only got a SQL-License for two hypervisors and the best practice was talking about four hosts that are needed to accomplish my goal.

Here is what I'm planning to implement:

- VM-VM affinity rule to keep both VMs separate

- VM-Host affinity rule that VM1 SHOULD run on ESX1

- VM-Host affinity rule that VM2 SHOULD run on ESX2

Is this setup a good idea or could/should I do anything different?

From what I found all features like for example evacuation and HA should still work?

There are lot more ESX servers in this cluster and they will be used in case of an outtage, right?!

There is also a dev-environment which looks the same. Could I include those hosts into the same VM-Host affinity rules or should I create new ones?

Btw. I'm using vCenter and ESX 5.5.0.

Thanks a lot!

Cheers

Nicolai

Reply
0 Kudos
1 Solution

Accepted Solutions
vThinkBeyondVM
VMware Employee
VMware Employee
Jump to solution


Affinity rules those you are planning makes sense. These rules will avoid these VMs to be on the same host most of the time.

Note:

1. As VM-Host rule is configured as "SHOULD" , in extreme cases DRS may violate these rules. However, in the next DRS invocation, DRS will try to resolve the violations. 

2. Note that: when you keep either of host into maintenance mode (ESX1 or ESX2), you will have to migrate VM manually to other host due to VM-VM anti affinity rule. Even DRS is in fully automated mode, VM will not be migrated automatically. This will apply only if there are only 2 host in the cluster.

3. Yes, HA should fine with this: However HA is not aware of "SHOULD" rules and in case of host failure it may not respect "SHOULD" rule.

4. VM-VM anti-affinity rule can be made HA aware by using advanced option. Let me know if you want help on this.

5. Yes, in case of outage other hosts in the cluster will be used but please aware on impact due to rules & licenses.

6. "There is also a dev-environment which looks the same." I did not follow what exactly you mean here:

7. DRS migration threshold settings also matters.

8.   DRS gives higher precedence to preventing violations of VM-VM anti-affinity rules than violations of affinity rules. (this is just for your info.)


Nonetheless,

1. If ESX1 goes down or VM-Host affinity rule that VM1 SHOULD run on ESX1 gets violated : VM1 may get migrated to other hosts in the cluster. As you do not have SQL licenses (more than 2), how this would be handled (I am not much aware about SQL licenses)

2. Above point applies to second VM-HOST rule as well.




----------------------------------------------------------------
Thanks & Regards
Vikas, VCP70, MCTS on AD, SCJP6.0, VCF, vSphere with Tanzu specialist.
https://vThinkBeyondVM.com/about
-----------------------------------------------------------------
Disclaimer: Any views or opinions expressed here are strictly my own. I am solely responsible for all content published here. Content published here is not read, reviewed or approved in advance by VMware and does not necessarily represent or reflect the views or opinions of VMware.

View solution in original post

Reply
0 Kudos
4 Replies
vThinkBeyondVM
VMware Employee
VMware Employee
Jump to solution


Affinity rules those you are planning makes sense. These rules will avoid these VMs to be on the same host most of the time.

Note:

1. As VM-Host rule is configured as "SHOULD" , in extreme cases DRS may violate these rules. However, in the next DRS invocation, DRS will try to resolve the violations. 

2. Note that: when you keep either of host into maintenance mode (ESX1 or ESX2), you will have to migrate VM manually to other host due to VM-VM anti affinity rule. Even DRS is in fully automated mode, VM will not be migrated automatically. This will apply only if there are only 2 host in the cluster.

3. Yes, HA should fine with this: However HA is not aware of "SHOULD" rules and in case of host failure it may not respect "SHOULD" rule.

4. VM-VM anti-affinity rule can be made HA aware by using advanced option. Let me know if you want help on this.

5. Yes, in case of outage other hosts in the cluster will be used but please aware on impact due to rules & licenses.

6. "There is also a dev-environment which looks the same." I did not follow what exactly you mean here:

7. DRS migration threshold settings also matters.

8.   DRS gives higher precedence to preventing violations of VM-VM anti-affinity rules than violations of affinity rules. (this is just for your info.)


Nonetheless,

1. If ESX1 goes down or VM-Host affinity rule that VM1 SHOULD run on ESX1 gets violated : VM1 may get migrated to other hosts in the cluster. As you do not have SQL licenses (more than 2), how this would be handled (I am not much aware about SQL licenses)

2. Above point applies to second VM-HOST rule as well.




----------------------------------------------------------------
Thanks & Regards
Vikas, VCP70, MCTS on AD, SCJP6.0, VCF, vSphere with Tanzu specialist.
https://vThinkBeyondVM.com/about
-----------------------------------------------------------------
Disclaimer: Any views or opinions expressed here are strictly my own. I am solely responsible for all content published here. Content published here is not read, reviewed or approved in advance by VMware and does not necessarily represent or reflect the views or opinions of VMware.

Reply
0 Kudos
Tungdil
Contributor
Contributor
Jump to solution

Thanks Vikas for your reply!

Regarding your notes:

2. Is it correct that VCenter doesn't move VMs to different hosts outside the DRS group if you mark a ESX-Host for maintenance?

4. Thanks for the offer but it's not neccessary

6. There is another SQL2012 always on cluster which we use for development and I would like to use the same affinity rules for that cluster as well

Regarding your other notes:

1. That's ok and I don't really care on which host the VMs are in case of a desaster. I just want to make sure that they are on the right ESX-Server during normal business.

The SQL-License also doesn't seem to cause problems even when your using more than 2 hypervisors but as we only bought this license I want to make sure we don't violate the license.

Thanks!

Reply
0 Kudos
vThinkBeyondVM
VMware Employee
VMware Employee
Jump to solution

2. Is it correct that VCenter doesn't move VMs to different hosts outside the DRS group if you mark a ESX-Host for maintenance?

[Vikas]::VM will not be migrated automatically by DRS (DRS in fully auto mode) only when there are only 2 hosts in the cluster, you will have to migrate manually .  If there are 3 or more hosts in the cluster and vMotion is configured correctly, VM will be automatically migrated to other host but rule will not be violated.


6. There is another SQL2012 always on cluster which we use for development and I would like to use the same affinity rules for that cluster as well.

[Vikas]: Yes, you can leverage same VM-host should rules, NO need to create new rules.

      i.e. VM1 , VM3 (dev VM) on ESX1: vm-host rule 1 and VM2, VM4(dev VM) on ESX2 : vm-host rule 2.


If you are OK with disaster & licenses works fine, then you are good to go ahead.





----------------------------------------------------------------
Thanks & Regards
Vikas, VCP70, MCTS on AD, SCJP6.0, VCF, vSphere with Tanzu specialist.
https://vThinkBeyondVM.com/about
-----------------------------------------------------------------
Disclaimer: Any views or opinions expressed here are strictly my own. I am solely responsible for all content published here. Content published here is not read, reviewed or approved in advance by VMware and does not necessarily represent or reflect the views or opinions of VMware.

Tungdil
Contributor
Contributor
Jump to solution

Thanks once again!

Reply
0 Kudos