SQL 2005 Upgrade to SQL 2008

I recently upgraded a CRM Test Server from SQL 2005 to SQL 2008.  The first step in the upgrade process is to run the SQL Upgrade Advisor and address any issues that require attention.  Don’t skip this step as it is very important and will help to ensure a successful upgrade.  I ran the Advisor and came across just a couple of warnings about SQL 2000 DTS packages.  My guess is they were left over from the CRM 1.2 days so I didn’t think they required any attention as we no longer use them.  Since all other items tested successfully, I moved forward with the upgrade.
The upgrade is fairly easy, just walk through the wizard and pay attention to the information.  Microsoft did a nice job with this upgrade package.  The upgrade took a little less than an hour to complete and when it was done I rebooted the server.  After the reboot, I checked the event viewer to see if there were any issues and when I found none, I tested the CRM Web site.  No problems so far, CRM came up without issue so I began testing a few items. 
Features Checked and Working
  • Customization file export, import and publish
  • System Jobs were running
  • I was able to make entity changes and publish
  • Created and saved new records
  • Able to run workflows

Features not Working

  • Reports
  • Knowledge Base full text search was not working 

 The was no event viewer error recorded for the Knowledge Base issue, just the friendly, ‘an error has occurred, see your system admin….’ error when I tried to search the Knowledge Base using full text search.  All other search types worked fine (Subject, Article Number, etc.)

The report error did log an event and it was the one I don’t generally like to see: "Error: Client found response content type of ‘text/html; charset=utf-8’, but expected ‘text/xml’".  When I see that error, I usually know there are issues related to folders, files, datasource used for the report or paths.

Fortunately, I was able to resolve the KB full search issue by following the instructions in this KB Article.  The KB issue was fairly easy to address by starting the service, adding the user to the Group as explained and rebuilding the catalog.  After I completed those steps, I was able to use the KB full text search.

The Report Server issue was a bit trickier.  I followed the instruction in the KB Article above but that did not correct the issue for me so I did some additional research and found this great article with a significant amount of information for Troubleshooting Dynamics CRM reports.  I previously tried installing the SRSS Data Connector but that didn’t fix the issue so I uninstalled it and configured the datasource to use Microsoft SQL Server rather than the connection type of Microsoft CRM Data Extension. 

The next thing I tried was disabling the organization in the Deployment Manager because I noticed that the Report Server URL did not have the port number in the string (not sure how that happened).  I disabled the Organization and edited the string for the report server to include the port number then selected the Next button.  When I finished the wizard, CRM worked through the update process and in the end returned an error:

Event Type: Error
Event Source: MSCRMReporting
Event Category: None
Event ID: 19969
Date:  10/27/2009
Time:  10:46:48 AM
User:  N/A
Computer: ServerName
Web service request SetItemDataSources to Report Server http://CRMSQLServerName:5555/reportserver/ReportService2005.asmx failed with SoapException. Error: The data source ‘MS Sql’ cannot be found.

I ignored the error above and enabled the Organization.  As expected, the Reports still did not work, but there was a new Report Folder on the Report Server with the new set of reports created as a result of updating the URL string for the report server.  My hope was that this change corrected any issues related to the previous report folder so I installed the SRSS Connector and did an iisreset and that fixed the issue.

I hope this information proves useful for someone.  Correcting the reporting issue was challenging but I learned a lot through the process and found some really great online articles with a wealth of troubleshooting information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s