Friday, August 10, 2007

Building a small Virtual PC with windows xp and SQL Server 2005 Developer edition

I love Virtual PC 2007, but I hate how every VPC image I used so far is at least 5GB and easily grows to 10GB. It is hard to keep multiple images active on your machine. So I started looking for a way to reduce the size of an image.

About the only usefull resource I found was the site of http://www.bold-fortune.com. This website contains the log of a guy that deletes windows files one by one and tests his system for stability.

My goal was to end up with the smallest possible Windows XP installation with the SQL Server 2005 service installed (and running).

Here is how I got there:

  • Created a Virtual PC with Virtual PC 2007 with 128Mb memory enabled.
  • Installed Windows XP SP2 and switched off as many of the optional stuff as I could.
  • Set the swap file size to a fixed 128MB
  • Installed virtual pc additions.
  • Disabled the Windows Security center
  • Disabled windows file protection (following the method described on the bold-fortune site)
  • Installed the SQL Server service with the following components: Database Server, Shared components, Replication support.
  • In The surface configuration wizard enable remote connections over tcp/ip.
  • Disable all protocols except tcp/ip in the server configuration manager.
  • Followed the deletion steps as described by bold-fortune except "WMI" and "Logical Disk manager". (also left the file drvmain.sdb)
    Between every delete I recycled SQL Server to see if I could still connect remotely and run queries afterwards.

When a in my view important service or component was deleted I restarted the vpc to see if everything was still in working order. (I check in event viewer for errors.)

Another small fix I did was running the following in a cmd.exe window:

net config server /autodisconnect:-1
This fixes most of the "MRxSmb the redirector failed to determine the connection type on the loopback connection"

When all this was done I switched on compression on the complete c-drive of the vpc (make sure sql server is running because it does not when the system databases are compressed). Then used the Whitney Defrag utility to defragment the drive. This defragmenter is extremely nice on virtual pc's because it has a tiny installation footprint and it moves all the clusters to the start of the disk.

When all is ready it is time to start compacting the Virtual PC. Start this process by capturing the ISO file "C:\Program Files\Microsoft Virtual PC\Virtual Machine Additions\Virtual Disk Precompactor.iso". This will automatically start zeroing out all the free space of the virtual hard disk.

Once the virtual hard disk is pre-compacted, shut down your Virtual Machine and compact the virtual hard disk by using the "Virtual Disk Wizard".

This (lenghty) process delivered me a Virtual PC of 826 MB and with compression on a footprint of 550 MB on disk which enables me to keep several virtual machines around. I do realise that a vpc with so little memory is slow and not usable for any serious testing, but it enables me to comfortably experiment with clustering and replication.

You can download the batch files i created to shrink my vpc images but be carefull. If you run these files on your normal pc, they WILL delete a LOT of files and certain software will NOT WORK anymore. So please be carefull. And I do not guarantee that these batch files will work perfectly for you. For me they do exactly what I need and leave me with a good usable VPC image for testing.

So If you want to try the same thing I did, you can download the batch files and start deleting windows.

Monday, August 6, 2007

What to do when you want to drop a FileGroup

I was working on a system to partition tables on SQL Server 2005 which means I was creating database filegroups and database files and I needed to drop my newly created objects regularly to retest my code. But sometimes it was not possible to drop the filegroups with complaints that the filegroup was not empty. After a lot of googling I found that it is possible that statistics are defined on the filegroup (most likely automatic stats). To see this you can run the following query.

select object_name(id) AS TableName, * 
from dbo.sysindexes 
where groupid = object_id('<yourfilegroup>')
After dropping the statistics I could drop the filegroup without problems.