Wade Wegner

The SQL Azure Migration Wizard will now migrate your data!

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Last month I blogged about the SQL Azure Migration Wizard created by George Huey.  This tool helps you to migrate your SQL Server database into SQL Azure and is available up on Codeplex.  To date, this tool has been downloaded almost a thousand times!

The number one request for the migration wizard was the ability to replicate your data up to SQL Azure (in addition to the SQL schema, which it already does).  Unfortunately, the original SQL Azure CTP didn’t support BCP, and the data migration process was very difficult and required an SSIS package to copy the data into SQL Azure.

With yesterday’s release of SQL Azure CTP 2, however, SQL Azure now supports BCP!

Minutes after CTP 2 went live, George published a new version of migration wizard that takes advantage of BCP to enable you to migrate not only your SQL Server 2005 / 2008 database objects but your data as well!  migration wizard allows you to turn on or off data migration via the application configuration file or during runtime via the options page.  When you select data migration, the export and import process can be quite lengthy.  migration wizard kicks off the export and import process on a back ground thread and will display the BCP results in the program window.  At any time during the process, you can hit the cancel button to cancel the background process.  As migration wizard processes information, it will display the results to the program window.

Here are the release notes for version 1.0 (and 1.1):

• Added data migration via BCP. Note that when you specify your SQL Azure username, specify your user name as "username@server". Also note that data migration only works on the latest release of SQL Azure (Server Location: South Central US).
• Modified App.Config to allow you to specify your Options. For example: If you do not want to migrate data, you can turn this off by modifying the App.Config file and changing ScriptData to false.
• Added a cancel button so that you can cancel while processing.
• Added a scroll toggle so that during processing you can keep the control from scrolling down to the bottom.
• Fixed an error in BCP command to allow the passing of the SQL Server instance name.
• Added color to the SQL results to better identify error messages.

If you’re using SQL Azure, go and grab the new version of the SQL Azure Migration Wizard!  For detailed instructions on how to use the wizard, take a look at the SQL Azure Migration Wizard whitepaper.

Installing Windows From a Bootable USB Drive

I find myself doing this over and over again, so I figured it would be worthwhile to post.  Below you’ll find a really useful way for installing a Windows O/S from a bootable USB device.  I am particularly dependent on USB drives, as I have a Lenovo X61 Tablet that doesn’t have a CD/DVD-ROM (unless I’m docked, but I’m hardly in the office).

A couple of notes:

• This assumes you’re running Windows Vista or Windows Server 2008
• I’ve tested by installing the following O/S’s: Windows XP SP3, Windows Vista, and Windows Server 2008
• You must have a machine capable of booting from a USB drive

Without further ado, here are the steps:

1. Open an elevated command prompt.
2. You must make your USB drive bootable.  Type the following in the command prompt:
   
   diskpart
   list disk (FIND YOUR USB DISK)
   select disk 1 (OR WHATEVER YOUR DISK NUMBER IS)
   clean
   create partition primary
   select partition 1
   active
   format fs=NTFS
   assign
   exit
   
3. Mount your Windows Server ISO (or unpack it) and copy the contents of the CD/DVD onto your USB.  Make sure you get all files, including hidden ones.

You should now be able to use this USB drive to install a new O/S.  Good luck!

Microsoft Hotfix Request Web Submission Form

Lately it seems that I have had to acquire a lot of hotfixes for Microsoft products.  Unfortunately, most of the KB articles don’t provide a link to the hotfix, but instead ask you to contact Microsoft Support (via phone) to acquire the hotfix.  Kind of a pain!

Fortunately, my friend Rich Finn just passed along this useful link:

Hotfix Request Web Submission Form

Very useful … no longer will I have to reach out an contact individual escalation engineers I’ve worked with in the past. <sheepish grin>

"Warning: the CID values for both test machines are the same"

I came across a frustrating interesting problem today where the BizTalk Server 2006 Configuration wizard would fail every time I applied a new configuration.

Unlike a typical BizTalk developer environment (which usually consists of BizTalk and SQL Server on the same machine), this environment consisted of two separate machines: one BizTalk Server 2006 and one SQL Server 2005 (i.e. the BizTalk databases are stored on the SQL Server).  Additionally, this is a virtual environment and both machines were cloned from the same base Windows Server 2003 R2 template.

I was installing the following components …

• Enterprise Single Sign-On (SSO)
  
• Group
  
• BizTalk Runtime
  
• MSMQT

It would successfully install ENTSSO, but would fail when installing the group.  The log file reported the following error:

Failed to configure with error message [Exception of type 'System.EnterpriseServices.TransactionProxyException' was thrown.]

The following Google search suggested to me that  the underlying problem was with MSDTC (aren’t all BizTalk problems?).  I checked, and double-checked, the MSDTC properties on both servers and couldn’t find anything wrong with the configuration.  So, I had to pull out the big guns.

I downloaded DTCPing (a very handy tool for debugging DTC issues) and ran it on both machines (make sure to read the instructions on how to use DTCPing as it is not straightforward).  In the generated log file I noticed the following warning:

WARNING: the CID values for both test machines are the same while this problem won’t stop DTCping test, MSDTC will fail for this …

A Google search on this warning helped me to understand that the underlying problem is that the CID values stored for MSDTC were not changed during the cloning process.  But of course!

If you’re experiencing this problem, check the following registry key on both of your machines.  Are the keys identical?

HKEY_CLASSES_ROOT\CID

Mine were.  Here’s the steps I took successfully reinstall MSDTC so that the CID values were unique.  Run this procedure on both machines:

1. Use Add Windows Components, and remove Network DTC.
   
2. Go to the command line and run: MSDTC -uninstall
   
3. Go to the registry and delete the MSDTC keys in HKLM/Software/Microsoft/Software/MSDTC, HKLM/System/CurrentControlSet/Services/MSDTC, and HKEY_CLASSES_ROOT\CID (if they’re still there).
   
4. Reboot
   
5. Go to the command line and run: MSDTC -install
   
6. Use Add Windows Components, and add Network DTC.
   
7. Go to the command line and run: net start msdtc

After running this on both servers I was able to confirm that the CID values were unique.  And, sure enough, when I next applied my configuration to BizTalk Server 2006 everything worked perfectly.

I hope this helps!

Commerce Server Staging error: Operation must use an updateable query

I encountered an error today while setting up a project within the Commerce Server Staging.  I haven’t encountered it before, which leads me to believe that I must have done something abnormal on my development virtual machine.

Commerce Server Staging (CSS) is a service that allows you to synchronize content and data between many separate environments. Utilizing CSS, you can reliably update both Web content and business data from the source to one or more destinations.

Using the CSS MMC, I created a simple project that stages my StarterSite content from my local directory (e.g. C:\Inetpub\wwwroot\StarterSite) to a temporary folder (e.g. C:\Temp\Destination).  It was meant to be the start of a proof-of-concept for a more complex network topology.  However, I almost immediately received an error when I attempted to start replication:

I just love errors like this!  Gives me something to research and figure out …

Fortunately, more valuable information was added to the application log:

Event Type:    Error
Event Source:    Commerce Server Staging
Event Category:    None
Event ID:    61208
Computer:    CS2007
Description:
Error occurred with the database StagingLog.mdb.  Error is: System.Data.OleDb.OleDbException: Operation must use an updateable query.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at Microsoft.CommerceServer.Staging.Internal.ProjectDeploymentLog.ModifyLog(String projectName, String status).

While this isn’t the most explicit error, it provided enough information to track down the problem.

You might be surprised to see that this error refers to an Access database.  CSS makes use of Access database files to store events and information it audits during the staging process.  Specifically, there are these two Access database files:

• StagingLog.mdb
  
  
  
  • Found here: C:\Program Files\Microsoft Commerce Server 2007\Staging\Data
    
  • Stores internal replication information used by CSS
  
  
• events.mdb
  
  
  
  • Found here: C:\Program Files\Microsoft Commerce Server 2007\Staging\Events
    
  • Stores staging information that is made available to reports

Searching on the keywords “mdb operation must use an updateable query” led me to KB article 175168.  This article indicated that the problem is most likely caused by a user not having the proper permissions to open the StagingLog.mdb file.

In comes File Monitor (aka FileMon), by SysInternals, to the rescue.  Seriously folks, save yourself the agony and get to know the tools available from Microsoft and SysInternals.

I was able to capture the following “Access Denied” message via File Monitor:

Solution: Turns out that the NT AUTHORITY\NETWORK SERVICE has been attempting to open the StagingLog.mdb file to no avail.  After giving the NT AUTHORITY\NETWORK SERVICE the rights to Modify the StagingLog.mdb file …

… I am now able to successfully start my CSS project and stage my StarterSite data to a separate folder.

UPDATE: Rather than only giving access to the StagingLog.mdb file, give the NETWORK SERVICE account access to the entire Data folder where the MDB file is located.  Otherwise it will have issues trying to write out an LDF (Access log file) file when updates are made.

Hopefully this not only helps someone resolve this particular problem, but also shows how useful it is to equip yourself with good debugging tools!

Good luck!