SQL Server Reference Guide

I've reviewed several tools that claim to help you manage and control SQL Server databases. In this tutorial, I'll show you a product that can help you with the platform on which SQL Server runs.

SQL Server is intricately linked with the operating system. In fact, it's so closely tied to the OS that it can be difficult to test service packs, upgrades, and changes without making material changes to the operating system. Unfortunately, you can't make these kinds of changes on your production systems. On the other hand, you shouldn't implement a change that you haven't tested.

In most shops, this means that you have to use another system for change management. This is fine; but some changes, such as certain patches, are difficult (if not impossible) to roll back. This makes it difficult to maintain a consistent test environment.

In addition, you often have to maintain a test system for each operating system and service pack level you have in production. If your shop sells software, that can be a nightmare.

Enter the Hypervisor, or Virtual Machine software. Virtual Machines are best described as a software implementation of hardware.

How Virtual Machines Work

When a computer system is powered on, a program stored in Read Only Memory (ROM) is loaded. This ROM startup program is hard-coded and doesn't change. The ROM startup program performs a "Power On Self Test" (POST) to make sure the hardware is running properly, and discovers its hardware using the Basic Input and Output System (BIOS).

In the second stage the ROM startup program passes control on to the hard disk, where the first part of a software operating system is found, called the bootstrap loader. That in turn calls the rest of the operating system.

A Virtual Machine emulates the first stage of hardware. It installs a ROM and a BIOS, along with a few control programs. You install this software on your current operating system, such as Windows Linux (or even a Macintosh, though Microsoft is no longer maintaining that version). The Virtual Machine runs "inside" your operating system, and multitasks your current CPU, RAM, and storage subsystems to make another “computer” inside. It's like having two or more computers running at the same time on the same hardware.

The system you run the Virtual Machine on is called the "host" operating system and a virtual machine that you create on it is called a "guest."

From the other side of your computer — that is, on the network — the Virtual Machine looks like any other computer. In fact, from the network, you can't tell any difference between a Virtual Machine and a "real" one.

How Virtual Machines Help SQL Developers and Admins

Here's the useful part. Since you're emulating hardware in software, everything about the new "computer" you build is stored in a file. The drive letters, video, operating system, patch levels, applications, memory, everything is stored in a file. This means that you can have dozens of computers stored on your local drive. You can activate as many of them as you have resources for, and power them down by using software.

You can copy the files that comprise the Virtual Machine to another computer running Virtual PC and start it up there. Since the hardware is emulated, the machine to which you copy the file doesn't have to be anything like your system. The "hardware" goes with the file.

Another major advantage is that, since the entire environment is really software, when you "turn the system off" you're really controlling the state of a few files. This means that you can apply any changes you've made to the system, or more helpfully, not save those settings when you shut down the guest machine.

Here's a practical example. Let’s say that you’ve built a Virtual Machine on your system, and set it up to run Windows Server with the latest service packs. You install SQL Server Enterprise Edition, and patch it to a particular SQL Service Pack. When you finish, you "power down" the Virtual Machine. When you do, you're asked if you'd like to save the changes. You do.

When the next Service Pack for SQL Server is released, you're asked to test it to make sure your production applications continue to operate. You power up your Virtual PC and apply the service pack. You have the users perform several tests, some of which make changes to the application's test data that can't be undone. When the test is complete, you power off the Virtual PC, but this time you answer "no" to the question about saving the system. The next time you start the Virtual Machine it will still be at the previous Service Pack, without any of the user's changes.

You might also have several Virtual Machines, each with different operating systems, patch levels and data. Whenever you need a new machine to test something out, you can build it without purchasing new hardware.

There are a few of these Virtual Machine software packages out there, most notably VMWare, and Virtual PC. Years ago, Microsoft bought Virtual PC from Connectix and released it as Microsoft Virtual PC. The interesting thing is that both the VMWare and Microsoft products are free — I’ll show you where to download them in the references section below.

Creating a Virtual PC

You need a fairly decent box to run this software. You can get away with a few gigabytes free on your hard drive and 512MB of RAM, but I wouldn't advise trying to do anything substantial on it. The system on which I'm writing this has 4GBB of RAM and a 600GB hard drive. You should plan on around 16GB per Virtual Machine (VM) and as much RAM as you want to dedicate to each machine. I try to give each machine at least 512 MB, or 1GB if I can get it. The reason you need so many resources is that your host environment (your normal operating system) still needs to run, and each guest machine (the VM’s you create) are full-on computers — think about regular hardware and you’ll have the idea. If you “starve” the guest with too few resources, using virtualization technology will be painful.

The software installation is fairly straightforward. If you're using a larger drive as a secondary, as I am, you'll want to be sure to install your Virtual Machines there for performance reasons.

After you install the software (Next, Next, Finish) and start the program for the first time you'll be presented with a wizard that walks you through the creation your first Virtual Machine.

Once you select "Next" at the opening screen, you're given three options.

Figure 118

The first option creates a new machine; the second quickly creates a standard machine with default options; and the third uses previously created Virtual Machines from its file or files.

When you create a new VM, you provide several bits of information. You name the machine, and set its location. (On my system, that's another drive.)

Use long, truly-descriptive names, especially if you're building "template" machines. What I mean by that is you build a machine with a base configuration, such as Windows Server and SQL Server. Once you've got that software installed on your Virtual Machine, you can “freeze” it; that is, you don't make any more changes to it. Then, whenever you need a test system, you can select the third option during the Virtual Machine wizard, to use your "Windows Server/SQL Server" base configuration to make a new environment. When I do this, the first step I take (after starting up the machine) is to change the name of the server, giving me an entirely new environment without having to build it each time.

Figure 119

Whenever you create a new VM, you select the OS you expect to install on it. Notice the "Other" selection at the bottom; I use this option to install my Linux test boxes. You can also install other flavors of Unix or even Novell NetWare — if you can still find a copy of it.

For the purpose of this example, I selected Windows 2000 Server, since I need to test a older software setup for compatibility testing and don’t want to dedicate hardware to it.

In the next panel, you indicate whether the software should automatically select the "machine's" RAM allocation, or if you want to adjusting it manually. (You can always change the RAM setting later.)

The next panel sets the drives you'd like to use for the Virtual Machine. Since I haven't set one up yet on this system, I let the wizard create a new one for me. You're given an opportunity to name and place the files the system will use for this "hard drive” — which again is really just a file. I normally set the name to be the same as the Virtual Machine. Note that you can actually make several hard drives if you like.

After confirming these choices, the computer will whir and click for a bit, and then drop you back in the main menu where it shows the name of the just-created system.

Figure 120

Virtual PC lets you create as many virtual machines as you like (though only one is shown in this menu). Each is limited to one Processor and has memory limitations, but other than that you can run several at one time. I’ve had five going at once before the performance on my test system started to be unusable.

You can click on the "Start" button in this panel to power on the Virtual Machine.

Figure 121

This is where your inner geek starts to smile. Right inside a small window on your system a little computer BIOS displays, showing a new computer that's starting up. In a moment or two the "computer" prompts you for a bootable CD or floppy, as the "hard drive" it created has never been formatted.

If you have multiple CDs in your host system, as I do, you might have to click the CD menu item in the Virtual Machine's menu bar to set the device it looks for. Once you do, you simply pop the operating system’s bootable CD in the drive and the familiar setup routine begins. I’ve also used a “Network Boot” option to load a operating system, but that’s beyond the scope of this quick tutorial.

Figure 122

Installing an OS on your virtual machine is just like installing on a "real" computer, only a bit slower.

A word of caution here: this can be a really lengthy process. It can take a couple of hours to get a system installed, and that doesn't count all the patches and updates. This is another reason you might want to use those "template" systems I spoke about earlier.

To use the mouse inside the Virtual Machine, click once inside its window. To get the mouse back, click the rightmost ALT key once. To press CTRL-ALT-DEL, use the "Action" menu item in the Virtual Machine's menu bar or press CTRL-ALT-INSERT.

Once the process finishes, the system is ready for use — almost. Log in one time, and then select the "Install or Update Virtual Machine Extensions" right away. This simulates a CD being inserted into your Virtual Machine, and starts a routine that installs drivers for all the virtual devices on your Virtual Machine. This will fix your monitor, mouse and keyboard inside the Virtual PC, and allow you to move the mouse “outside” of the Virtual PC Image.

Figure 123

Now that you've done that, you're ready to patch. Visit the Windows Update site or grab your patching CDs to ensure your Virtual Machine is patched to the latest available level. While you're at it, install virus protection software. After all, this is a real computer, and it can be infected like one.

When you power down your Virtual Machine, you'll be asked if you wish to keep or discard your latest changes. This is one of the most useful features of a Virtual Machine, and allows you to make major changes to a test environment without permanently altering the machine in the process.

You can now use this machine whenever you need it. To change any of the VM settings, select the "Settings" button at the Virtual Machine console. These settings affect everything, from using the host's soundcard to the network configuration. You can, for instance, configure your VM to use the host's network card as a bridge, or merely as a DHCP server, effectively isolating the VMs to your system. You can add "hard drives," change the memory, and more.

So what's the bottom line on this software? If you do any development work requiring test servers, you need it. If you want to play with other operating systems, including Linux, you need it. If you operate a classroom environment that needs to be reset each class, you need it. If you offer product demonstrations requiring a dedicated server, you need it. In short, you probably need it.

I think Microsoft has done a great job in extending the Connectix offering. I've used VMWare for years and found it invaluable, but the Microsoft product is just as capable – and it’s free!

One final, but very important note is worth mentioning here. Although Virtual PC is free, the licenses for the operating systems you install are not, unless you’re using open-source software. If you install VPC and fire up 5 new servers, you have to license them all. Getting an MSDN subscription happens to get you five licenses of most Microsoft products for testing, so that’s always a good option. Just remember: good DBA’s and developers don’t steal software.