SQL Cluster design

Aamer-
Aamer- used Ask the Experts™
on
we are planning to deploy an centralized SQL Cluster that will host databases for
1.      Sccm site database
2.      Scom Operational database
3.      Scom datawarehouse database
4.      WSUS database
5.      Scom reporting
6.      Sccm reporting
7.      Two small databases for antivirus and a network device monitoring solution
Need some suggestions and Recommended on the  sql cluster design

should we go for a active/passive cluster as MS recommends an A/P cluster for SCOM databases. how many instances should I create and how should the luns be configured as both the nodes will be virtualized on Vmware
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
should we go for a active/passive cluster as MS recommends an A/P cluster for SCOM databases
Mind that this recommendation for A/P by MS is assuming that you won't nothing else in the cluster. If you do, my recommendation is to have a 3 node cluster being all active/active/active nodes so you'll be able to balance the load.
For example:
Node 1 - SCCM database; WSUS
Node 2 - SCOM Op; SCOM DW
Node 3 - Reporting (SCOM & SCCM); AV

Commented:
@Vitor, Does that means using Availability Group to have it as Active/Active ?

Author

Commented:
I would like to try this in a test lab. appreciate if you can direct me to a document to build this 3 node A/A cluster. please I am not an SQL guy, I work on system center and am worried about the SQL configuration. some sort of a step-by-step guide please
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Author

Commented:
scom and sccm reporting cannot share an instance of SSRS. so I cannot install scom an sccm reporting on node 3 together.  and SSRS is not cluster aware. so is it recommended to install SSRS on node I and use it for sccm reporting and SSRS on node 2 and use it for SCOM reporting. it will not be able to failover as SSRS is not cluster aware. and appreciate if you can suggest the number of instances. is it the same instance we ar using on all nodes or separate instances of sccm and scom.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Does that means using Availability Group to have it as Active/Active ?
No.

appreciate if you can direct me to a document to build this 3 node A/A cluster
If you can build a cluster with 2 nodes then you'll be able to build a 3 nodes cluster. The process is exactly the same just having one more node to be installed and configured.

scom and sccm reporting cannot share an instance of SSRS. so I cannot install scom an sccm reporting on node 3 together.  
I didn't know that. Do you have any link to a document that says that? If that's true then what you can do is to install 2 MSSQL instances in node 3, one for SCOM SSRS and another one to SCCM SSRS.

SSRS is not cluster aware
I'm assuming the cluster is only for databases and not for applications. You should have SCOM, SCCM and SSRS in different servers and more important, not installed in this cluster.

Author

Commented:
This will considerably increase the number of SQLl enterprise licenses as I need multiple licenses for three active nodes. then if I install SSRS on the sccm and scom servers, additional licenses will be required just to run SSRS
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
This will considerably increase the number of SQLl enterprise licenses as I need multiple licenses for three active nodes.
Depends if you have licensing by CAL or by Processor. Which one do you have?

then if I install SSRS on the sccm and scom servers, additional licenses will be required just to run SSRS
I'm not aware of how SSRS license works but I think is similar to SQL Server.

Author

Commented:
we have processor licenses. sql will be installed on virtual machines and SSRS needs a full sql enterprise license even when you do not install the database services. for using VMware anti affinity rules three nodes of the cluster will be kept on three different physical hosts. my question is when you have a three node cluster, will I use the default instance on all the three servers or will I be creating named instances. and in case of a single node failure or is under maintenance the resources should move to a functioning server. I am not very sure about the sql A/A cluster
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
my question is when you have a three node cluster, will I use the default instance on all the three servers or will I be creating named instances.
Clustered instances can't be default named. They are always named instances.

I am not very sure about the sql A/A cluster
You'll need to pay licensing in the Passive node anyway, so why let a node completely Passive when it can help your business to have a better performance? Don't forget that in an Active/Passive mode, the Active node need to have double of the resources to keep everything running smoothly. So, double of RAM and CPU.

Author

Commented:
I am told, in a active passive cluster you just need to license SQL for the active node only.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Who told you that? Microsoft?
I would confirm that information if I was you.

Commented:
Based on my understanding, SQL server 2012 with AG requires both of them licensed.

Maybe that was the case with SQL 2008R2 and below.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
As far as I know, the only way you don't have to pay an extra license is to have the machine shutting down. Usually DR solution where you're only allowed to have the machine ON for few hours my year for software update purposes and DR testing.
For a Cluster solution I'm almost sure that you'll need to pay licenses for Passive nodes as well. If you can, confirm this with Microsoft.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
At the risk of adding yet more confusion and contradictions, what I read stated that the first passive AG group must be licensed but not the second, i.e., 2 licenses not 3 for a primary and two AGs.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
At the risk of adding yet more confusion and contradictions, what I read stated that the first passive AG group must be licensed but not the second, i.e., 2 licenses not 3 for a primary and two AGs.
That's strange because you license MSSQL instances and not AGs. A MSSQL instance can have an AG and not all databases need to be part of an AG.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Yes, I phrased that poorly, please disregard.  I am definitely not an expert on SQL licensing :-).

Author

Commented:
we have different flavors for sql high availability . I was considering A/P initially as all system center products support it. but the client insists on an active/active cluster of some sort. always on is not supported by SCCM. so the option of availability groups is ruled out. the next option is to build an A/A cluster. I need a doc like a step-by-step guide for building an A/A cluster. all the docs I found is for availability groups. the concept of A/A cluster is not so clear. do the LUNS have to be shared between the servers. how will I configure LUNS. etc. so a document is appreciated
MSSQL Senior Engineer
Distinguished Expert 2017
Commented:
I need a doc like a step-by-step guide for building an A/A cluster
The only difference from an A/A to an A/P solution is that in A/P the 2nd node doesn't have any load. Passive really means that the node is up and being ready to receive the resources from the Active node in case of any issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial