Voice of the DBA

Category: Blog

Cloning Virtual Box Images in Windows 7

I use VirtualBox from Sun to do SQL testing since it supports 64-bit guest images, which I want need for some things, like Windows 2008 R2, which only comes in x64.

However I hate reinstalling stuff that I don’t need to. And with a multitude of SQL versions, and a question today on eval edition (which I never install), I wanted to be sure I could quickly setup a new image when needed. In older VM software, I’ve just copied a VHD disk image and then added a new VM with it. However it’s not as simple with Virtual Box.

I found a nice procedure from Stuart’s Notes on cloning and copying images, so I started there, but found a few differences on my machine, so here’s what I did.

Found my Virtual Box files, which are located at: C:\Users\Steve\.VirtualBox\HardDisks (note, this is different than Stuart’s procedure. This is where things were installed on my Windows 7 x64 machine. I don’t have a “VDI” folder.

In Windows, you can right click this in the Explorer and get the path. I then pasted that with a “CD” in front of it in a command prompt.

Next I grabbed the path to the Virtual Box installation, since I need to run the “vboxmanage.exe” program. For me, the path was “C:\Program Files\Sun\VirtualBox”

At this point, I essentially needed to run

vboxmanage clonevdi “Old Disk" "new disk"

That, however, doesn’t really work easily in Windows. Instead, you need pathing, quotes, and other things to ensure that you get a copy. I had to run this:

C:\Users\Steve\.VirtualBox\HardDisks>"C:\Program Files\Sun\VirtualBox\vboxmanage
" clonevdi "Win7 64.vdi" "Win7 SQL 2K.vdi"

This completed, with these results:

From there, I fired up Virtual Box, and created a new machine. I named it picked the old OS, and then got to the Virtual Disk dialog:

I clicked the button that has the arrow in the image. This brings up the disk selection dialog. I clicked “Add”

From there, the next step was to select my new disk image:

I picked it, then closed the dialog and I saw this image listed for my VM.

Complete the wizard, click start, and I had a clone of my virtual machine, same state, guest additions installed, and same pwd. I changed the background for my guest and then installed the software I needed on this one.

September 10, 2010
T-SQL – Is Saving Space Worth It?

I ran across this blog post on Joe Celko’s Restaurant Seat Assignment Problem that found a way to implement the smallest amount of storage for a classic problem. It’s more of a challenge than a real solution.
Or at least I hope so.
The solution actually uses some bitmasking to save space and cram more information into a single field. It’s creative, and interesting to look through, but is it the type of solution that you might want to use in your actual code?
Personally I like seeing some elegant solutions in code, but not so esoteric or obfuscated that the average developer can’t understand it. Once something gets too complex, it can require more time to support and understand than it might be worth.
Not that we shouldn’t be educating or improving the skills of other developers in your company, but if there is something that is an order of magnitude more compelx than most other code, I think that a meeting, a brown bag, or even a blog post to explain it is warranted.
Back to the title of the post. Is saving space worth it? At times it is. I don’t advocate using INT for all fields when a tinyint or smallint will do, but I’m not sure that I find bitmasking to be worth it. In general, I think that creates more confusion than it solves problems or increases performance.

September 9, 2010
Common SQL Server Mistakes – GUID as a Clustered PK
I haven’t been thrilled with GUIDs as primary keys, mainly because I think that it’s hard for humans to work with GUIDs. A GUID, or uniqueidentifier, looks like this:
ECB6ECB4-ACCB-4382-84D1-19990D59CA2F
Not exactly something I want to try and type or include in a query. Cut and paste works, but it’s cumbersome. Much easier for me to work with integers.
I understand that GUIDs have some good advantages. They can reduce round trips, allowing the client to build a primary key and send it to the server. That’s a nice performance trick, and one I’d encourage.
The real issue, however, is when you make a GUID a primary key on your table, using the defaults. Most people use the defaults, and that’s typically OK. However in this case the defaults cause a problem.
The default setting for a primary key is a clustered index. For an integer, especially with the identity property, this is OK. All new rows are added to the end of the index, in new space allocations. This creates a hot spot for heavy insertions, but SQL Server handles those OK.
For a GUID, if I create new rows, I get values like this. These are three new GUIDs I created on my local instance.
ECB6ECB4-ACCB-4382-84D1-19990D59CA2F
3406A5AE-A963-48A6-B2FC-03197DC72478
C5D75C4F-D9EA-4355-A025-2FCC541D6E1E
If you examine these values, you’ll see that they appear to be random. That’s OK, and it can be a good thing. But for inserting new values, that means that item 3 would be inserted before item 1, and that can cause page splits.
Page splits are bad for performance. Data has to be moved to a new page, so not only are you inserting xx amount of data onto a page, you might be moving yyy data to a new page. It’s entirely possible that yyy > xx, which could be really bad.
There are a number of more technical explanations in the references below, but there really is a penalty there. This is in addition to the extra space (16 bytes v 4 bytes for an int). That’s less of an issue, but it’s still an issue.
The other thing is that all this page splitting creates fragmentation. So not only are your inserts slower, but potentially your read queries are also slower.

What can you do?

I think that the first thing you ought to do is read some of the articles below, and consider if you really want to use a GUID as a PK. If you do this…

then do this:

That will at least minimize some of the performance issues that you might have.
The other thing you can do on the server, if you are generating the keys with SQL Server, you can use NewSequentialID, which should generate sequential GUIDs, in the same manner that the identity property builds sequential numbers. There are some potential issues, so don’t assume these will always be sequential, especially if you generate some on .NET, but this is better than a clustered index on a GUID.
Be careful when using defaults, and if you use GUIDs, make sure that it is a good choice for you.

References:

A few posts from around the web on the issues of GUIDs as clustered primary keys.
September 8, 2010
Virtual Labs – A Great Resource for SQL Server

I had someone send me a note recently asking some questions about how to get set up to work with SQL Server. This was a person that had used SQL Server in the past, but had become a manager and then lost their job. So they wanted to start working with SQL Server and get a new job, however they didn’t have a server or many resources.
My first recommendation is that you grab the SQL Server developer edition for US$50. You can get it from Microsoft, Amazon, or many other places, but this is essential. It gives you a good basic point from which to start and test features.
However if you don’t have a spare machine, or you don’t want to put SQL Server on what you have for some reason, you have another option.
TechNet Virtual Labs
There are a whole variety of labs available, including a series on SQL Server 2008 and other versions. These allow you to RDP to a virtual instance of SQL Server and actually practice working on things.
There are other labs for Windows, Exchange, etc. You can spend time working on these technologies, either guided or unguided, and get some hands on practice. You can’t necessarily save your work, but this is a great way for you to get started on some technology that you want to add to your skillset and resume.

September 7, 2010