Required hardware to build a development environment in Sql Server

Question

We have a bunch of applications that depends on about 10 different databases servers (all sql server) in our network.

We are looking to build a development environment where we could have a mirror of the production databases.

Our plan is to have one server and put all the 10 databases inside it as separate sql server instances. All the 10 databases currently sum up to 1TB of disk space.

We won't have many users connected to these databases and won't do any kind of heavy use, just development.

What kind of hardware do I need to run this and not have a too bad performance? We came up with a Dell machine that is in our budget with 2x Xeon Quad Core + 32GB Ram + 8TB of disk (SATA 7200RPM) configured in RAID. Will that do?

Thanks!

Answer 1

Ideally, you'd have the same configuration in development as you do in production. That way, you can have a higher probability that if it's slow in dev, it will be slow in prod, and if it's fast in dev, it will be fast in prod. I'm guessing that this would be a pretty significant drop from what you have in production. But given that few places have the budget to have dev match prod, I understand dealing with what you have.

With 10 instances, the part I'd be concerned about is the RAM. Figuring that somewhere around 4-6 GB should be held back for the OS, any other software on that server (A/V, etc.), and an emergency buffer, this means you have about 26-28 GB of RAM for 10 instances, or under 3 per instance. I don't know what you're averaging now, but it's probably a good bit more.

As such, I'd try to max out that server's RAM and set the maximum memory per instance equal to what it is in production. Even though there won't be many people using the server, skimping on RAM will give you false negatives with performance and will make it harder for developers to get accurate performance tests.

Unless you have dozens of developers constantly going at the databases, you'll probably find CPU to be adequate (though not really good). The low disk speed is a bit concerning, but get enough RAM and the disk speed shouldn't be too big of an issue for a development server. Again, you'll likely find additional performance-related problems in dev that you won't in prod (if you have more CPUs per server and faster disks and more spindles available per server), but when you're on a budget, those are the trade-offs you have to make sometimes.

Edit

I would be totally remiss if I didn't point out Glenn Berry's book on the topic.

Answer 2

Plus one on the book suggestion and the thoughts on leaving memory for the OS and about the CPU load in Kevin's answer and Kevin brings up some good points but a few more thoughts for you to ponder as you settle on a solution:

1.) Development environments don't always (and in my experience don't typically) match production and I'm not sure you always need to have a dev environment match production in terms of size and performance. In a perfect world with an unlimited budget, sure. As long as you have -some- environment somewhere that can mimic productions performance for you to do serious performance and load testing on you'll be good.

2.) Having 10 instances on one machine will definitely cause you some occasional heartache at times if all 10 instances are being worked on at a time. But then again, so would having all of those databases in one instance with lots of people working on them at a time in the same instance. So look to how you have it set up in prod and why you have it that way. If permissions are different and there are cross instance communications between your applications (do your apps talk to the different prod instances at different times?) then it makes sense to provide some separation via instances in dev.

3.) In prod are you multi-instance or multi server? You might consider looking at virtualization in dev, though that increases some complexity and license cost depending on how you go. It also makes dev look a bit less like production if it is on a virtual, but it is not unheard of at all to give developers virtuals even in a physical production environment.

4.) Sounds like you have the space in dev, but sounds like you won't get the IO performance. I think for dev, this is likely fine, but make sure you have a more prod like environment someplace to do performance testing - or make sure you have the ability to attack performance problems after deploy in production. Not sure what industry you are in, what up-time requirements you have and how your deploys work..

5.) Also pay attention to the license in dev. SQL Server Developer edition is great. But if production is not Enterprise, make sure your developers are not developing with assumptions that things will just work the same in prod. Dev edition works just like enterprise. So know what features will and won't work in standard if prod isn't enterprise and just make sure your developers appropriately manage this risk. This link will help there.