July 23, 2017 at 3:48 am
Dear Everyone
Ikeep reading online that its not recommended to virtualize SQL Server clusters (4 nodes)
My company wants to implement SQL Server 2016 SP1 Enterprise Edition on WSFCusing Windows 2012 R2 Standard Edition clusters in 2 different countries having the following specs:
RAM - 8 to 16 GB
Disk space of 80 GB for C drive and 80 GB for E drive
Also the database size wont be more than 5 GB in size
I have recommended the following:
1. Dont virtualize the clusters as Clustering onVirtualization has many different issues related to IO and network connectivity
2. Use a minimum of 32GB of RAM per server
The database size is too small to use a cluster but they are insisting on this as they will also add 24/7 applications in the future on this server once they become available
What do you guys think?
KY.
July 24, 2017 at 12:43 am
I don't agree that virtualization would cause IO and network connectivity issues. I've seen it being a common practice these days to make your SQL Server spread out virtually and then use some kind of HA technology e.g AlwaysOn to support the 24/7 applications. In fact, we ourselves have a 5 node AlwaysOn cluster and there is no issues as what you are pointing out. IO and network latency can occur even on a physical host...basically those are separate issues and is irrelevant to clustering or virtualization. Trend is towards cloud services such as using Microsoft Azure and Amazon as a hosting provider and people have no issues whatsoever placing their critical databases on a virtual host.
However, what if I agree with you...do you have facts to prove your point..maybe build a test setup and see if you can prove them that it doesn't work the way they thought it would...best of luck 🙂
Thanks
July 24, 2017 at 12:50 am
I will see if i can have the company carry out a POC because using a cluster for a 5 gb database doesnt make sense
Or am i missing something here?
Another thing is do we really need to use the WSFC and FCI? Or can we get with using FCI only to enable HA?
KY
July 24, 2017 at 3:59 am
You are doing the standard techie thing of talking about technical solutions without outlining the business problem.
Even if your managers have said they want a cluster (especially if it is managers who said this!) it is good to understand the business problem. Managers seldom understand what current technology can do, and are prone to provide specifications that might have been reasonable 10 years ago but which today are next to obsolete.
Please ask what the business wants in terms of resilience, availability, and performance. What are the Recovery Time Objectives and the Recovery Point Objectives. What level of budget is available and what are the timescales for delivery. Are there any business or legal reasons about where the servers can be located - do you have to own the physical kit or can it be in the cloud.
There will always be compromises in deciding the optimum solution, with money often the main constraint. Getting the wrong solution can mean you have less resilience, availability, etc than current technology allows, and you get it at a price higher than you need to pay. Getting the right solution means you are meeting agreed requirements at minimum cost.
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 24, 2017 at 4:09 am
hurricaneDBA - Sunday, July 23, 2017 3:48 AMDear Everyone
Ikeep reading online that its not recommended to virtualize SQL Server clusters (4 nodes)My company wants to implement SQL Server 2016 SP1 Enterprise Edition on WSFCusing Windows 2012 R2 Standard Edition clusters in 2 different countries having the following specs:
RAM - 8 to 16 GB
Disk space of 80 GB for C drive and 80 GB for E drive
Also the database size wont be more than 5 GB in sizeI have recommended the following:
1. Dont virtualize the clusters as Clustering onVirtualization has many different issues related to IO and network connectivity
2. Use a minimum of 32GB of RAM per serverThe database size is too small to use a cluster but they are insisting on this as they will also add 24/7 applications in the future on this server once they become available
What do you guys think?KY.
one database of 5GB in size is unlikely to warrant a sql server instance with 32GB ram.
The issues around network and disk I\O on virtual systems are generally down to the way the virtual infrastructure is architected and configured.
Virtualisation will always have some overhead on the Vms but it can be minimal with the appropriate design and configuration
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 24, 2017 at 4:27 am
Dear ED
The only answers i have are:
Availability we dont have any applications running 24/7 but this cluster is being setup now to be able to cope with future applications which will be 24/7 such as IAM, etc
The availability is for business hours for now but 24/7 later but i dont know the time line yet
Performance - Since the cluster will span 2 different countries i will check on this
I still dont know when configuring the cluster do we use WSFC or FCI or both using 4 nodes on SQL Server 2016 Enterprise edition
July 24, 2017 at 4:50 am
hurricaneDBA - Monday, July 24, 2017 4:27 AMDear ED
The only answers i have are:
Availability we dont have any applications running 24/7 but this cluster is being setup now to be able to cope with future applications which will be 24/7 such as IAM, etcThe availability is for business hours for now but 24/7 later but i dont know the time line yet
Performance - Since the cluster will span 2 different countries i will check on this
I still dont know when configuring the cluster do we use WSFC or FCI or both using 4 nodes on SQL Server 2016 Enterprise edition
It's not a case of using WSFC or FCI, you would use either of the following (WSFC forms the base)
Please read my stairway to AlwaysOn starting at this link
http://www.sqlservercentral.com/stairway/112556/
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 24, 2017 at 5:01 am
Which one is the best practice?
WSFC + FCI
FCI + AG
WSFC + FCI + AG
July 24, 2017 at 5:15 am
Personally I would look at using Availability Groups instead of clustering SQL Server. AGs will meet your availability and resilience targets and are a far more strategic direction for SQL Server than SQL Clusters. Also, installing Enterprise Edition for a 5GB database is overkill - Standard Edition can give you what you need for much less money. If you need EE capabilities in 2 years time then buy them then - if you never need EE then you have saved money. I much prefer to install SQL in a guest environment - it simplifies platform management and upgrading as you can treat your Windows instances more like cattle than pets.
I would also look at hosting this in the cloud (either AWS or Azure) using as a minimum multiple Availability Zones in AWS with the option to include an additional AWS Region in the future (or similar concepts with Azure). Hosting in the cloud allows you to right-size your equipment for your current workload, and upscale easily as workload increases. If you do not already have a production environment in the cloud then part of managing the risks of moving there is to hire a consultant who has previous experience of what you want to do.
If you do decide to host it yourself, then kit sizing is a problem. Hyperconvergence is starting to sweep through the industry, and where I work now the saving in environment costs will pay for the new kit before it becomes obsolete, never mind the almost 2 order of magnitude improvement in performance. If you buy just for this project and guess what you might need in 2 years time you will be paying more than you need (read this as making your company less competitive). If you buy for your entire hardware stack and go for hyperconvergence then costs are higher upfront but gives massive savings overall. However, unless like us you are a NFP organisation that gets big kit discounts then cloud is almost always the right choice.
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 24, 2017 at 6:00 am
I will check with the company offering the proposal but i wanted to know for a solution spanning 2 different countries will using AG suffice or an sql cluster is required here?
July 24, 2017 at 7:09 am
If you are going for what is called 'Out of Region' second node with SQL Clustering then you will need a mechanism to keep both data stores synchronised. Windows 2016 has all of this built in to it, but W2012 R2 and below would need a 3rd party component.
If you are doing Out of Region with AGs then you would need Enterprise Edition (not Std as I said previously) because you would need to use Asynchronous mirroring, which is only available in EE.
You should ask the business why it wants Out of Region resilience, as this will add cost and complexity to the solution. If they want want isolation from a single disaster event then multiple Availability Zones within a single AWS region will do that Each AZ is geographically separate from all the others (except for US Virginia) but within the allowable distance for Synchronous replication of about 50Km. It is similar with Azure.
I am guessing from your replies that your organisation has limited experience in setting up and running geographically distributed computing. If so, then I advise you get a consultant who has this experience to help you with the architecture. Putting this together so that it works reliably is much more then clicking Next on an install. Your organisation will need to review all the network kit it uses to make sure it is up to the job, and make sure your comms lines can handle your current and proposed traffic levels. The SQL install is the easy bit.
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 24, 2017 at 9:46 am
hurricaneDBA - Monday, July 24, 2017 5:01 AMWhich one is the best practice?
WSFC + FCI
FCI + AG
WSFC + FCI + AG
please read my stairway to Alwayson linked above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 24, 2017 at 11:17 am
My company has its headquarters in country A which is 600km away from the country I reside in. The main application server is in country A and they want to cluster it with a server in country B.
They've brought in a consultant to do the job but they're offering what I mentioned above:
Geo cluster using sql server 2016 EE using wsfc and AG and I think FCI but since I don't have experience in clustering or AG I'm trying to see what's best for the business and won't cause another layer of troubleshooting for myself and the network and system teams.
I'd still like to get someone else's view besides this 3rd company so I think I'll ask business to bring in another consultant.
What do you guys think?
KY
July 25, 2017 at 12:20 am
Hi guys so i got more information for you to help me decide what to do. I sent the following questions and got the reply in red:
If so then what is the needfor clustering? Why can’t you just use Availability Groups?
In our proposal we have 4nodes, each running an instance of SQL server, participate in a single windowsserver failover cluster that span two data centers. Also, it’s important tonote that this is a non-shared solution, and the nodes don’t share any storagewith another node. Each node is running an instance of SQL server and has itsown copy of data.
Back to you question whatis the need for clustering?? How you can build SQL cluster without windowscluster? This is per SQL Architecture, in order to enable Always on or any SQLCluster you need windows cluster after that you need to install SQL Cluster(Active / Passive) then you can enable always on. This is an active activesolution and specific for geo-cluster.
Why are you proposingenterprise edition since we can have 2 nodes of SQL Server cluster on 2016Standard edition?
Enterprise edition isrequired for Always on, Always on feature is available only with Enterpriseedition. You will have 4 nodes of SQL Server cluster (two in eachdatacenter (active / passive)) then we will enable the always on between thetwo SQL Cluster. With this you will achieve 99.99 % High availability betweenthe sites and across both data center.
Why didn’t you proposeanything on the CLOUD?
Hosting in the cloud allowsyou to right-size your equipment for your current workload, and upscale easilyas workload increases
We can do the same oncloud, but you need a site to site VPN and you need a good bandwidth for datareplication, in other hand, you need to consider the XenMobile Citrix Databaseand the performance. If your company policies allow the use of Windows Azure weare ready to change the design. Hence, you need to take in consideration thestorage, network, VM and SQL licenses + Consumption. (Same one Azure you needSQL enterprise with Software Assurance)
What do you guys think?
I feel something is not completely true as on top of all this the clusters will be virtualized so is this a yes or a no in your books?
KY
July 25, 2017 at 3:47 am
hurricaneDBA - Tuesday, July 25, 2017 12:20 AM
If so then what is the needfor clustering? Why can’t you just use Availability Groups?
In our proposal we have 4nodes, each running an instance of SQL server, participate in a single windowsserver failover cluster that span two data centers. Also, it’s important tonote that this is a non-shared solution, and the nodes don’t share any storagewith another node. Each node is running an instance of SQL server and has itsown copy of data.
Back to you question whatis the need for clustering?? How you can build SQL cluster without windowscluster? This is per SQL Architecture, in order to enable Always on or any SQLCluster you need windows cluster after that you need to install SQL Cluster(Active / Passive) then you can enable always on. This is an active activesolution and specific for geo-cluster.
As I stated before, WSFC is the underlying technology behind SQL Server AlwaysOn, it's a required technology.
The vendor is recommending traditional AGs with no shared storageand no FCI. Read the stairway
hurricaneDBA - Tuesday, July 25, 2017 12:20 AM
Why are you proposingenterprise edition since we can have 2 nodes of SQL Server cluster on 2016Standard edition?
Enterprise edition isrequired for Always on, Always on feature is available only with Enterpriseedition. You will have 4 nodes of SQL Server cluster (two in eachdatacenter (active / passive)) then we will enable the always on between thetwo SQL Cluster. With this you will achieve 99.99 % High availability betweenthe sites and across both data center.
You can have a basic Availability group between 2 replicas in sql server 2016 standard, but it is basic. Enterprise edition exposes the full features of AlwaysOn Availability Groups, which you'll likely require in cross datacentre deployments
hurricaneDBA - Tuesday, July 25, 2017 12:20 AM
Why didn’t you proposeanything on the CLOUD?
Hosting in the cloud allowsyou to right-size your equipment for your current workload, and upscale easilyas workload increases
We can do the same oncloud, but you need a site to site VPN and you need a good bandwidth for datareplication, in other hand, you need to consider the XenMobile Citrix Databaseand the performance. If your company policies allow the use of Windows Azure weare ready to change the design. Hence, you need to take in consideration thestorage, network, VM and SQL licenses + Consumption. (Same one Azure you needSQL enterprise with Software Assurance)
Their points above are valid, no issue here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply