Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.
Course Of The Month
3 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Review MS SQL cluster diagram
Posted on 2016-08-18
Hi
I need help to determine or review MS SQL diagram for new build.
What I want to accomplish is HA and HP MS SQL cluster build.
That would contain:
1. load balancer for WEB\Application server that would be hosted on VM, Win 2012r2
2. Side A 2 nodes cluster (active, passive) Physical servers - Win 2012r2
3. Side B 1 warm server for DR. Physical servers - Win 2012r2
4. SUN data storage.
I am wondering is this diagram has any logic.
What can be change to improve availability, performance, DR, etc.
What hardware components do I need to accomplish this?
Thx, Michal
SQL-Cluster-diagram.png
I need help to determine or review MS SQL diagram for new build.
What I want to accomplish is HA and HP MS SQL cluster build.
That would contain:
1. load balancer for WEB\Application server that would be hosted on VM, Win 2012r2
2. Side A 2 nodes cluster (active, passive) Physical servers - Win 2012r2
3. Side B 1 warm server for DR. Physical servers - Win 2012r2
4. SUN data storage.
I am wondering is this diagram has any logic.
What can be change to improve availability, performance, DR, etc.
What hardware components do I need to accomplish this?
Thx, Michal
SQL-Cluster-diagram.png
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
2-
2
- +2
9 Comments
Active 2 days ago
For the first look Side B is not a warm it is a cold spare server because your databases are not synchronized from Side A to Side B so in case of failover you have to copy/restore all databases from Side A.
1. HA
If you want to implement a real DR side you have to use a synchronization between Side A and Side B (SQL Server Mirroring or SQL Server Always On or BI-Directional Transactional Replication).
You don't need a FailOver Cluster on Side A (as currently) with shared storage because it increases the Availability but it has no DR role. It is expensive and it does not meet your requirements.
2. HP
Unfortunately there is no standard technology in case of MS SQL Server for scallability (to increase performance). So it is not so easy than in case of Oracle RAC. But if you use Database Mirroring / Always On / Transactional Replication then you can use Seconday Read-Only servers for Reporting, Statistic or backup purposes.
My recommendation: Remove failover cluster on Side A, build a (low bandwith) network between Side A and Side B and build an AlwaysOn cluster between Side A and Side B.
This will keep the current availability level, but give Disaster Recovery role and increase performance because Side B is read-only (for reporting or backup) and posibility for addidional read-only servers.
1. HA
If you want to implement a real DR side you have to use a synchronization between Side A and Side B (SQL Server Mirroring or SQL Server Always On or BI-Directional Transactional Replication).
You don't need a FailOver Cluster on Side A (as currently) with shared storage because it increases the Availability but it has no DR role. It is expensive and it does not meet your requirements.
2. HP
Unfortunately there is no standard technology in case of MS SQL Server for scallability (to increase performance). So it is not so easy than in case of Oracle RAC. But if you use Database Mirroring / Always On / Transactional Replication then you can use Seconday Read-Only servers for Reporting, Statistic or backup purposes.
My recommendation: Remove failover cluster on Side A, build a (low bandwith) network between Side A and Side B and build an AlwaysOn cluster between Side A and Side B.
This will keep the current availability level, but give Disaster Recovery role and increase performance because Side B is read-only (for reporting or backup) and posibility for addidional read-only servers.
Active 3 days ago
In order for site B to be warm, there needs to be disk replication (or SQL replication) that keeps that copy of the data up to date. If you can use mirroring or log shipping/replication, that would work, or you can use physical disk replication if your SAN supports it. Another option may be to use AlwaysOn - it would make failovers to this site a bit easier to handle, and you can still keep the primary site on an active/passive failover cluster and only move to the secondary site when you decide it's necessary.
We use physical servers for our SQL clusters and I don't like using VMs - in our environment, they haven't been stable or low-latency enough to handle the clustering requirements. This may be a factor of how they're set up or something isolated to our environments, but I don't quite trust VMs for failover clusters from what I've seen.
We use physical servers for our SQL clusters and I don't like using VMs - in our environment, they haven't been stable or low-latency enough to handle the clustering requirements. This may be a factor of how they're set up or something isolated to our environments, but I don't quite trust VMs for failover clusters from what I've seen.
Active today
I've had similar issues with VMS for SQL Server, esp. with performance. We were just never able to get good performance out of them.
Hi All
I agree with Ray and Scott about SQL clustering \failover.
I don't want to use VM for SQL server. I hear a lot of negative comments about performance (stability, low-latency, etc)
Thank you, M
I agree with Ray and Scott about SQL clustering \failover.
I don't want to use VM for SQL server. I hear a lot of negative comments about performance (stability, low-latency, etc)
Thank you, M
Active 3 days ago
Just off the top of my head, here are some advantages of each choice:
Pro for physical:
- More straightforward troubleshooting (you know you're not sharing hardware)
- No hypervisor latency (should be small if properly configured, but still exists)
- Not affected by "noisy neighbors" as it has the hardware dedicated
- Licensing is more straightforward (VM licensing, even for SQL Server, can cause some confusion and in some cases, requires you have SA to use them the way you want - physical doesn't have those issues)
Pro for VM:
- More HA options (live migration, for example)
- Hardware failure not an issue (just boot the VM somewhere else)
- More easily scaled (if you need to add memory or CPU cores, it can be done with a reboot)
I'd always prefer physical based on what I've seen, but that's admittedly limited - I have peers that run a fully virtualized data center, including SQL Server, and they have no regrets. If you've got a good relationship with the sysadmin/VM admin and you're comfortable with the technology, the extra layer of abstraction may not be an issue - the way I've seen it, it's extra things to troubleshoot and more places to check and see if there's a problem (which is one reason I still prefer physical).
Pro for physical:
- More straightforward troubleshooting (you know you're not sharing hardware)
- No hypervisor latency (should be small if properly configured, but still exists)
- Not affected by "noisy neighbors" as it has the hardware dedicated
- Licensing is more straightforward (VM licensing, even for SQL Server, can cause some confusion and in some cases, requires you have SA to use them the way you want - physical doesn't have those issues)
Pro for VM:
- More HA options (live migration, for example)
- Hardware failure not an issue (just boot the VM somewhere else)
- More easily scaled (if you need to add memory or CPU cores, it can be done with a reboot)
I'd always prefer physical based on what I've seen, but that's admittedly limited - I have peers that run a fully virtualized data center, including SQL Server, and they have no regrets. If you've got a good relationship with the sysadmin/VM admin and you're comfortable with the technology, the extra layer of abstraction may not be an issue - the way I've seen it, it's extra things to troubleshoot and more places to check and see if there's a problem (which is one reason I still prefer physical).
Active today
Physical can make SQL licensing harder, or at least more expensive. If using core licensing, you have to license ALL cores available to the operating system environment. Modern servers have easily 8-12 core per socket. What if you only need four cores, which is the minimum number Microsoft requires you to license (even if you only have 1 core)? Virtualization to the rescue. Instead of trying to find a server with 4 cores, take your hardware, load up a hypervisor, and then give the VM only 4 cores. You can now legally license only 4 cores for that instance of SQL server, even is the hardware has 32 cores.
Virtualization also make moving to new hardware easier. It can be done without even taking the VM offline. You can migrate a running SQL VM from Hyper-V Server 2012 on HPe hardware and spinning rust to Dell hardware, Hyper-V 2016, and NVMe SSD without bringing down the server. Want to move it to PureStorage SSD SAN? No problem. Want to move it to HDS? No problem, and no downtime required. *
*Note, there may be some limitations when moving storage when using shared storage for a HA cluster, depending on how you set it up. SQL doesn't require shared storage depending on how you configure it, so that is less of an issue.
Virtualization also make moving to new hardware easier. It can be done without even taking the VM offline. You can migrate a running SQL VM from Hyper-V Server 2012 on HPe hardware and spinning rust to Dell hardware, Hyper-V 2016, and NVMe SSD without bringing down the server. Want to move it to PureStorage SSD SAN? No problem. Want to move it to HDS? No problem, and no downtime required. *
*Note, there may be some limitations when moving storage when using shared storage for a HA cluster, depending on how you set it up. SQL doesn't require shared storage depending on how you configure it, so that is less of an issue.
Active 3 days ago
VM definitely has the advantage of easy migration to a new environment, and I agree that it can be cheaper as well - my point is just that it requires Software Assurance and if that's not something you have for your licenses (or don't want to pay for), the license is written in such a way that it's difficult to virtualize. Modern servers do easily have 8 cores/socket, so it can get spendy to go physical as well, and moving to new hardware can add a wrinkle as well if you're installed on a physical server.
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Course of the Month3 days, 8 hours left to enroll
759 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.