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.

PDC09 Day 1 - SketchFlow

Today I attended a session on a product called SketchFlow, part of Expression Blend 3, which for us Microsoft Gold Partners is part of Expression Studio 3 on MSDN. SketchFlow is a tool that uses WPF and helps you quickly prototype, gather feedback, and document for projects that will use WPF or Silverlight as the UI. Prototyping, in general, helps you do design up front leading to more accurate estimations, share ideas quickly, get feedback quickly, and fail faster when you are going down the wrong path.

We recently used a tool from Balsamiq to produce mockups for a new project, which was very helpful in crystallizing design and gathering feedback from our client, as well as providing our UI developers with a starting point.

I’ve no doubt that SketchFlow is going to be our new prototyping tool – not only does it allow us to do all of the above, but it actually creates an application in WPF.

One of the great features of SketchFlow is that you are using real WPF and the prototype is a real application and not just screen shots. There all features built right in to allow folks to provide real feedback directly in the prototype, and ways for you to collect and review that feedback.

SketchFlow allows to use Sketch styles to give your prototype a “draft,” or work-in-progress, look and feel – this is actually important to get the right feedback from people at a higher level instead of letting people get bogged down in details like button size and color, etc.

In SketchFlow you create a high level map of the forms/windows that you are going to use in your application and then connect them together to create a flow. Then you can select any of those to mock up a particular form.

You can also quickly add sample data to your application by defining data and filling it in right from within the application, or by connecting it to a database. You can drag and drop to create lists using your sample data, and manipulate the controls for how the data is displayed right in SketchFlow. You can put C# code behind various actions and events in the prototype as well, to simulate various aspects of the application you are prototyping, including storing global state information to keep track of from one screen to another.

Once again SketchFlow uses WPF, so you can easily use it as the basis for a WPF application, and with some limitations you can use it for Silverlight as well. Although you can certainly use it to prototype other application types such as web sites, it will not generate ASP.NET etc. – the output is WPF or Silverlight only.

SketchFlow also has a cool feature to allow you to export your prototype to Microsoft Word, creating a table of contents and including all of the screen shots – this will certainly help create documentation much more quickly (we copied and pasted about 40 images in a recent specification document, this will automate that task in future specs for us in 1 click!).

After attending the SketchFlow session yesterday I immediately installed Expression Studio 3, and I am getting ready to use this with our designer for prototyping the front end of new project I have coming up.

PDC09 Day 1 – Data-tier Application projects in VS 2010

I made it back to PDC this year – although I wasn’t able to get in early to attend any preconference workshops. After getting up a 4am and catching a 6am flight to LAX, it was the LA traffic that got me in 45 minutes after the keynote started. I hear I didn’t miss too much, mostly just Ray Ozzie talking about cloud computing.

The first session that I attended was about a new project type in Visual Studio 2010 called a Data-tier Application. It doesn’t replace the existing Database Projects (e.g. with VS.NET 2008 Database Edition + GDR2) – coming out of the session, my understanding is that it is an alternative project type with some really cool features, but intended for “smaller” projects (“departmental” vs. “LOB/Mission Critical”, or “under 1000 objects”). I’m not quite sure what to make of that – based on some commentary I believe that it has a couple limitations but also the intended audience is different.

First off, here are some of the really cool features discussed. I imagine that some of these features apply to all SQL edited in VS.NET 2010 and not just this new project type:

• Intellisense when editing SQL in VS.NET
• Full integration with the VS.NET 2010 debugger – e.g. when debugging a stored procedure, you can add watches and view the call stack. I hope you can do more – look at a temp table for example, or see what the results of a query might be, but that wasn’t specifically discussed. In any case this is a big leap forward.
• “Compile” errors and warnings, for example, warning if you use “select *” in a view or stored procedure.

The following cool new features are definitely specific to the new project type only:

• Deployment from either VS.NET or SSMS using the “dacpac” output
• Migration to new schema during deployment without writing any migrations scripts – it looks at the old schema, the new schema, and “makes it so”! How cool is that?!
• When you deploy the dacpac using something called a Control Point, you can get a really cool dashboard in SQL Server that gives you usage statistics and other interesting information about your database

Sounds too good to be true?

Here are the “gotchas,” as far as I can tell, for the Data-tier Application project:

• It only works with SQL 2008 R2, which is still in CTP (not released officially yet)
• It is intended for “departmental” databases
• “up to 1000 objects” was mentioned
• only a subset of T-SQL is supported
• limitations to the ability to perform automatic migrations when changes are made
• other? not clear
• It is not yet released… I’m sure it’s leveraging the years of experience leading up to the GDR2 of the database edition, but that was a long and painful road – anyone who’s run into a bug in VSDB that blocked a deployment can relate to this fear

Bottom line: it sounds very promising with some great productivity features and general advancements in the managing database development, but when it comes to the new project types I would proceed with caution and have a backup plan (such as switching to the tried and true database projects).

Accessing a remote network via VPN on a Virtual Machine

I have Windows 7 64-bit which means I can't use the Cisco VPN Client (hey Cisco, if you are listening, please give us a 64-bit Windows 7 compatible version of your IPSec client!), even though I have several clients who's hardware requires it -- so I bought NCP’s Universal IPSec VPN Client as a replacement and it has worked really well. That is, until I needed to connect to a new client’s network using SonicWall. I couldn’t get NCP to work with the SonicWall, so I installed a Virtual Machine (I still use Virtual PC 2007 SP1 since Windows Virtual PC is still in beta and causes my HOST to crash with the BSOD) with Windows XP on my Windows 7 laptop. I installed the SonicWall client on there, and I am able to connect to their network from the VPC.

However, accessing my client’s network only from the virtual machine is inconvenient -- I can't use SQL Management Studio or VS.NET Database Edition to connect directly to a database on my client's network because all those tools are installed on my host machine, and I really don’t feel like reinstalling them all onto a bloated virtual guest.

I heard from a colleague about the possibility of routing traffic through the VPC guest machine from the host, but I had trouble finding a specific guide to doing this. The good news is that I finally got it working, and I am about to tell you how!

There are of course security considerations (you are essentially creating a path of connectivity between two networks, so malicious software may be able to spread, etc.), so please use your best judgment as to when and how to make use of this.

Here's what I did:

1. Install a Loopback Adapter on the Windows 7 host OS (Add Hardware or run HDWWIZ.EXE, select “manual”, “network interface”, “Microsoft Loopback Adapter”...)

2. Configure the loopback adapter to use a static IP 192.168.0.2 / 255.255.255.0 (leave everything else blank -- no gateway etc.)

3. With the XP guest OS shutdown, use VirtualPC to change the settings so that it has two Network Adapters: the Loopback Adapter that you just added and Shared Networking (or whatever other NIC you want to use for the XP VPC to connect to the Internet).

4. Boot up the XP guest OS. I'm assuming you've already installed the VPN software (SonicWall in my case). If not, install it and make sure it can connect.

5. Change the Loopback adapter on the XP VPC to a static IP 192.168.0.1 / 255.255.255.0 (leave everything else blank -- no gateway etc.). You may need to turn off the firewall on this NIC. Ping the host (192.168.0.2) from here, and vice versa, to make sure everything is working.

6. In Network Connections on the XP VPC, open Properties for the VPN's NIC and turn on Internet Connection Sharing. Set the "Home Networking Connection" the Loopback Adapter. This will force its IP to 192.168.0.1 which is why I chose 192.168.0.2 for the host machine. Use Settings to select the services that should pass through (Remote Desktop is on by default).

7. On the host (Windows 7 in my case), you need to add routes for the specific IP addresses in the VPN network that you want to access. I needed to get a machine at 192.168.8.88, which is the SQL Server on the network I am VPN’ing into. I created AddRoute.bat with the command "route add 192.168.8.88 MASK 255.255.255.255 192.168.0.1" and put a shortcut to that in my Quick Launch. Run that command (as an administrator).

Go to the XP virtual machine and connect to the VPN. Now go back to the Windows 7 host OS and you should be able to Ping the destination machine, in my case "ping 192.168.8.88". If this works, celebrate! Now you'll be able to Remote Desktop, connect to SQL Server, use File Sharing, etc.

There are some pros and cons to using this method. On the downside, you don’t have DNS and you do have to route to each IP address explicitly. On the plus side, you can get to a couple remote machines on one VPN and you could even VPN into another network (like your corporate headquarters) from the host OS at the same time.

One other thing to note -- before I tried the ICS method, I had enabled IP forwarding in the XP VPC by setting the IPEnableRouter registry setting (see http://technet.microsoft.com/en-us/library/cc962461.aspx). I was able to verify with Network Monitor that the requests were indeed re-broadcast to the VPN interface, but it was ignoring them - presumably because they came from another IP address. I didn't turn that setting back off, so I can't confirm that isn't required for all this to work.

Another note – I tried this with the Cisco VPN client on the guest OS and it did not work. I’m not sure why I could get it to work with SonicWall and not Cisco, so your mileage may vary. Please do comment if you have anything to add on this topic.

Originally I did all this with Vista 64-bit as my host. When I upgraded to Windows 7, it deleted my Loopback Adapter and I had to re-add it, but all the settings came back when I did so.

Finally, there may be better solutions out there. I read that with Windows Virtual PC you can, say, run the Cisco VPN Client from the XP virtual machine as a program from the host OS and use it to connect the host OS to a VPN. That’s a pretty cool workaround to a lame problem (Hey Cisco, how about adding 64-bit support to the IPSec VPN client?! Sure, I’d rather use SSL VPN, but I don’t have control over what my clients’ IT departments support!). On that note, I’ve gotten reports that the free Shrew Soft VPN Client works well under Vista and Windows 7 64-bit but I haven’t personally tried it yet. At one point I had a solution using vpnc on cygwin working on Vista, but it stopped working and I never got it going again.