Building (for FREE) a Virtual Server for SQL Demos

Did you see the news this week?  SQL 2012 has gone reached RTM!  

Have you downloaded a copy yet?  
If/when you do, where will you install it?  
Do you have an extra machine lying around that you can use?
If you’re like me, you don’t have.  Instead, you’ll spin up another virtual machine on your laptop or desktop, install windows 2008 server for the 100th time, then install SQL Server.  The power and flexibility offered by virtual machines is incredible, but it can also be incredibly tedious trying to organize and manage them all.
I recently had to rebuild my desktop, and decided to use that as an opportunity to start fresh with my VM collection.  What I had before was a loose assortment of VM’s, not really organized in any way.  I wanted to do things differently this time.  I found an excellent article by Jonathan Kehayias, documenting exactly what to do to build an entire VM lab.  At first, this seemed like what I wanted, but after spending some time following his steps, I decided that this is overkill for my needs.  I don’t need an entire lab, I just need an instance or two with which to build demos and maybe practice some skills for certification.  I took a step back and started over.
There is one nugget of particular value that I got from Jonathan’s article – the concept of cloning a virtual machine.  This is something I was not doing with my VM’s before.  The potential space savings is tremendous, as is the speed with which new VM’s can be created.
What follows is the process that I followed to create a reusable base image for a SQL Server 2008 R2 instance running on Windows 2008 R2 Core.  I couldn’t find the entire process documented from end-to-end, so I decided to do that here, perhaps someone will find it useful.
To start, you’ll need to download some software:

Download Virtualbox
Download Windows 2008 R2 ISO from MSDN or from here
Download SQL Server 2008 R2 ISO from MSDN or from here
Download Adventureworks2008R2 (I’m using the version without FILESTREAM data)

Install Virtualbox, I left all options at their defaults.  Click the “New” button to create a new virtual machine, the “Create New Virtual Machine” wizard will appear.  The screenshots below show the process and the options that I chose:

My host machine is a quad-core box, so I allowed my VM to use four processors.

I changed the network adapter to a “Bridged Adapter”, so that my virtual machine will be visible on my LAN just like any other machine.

The wizard will power on the new virtual machine, which launches the “First Run Wizard”.

When prompted for the installation media, provide the location of the Windows 2008 Server ISO file that was downloaded.

Click “Start” on this screen will boot the VM, using the Windows 2008 Server ISO and the boot medium.  This will launch the setup for Windows 2008 Server.

I chose to install the core version of Enterprise Edition. I don’t plan to need the GUI features of the operating system, and by using the core version, the patching requirements are greatly reduced, as is the overall footprint of the operating system.

Once the Windows install completes, the VM will reboot, and you’ll find yourself being prompted to login to the operating system.  Since I chose the “core” version, my instructions from here on are targeted to that version.  Instructions for the other version of Windows may differ.
There are certain pre-requisites required before SQL Server can be installed.  These are simple to install, using the following commands:
dism /online /enable-feature /featurename:NetFx2-ServerCore

dism /online /enable-feature /featurename:NetFx3-ServerCore
dism /online /enable-feature /featurename:NetFx2-ServerCore-WOW64
dism /online /enable-feature /featurename:NetFx3-ServerCore-WOW64
dism /online /enable-feature /featurename:IIS-WebServerRole
dism /online /enable-feature /featurename:IIS-ISAPIFilter
dism /online /enable-feature /featurename:IIS-ISAPIExtensions
dism /online /enable-feature /featurename:IIS-NetFxExtensibility
dism /online /enable-feature /featurename:IIS-ASPNET

In order to access SQL Server from outside the VM, you’ll need to allow connections through the Windows firewall, or disable it.  I chose to disable it entirely:

netsh advfirewall set allprofiles state off

We’ll be using this VM as the basis for other ones, so we’ll want to make sure this one is current on patches and updates. This is easily done through the configuration utility “sconfig”, included in Windows core:

You can choose to install all Windows updates, or just the recommended ones. I opted for the recommended ones. After the updates have installed, the VM will reboot once again.

After the VM reboots, login again. Unmount the Windows ISO (Devices -> CD/DVD Devices -> Remove disk from virtual drive). We now need to install the Guest Additions for Virtualbox – these are needed so that we can access files on the host operating system. Install the Guest Additions (Devices -> Install Guest Additions). If the installer doesn’t start, run it manually – on my VM, this was done by running D:\VBoxWindowsAdditions.exe.

Allow the VM to reboot again. Upon restart, you’ll be prompted to login again – there’s no need to login at this point.  You’re finished with the Windows install, and now have a clean virtual machine running Windows 2008 R2 core.  Shutdown the VM cleanly, we’re done with it.

 We can now use this clean Windows VM as the basis for any number of additional VM’s that we want to create.  Let’s do so now.  Right-click on the VM in the Virtualbox console, and choose Clone:

I named my new virtual machine “SQLDEMO”:

This step is key to significant disk space savings.  A “linked” clone will share elements of the disk image from which the clone was created.

Once the clone has been created, we can power it up.

You’ll be prompted to login to a Windows instance that looks remarkably like the first VM that we created. In fact, it’s “virtually” the same instance, identical in every way.

Let’s start by changing the computer name of this cloned VM. Run the sconfig utility, and choose the option to rename the computer.  Let’s use SQLDEMO for the computer name, to match the name of the VM. You’ll be prompted to reboot the VM, go ahead and do so.

We’re now going to install SQL Server. To do that, we need to mount the ISO that was downloaded earlier. Click on Devices -> CD/DVD Devices -> Choose a virtual CD/DVD file, and navigate to the ISO file.

I’m going to assume that you’re familiar with the installation procedure for SQL Server, and that you know which features you want. I’m not going to detail that installation process here. To being the installation, run the setup program (for me it was on drive D:):
D:\SETUP 

After the SQL Server installation has finished, the last thing to do is to attach a copy of AdventureWorks. You don’t have to do this, but for me it was important. I want to be able to build demos that other people can use, and AdventureWorks is something we all have access to.

We’ve downloaded AdventureWorks already, it’s sitting someplace on our host machine. We need to get access to that from within the VM, which is why we installed the Guest Additions.

My download of AdventureWorks is sitting in “C:\DVD_Images”. From within the VM, create a shared folder mapped to that location (Devices -> Shared Folders), name it DVD_Images.

Return to the Windows command prompt, change to the SQL Server data folder (The default path is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data).

Copy the AdventureWorks data file from the host location into the VM (copy \\vboxsrv\DVD_Images .). The name “vboxsrv” is an internal reference to the host machine.

Launch Management Studio (on the VM or on the host), login to your new virtual SQL Server instance, and attach the AdventureWorks data file:

CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_Data.mdf') -- change the drive and file path
FOR ATTACH_REBUILD_LOG;

Shut down the VM now, don’t make any further changes to it.

At this point, you have a clean SQL Server 2008 R2 instance, running on a clean Windows Server 2008 R2 Core machine. As with the original Windows VM, you can now clone this SQL Server VM to create any number of SQL Server instances that you’d like.

See how easy that was? Want to experiment with mirroring? Simple, clone this VM to create two new VM’s, change their names using sconfig, and experiment away. Want to practice upgrading from SQL 2008 to 2012? Create a clone and upgrade. Repeat as many times as necessary until you’re comfortable with the upgrade process. The uses for this cloning technique are endless.