Build Virtual Appliances: SQL Server 2005

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

With the advent of machine virtualization technologies such as VMware’s Virtual Infrastructure, Citrix XenServer, and Microsoft’s Hyper-V, IT pros are looking to new formats for server configurations. One of the most popular is the virtual appliance—the creation of a standard, configured virtual machine including specific applications. This article begins a series whose objective is to demonstrate how you can build your own virtual appliances based on installation and configuration best practices. Then, once the appliance is built, you can use and reuse it to your heart’s content.

Virtual machine technology is taking the IT industry by storm. Organizations everywhere are looking to virtualization technologies to improve infrastructure management strategies and ‘green’ their datacenter. There is no doubt that virtualization is here to stay with now the proliferation of new virtualization engines on the market today. But what does it really mean to you, the IT professional?

First and foremost, it means that your practices and procedures will change. As you know, a virtual machine (VM) is nothing but a set of files on a disk (see Figure 1). There are several advantages that derive from this feature:

• First, because the machines are nothing but files on a disk, you can move them from host to host. Just pause the VM, move the files from one host machine to another, and then launch it on the new host machine. Even better, configure your hosts with shared disks so that you don’t even have to move the files from one place to another.

• Second, since VMs are nothing but a set of files, provisioning servers has just become really simple: just create a VM and run the Windows Sysprep command against it. Then when you need a new machine, copy the original VM and open it to personalize it again. In most cases, you can do this in less than 20 minutes!

• Third, since VMs are self-contained and transportable, they can be delivered in a pre-configured state. This is the virtual appliance (VAP). Think about it. You may never need to install another product again; just get it in a VM and run it.

Obviously, this is not for the faint of heart. But, since virtual appliances for Microsoft products haven’t hit the market yet for certain products, you can and should build your own. Then, just reuse them wherever you need them. This way you only need to install your products once. You can build VAPs for a multitude of different products.

build a virtual appliance

Figure 1. The make-up of a virtual machine

Your Objective

To build your first virtual appliance, you’ll need to create a standard system build for Windows Server 2003 into a virtual machine. Hopefully, you’ll use the R2 version and run the appropriate edition to meet your needs. This particular procedure outlines how to build a VAP for SQL Server 2005. Use this shortcut procedure to do so.

Your Required Toolkit
You’ll need the following tools to perform your installation into the virtual appliance. Download them or locate the appropriate installation disks. Ideally, you would be using ISO CD images since they are easier to work with when working with virtual machines.

build a virtual appliance

Virtual Machine Preparation
Note: Microsoft virtual machine file extensions are used here, but this does not mean you need to use Microsoft Virtual Server. You can in fact, use any virtual machine technology. Microsoft extensions are only used as examples here.
In addition, a 32-bit installation is discussed here, but you can use this same procedure to create a 64-bit virtual appliance.

1. Begin by preparing a new virtual machine running Windows Server 2003 R2, Enterprise Edition. Make sure it is up to date in terms of software updates. You should also install Internet Explorer 7 since it is the latest version of the browser. In addition, this machine should have the following characteristics:

build a virtual appliance

2. Once the new disks have been made, attach them to the new virtual machines your virtual machine management console.

3. Launch SQLSource, personalize it and configure the network card.

4. Keep the machine in a workgroup in order to make it transportable from one environment to another.

5. Launch Computer Management and go to Disk Manager. Create the signature on the two new disks but do not convert them. Both disks should be Basic disks. Format both disks with NTFS and give them the following properties:

a. 100 GB Disk Name: Data

b. 100 GB Disk Letter: D

c. 25 GB Disk Name: Logs

d. 25 GB Disk Letter: E

6. Add two disks to the machine and create the D:SQLData and the E:SQLLogs folders.

7. If you will be installing SQL Server Reporting tools, then you must install Internet Information Services (IIS) on the server. Use the default settings and include ASP.NET.

SQL Server Installation and Configuration
Note: Obtain additional information on the SQL Server installation from Microsoft Knowledge Base article number 907284 at http://support.microsoft.com/kb/907284.

1. Install SQL Server Enterprise Edition with the following settings:

build a virtual appliance

Note: You use a default instance to keep the machine generic. This is also why you use the LocalSystem account as the service account. Using generic values will let you more easily replicate the machine as needed.

2. Once the installation is complete, add Service Pack 2 . Select all applicable modules; the required modules should already be selected. Apply the upgrade to all instances. Use your organizational settings for Error and Usage Reporting. Stop any process that is locking a file in order to avoid reboots. Monitor the Service Pack installation progress.

3. At the end of the process, Launch the Vista User Provisioning Tool. This will let you grant Vista users access to SQL Server and Analysis Server administration roles. Do this even if you don’t have Vista users yet. You never know when they will arrive and doing it will ensure your SQL virtual appliance is ready for anything. Add all existing users to all roles.

4. Reboot the server if you need to. SQL Server is ready for customization.

5. Launch the SQL Server Management Studio and connect to the local database instance.

6. Right-click on the name of the SQL Server (SQLSource) and select Properties. Configure the settings as follows:

build a virtual appliance

7. Click OK to close the dialog box and apply the settings.

8. The last step is to move the Temporary database so that it can profit from the data and log disks. Moving the Temporary database will increase performance on the server. To move this database, refer to the Moving the tempdb database section of Microsoft Knowledge Base article number 224071 at http://support.microsoft.com/kb/224071.

9. Close the SQL Server Management Studio. SQL Server is ready to run.

10. Use the Windows Update site to verify if the machine needs updating. The best way to do this is to use the shortcut in the Tools menu in Internet Explorer. Once updates have been applied, your configuration is complete.

Your virtual application is now ready. To use it, copy the files that make it up and run the Microsoft Sysprep command against the new copy. Sysprep can be found on the Windows Server 2003 Installation CD under SupportTools; it is contained within the Deploy.cab file. Once the copy is de-personalized, you can use it as a seed machine for any other virtual instance of SQL Server 2005 you need to deploy. That’s it. You may never need to install SQL Server 2005 ever again!

Note: For information on Microsoft product licensing in virtual machines, download the white paper at http://go.microsoft.com/?linkid=7327814 or go to the Microsoft Windows Server Virtualization Calculator at http://go.microsoft.com/?linkid=7327815.