Avatar of Aamer-
Aamer-
 asked on

SQL Cluster design

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
SCCMMicrosoft SQL ServerSSRSVMwareMicrosoft 365 Enterprise

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Vitor Montalvão

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
Albert Widjaja

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

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Aamer-

ASKER
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ão

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.
Aamer-

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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.
Aamer-

ASKER
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ão

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Aamer-

ASKER
I am told, in a active passive cluster you just need to license SQL for the active node only.
Vitor Montalvão

Who told you that? Microsoft?
I would confirm that information if I was you.
Albert Widjaja

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

Maybe that was the case with SQL 2008R2 and below.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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 Pletcher

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ão

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Scott Pletcher

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

ASKER
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
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.