Our company has had virtual servers in place for quite some time. We've used them for testing new operating systems or software, hot fixes, service packs and the like. For a long time our development teams have been stepping on each others toes, attempting to develop software in a shared environment. We've decided to implement virtual servers in order to seperate the various development teams into their own isolated environments. From a DBA perspective, this has introduced a number of interesting challenges ranging from simple connectivity to complex process issues around deployment.
I'm going to attempt to document the issues that we've encountered and how we overcame them, or didn't. To a degree this may read like a series of blog entries. I'm trying to describe the events in the order they occurred, not simply lay out a problem statement and a solution, skipping over the fact that the problem statement evolved over time and the solution was a horror show of trial and error followed by resets and reexaminations as well as a few recriminations. In other words, I'm going to include the sloppy parts of the process.
In the Beginning
Let's start off with the good news. Once a virtual environment is configured, creating a new server, getting SQL Server installed and configured is a piece of cake. It's no different than any other SQL Server install. Seriously. The virtual server environment defines itself as a Windows Server, the SQL Server install routine recognizes this. All the virtual disks and virtual disk space and virtual memory appear to be the real thing once you're working within the virtual environment. So the install came off without a hitch..
Once installed, an application under development was chosen for use in a pilot program. We had to begin deploying databases. This is where things got interesting, quick. We're using VMWare's LabManager. It's a great tool. It has this concept called a fence. It allows you to expose the virtual environment to the outside through an IP address and a port number. You have to either use this through the connection windows in the various tools or you need to set up an alias in the SQL Server Configuration Manager. This is a very small hurdle, but it was our first management concern. A given instance of an environment will only exist for a short period of time, approximately 8-12 weeks on average. After deleting an environment and creating a new one, it might have the same IP address or a different one. The individual dba's will have to clean out the aliases in Configuration Manager periodically or they'll end up connecting to the wrong server. This issue will also affect developers writing and testing stored procedures on the virtual SQL Server.
Our first deployments went well. We use Visual Studio Team System 2005 Database Edition for most of our new development. Once the alias was set, deploying from here went very well. No issues. The problems came up around the definitions of what we were to deploy. Our applications have a great number of dependencies on other applications and the services those applications supply and, of course, the variouis databases that feed those services. While we deployed the app we intended to test, we didn't deploy all it's dependent systems, their databases, and systems they were dependent on. Now began the long period of extreme fun where we'd deploy the three or four things we knew about in order to get one or two steps into the application so that we could find the next system or the correct version of a given system that we needed. Finally we started running into some of our older code and older systems that we haven't yet moved into the VSDB deployment process. Here we found ourselves running backups and restores from different locations in an attempt to find the correct version of the database and then running differential scripts to update this version with newer bits of code that have been released to QA. As deployments go, it was a debacle. Beginning to end, it was well over a week to complete although the actual labor involved from the DBA team was only about two days. The rest was taken up by testing and documentation.
Believe it or not, that was declared a success. We were then asked to go to phase II, outlining the implementation methodologies for all the other development teams.
A New Process
The biggest issue that we were running into was a combination of defining the dependencies for a particular system, followed by the dependencies that it's dependencies had, etc. One DBA would be required to deploy all those systems. We only wanted one DBA because of the need to make each development team independent of the others. We have a dba assigned to a development team, sometimes committed full time to a single team, sometimes one person shared amongst several teams. That DBA would be responsible for deploying all the databases because we didn't want every virtual server reset to be an all hands on deck operation requiring every DBA on the team to drop what they were doing each time any individual team needed a deployment.
We began exploring methods that would allow a team to operate on their own AND have all their dependencies in place. Finally, one of the lead developers came up with the idea of treating our virtual environments like source code. Using this approach, we create a common environment that all the teams can check out (create a copy). We'll ensure that this environment functions prior to allowing any team to have it through a series of automated application tests maintained by the QA team. When a given project team has completed enough functionality that they want to put it into testing, they'll then do a build to the common environment. Once the build is done, they have to re-run the full suite of QA tests to verify that they didn't break anything. Upon successful completion of these tests, they'll check that environment back in so that it is available for other teams.
If another team needs the new version of the code, they can now make a copy for themselves. As any given team completes an iteration, they can perform the build and test on their own schedule. This narrows the focus for integration to a single team at a time. The approach solved a lot of the problems we were encountering. It correctly put the responsibility for seeing that the code functioned on the team that was introducing changes. This also makes it so that instead of 2 to 5 DBA's (as well as all the developers for multiple teams) involved in a single build, only one DBA, the one responsible for the database(s) for that project, is performing all the work.
We've started trying to bring teams online, slowly...
Never Mind
Every time we tried to bring a team in, we ran into two identical sets of problems. First, the security was always a horror show. We never could keep the user permissions synced properly between our real environment and the sets of virtual servers that defined the fenced off development environments. I'm not an Active Directory expert. I'm not even an AD beginner, so I don't know the details of everything that they tried, but suffice to say the amount of work to try to keep these environments synced with our ever changing regular environment was pretty intense. Second, performance was always an issue. No mattere how many times we prepped dev teams with "you'll probably see about a 20% decrease in speed" they were always horrified to see a 20% decrease in speed.
Finally, one day, in a meeting that I was not involved in, all this was discussed and the whole project, despite the weeks and weeks of work and planning and the near flawless new process, was scrapped.
I'm using a virtual server for testing SQL Server 2008. I've also got a little set of networked virtual servers with their own little AD network, not synchronized with our production network, that I'm using for evaluating new monitoring software to replace our current tool set. I know the server teams are still using virutals for testing patches. The whole idea of using virtuals to create ad hoc development environments is currently off the table.
