July 25, 2017 at 4:12 am
Thanks Perry and everyone else for the help
Have a lovely week
Kal
July 25, 2017 at 6:48 pm
Using Distributed Availability Groups could also be an option if your nodes are supposed to be in different countries. That would give you more scalability in future (not being limited to 8 secondary nodes anymore) and you wouldn't have one Windows cluster spanning 2 countries as Distributed AG relies on one separate Windows clusters per region.
Pooyan
July 26, 2017 at 4:24 am
I think your consultant is giving you a gold-plated solution. It undoubtedly has the capability to work but will cost a lot to implement.
If you are not already familiar with SQL Server clustering and AGs then there is a steep skills hill to climb. I like to say that SQL Clustering introduces some new and interesting ways to totally trash your data if you get things badly wrong - such as what an inexperienced person might do in the middle of the night when clustering has crashed and you are under pressure to get things working for 7am. The same is true for AGs.
You can achieve the same degree of resilience with a 3-node (N+1) solution using AGs and no SQL clustering for the same license money, and maybe save some kit cost. You can have 2 out of the 3 systems crash and still serve all your data, plus you avoid the complexity of knowing about and supporting SQL Clustering.
At the end of the day, you are hosting a 50GB database, tiny in today's terms. The ideal place for this is Azure or AWS, not hosted in-house. The USP of your organisation is in whatever it sells, not managing kit.
Finally, whatever architecture you adopt, you will pay the same in licensing if you use W2016 and SQL2017 as you would using W2012R2 and SQL2014. If you go for the older software you lose a lot of functionality that has come in with the newer stuff, plus for Windows you are on a deprecated platform. SQL2017 is currently at Release Candidate stage and my guess is a September or October launch. It is a top quality product and will undoubtedly be available to buy before your hardware arrives, so go with the new stuff.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 26, 2017 at 5:12 am
EdVassie - Wednesday, July 26, 2017 4:24 AMI like to say that SQL Clustering introduces some new and interesting ways to totally trash your data if you get things badly wrong - such as what an inexperienced person might do in the middle of the night when clustering has crashed and you are under pressure to get things working for 7am. The same is true for AGs.You can achieve the same degree of resilience with a 3-node (N+1) solution using AGs and no SQL clustering for the same license money, and maybe save some kit cost. You can have 2 out of the 3 systems crash and still serve all your data, plus you avoid the complexity of knowing about and supporting SQL Clustering.
SQL Clustering complexity!
The complexity here is the WSFC which you are still employing with Availability Groups, this is where, in my experience many people fall over. Once the WSFC has issues it will usually affect the applications running upon it.
EdVassie - Wednesday, July 26, 2017 4:24 AM
At the end of the day, you are hosting a 50GB database, tiny in today's terms. The ideal place for this is Azure or AWS, not hosted in-house. The USP of your organisation is in whatever it sells, not managing kit.Finally, whatever architecture you adopt, you will pay the same in licensing if you use W2016 and SQL2017 as you would using W2012R2 and SQL2014. If you go for the older software you lose a lot of functionality that has come in with the newer stuff, plus for Windows you are on a deprecated platform. SQL2017 is currently at Release Candidate stage and my guess is a September or October launch. It is a top quality product and will undoubtedly be available to buy before your hardware arrives, so go with the new stuff.
Windows 2016\SQL 2016 would make the best sense in my opinion and provide the ability to truly stretch your clusters without the requirement of an Active Directory domain and the admin costs that go with it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 27, 2017 at 12:26 am
So correct me if I'm mistaken
AlwaysOn needs WSFC and being on nodes and with AlwaysOn you enable AGs
Now my question is do I need to use sql server clusters?
Are sql server clusters the same thing as FCI?
I'm still reading stairway to AlwaysOn
Kal
July 27, 2017 at 1:01 am
In order to have AG you would need to have WSFC installed on the windows level but you don't necessary need to instal SQL Sever as a clustered instance(although you can mix FCI with AG).The clustered service would be AG itselft and not the SQL Server.
Pooyan
July 27, 2017 at 5:34 am
hurricaneDBA - Thursday, July 27, 2017 12:26 AMSo correct me if I'm mistaken AlwaysOn needs WSFC and being on nodes and with AlwaysOn you enable AGs
Yes, as i said it's all in my stairway to AlwaysOn.
Whether you use
You need a WSFC to suppport any of the above
hurricaneDBA - Thursday, July 27, 2017 12:26 AM
Now my question is do I need to use sql server clusters? Are sql server clusters the same thing as FCI?
A sql server cluster is an FCI, you don't necessarily need an FCI for AGs, in fact introducing an FCI in some ways defeats the object as you're introducing SOP on the storage.
hurricaneDBA - Thursday, July 27, 2017 12:26 AM
I'm still reading stairway to AlwaysOn Kal
\O/
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 31, 2017 at 3:40 pm
Hi everyone
Business came back and approved to have a windows cluster with one node in each location. Hence now when we implement this solution we'll have a one WSFC spanning 2 locations but each node will be part of 1 AG and 2 replicas?
Another question is can we add more nodes to each location say if we choose to make it 2 nodes per location in the next 5 years?
Implementor was planning on creating 2 sql server clusters with each cluster having 2 nodes per location.
Since we have 1 node per location what's the best practice to setup to utilize AlwaysOn?
Thanks
Kal
August 1, 2017 at 3:18 am
hurricaneDBA - Monday, July 31, 2017 3:40 PMImplementor was planning on creating 2 sql server clusters with each cluster having 2 nodes per location.
Do you mean 2 WSFCs?
if so, this can only be achieved by using Windows 2016
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 1, 2017 at 10:18 am
No
1 WSFC spanning two locations with one sql server node in each location. Is this doable?
August 1, 2017 at 10:28 am
When you install SQL Serve rit has to either be installed as a SQL Cluster or as a stand-alone instance. You cannot change a non-clustered SQL install to a clustered one at a later stage. However, you can install a single-node SQL Cluster today and add additional nodes to the SQL cluster in the future.
So to answer your question without deviation, yes you can add an additional node to your Windows Cluster and to a SQL Cluster so long as you install SQL as a single-node cluster at this time.
However, you would gain more resilience if you installed SQL as non-clustered and just added additional nodes to your AGs. The additional nodes could be part of your original Windows cluster, in a separate Windows cluster, or (once you have upgraded to SQL2017) on Linux.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 1, 2017 at 12:42 pm
Hi Ed
So to summarize what you said:
A clustered node uses FCI correct or is the cluster using AGs? I'm still learning about clustering in sql server
If we use one node per location using one WSFC spanning two locations then we can't add nodes to it unless we use FCI.
If we use AGs and non clustered sql server then we'll have 1 replica in location A and one replica as the passive node in location B.
But if I want extra nodes into the AGs they need to be on a new WSFC or can we use the existing WSFC?
Thanks
Kal
August 2, 2017 at 9:37 am
hurricaneDBA - Tuesday, August 1, 2017 10:18 AMNo 1 WSFC spanning two locations with one sql server node in each location. Is this doable?
you can but that's not what you descriped in your original post.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 2, 2017 at 9:44 am
hurricaneDBA - Tuesday, August 1, 2017 12:42 PMA clustered node uses FCI correct or is the cluster using AGs?
I thought we'd been through this.
A cluster node may either host an FCI or a standalone instance.
hurricaneDBA - Tuesday, August 1, 2017 12:42 PM
If we use one node per location using one WSFC spanning two locations then we can't add nodes to it unless we use FCI.
FCI has nothing to do with it. If you have 2 datacentres and 1 node per location that's 2 nodes. You can still add further nodes up to the maximum (16) even from different datacentres.
Please fully read and digest the info i have provided you.
hurricaneDBA - Tuesday, August 1, 2017 12:42 PM
If we use AGs and non clustered sql server then we'll have 1 replica in location A and one replica as the passive node in location B. But if I want extra nodes into the AGs they need to be on a new WSFC or can we use the existing WSFC? Thanks Kal
This depends on the Windows Operating system and SQL server version you deploy.
To make things uber complicated, Windows 2016 allows you to create a cluster across domains or across workgroups.
Stick with simple and go with the proviso that all nodes must be part of the same WSFC
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 2, 2017 at 9:52 am
So you have WSFC which provides clustering at the Windows level, and SQL FCI the provides clustering at the SQL Server level. FCI sits on top of WSFC.
For Availability Groups until SQL2016 you needed WSFC on which you typically installed stand-alone instances of SQL Server. The SQL instances are then linked together using Availability Groups. A given data base gets placed in a specific AG. It is common to have all the databases for a given application in the same AG, and use a different AG for the databases of a different application. Using WSFC is still the most common way to set up the infrastructure for AGs, but with SQL2017 you are no longer tied to Windows clustering software, or even tied to having all of the nodes for your AG on Windows (some nodes can be on Linux).
If you define a Listener Name for a given AG, then your clients can connect using the listener names and you can 'fail over' the primary node of the AG to any of the secondaries, giving the same effect as failing over a FCI cluster. However, with AGs you do not have the short application outage that you have with SQL Clusters at failover time.
Certainly for SQL2016 and above you are not tied to having all the nodes for your AG on the same Windows Cluster. This can be helpful for geographically distributed scenarios where you want AG nodes on different sites for Business Continuity reasons.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply