SQLServerCentral / Discuss Content Posted by Grant Fritchey / Article Discussions / Article Discussions by Author / Virtualization for Database Development / Latest Posts

RE: Virtualization for Database Development

Grant Fritchey — Fri, 22 Aug 2008 05:40:05 GMT

We were going to be dealing with exactly this issue. The plan was for there to be planned integration dates when all the development teams would check in their code and reset their images. At that time, if needed, we could get a new copy of the system from production so that we reflect the latest production code.Of course, since our entire plan never got off the ground, I can't tell you how that plan worked in practice. If you do figure something out, I'd be interested in hearing about it.

RE: Virtualization for Database Development

LondonNick — Fri, 22 Aug 2008 03:46:54 GMT

Has anyone experience of using Lab Manager for multi-streamed development with different data recency requirements? That's a stumbling block we're trying to plan for in a planned implementation. Let's say you have a VM version of production. As I understand it, Lab Manager will then allow developers to build their own fenced environments from that in which they can do independent dev/testing. The storage requirement is small as it only holds the deltas from the base VM image. If a developer encounters a problem in their fence then they can destroy their cloned environment and quickly bring up new ones from the base image. That means the dev teams are working independently and no-one is modifying the base image thereby avoiding polluting that with code which may not work or be deployed. So we can continue to make multiple copies of servers and tear them down when they are no longer required.The problem comes when one dev team need recent production data on the base image. Perhaps a change has recently been deployed to production (or a new external data feed has started) and the new data is required in dev/test in order to develop new reports or data warehousing ETL work with "real-world" data. If we update the databases on the base image with production data then we will no longer be able to support the existing clones because the base for the delta has gone. So all dev environments have to disappear. Another dev team may be working on a long term project which involves the generation of lots of dev/test data so can't afford to keep losing their environments. Has anyone experienced these kinds of challenges and come up with a way to overcome them?

RE: Virtualization for Database Development

Grant Fritchey — Sun, 27 Apr 2008 20:32:41 GMT

Any performance degredation is not linear. Depending on how you're working with the system, there may be very little degredation at all. In the instances within the article, we were running, not just multiple servers, but multiple sets of servers. This lead to very perceptible performance issues.

RE: Virtualization for Database Development

David.Poole — Sun, 27 Apr 2008 14:30:42 GMT

Forgive my ignorance on the subject but if you have a virtual server instance with the same spec (CPU, RAM etc) as a physical server am I to expect a 20% performance drop?I can see the benefits of virtual servers for development/integration but how about for a LIVE environment?If there is a % performance drop for a virtual server is it across the board or does it focus on certain aspects, say CPU?

RE: Virtualization for Database Development

Grant Fritchey — Mon, 14 Apr 2008 05:21:10 GMT

Excellent feedback. Thank you for that. From everything we were told by the vendors, we should have been dancing through the streets like we were in "On the Town." Instead it all had a serious "Reservoir Dogs" feeling to it.We probably will be expanding more & more into virtual servers for things other than SQL Server. Instances act so much like a virtual machine and are so much easier to maintain, that's probably how we'll manage things there. For now at least. Who knows what technology changes may come down the pike.

RE: Virtualization for Database Development

K. Brian Kelley — Sun, 13 Apr 2008 01:14:00 GMT

I had a long conversation with our VMware SE (he's moved on to bigger and better things within VMware now) who I had a previous working relationship with long before he got to VMware. As a result, the discussion was quite candid. Lab Manager, speaking from a directory services administrator side (my primary job) is a nightmare to keep synchronized. The idea is great in concept: you have a nice little sandbox to play in that can't affect the outside world. It does this by effectively creating a snapshot in time. Meaning all changes in production have to be performed in the LabManager environment as well. That's duplicate work. And while I'm sure scripting could have been worked out using VMware's APIs and packages, um, no.In my current organization we treat VMs as physical servers, as another poster indicated. They are in with the "regular" servers. We do segment production and development into two forests, and there is some duplication of effort, but once a change is applied on the development side, it remains. Since Lab Manager is designed to be replicated and refreshed from a point in time over and over again, you have to look at applying changes every time you stand up the environment. So while there is duplication of effort with a separate forest, it is far less than going the Lab Manager route. In our case we use the development forest to test security models and then roll them forward to production, so we put up with the duplication of effort in order to facilitate the improvement in modeling we gain.

RE: Virtualization for Database Development

Grant Fritchey — Fri, 11 Apr 2008 07:56:39 GMT

Thanks. I'm glad it was helpful. I originally wrote the thing when it looked like we had a success. I then went back and added new information to portray the failure.

RE: Virtualization for Database Development

Ian Crandell — Fri, 11 Apr 2008 07:33:49 GMT

Grant - good article. The timing is ironic - last night at my local developer group, we were given presentations on virtual machines. They mostly talked about virtual PCs, so your information about the virtual servers built on what we talked about.Also, the comments provided by Mark Stacey matched very closely to what our presenters said about setting up the virtual machines. This has gotten me thinking of how I could possibly use this technology at work and at home (and what to look out for).Thanks,

RE: Virtualization for Database Development

GSquared — Wed, 09 Apr 2008 07:42:06 GMT

Good article. I've been looking at virtualization for dev and QA, and the article and discussion are definitely going to affect how I set this up.

RE: Virtualization for Database Development

Grant Fritchey — Wed, 09 Apr 2008 05:41:45 GMT

[quote][b]michaela (4/9/2008)[/b][hr]This looks like a bad experience. The trick with using VMs is to treat them - logically speaking - as real machines. Then the next question would be: why would you install sql server AND application on the same server? [/quote]We didn't install in the same machine. The system we were trying to implement involved having a set of virtual servers that could be created as a group. That way, the application server, the web server, the database server, would all be in a common, working state, synchronized like it was a working production environment. Then copies of that environment, all the servers, would be "deployed" for use by various development teams.[quote]And next: why would you integrate software changes in a development environment? If having real servers, wouldn't you use an integration environment with application and database servers? This would be the right environment to keep security under control because not any developer will deploy in this environment, but the DBA will do (database) deployments and db versioning. As a note, being a DBA, I talk only about the db side of deployments, but the actual work has to be done with a designed person that deploys application changes as a team.[/quote]The problem with saying that the developers shouldn't have an integrated environment is, what happens when they're developing code that relies on interaction from other sources? This was designed to support a Service Oriented Architecture (SOA) approach to development where each application provides a service to the others. Some apps absolutely required the other apps to be in place to work. So you're faced with one of two choices, each of which we've tried to work with before we tried this alternative. Option 1) all the developers work on the same environment, but then code changes in Service 'A' breaks Application 'B'. Option 2) Have Service 'A' deploy their code to Application 'B' servers when it's "ready" but then you see your developers constantly troubleshooting new deployments and integration inconsistencies instead of developing new code.[quote]And an important thing to make a DBA's life easier: do not bring developer VMs under domain; you cannot and shouldn't control any AD change.If you would need to re-create a SQL Server VM with same name as an old one then, again, treat this one as a real server, which means assign a static IP which, by the way, it's not necessary to be the same with the one for the old vm, rename the server ann then rename the sql server instance (if already installed) and finally, decommission the old VM.There are many other things to be said both pro or against using VMs for sql servers. Hope these lines would help someone in setting up VM environments.[/quote]Ah, but then you get all these servers communicating out into your general network instead of safe behind a "fence." Suffice to say, it didn't work out, but it was a very good idea.Also, the DBA's weren't responsible for administering all these servers. We were only part of the whole process & team. We didn't administer active directory or the application servers. We just worked with the databases & database servers as usual.

RE: Virtualization for Database Development

michaela — Wed, 09 Apr 2008 01:32:18 GMT

This looks like a bad experience. The trick with using VMs is to treat them - logically speaking - as real machines. Then the next question would be: why would you install sql server AND application on the same server? And next: why would you integrate software changes in a development environment? If having real servers, wouldn't you use an integration environment with application and database servers? This would be the right environment to keep security under control because not any developer will deploy in this environment, but the DBA will do (database) deployments and db versioning. As a note, being a DBA, I talk only about the db side of deployments, but the actual work has to be done with a designed person that deploys application changes as a team.And an important thing to make a DBA's life easier: do not bring developer VMs under domain; you cannot and shouldn't control any AD change.If you would need to re-create a SQL Server VM with same name as an old one then, again, treat this one as a real server, which means assign a static IP which, by the way, it's not necessary to be the same with the one for the old vm, rename the server ann then rename the sql server instance (if already installed) and finally, decommission the old VM.There are many other things to be said both pro or against using VMs for sql servers. Hope these lines would help someone in setting up VM environments.

RE: Virtualization for Database Development

Anipaul — Tue, 08 Apr 2008 23:48:34 GMT

My company is also considering to implement virtual servers. This input will really help us to consider. Thanks Grant Fritchey for sharing your inputs. Also special thanks to Mark Stacey for his valuable comments.:)

RE: Virtualization for Database Development

Grant Fritchey — Tue, 08 Apr 2008 11:48:28 GMT

Great info. If they relaunch the effort I'll definately use this stuff as a reference. Thanks.

RE: Virtualization for Database Development

Mark Stacey — Tue, 08 Apr 2008 11:26:21 GMT

We have worked with virtual servers extensively, both in the context of cloning entire environments, and in the context of insufficient machines (the live environment is 9 servers, and we have 5 machines in the QA lab, and we're not willing to re-use dev boxes as QA servers, THAT is a nightmare for deployment)In my experience, project managing deployments onto virtual servers is a very good way of pre-testing deployments onto live machines, mostly because you can save a base state in a way that you cannot do when uninstalling windows - in other words, you can have a clean windows, or windows + SQL, or whatever, install by just "shut down, copy, paste, startup"We were working MS Virtual Server and Virtual PC, not VMWare, only challenge we experienced is that the servers could not attach to the USB ports, which I understand VMWare can.Most of our development (lots and lots of sharepoint) happens on VPCs - or at least, sharepoint resides on a VPC, and Visual Studio on a local box, one place we've found performance problems.But have a look at this post from MSDN blogs about performance:http://www.hanselman.com/blog/VirtualPCTipsAndHardwareAssistedVirtualization.aspxhttps://www.microsoft.com/windowsserversystem/virtualserver/default.aspxhttp://blogs.msdn.com/virtual_pc_guy/archive/2006/03/14/550712.aspxVPC instances can be optimized a number of ways 1. Have a processor that supports hardware virtualization. It does matter! See the diagram below: 2. As you could see, there's an option in the VPC settings that allows hardware-assisted virtualization if your processor supports it. Unfortunately, in my case even my notebook PC does not support that. My processor configuration is as follows: According to Scott's blog, and I quote him: If you're running Intel, refer to this table at Intel to see if your chip supports VT. All the Core Duos support VT except the ones that end in "E" like the Intel Core Duo processor T2300E .3. In no particular order, you should check the Intel Virtualization Technology (VT) web site to find out what processor supports hardware-assisted virtualization, also known as "Virtualization Inside" (cool name). Also check out AMD virtualization technology. I do not take sides, as I have processors from both AMD and Intel. 4. My home PC has an AMD Athlon 64 X2 Dual-Core processor and it supports hardware-assisted virtualization. Whenever I am preparing my demo on a VPC image, I work from home (a valid reason to my boss that I have to work from home) 5. Memory allocation: In the above VPC instance, I had 1.6GB of memory allocated to my VPC instance. In my home PC, I allocate 2GB, or more if I do not run many applications in the background. Size does matter. 6. Do not run your VPC on the same hard disk as your host OS. Run your VPC on an external HDD that has a high RPM speed. Or if you PC has two separate physical HDDs, run and store your VPC on the HDD that is not where your host OS is installed. I am using an external Maxtor HDD with an external power source. Do not use those tiny 2.5" pocket HDD that draws power from another USB port. The power from another USB port is not enough to juice it up, besides most likely the RPM speed of the pocket HDD is low. 7. I compress the folder which contains my VPC image. The reason behind this is not just because I want to optimize my storage space, but even more importantly is the fact that I want to reduce the amount of disk reads from the HDD. It's better to let my CPU work hard rather than my HDD work hard. Everyone knows that the weakest link in terms of the performance of a PC is the HDD. 8. This is how I compress the folder, right-mouse click on the folder and choose Properties: 9. You should also compact your VPC image (I don't do this as often as I should though). Here's how: 10. If you are running your VPC on Windows Server 2003, for instance, use Microsoft Virtual Server 2005 R2 instead. If you want to read more about VPC optimization, you may check out the following blogs: Scott Hanselman The Virtual PC Guy

RE: Virtualization for Database Development

Grant Fritchey — Tue, 08 Apr 2008 11:17:23 GMT

It was basically functional. The key issues, apart from deployment issues & some performance, were around how we could manage the virtual versions of AD. It apparently wasn't surmountable.

RE: Virtualization for Database Development

Patrick-388961 — Tue, 08 Apr 2008 10:47:01 GMT

Grant,Sorry to hear of your experience with VMware. I recently helped delploy VMware in production. The company had nearly 50 physical SQL Servers, almost all use SQL Enterprise version and under utilized. Two big SQL2000 Servers were in a Active/passive Cluster.We elliminated the need for all Enterprise versions and clustering by employing VMware. We greatly reduced the amount of hardware and expensive liscensing of the Enterprise version (much was not really needed). VMware will spin up a new SQL box if a hardware failure occurs on another.Patrick.

RE: Virtualization for Database Development

Grant Fritchey — Tue, 08 Apr 2008 10:30:47 GMT

[quote][b]Yu Yang (4/8/2008)[/b][hr]What were the key factors make to choose virtualized OS (VMware) vs. virtualized SQL (instance)? VMware will introduce some perform overhead, as well as extra admin effort. On the other hand, if using SQL instance, to deploy a standard SQL instance could be a easy routine process.[/quote]The virtual systems represented complete application environments, not just databases and database servers. The trick was to keep all the servers integrated with a particular version of the functioning software including the database code and the servers, BizTalk, client code, etc. Talking about deploying any one database or creating new instances for deploying databases is easy. The hard part was coordinating that process with all the other servers. The parts that we got to work, worked well because everything, all code, moved together. Does that make it a bit more clear?

RE: Virtualization for Database Development

DBSlave — Tue, 08 Apr 2008 09:00:24 GMT

What were the key factors make to choose virtualized OS (VMware) vs. virtualized SQL (instance)? VMware will introduce some perform overhead, as well as extra admin effort. On the other hand, if using SQL instance, to deploy a standard SQL instance could be a easy routine process.

RE: Virtualization for Database Development

Pete T-366679 — Tue, 08 Apr 2008 07:34:09 GMT

Thank you for the interesting article! I'm just starting to try and get real virtualization going as a development environment and have run into similar issues, too. Since we're still "trying out" utilizing VMs to develop and test things, we also are working against a very limited budget. I.e., VMware Server (free), old retired server hardware (CPU, RAM, and disk storage definitely limit what all we can do), and then trying to get everyone on the same page. It would be nice if there was a better (free) way to manage everything, too....as we don't have the resources now to go with one of the relatively expensive management tools. Either way, it's a good learning experience and we're starting to see how powerful it can be. Thanks for the idea of versioning the VMs with source code software, too!

RE: Virtualization for Database Development

Grant Fritchey — Tue, 08 Apr 2008 05:57:50 GMT

Just for the virtualizations. We already do resets from production as a part of our QA testing. We were just hoping to incorporate a similar process into an automated development methodology. No joy.

RE: Virtualization for Database Development

ALZDBA — Tue, 08 Apr 2008 05:46:54 GMT

Did they pull the plug only for the virtualisation part or for the full ad-hoc regression test infrastructure(s) ?

RE: Virtualization for Database Development

Grant Fritchey — Tue, 08 Apr 2008 05:35:09 GMT

The thing is, I think we could have made it work. But the expectations were so unrealistic that while we had functionality, we didn't have perfection. Sans perfections, they pulled the plug. After a while I got some of the feeling that Cassandra must have had when dealing with the Greeks.

RE: Virtualization for Database Development

ALZDBA — Tue, 08 Apr 2008 01:49:30 GMT

Thank you for sharing your struggles ... Especially the fact that despite the warnings we give, people confirm them not to be a problem (again and again) until they are actually confronted with these warnings and then hell breaks out. I'm no longer feeling alone :w00t:

Virtualization for Database Development

Grant Fritchey — Mon, 07 Apr 2008 23:43:22 GMT

Comments posted to this topic are about the item [B]Virtualization for Database Development[/B]