Migrating to Hyper-V from Virtual Server or Virtual PC

These instructions seem to work well for migrating guest machines from Virtual PC or Virtual Server 2005 to Hyper-V. As noted some steps may not be necessary, but this is comprehensive should you run into issues – e.g. migrating from Virtual PC to Hyper-V is not supported and machines that originated from Windows XP or Virtual PC might not work without the undocumented/unsupported steps to replace the HAL with an ACPI HAL.

1. BEFORE migrating to HYPER-V (optional but helpful)

a. Log into the machine as an Administrator (e.g. a domain admin) so that the password is cached to log in later when there is no network access available to validate against the domain. If you are using the local admin account you can skip this. If you did skip this step and you get a message when you log into the guest machine about not being able to contact the domain admin, then you will need a local (non-domain) admin account password to migrate the machine.

b. Make a note of network settings, such as TCP/IP settings for the existing network adapter(s) in the VM guest – these adapters will be deleted (or at least hidden) during the migration.

c. Uninstall VM Additions from the VM and shut it down properly.

2. Install HYPER-V

3. Copy guest VHD to the HYPER-V server – or backup the VHD to another location in case you need to get back to the original VHD if something goes wrong.

4. Then run HYPER-V Manager (hit Start and being typing HYPER):

a. If you don’t have Hyper-V Manager, then download it or get the Remote Server Admin Tools for Windows 7: http://go.microsoft.com/fwlink/?LinkId=131280

5. Connect to SFWVIRUTAL01 (Click HYPER-V Manager in the MMC and then Action or Right-Click … Connect to Server and enter the name of the server with Hyper-V.

6. Actions à New à Virtual Machine

7. Give it the same name as the old machine and then Next:

8. Set the network connect in the next window:

9. Then find the EXISTING VHD – do NOT create a new one:

10. Click Finish – HYPER-V will do some configuring and give you status…

11. Now select the new machine, click START then after it is running click CONNECT:

12. I had one unfortunate experience where I could not use the mouse in the new machine until I uninstalled the old Virtual Machine Additions. In this particular case, mastery of the keyboard commands in the Virtual Machine Connection window is essential. The following list of keyboard shortcuts will come in handy: http://blogs.msdn.com/b/virtual_pc_guy/archive/2008/01/14/virtual-machine-connection-key-combinations-with-hyper-v.aspx

13. Next steps are to log in several times to uninstall VM Additions (from Virtual Server 2005 or before) and then install the new HAL and then install Integration services / VM additions (Action -> Insert Integration Services Setup Disk; you may need to then manually run the CD from the guest machine after that) from HYPER-V which installs new hardware… several reboots…

a. Log in

b. Under some circumstances, you may get a message that Windows needs to be reactivated do to hardware changes. Do NOT do this YET – wait until after the process is complete (after the new HYPER-V integration has been installed):

c. Uninstall previous Virtual Machine Additions, if applicable (Control Panel, Add/Remove Programs, etc.):

d. Once the machine reboots, log in again and “insert the integration “

e. If you get this dreaded message, “Setup cannot upgrade the HAL in this virtual machine. Hyper-V integration services can be installed only on virtual machines with an ACPI-compatible HAL.”, then you need to perform extra unsupported steps.

f. Open Device Manager (e.g. Start, right-click Computer, Manager) and open Computer, then right-click Standard PC. If Uninstall shows up in the drop down menu, click it and then reboot and try installing integration services again. You may not have an Uninstall option, in which case you need to continue with an unsupported workaround:

g. Shutdown and then Turn off the new virtual machine.

h. MAKE SURE YOU HAVE A BACKUP OF THE VHD BEFORE CONTINUING!

i. Get access to a Windows XP installation CD e.g. with i386. E.g. mount the ISO with something like VirtualCloneDrive.

j. In the host server, mount the VHD as a drive. (To mount a VHD, right click Disk Management and Attach the VHD, do NOT select Read-Only): http://thelazyadmin.com/blogs/thelazyadmin/archive/2009/01/15/mount-a-vhd-within-windows-7-server-2008-r2.aspx)

k. Basically, you are going to replace the HAL.DLL on the Guest A’s VHD with the ACPI version of the HAL.DLL on the Windows XP image:

i. Open a Command Prompt as Administrator.

ii. The following assumes the VHD is mounted as I: and a Windows XP CD is mounted as G:
C:\>I:
I:\>cd windows\system32
I:\WINDOWS\system32>rename HAL.DLL HAL.DLL.BACKUP
I:\WINDOWS\system32>expand G:\i386\HALACPI.DL_ .\HAL.DLL
Microsoft (R) File Expansion Utility Version 5.1.2600.0
Copyright (C) Microsoft Corp 1990-1999. All rights reserved.
Expanding g:\i386\halacpi.dl_ to .\hal.dll.
g:\i386\halacpi.dl_: 40176 bytes expanded to 81280 bytes, 102% increase.
I:\WINDOWS\system32>

l. Now Detach the VHD (right-click on the DISK (not the drive letter) in Disk Management and select Detach VHD, do NOT select the option to delete the VHD afterwards).

m. Unmount the Windows XP image (optional).

n. Start the virtual machine again

o. Log in, and skip through any screens asking to activate windows; click yes to attempt to install any new hardware but ignore any messages about not being able to install it. If asked to reboot, say NO:

p. Open Device Manager again and Uninstall the Standard PC.

If prompted, confirm that you want to remove device Standard PC. Reboot if prompted.

14. Let the integration services / VM Additions for HYPER-V install, reboot, log in again, and reboot again as necessary.

a. You should receive a message that the HAL needs to be upgraded:

b. Click OK to continue. HYPER-V Installation will upgrade the HAL an d then prompt you that a Restart is required. Click to restart.

c. Log back in; and if prompted, do not activate windows yet.

d. HYPER-V Integration Services installation will automatically continue (if not, insert the setup disk again) and begin installing the Windows Driver Framework, Guest Components, etc. You will need to Restart again to complete the installation.

e. Log in again. If Windows wants to re-activate, this is the time to do it.

15. The original network adapter(s) have probably been deleted (or at least hidden because the device(s) are not there). You may need to manually configure the new network adapter(s) for any static IP settings etc. Note also that the MAC address is probably different than before in case you are using DHCP reservations. You may want to reset or check firewall settings as well.

Don’t Repeat the FETCH Statement with SQL Server Cursors

If you are anything like me, then you are always trying to avoid repeating the same code whenever possible. One thing that has always bugged me is how all of the T-SQL code examples for using cursors in SQL Server use the FETCH statement right after opening the cursor and then repeat the exact same FETCH statement again inside the WHILE loop itself. Using those examples, if you ever need to update the list of columns you are fetching then you have to edit the code in two places. Here I provide you with an alternative -- there is another way!

In order to do this, I take a slightly different approach. I create a WHILE loop where the test condition is always true, FETCH, and then I BREAK out of the WHILE loop if the @@FETCH_STATUS was not zero. In the code sample below, see the highlighted sections of the before and after code:

-- Two FETCH statements, straight from MSDN...
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2008R2.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
   BEGIN
      -- do something here
      FETCH NEXT FROM Employee_Cursor;
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

-- Only one FETCH statement!
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2008R2.HumanResources.Employee;
OPEN Employee_Cursor;
WHILE 1=1
   BEGIN
      FETCH NEXT FROM Employee_Cursor;
      IF @@FETCH_STATUS != 0 BREAK
      -- do something here
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

 

Note that the WHILE condition is “1=1”; this is simply an expression that always evaluates to true. One is always equal to one -- T-SQL does not have a “true” value like C#. You could use another expression, as long it always evaluates to true.

Just don’t forget to check the FETCH_STATUS and BREAK out of the loop! This code is more succinct than the examples that use the FETCH statement twice (especially if it is a large FETCH statement) and I use it many production systems without any issues. Now you only have to have one FETCH statement when you need to use cursors in your T-SQL code. Enjoy!

Missing the Hidden Boot Process in Windows Virtual PC? SHIFT-ESC!

Windows Virtual PC sure has some nifty improvements over Virtual PC 2007. Take USB support, for example. Without it, I would have had to get a new scanner at home, since the only drivers available for my scanner are for XP. Now, instead of getting a new scanner, I just have to explain to my spouse the complex process of why when she scans something and saves it to the desktop it’s not really on a desktop she can see, but it’s on another desktop hidden behind the scenes… hmm. Maybe I should have just bought a new scanner. I digress.

Now let’s get to the meat of this post. If you are a power user of Virtual PC like I am, you are going to love and hate Windows Virtual PC. First off, there is no longer a Virtual PC “application” per say where you can list your virtual PCs and their current running states and configure them. Instead, this is all integrated into Explorer. Unless it’s broken like it was on my machine… if that is the case then see here: Fixing a missing “Create Virtual Machine” option.

Well almost right away I ran into some issues because I was trying to boot a virtual machine from a CD (using an ISO) and I needed to see the boot menu, before Windows started up. I had some fancy new dialog box telling me that the virtual machine was starting, even though clearly it was not:

So I did the logical thing – hit F1 for Help – but I couldn’t find anything useful. I tried to search for documentation for Windows Virtual PC on Microsoft’s web site, but all I could really find was high level marketing mumbo jumbo about great Windows Virtual PC is why I am going to simply LOVE XP Mode. This wasn’t very helpful. I even scoured through the Virtual PC Guy’s blogs and MSDN forum postings to no avail. Then finally I found an obscure post somewhere with the magic bullet!

Press SHIFT+ESC to get from the “Starting the virtual machine…” dialog to the REAL virtual machine display behind the scenes. Now fast forward from a few months ago to this week, when I was tackling a similar problem, and I couldn’t remember the key combination! I rolled up my sleeves and searched again for a complete list of keyboard shortcuts, etc. I even went Googling (er, Binging?) and STILL couldn’t find it. I sat on it for a couple days, hoping it would come to me in a dream, and I scoured the Internet one last time. And finally, once again, I found a hint somewhere in a comment buried deep in an arbitrary blog posting. Woo hoo!

This time I decided to dedicate an ENTIRE blog posting to Windows Virtual PC SHIFT-ESC just so I would never forget it again, or least if I did, I would know where to look ;-)

This can help you with all kinds of trouble. For example, let’s say you accidentally had a bootable CD in your CD drive and you went to startup XP Mode for the first time, and XP mode failed to setup after a half hour or so and you didn’t know why. You could use SHIFT-ESC to see the boot screen (and maybe even get into the BIOS of the virtual machine) and find out that the new XP Mode virtual machine was booting from your CD and not from Windows!

Another use might be that you want to run some Linux utilities on your virtual machine – perhaps CloneZilla, ntfsresize, GPartEd, or any other number of useful goodies out there. Well if Windows thinks your virtual machine is a Windows machine, its going to hang on that dialog box thinking that Windows should be starting any minute now, while the whole time there is some command prompt from Linux awaiting your every input. Or maybe Windows keeps starting and you need to adjust the BIOS on the virtual machine to let you boot from your fancy Linux tools ISO. SHIFT-ESC to the rescue! Just be sure to hit it really fast after the dialog pops up. Then have a finger ready to hit DEL to get into the virtual BIOS.

Just for fun I hit SHIFT-ESC (should I use a dash or a plus between those two?) while booting a Windows XP virtual machine:

… and while booting a Windows 7 virtual machine:

 

Interestingly, although you can also hit SHIFT-ESC when you are shutting down, I just seem to get a black screen on the virtual machine instead of the “Windows is shutting down” message. Also you have to be pretty quick the with the SHIFT-ESC if you want to get into the BIOS.

Now why isn’t this documented anywhere? And better yet, what other goodies are buried away in Windows Virtual PC on Windows 7? If anyone from the product team is reading this, PLEASE improve the documentation, and keep us old Virtual PC 2007 power users in mind, not just the new Windows 7 XP Mode target market.

A Poor Man’s Object Search in SQL Server

Have you ever wanted to find all of the stored procedures, functions, maybe even views in SQL Server where you reference a particular column? Or maybe you are planning to change a table and you want find all of the T-SQL code in your database that references that table directly? Or maybe you just want to find all of the objects where you placed a comment like “-- TODO” into you code. Over the years this is something has come up so often for me that I have memorized the SQL query to make it happen:

select
    distinct object_schema_name(id), object_name(id)
  from sys.syscomments c
  where text like '%SomeText%'

Note that there are some pretty important limitations, so if these results are life and death, then you may very well want to use a robust solution instead. You see, the syscomments table breaks up the text of your objects into 8000 character chunks – so if you happen to be so unlucky that your text is broken across that 8000 character boundary and ends up in the two different syscomments records, then you will miss some results. But for a quick and dirty search, this works much of the time.

If you need to be 100% sure you found everything, though, there are some alternatives to consider. You could reverse engineer your database into a Visual Studio Database Edition (VSDB) Database Project – that will bring in the full text of every single object into Visual Studio, where not only can you search, but you can search using options like “whole word” or "match case”. Even better, you can use the Schema Explorer and refactor with built in tools that are smart to parse all of your SQL and really just rename that column you want to rename and not any other columns with a similar name – much more precise than search and replace.

Another alternative would be to use SQL Server Management Studio’s Generate Scripts command to script out all of the objects in your database and then search the output for your text. Depending on how big your object are, though, you might find yourself doing a lot of scrolling around to try and make a list of the objects that reference your search text.

If you are simply looking for dependencies, you could always run some queries on sysdepends. Of course the problem there is that in any real database that wasn’t just created from a script in the perfect order, there may be many missing entries. For example, let’s say that View B depends on Table A. Then you drop and re-create Table A for some odd reason. Unless you drop and re-create View B, View B still works but there is no longer an entry in sysdepends saying that View B depends on View A.

What’s your favorite way to search for some text across objects in a database? Leave a comment and let me know!

SQL Azure Pros and Cons

A friend of mine is working at a start-up and they are using Azure to host their website and database. I’ve been discussing ways that they can manage the database development cycle specifically as it relates to SQL Azure.

For traditional SQL Server development, my preferred development process leverages Visual Studio Database Edition. We generally only use RTM products for client development, so I have been using Visual Studio 2008 Database Edition with SQL Server 2008 Database Projects for some time now. I am looking forward to seeing what new features are in Visual Studio 2010.

Generally, members of my development team have their own development databases where they use whatever tools they prefer – SQL Server Management Studio, Visual Studio, etc., to make schema changes and add or update stored procedures, functions, views, etc. Then they use the Schema Compare functionality to sync those changes back to our project and then to source control, where other members of the team can get to them and we can keep version history, validate the builds before deploying, and either deploy new instances or use schema compare to help create migration scripts for existing databases. These tools are fantastic, and although there is definitely a learning curve for the team, I highly recommend using them.

So my friend was asking me what I recommend for their development process for the database against SQL Azure. They are in dev/QA right now and are expecting to start getting real data into the SQL Azure database that they are going want to persist, which means moving from a simple “drop the entire database and then run the big create script” to a managed approach where copies of the deployed database can be migrated to the latest version.

In this scenario, here is generally what I would want to do in an ideal world:

1. Take a backup/snapshot of the SQL Azure database that is deployed.
2. Restore that backup/snapshot to another location – it could be in the cloud or could be local.
3. Run a Schema Compare from the Database Project to the restored snapshot and create a migration script.
4. Test the migration script against the restored snapshot and verify that everything went as expected.
5. Take the web site offline (so there are no more changes to the database) and take another snapshot of the SQL Azure DB just in case.
6. Update the deployed SQL Azure database with the migration script, and take another snapshot post-migration.
7. Bring up the new website (without getting into detail, assume you deploy the new code to Staging, test it, then swap it into Production).

This would all be great, but there are several gaps in the toolset for SQL Azure:

1. VSDB does not support database projects for SQL Azure (see this thread). Apparently, neither does VS 2010 (yet – I hope they are planning this). The workaround here would be to create you DB project for SQL 2008. Then you have to run any scripts you generate through tools / update them to be SQL Azure compliant. The SQL Azure Migration Wizard can help you do this, but I still consider this all a big hack that is going to take time away from raw development tasks.
2. SQL Azure does not have a mechanism to take a snapshot or a backup. This is a HUGE shortcoming. I wouldn’t consider putting a production database up in SQL Azure without the ability to take snapshots or backups. I wouldn’t be comfortable running a migration script against it if I didn’t have a backup and if I haven’t been able to test that script against the real database. Fortunately, this feature is in the works. You won’t be able to restore a backup to a local machine, though, at least that doesn’t appear to be planned. See this thread on MSDN. Also, the SQL Migration Wizard can help you copy schema and data, but that’s no replacement for a real backup mechanism.

Sure there are ways to get around all this. You can use “Generate Scripts” in SSMS for SQL 2008 R2 from a SQL Azure database, and you can use BCP. But none of that replaces a real backup. You have make sure nothing is changing the database – e.g. what if you get inconsistent data which causes problems on the other end do to changing data, foreign keys, etc.? I’d rather have a real backup. Also, once I have the scripts and data I need to restore them to a local database somehow through this manual process and then sync that back to my project / source control system. Some folks out there have written some great tools to help do this, and even RedGate has some SQL Azure tools in beta – but here is what I would like to see:

1. Backup/restore support in SQL Azure with the ability to restore a backup to another SQL Azure instance
2. Staging/Production support in SQL Azure, similar to how you do it with Azure web sites.
3. Ability to take a backup (snapshot) from SQL Azure and restore it to a local development environment.
4. A “SQL Azure” mode in a local SQL Server environment that has all of the same rules/restrictions as SQL Azure to simulate SQL Azure locally
5. Full support for a SQL Azure Database Project in Visual Studio 2008 Database Edition as well as the equivalents in VS 2010 (Database Projects AND Data Tier Projects).

I’m sure we’ll see all or most of this eventually – until then we will have lower productivity and we will have to rely on hacks and community / 3rd party tools.

Big SQL Server Log File and Recovery Mode

Occasionally I will be working on a project where we have some database operations that delete/update/insert large amounts of data by design. Every now and then we get caught by surprise when one of our development or QA servers runs low on disk space, and then we discovery that the log files for the databases are HUGE.

In most cases it turns out that we had the databases set to FULL RECOVERY MODE. You can find many articles about this on the Internet and on MSDN, but the bottom line is that for our purposes SIMPLE recovery mode is more than adequate and it will greatly reduce the size of these log files. Full recovery would allow us to roll the database back to point in time – generally we don’t need this, especially on our development and QA databases.

I have some cool backup/restore scripts and we simply added an ALTER DATABASE statement to end of the restore script to make sure that the database is in SIMPLE recovery mode. Then I got to thinking, why not just create a one-liner that will set all of the user databases on a given server to SIMPLE recovery mode? Well – here it is. Enjoy!

EXECUTE sp_msforeachdb 'IF ''?'' NOT IN (''tempdb'', ''master'', ''model'', ''msdb'') exec(''ALTER DATABASE [?] SET RECOVERY SIMPLE'')'

In case you are wondering why the ALTER above is in an EXEC, apparently SQL server will parser the ALTER statement and stop on tempdb without even executing the script, so even though tempdb would be excluded in the IF statement, SQL still sees the ALTER code and doesn’t like it. Turning this into a dynamic query avoids the problem.