August 11, 2015 at 11:23 am
We recently purchased a tool, and the vendor is on site this week. I was just informed that in order for their tool to work properly, it's login needs to be in the sysadmin role. I don't really want to get into a discussion on that point, but instead on working with that requirement.
I brought up the concept of continuing in our current methodology of a single VM per SQL instance, and our VM Admin is wondering it it's overkill. So we starting talking about creating a single VM that will support multiple instances, and if/when we purchase future tools which also need sysadmin privileges, we'd start another SQL instance and put it on that one server.
So I'm looking for some advice, especially since either decision lands any SQL Instance in a VM environment. To me, a new VM is a simple thing to setup... but not being a VM admin, I'm a bit in the dark about the demands of administering them, and whether I'm asking for a lot if more of these requests come in the future.
If someone suggests a 1 VM per 1 SQL Instance, I was wondering if you also had any suggestions on CPU counts and memory for a server sporting a small database, kind of like a template for a minimally provisioned VM environment for future cases like these.
Thanks,
--=Chuckl
August 11, 2015 at 11:30 am
My preference is one per VM. The resource and security separation you get from two instances on the same VM is limited. You can get much better separation by putting them on separate VMs and consolidating databases into instances where it makes sense from a resource and security point of view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2015 at 4:00 pm
Thanks for the reply. I think we're leaning that direction, more heavily than the multi-instance approach.
I know that estimating another person's environment is not a fair question, so maybe I can ask this another way. I am having the VM start out with a single quad core CPU, and 8Gb of RAM (setting the max memory within SQL Server at 4Gb, per some Brent Ozar suggestions).
Does that sound like it's an unreasonably low amount of RAM, assuming for now that this is the only database that will live on this server? I'm thinking about it in the sense of someone seeing this plan and saying something like, "I've never even seen 8Gb work in a dev environment, so it's going to be painful even in a low-use production environment."
The reason I ask is that I've also been reading about estimates on max memory settings, as a percentage of total RAM, and they always start out at RAM sizings that seem higher than would ever be are appropriate here ("Let's say you have 16Gb of RAM, or more preferably 32Gb..."). I expect the database, at it's largest, to not exceed 10Gb, so even 4Gb seems high to start with.
Thanks,
--=Chuck
August 12, 2015 at 2:46 am
Hi,
I have always felt that 4Gb RAM is a really low value for most common SQL Server environments. Specially in x64 architecture. I should recommend 8Gb. minimum. Maybe you can get a better number checking the size of the most accessed tables in your 10Gb databases or capturing a baseline after deployment.
I hope that helps.
August 12, 2015 at 2:52 am
chuck.forbes (8/11/2015)
Does that sound like it's an unreasonably low amount of RAM, assuming for now that this is the only database that will live on this server?
Very hard to say. It'll depend on the DB's size, the 'working' portion of the data, the workload, etc, etc.
I've seen dev environments with 2GB allocated to SQL. My laptop has 2.5 mostly so I can run stuff in SQL and still do other work. With 8GB on the server, assuming it's a dedicated SQL machine, you can probably set max server memory to 6GB safely. Start there and monitor, nice thing about a VM is you can always allocate more memory.
See https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply