Using virtual servers for hosting SQL Servers

Question

We develop websites using SQL Server and ASP.NET.

Development: web

1. on developer PC, database on DEV server
2. Testing: both on TEST server
3. Live: both on LIVE server

At the moment, DEV and TEST servers are separate physical servers. I'm considering making them virtual servers on the same physical servers.

It is important that is easy to deploy web and database changes from DEV to TEST to LIVE etc (we use Red Gate SQL Compare to sync the database changes to the next SQL Server).

Before I go ahead, I would like to know:

• Has anyone done this? Does it work well
• Any advice implementing it would be welcome. Are there any good resources out there.
• Also, what software would you recommend for this -- Virtual server or VMWare.
• Any best practices that I should know

Answer 1

Yes; we do this. It works very well. Making changes to the virtual machines is identical to making changes to physical machines; that is one of the biggest advantages of virtualization. In fact, most apps don't even notice that the machine is virtualized (although I suspect that behemoths like SQL Server might have some subtle internal detection / compensation code).

If the servers are only hosting SQL server, another alternative is named instances - i.e. you have yourserver\dev and yourserver\test, which are separate SQL Server instances on the same OS; this reduces isolation and changes your process slightly, but means you don't have to load 3 operating systems (1 host, 2 guest) into memory. You'll still have 2 complete SQL Server instances in memory, of course. The other downside is that you can't try a host OS upgrade on "dev" without also impacting "test". Obviously if other custom software runs on the machine, you might want to go for isolated virtuals instead.

Most virtualization tools offer a P2V tool; there are usually some limitations about the original and host hardware, however. If the server is just a SQL Server, you might want to start clean instead.

Answer 2

I set up two SQL 2005 servers (W2003 Server) to test database mirroring. It worked well, despite my machine not being the greatest in terms of memory or disk space. Definitely well worth setting up. I ended up creating the first Virtual Serve and when I was happy with it I cloned it to create the second. While the first was turned off I started the second and ran NewSid to change the name and SID for the server and SQL parts.

Answer 3

Definitely fine for dev. Probably fine for testing. Never for production, unless you have a very limited site.

Answer 4

You might also check this question and this one.

Here is another recent question, but there isn't much in terms of answers yet.

Answer 5

I'd also suggest not putting it into production yet. Are trying this with sql server 2008?

Answer 6

Before hosting SQL Servers inside virtual environments, check if they're currently accessing data on a SAN. If so, are they using multipathing - meaning, do they have more than one HBA plugged into them? Ae they using any load balancing software to get more SAN throughput - like EMC's PowerPath?

VMware and Hyper-V both restrict a single guest's SAN throughput to 1 HBA right now (10/2008). If you take a database server that's currently getting 2-4 HBA's of throughput and virtualize it, storage performance is going to be pretty bad.

If they're not on a SAN - meaning you're using local disks - be aware that sharing the same set of local disks can be a performance nightmare for SQL Server. Now you're talking about two sets of data files, two sets of logs, and two sets of TempDB on the same set of spindles. That's going to be slow unless you have fairly small databases (under 20-100gb).

Make sure you don't schedule backups, index defrags or other maintenance jobs on both servers at the same time. Stagger them to get the best performance.

Make sure you don't overcommit on memory. If you put both virtual servers on a box with say, 16gb of memory, only set up each guest with 7gb of memory, and set up SQL Server with hard-coded min/max memory settings of 6gb. Yes, in theory you can overcommit VMware on memory, but your performance will suffer. Definitely don't use dynamic memory settings.

One thing you might actually like - if you do backup & restore between the two (instead of data compare & sync) you can get very fast network throughput between guests in the same physical server.