Central SQL Server development database server

Question

We are in the process of rethinking our development environment. At the moment, we all have Elitebook notebooks which are not as fast as we'd like. We're thinking of virtualizing our development environment to a central VM server.

Our developers work in Visual Studio and use SQL Server as a database. We also have a few SharePoint developers who need a 64bit Win2k8 machine for SharePoint 2010. These are already virtual machines with their own local SQL Server installation.

Every developer's machine or VM has SQL Server installed. This requires resources from all boxes, and is challenging when working with a team on a project. Therefore we're looking into the possibility of centralizing the resources into a single DB server. That box would have to run multiple SQL Server instances (each Sharepoint developer needs a separate one to begin with). We also have the need for an older SQL Server 2005 and SQL Server 2000 installation for backwards compatibility. Besides the SQL Server box, the plan consists of a VM session for each developer with the development tools installed. So a developer could just RDP into the development environment, have his own image and make use of the centralized DB server. Test servers will also be virtualized in the same environment.

I'm looking for some tips and best practices on this matter. For instance:

• What's the number of SQL Server instances a normal box can take? And if we upscale virtualized cores / memory; is that enough to add new instances? I don't expect heavy usage in dev.
• What's the downside on centralizing the SQL Server instances as opposed to keeping a local instance on every development box?
• How should this be integrated in a DTAP strategy?

Answer 1

Just some thoughts:

The number of instances is hardware dependent; I'm not sure if there's a mathematical formula to help calculate how many instances you can run on a VMbox, but it sounds like you're going to need a beast of a machine to run multiple instances per developer PLUS development tools (I can barely get SSMS and Visual Studio to play nice on my laptop). Better hardware = more cost.

Developing on a terminal system may have some advantages, but I can think of a few disadvantages as well:

• network latency. If your developers work remotely at all, they'll need a fast internet connection in order to do anything. If the dev environments stay on their laptops, they could work disconnected.

• instance interdependence. Although SQL Server instances are completely seperate, sometimes you gotta reboot the server. You'll need to coordinate that with all of your developers (may not be a big deal if there's only a handful of you).

• redundancy/maintenance. If one developer's machine is down, you lose a person-day (is that even a word?) of productivity; if the server goes down, your company is paying for a holiday for everyone
  :)

Have you priced out what it would cost to upgrade your individual workstations so that you could compare costs on building out a centralized infrastructure?

Again, these are just some challenges that I think you should consider; there are probably ways to offset them, but they may factor in to your decision.

Answer 2

I would centralize the database servers as best as you can. Having all the dev's work off of centralized database instances should make migrating changes between environments MUCH easier. That alone is worth the effort.

For the SharePoint development environment, I'd highly recommend investing in a few books to make sure you go down the right path. You should be able to have all the dev's working off the same development instances for that too. Here is a good book on the subject, I'm sure there are others: SharePoint 2010 Development

As far as making the developer machines VM's too, I would seek the input of the development staff first and foremost and seriously consider Stuart's pros & cons.