April 24, 2017 at 8:02 am
Hello,
We have a SQL Cluster (2008 + R2) with our database content sitting on a SAN. SAN is coming to end of life, so we're looking to move content.
We're in the process of configuring a VM Cluster, we'll use SQL Server 2012 Standard (downgrading from Enterprise). My question, we have 4 instances on the SQL Cluster, want to create 4 instances on the VM, keep the migration simple. Then I got to thinking to create 4 instances (1 default, 3 named) on one SQL Server. Not so sure I like that idea though, keep them separate on individual servers. It would require more maintenance effort, but it would be easier to detect any problems that erupt.
Any thoughts/experience on this? Comments would be appreciated - thanks.
April 24, 2017 at 8:43 am
If you were willing to spend some money, I'd do it with 4 separate VM's and then load up a tool like DxEnterprise (similar to Polyserve back in the day). That way you can have all of the instances running at once, and if any of the VM's goes down (windows updates, gremlins, bluescreen, IT clicked the wrogn button, etc), you just get a few seconds of downtime while things fail over.
This gives you fewer points of failure.
One thing to remember though is that when you are licensing for the VM it gets more expensive as you need to license for the number of CPU's (or is it cores?) on the host machine, not the number you provide to the VM.
There are advantages and disadvantages of having a single machine:
pros:
- only need to manage windows updates on 1 machine instead of 4
- only need to manage permissions on 1 machine instead of 4
cons:
- 1 point of failure
- CPU/RAM is shared between all 4 instances
- reboots (due to windows update) take down all SQL instances at once
Where I work, we have about 20 instances on our live servers and 3 servers to manage those and it works fairly well. I'd analyze what you need and decide from there. If these 4 are all high CPU/RAM instances, I'd have them on separate VMs. If it is all very light, then having them all in 1 may work.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 24, 2017 at 9:30 am
Thanks for the insights. A couple of thoughts, you said when you are licensing for the VM it gets more expensive as you need to license for the number of CPU's (or is it cores?) on the host machine, not the number you provide to the VM. My understanding at least for SQL licensing, the more virtual processors you have, you are paying for them.
Also:
pros:
- only need to manage windows updates on 1 machine instead of 4 -- Server guys pushing for 1.
- only need to manage permissions on 1 machine instead of 4 -- good point
cons:
- 1 point of failure - it's a VM cluster (2 nodes). Am I misunderstanding this?
- CPU/RAM is shared between all 4 instances
- reboots (due to windows update) take down all SQL instances at once -- This one hurts, had not thought about that.
A lot of the databases are not heavy hitting. On our SQL Cluster we have 256 GB memory, we use 192 GB. My main concern, with SQL Server Standard you have a 64 GB limit. That's a mindset change. There is one database that is heavy hitting, I might separate that from the others. There's also the concern with pooled resources (per the vendor). They've had issues with VMs but as long as it's configured properly, it should be good..
I'll discuss with my boss, then finalize. Thanks again.
April 24, 2017 at 9:53 am
For the licensing, a good read is:
http://download.microsoft.com/download/C/3/7/C37F243B-0246-493E-ABFC-41A7FFD6DE38/SQL_Server_2012_Virtualization_Licensing_Guide.pdf
I misunderstood the licensing model. What I was stating was licensing for maximum virtualization. The advantage of using this is you get unlimited number of VMs. Disadvantage is you need to license all of the physical cores on the server and have software assurance.
I suppose with a VM Cluster you have 2 nodes so that does remove the single point of failure unless the OS disk is what goes bad? I've not worked with VM Clusters before. I am more of the DBA guy where I work and we have a separate team for the VMs and physical boxes. With a VM Cluster, do they share the OS disk or do they each have their own? I see issues with both scenarios:
shared OS disk: some update corrupts the disk, fails over and disk still corrupt. Both nodes are down
non-shared OS disk: install SQL updates on primary box (service pack type stuff) everything works fine on the primary but it fails over before you can update the secondary. SQL fails to start as the system database versions differ from the OS versions in the software.
I am not entirely sure which model VM clustering uses, but I'm seeing issues with both. Mind you, I'd expect that you'd update the secondary box and then fail it over which would cause SQL to perform the updates and if there were any issues, you'd fail it back to the primary. updating the Primary before updating the secondary just sounds risky...
Although, having a hot/cold spare likely means you will need additional licensing, no? So you'd need to license it for both nodes I believe.
So I suppose another con is extra licensing (unless you license for maximum virtualization). One more con would be it isn't very scalable having everything on 1 VM. In the event that you need to build more SQL instances up as the company grows, you may get to a point where a single VM just doesn't cut it and then scaling out can get interesting (ie tricky).
I've heard of issues with VM's where VMWare will see the VM is used lightly and cut back on the allocated resources then it gets hit with a heavy load and it needs to baloon the resources back up. But that is a configuration thing on the VM. I expect as long as you have dedicated resources set for the VM(s) you shouldn't have those issues.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 24, 2017 at 2:13 pm
We run similar to this. We have an Always On cluster with 2 instances in the main location and one at the DR location. The main location instances are set for automatic fail over and it works pretty awesome. We license each instance with no SA.
For SQL 2012 Standard we're going to do db mirroring to the DR location and have one instance at the main location
April 24, 2017 at 5:15 pm
If you are going to continue using Always On Availability Groups, you will need the Enterprise Edition of SQL Server 2012.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply