1

I have a new Windows Server 2016 box with 32GB of RAM, it will be used for dev testing by one or two users max. There will be occasional use of VM/Hyper-V instances and a couple of IIS test sites running from time to time.

I also need to install all versions of SQL Server from 2008 through to 2016. These will each have many databases attached but very few will actually be active at a time unless testing calls for it.

Assuming there is no practical issue with installing several different SQL Server installations on the same box, what options do I have to configure the best use of RAM? I know that on a standard installation, SQL will try to hog all available RAM but I want to try to avoid this leaving around 12GB for other services and allocate a maximum of 24GB for ALL the SQL instances to share equally without fighting with each other over resources.

Is there a good way to achieve this?

userSteve
  • 1,503
  • 4
  • 20
  • 32
  • To add another voice, I 100% agree with what TomTom said below. Do not run any other workloads directly on the Hyper-V host. – EEAA Mar 16 '17 at 10:02
  • Not sure I understand? I only have 2 or 3 VM's I need to run once or twice a month. Why is this a bad idea? – userSteve Mar 16 '17 at 10:05
  • Running VMs is fine. What you should avoid is running anything else on the host (the bare-metal OS) than just the Hyper-V role. For instance, keep all of your SQL Server instances in VMs. – EEAA Mar 16 '17 at 10:09
  • Ok but why? Whats wrong with running SQL on the bare-metal, are you referring to the RAM allocation or something else? – userSteve Mar 16 '17 at 10:45
  • See my answer here. This answer was for KVM, but it apples to any virtualization solution. http://serverfault.com/a/837079/20815 – EEAA Mar 16 '17 at 10:49
  • If you read MS documentation, even they will recommend the same. – EEAA Mar 16 '17 at 10:49

1 Answers1

1

There will be occasional use of VM/Hyper-V instances and a couple of IIS test sites running from time to time.

Never. Do. That. Install Hyper-V run everything in Hyper-V instances. Do not run load on the "physical" machine.

Assuming there is no practical issue with installing several different SQL Server installations on the same box

Not totally sure, mostly because of your large selection. I keep a separate VM for every generation - easier to get rid of them this way.

Is there a good way to achieve this?

Back to the start. Dump SQL Server in a VM, assign 24gb, finished. SQL Servers, running alone in the VM, will never use more memory.

COnsider upgrading RAM - 32gb is quite low for a development all kind of stuff server in days where workstation motherboards can go up to 64gb.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • 1
    Thanks, I will consider the VM idea, but there might be licensing issues and time to create, and maintain each separate OS. Also, presumably a large amount of the allocated RAM will be taken up by the OS rather than fully used by SQL. – userSteve Mar 16 '17 at 09:58
  • There is no licensing issue, particularly not for development. WHat you are unaware of is that if you install Hyper-V the ROOT partition always has priority. RAM, CPU - if the root partition needs it, the VM's starve. Which is why "nothing on the host" is the rule on a virtualization system. – TomTom Mar 16 '17 at 10:45
  • Aha! I see. Great advice. Ok so what if I don't install Hyper-V at all, what advice would you give for the SQL instances ? – userSteve Mar 16 '17 at 11:06
  • Getting real. Seriously. Not joking. FOr anything that is a development system I would not forego the power and flexiblity of a virtualization layer, ever. For production, you can regularly plan load - for development things change fast. – TomTom Mar 16 '17 at 11:55