Upgrading from Microsoft Dynamics CRM 4.0 to CRM 2011 Installation Failures

Update: I fixed the problem and you can see the short answer version at the bottom of this post
We have an existing Microsoft Dyanmics CRM 4.0 implementation and I decided to see if I could upgrade it to 2011 without too much hassle (the answer was no), and I ran in to several problems that I couldn't find very much information about.  Because our CRM 4.0 solution is set up on a Windows Server 2003 machine I had to build a new server with Windows 2008 R2 and SQL Server 2008 R2 to support Microsoft Dynamics CRM 2011.  In order to avoid breaking our production system I took a backup of our existing CRM implementation, copied it to the new server, restored it under the same name, and then did all of my import testing there.  Please note that when you import a database it makes tons of modifications to it making the old implementation useless, so it's smart to do all of your testing off of a copy of your production database.

Problems:
1) I didn't read the full requirements list to find out SQL Express wasn't supported, so when I saw it on the ISO I assumed that it would work.  Think again, CRM requires a full version of SQL.  Why is the express version on the ISO if it's not supported?  Seriously?

Time Lost: 20 Minutes to install and then uninstall it.

2) After getting over that hurdle I opened the CRM splash.exe launcher.  I clicked install on "Install Microsoft Dynamics CRM  Server" and it crashed like so:

I did some searching through the ISO and found the Server\amd64\SetupServer.exe file and ran that.  From some google searches it looks like having IE9 installed on your system causes this to happen, with no explanation or solution other than to go to the setup files directly.  Irritating.  I did confirm this theory by uninstalling Internet Explorer 9 from the View Installed Updates of the Add Remove Programs section in the control panel, and then the splash screen worked as it should.

Time Lost: 10 minutes to find each application and the SRS Extensions executables to run by hand.  

10 more minutes to uninstall IE9 to verify that was the problem for the purposes of this post.

3) In order to upgrade my existing installation without breaking it I copied the CRM database to my 2011 server and imported that.  10 minutes in to the upgrade process it fails with this error: "Column names in each table must be unique. 

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Column names in each table must be unique. Column name 'IsIntegrationUser' in table 'SystemUserBase' is specified more than once.

This is a confusing error because the column does exist in the database once so I'm curious what the upgrade process was attempting to do when it caused this problem.  I looked at the SystemUserBase table in the CRM 2011 database and it looks fairly similar to the 4.0 database but with a few more columns.  Searching around I found very little, there was one post saying it may be a case sensitivity issue but the column is named exactly as it should be so that isn't it.  

I decided to rename the column to IsIntegrationUserOld because the column was set to 0 for every user.  I ran the import again and this time I got a new error:

System.Exception: Action Microsoft.Crm.Tools.Admin.UpgradeDatabaseAction failed. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: There is already an object named 'DF_SystemUserBase_IsIntegrationUser' in the database.

Okay so that didn't work, I deleted my import, restored my database again and this time I simply deleted the IsIntegrationUser column.  10 minutes later it failed again, but with a new error!:
 Error| Exception occured during Microsoft.Crm.Tools.Admin.OrganizationUpgrader: Action Microsoft.Crm.Tools.Admin.UpgradeDatabaseAction failed. InnerException: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: An item with the same key has already been added..


A suggestion on the CRM Forums in this post was that I shouldn't have this field, so on my production system I should mark it as a custom field then remove it in the Customizability section.  I used this command to do so:
Update attribute set IsCustomField = 1 where name = 'IsIntegrationUser'  
Then I went to this screen and removed the IsIntegrationUser attribute (I took this screenshot after removing it, so you won't see it in my list):

I did this and got a generic error.  I checked the Event Viewer for more detail and it didn't give me anything, so I went to discover how to enable logging.  Turns out you need to use Tracing, so I enabled tracing by updating my registry entry to look like this with help from the KB article here http://support.microsoft.com/kb/907490:

With tracing enabled I performed the delete again, got the error message, and found this error in the log file:
ALTER TABLE SystemUserExtensionBase DROP COLUMN IsIntegrationUser Exception: System.Data.SqlClient.SqlException: ALTER TABLE DROP COLUMN failed because column 'IsIntegrationUser' does not exist in table 'SystemUserExtensionBase'.

So I added this value (IsIntegrationUser,bit) to the table SystemUserExtensionBase thinking maybe if it exists it will let me delete it cleanly, and.... SUCCESS!!!!  I got past this error.  Now I made a new backup of the database, copied it to my CRM 2011 server, imported it in to SQL, and attempted to import the 4.0 implementation in to 2011.

I ended up getting the same error as before:
System.Data.SqlClient.SqlException: Column names in each table must be unique. Column name 'IsIntegrationUser' in table 'SystemUserBase' is specified more than once.

In theory now the IsIntegrationUser record should be deleted, but the column still exists in the SystemUserBase table.  I removed the column from the table and re-ran the import.  It made it past this step and everything seems to be good.

Time Lost: 2 Days through Trial and Error and Troubleshooting (every attempt took 10 to 20 minutes)

Final Solution:

1. On the CRM 4.0 Server
1. Update attribute set IsCustomField = 1 where name = 'IsIntegrationUser'  
2. Modify the SystemUserExtensionBase table and add a column for IsIntegrationUser with type bit.
3. Navigate to Settings -> Customization > User (Double click) -> Attributes  -> and delete the IsIntegrationUser value
4. Now go to the SystemUserBase table and delete the IsIntegrationUser column if it still exists.
5. Backup the CRM Database from SQL Management Studio
2. On the CRM 2011 Server
1. Restore the 4.0 CRM Database to SQL
2. Navigate to Organizations, and import the old CRM Database