March 29, 2017 at 7:57 am
I'm trying to build up my clustering skills in my Lab, which is an all virtualbox environment.
I wanted to create what i thought would be an enterprise like server with everything installed: SSAS/SSIS/SQL/SSRS.
I created a Windows2016 server from the 120 day evaluation, patched it, added clustering services to it and then cloned it.
After being cloned, I created a cluster that featured no shared storage, and each server has four local drives, which I've planned out the drives as follows
C:=Operating System,
D:=SQL Data,
T:= TempDB
L:=Log files .
Then i was following a how-to from Brent Ozar's first responder kit, which has a PDF on building an always on, and here is where i painted myself into a corner, and probably need to restart.
His article was talking specifically about HADR and always on availability groups, and he mentions that adding shared storage for the cluster is outside of the scope of his article.
So after establishing the cluster, I installed "New SQL Server stand alone installation or add features to an existing installation" but I also installed all other services, so I've got SSAS/SSIS/SSRS instances on each node.
Previous clusters I had run the "New SQL server failover cluster" and then "Add node to a SQL Server failover cluster", instead of stand alone instances.
However, Now I think i needed shared storage at the beginning, and then to add those things to the cluster, instead of stand alone.
so my questions are fairly basic, it's probably just that I'm looking for a reassuring pat on the back and a "go for it"
Am I correct that SSAS, in order to be clustered, need shared storage?
Can a clustered instance of SQL still do Always On Availability Groups too? or is it an either-or scenario? cluster + shared storage or stand alone + AO?
Say i restart, add shared storage, and install SQL and all services on each cluster, instead of as side-by-side stand alone.
Would that be a typical representation of the real world? A cluster with both shared and not-shared storage to support SSAS and also Always On?
I'm second guessing myself, I'm thinking i should l try uninstalling all SQL-related services and adding them back as clustered installations,
March 29, 2017 at 8:22 am
bobbytables - Wednesday, March 29, 2017 7:57 AMWould that be a typical representation of the real world? A cluster with both shared and not-shared storage to support SSAS and also Always On?
No.
If you want to use AGs, then no shared storage, SQL is not installed as clustered. The cluster service is there to support SQL's failover behaviour.
Typically, on busy 'enterprise' server, you'll find SSAS and SQL engine on different servers, because of the resource requirements. especially if the failover requirements are different. SSRS also separate, more because of security and patching there (it's a web server)
Only time I've seen all of them on one server is when someone went 'next, next, select all, next, next...' through the install dialog, not because they were intentionally designed to be all on one server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 9:30 am
very helpful thank you!
so for my lab example then, where i eventually have all services on each node of the cluster, i would install SSAS/SSIS as clustered services, but a second pass to install SQL as a stand alone on each node? SQL Availability groups cannot exist on a clustered SQL instance, then?
March 29, 2017 at 11:12 am
No, because it makes no sense to have it like that, it's likely to cause problems when the cluster fails over. Create more virtual machines and separate them out
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 11:22 am
but wouldn't licensing costs be a major cost issue, if the services are spread out on multiple servers?
I believe software assurance would let us have an active / passive cluster for the cost of the active node's license, right? but multiple machines would multiply the license cost quite a bit;
That's the thrust of the idea i was attempting: a cluster with all services, that happen to also feature the latest tech of always on availability groups.
March 29, 2017 at 2:56 pm
Yes, but a cluster with all services, and trying to merge failover cluster and AG (and I don't even think you can do that), is a really bad idea. Overloaded, resource contention, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 3:19 pm
bobbytables - Wednesday, March 29, 2017 11:22 AMbut wouldn't licensing costs be a major cost issue, if the services are spread out on multiple servers?
I believe software assurance would let us have an active / passive cluster for the cost of the active node's license, right? but multiple machines would multiply the license cost quite a bit;That's the thrust of the idea i was attempting: a cluster with all services, that happen to also feature the latest tech of always on availability groups.
You are correct, but at what cost to the business? Assume you have a single cluster that has all of your services on one node. How big is that machine to support all of these services? If it IS powerful enough, how do you manage the resources between each service? Do you know how your licensing is set up? If a host is licensed, you only pay for the cores once on the host. If the individual guests are licensed, then you pay for the cores on each one. That being said, in order to make this single machine big enough to support everything, aren't you still paying close to the same? I suppose it depends on what resources are needed... In general, it is a really bad idea to do this. If the business is affected and performance is really bad, does anyone care that you saved them some money? Not when they are losing revenue because of poor performance, or failures on the server.
Finally, as Gail mentioned... you run into issues with a FCI and an AG. Not to mention, SSIS is not cluster aware and would still have to be managed as 2 separate services.
Jared
CE - Microsoft
March 29, 2017 at 3:25 pm
SQLKnowItAll - Wednesday, March 29, 2017 3:19 PMNot to mention, SSIS is not cluster aware and would still have to be managed as 2 separate services.
Neither is SSRS, though it's more likely to be found scaled out and load-balanced with its databases on a clustered SQL instance (or in an AG) than in any type of makeshift cluster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2017 at 7:25 am
bobbytables - Wednesday, March 29, 2017 7:57 AMI'm trying to build up my clustering skills in my Lab, which is an all virtualbox environment.
I wanted to create what i thought would be an enterprise like server with everything installed: SSAS/SSIS/SQL/SSRS.I created a Windows2016 server from the 120 day evaluation, patched it, added clustering services to it and then cloned it.
After being cloned, I created a cluster that featured no shared storage, and each server has four local drives, which I've planned out the drives as follows
C:=Operating System,
D:=SQL Data,
T:= TempDB
L:=Log files .
Then i was following a how-to from Brent Ozar's first responder kit, which has a PDF on building an always on, and here is where i painted myself into a corner, and probably need to restart.His article was talking specifically about HADR and always on availability groups, and he mentions that adding shared storage for the cluster is outside of the scope of his article.
So after establishing the cluster, I installed "New SQL Server stand alone installation or add features to an existing installation" but I also installed all other services, so I've got SSAS/SSIS/SSRS instances on each node.
Previous clusters I had run the "New SQL server failover cluster" and then "Add node to a SQL Server failover cluster", instead of stand alone instances.
However, Now I think i needed shared storage at the beginning, and then to add those things to the cluster, instead of stand alone.so my questions are fairly basic, it's probably just that I'm looking for a reassuring pat on the back and a "go for it"
Am I correct that SSAS, in order to be clustered, need shared storage?
Can a clustered instance of SQL still do Always On Availability Groups too? or is it an either-or scenario? cluster + shared storage or stand alone + AO?Say i restart, add shared storage, and install SQL and all services on each cluster, instead of as side-by-side stand alone.
Would that be a typical representation of the real world? A cluster with both shared and not-shared storage to support SSAS and also Always On?I'm second guessing myself, I'm thinking i should l try uninstalling all SQL-related services and adding them back as clustered installations,
Please see my stairway to AlwaysOn starting at this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 31, 2017 at 9:20 am
GilaMonster - Wednesday, March 29, 2017 2:56 PMYes, but a cluster with all services, and trying to merge failover cluster and AG (and I don't even think you can do that), is a really bad idea. Overloaded, resource contention, etc.
Actually, if I remember correctly (and I'm not an expert) AG needs clustered Windows servers to work, even though AG requires unshared drives.
The way we have it set up is a 4 node cluster shared across two data centers with standalone drives on each server for the AG. The AG is setup as standalone SQL instances. We also have (on each data center) one SQL Clustered instance for our report databases. The clustered instances use SSRS, SSIS, and SANs. We really haven't had an issue except when there are network / firewall issues. It's just a matter of knowing what hardware to use, what SQL Server properties are required, etc.
March 31, 2017 at 9:29 am
Brandie Tarvin - Friday, March 31, 2017 9:20 AMGilaMonster - Wednesday, March 29, 2017 2:56 PMYes, but a cluster with all services, and trying to merge failover cluster and AG (and I don't even think you can do that), is a really bad idea. Overloaded, resource contention, etc.Actually, if I remember correctly (and I'm not an expert) AG needs clustered Windows servers to work, even though AG requires unshared drives.
The way we have it set up is a 4 node cluster shared across two data centers with standalone drives on each server for the AG. The AG is setup as standalone SQL instances. We also have (on each data center) one SQL Clustered instance for our report databases. The clustered instances use SSRS, SSIS, and SANs. We really haven't had an issue except when there are network / firewall issues. It's just a matter of knowing what hardware to use, what SQL Server properties are required, etc.
Correct. AGs use WINDOWS clustering to manage an IP Address resource (Listener), but that's it. FCIs use shared storage as the resource. Still, its all in a WSFC (Windows Server Failover Cluster).
Quick summary for those who aren't familiar:
AG uses WSFC to cluster the machines and an IP. It has SEPARATE instances of SQL installed as stand-alone
FCI uses WSFC to clluster the machines and storage. It uses a specific install of SQL for failover clustering.
SSRS is not cluster aware, but uses scale-out and can be load balanced. SSIS is not cluster aware and runs as 2 separate instances. SSAS can be clustered.
Can you install AGs and FCI? Yes... I have done a 4-node WSFC where 2 nodes were one FCI and the other 2 were a separate FCI and then both FCIs were participating in an AG. PM me for more details as this isn't the forum for that. 🙂
Jared
CE - Microsoft
March 31, 2017 at 11:07 am
GilaMonster - Wednesday, March 29, 2017 2:56 PMYes, but a cluster with all services, and trying to merge failover cluster and AG (and I don't even think you can do that), is a really bad idea. Overloaded, resource contention, etc.
You can have an Availability Group where one or more replicas are FCI's.
It simply reintroduces shared storage to the equation and provides no auto failover with the AG
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 31, 2017 at 11:55 am
Perry Whittle - Friday, March 31, 2017 11:07 AMGilaMonster - Wednesday, March 29, 2017 2:56 PMYes, but a cluster with all services, and trying to merge failover cluster and AG (and I don't even think you can do that), is a really bad idea. Overloaded, resource contention, etc.You can have an Availability Group where one or more replicas are FCI's.
It simply reintroduces shared storage to the equation and provides no auto failover with the AG
Yup, but what I think the OP was asking about was two nodes that are both failover cluster nodes for SSAS as well as replicas for AGs, and that's what I don't think will work, combining both things onto one 2-node cluster
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2017 at 7:01 am
GilaMonster - Friday, March 31, 2017 11:55 AMPerry Whittle - Friday, March 31, 2017 11:07 AMGilaMonster - Wednesday, March 29, 2017 2:56 PMYes, but a cluster with all services, and trying to merge failover cluster and AG (and I don't even think you can do that), is a really bad idea. Overloaded, resource contention, etc.You can have an Availability Group where one or more replicas are FCI's.
It simply reintroduces shared storage to the equation and provides no auto failover with the AGYup, but what I think the OP was asking about was two nodes that are both failover cluster nodes for SSAS as well as replicas for AGs, and that's what I don't think will work, combining both things onto one 2-node cluster
AS is cluster aware and can be installed in the same cluster. The AG group configuration should not take any notice of this as it's not a database engine instance. The pre reqs check whether another database engine instance within the same AG can possivbly be hosted on the same node, if it can then pre reqs fail.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply