A Complex Upgrade

I had the opportunity this past weekend to do an upgrade that challenged just about every skillset that can possibly be required during a CRM implementation. It took 14 hours, and involved changes, installation or configuration of: Small Business Server, DNS, Active Directory, SQL Server, Windows Server components, database restores, IIS, Reporting Services, registry hacks, virtual servers, Office service packs, and possible several other things I'm not recalling right now. For those of you who are masochists, here's a lengthy narrative to describe the process:

CRM 3.0 SBE was on a virtual server running Small Business Server 2003 – R1, not R2, so it was using SQL 2000. CRM 4.0 requires SQL 2005 SP2, so we could not do a straight upgrade in place on the SBS box. Rather than upgrade Small Business Server to SBS 2003 R2, since they had already installed a new SQL 2005 server, we backed up the CRM databases and restored them to the new SQL 2005 box – which, by the way, was also a new virtual machine.

We then re-configured the existing CRM 3.0 installation to use the new SQL 2005 databases, and tested that everything was working. One thing I did not do – though I thought about it at the time, and ruled it out for the sake of time – was to move the SQL Reporting Services databases to the new SQL 2005 server and upgrade SQL Reporting Services for the existing CRM 3.0 installation. In the end, I might have saved more time by having the SSRS reporting function pre-migrated to SQL 2005.

So we restored the CRM databases to SQL 2005, and then we were ready to start an in-place upgrade to CRM 4.0 right on the SBS box. We had to add SP2 to SQL 2005 before upgrading, and we found out that the IT partner had pretty much given us a raw server for CRM, so we had to install IIS and ASP.NET support, as well as indexing and message queuing, and full-text indexing for SQL. While we were at it, we went ahead and installed SQL Reporting Services on the SQL 2005 server to get it ready for our final step, which was to install CRM 4.0 a second time on the new CRM server and import the organization that had just been upgraded on the SBS box.

We had to add a registry entry to disable loopback checking to get the Reporting Services website to work, and of course run the configuration wizard for Reporting Services. Additionally, there are a couple of custom add-ins that I had developed for 3.0 which required some tweaking and migration of their own in order to make them available to the new 4.0 installation.

At this point, everything seemed to be functioning correctly, but as soon as we powered down the SBS virtual server, reports stopped functioning from the new CRM 4/SQL 2005 environment. My suspicion was that some of the Active Directory security roles that had been controlled by the SBS box had not replicated to the new Primary Domain Controller that was replacing the SBS box. So after trying to manually configure these unsuccessfully, we brought the SBS box back online to see if our suspicions were correct. We also installed the SQL Reporting Services Connector for CRM, but unfortunately, we couldn’t get reports working again, and we’re going to have to wait until the SBS box is completely decommissioned, with the FSMO roles, DNS and Active Directory fully migrated to the new Primary Domain Controller.

We installed the Email Router for CRM and configured the Incoming and Outgoing rules. This is still a highly un-intuitive process, and after a few minutes of trial and error, the tests of the Email Router configuration returned successful results.

Another glitch we ran into was when we were trying to install the Outlook client the first time. Actually, the installation of the Outlook client went fine, but when running the configuration wizard, after entering the new address of CRM, we kept getting errors that the Outlook add-in couldn’t resolve the CRM address (the error message suggested that maybe the CRM server was shut down). Turns out these error messages were misleading. I initially thought there was some confusion due to using the same port number for the new CRM website as for the original one, so I changed the port numbers IIS was using, and still had no luck. After some digging, we found that the configuration wizard was in fact resolving correctly to the new CRM server, it just wasn’t getting the right message back from the database because there were still some lingering entries in the DeploymentProperties table that had the SBS server name in them. Here’s the SQL script (thanks to the CRM newsgroups) to update the database to make sure it contains the right server:port entries if you have remnants from a previous install:

UPDATE dbo.DeploymentProperties SET NVarCharColumn = 'newcrm:5555' WHERE
ColumnName IN ('ADWebApplicationRootDomain', 'AsyncSdkRootDomain',
'ADSdkRootDomain');
UPDATE dbo.DeploymentProperties SET NVarCharColumn =
'HTTP' WHERE
ColumnName IN ('ADRootDomainScheme');

After manually updating the database to get rid of these remnants of the old SBS installation, we were able to successfully configure the Outlook add-in.

So, between virtual machines, an upgrade, a re-deployment (or, more precisely, a reinstallation of CRM that pointed to existing databases that had been upgraded both to SQL 2005 and CRM 4.0, and an import of the CRM Organization), and various and sundry updates, patches, hotfixes and setting configurations, CRM 4.0 is up and running. Performance is great, with sub-second response times and form loads, and I’m confident we’ll get the reports working once the SBS box is totally out of the mix.

UPDATE: I got the SQL Reporting Services website working. Once the SBS box was removed from the network and all the FSMO roles and DNS had been migrated to the new domain controller, I was able to add the correct security permissions to the SQL Reporting Services website.

What was strange was that the installation/upgrade added the folder to the SSRS website for CRM 4.0. (In CRM 3.0, there was a flat hierarchy in the SSRS website - you had a folder called MyOrganization_MSCRM, and inside were all the RDL files and data source settings for the CRM reports. In 4.0, when you upgrade from 3.0, you get a new folder under this called simply "4.0" and all the new reports are under this folder.) I couldn't see the 4.0 folder at first when I browsed to http://sqlreports/reports, so I tried adding it. That's when I got an error message saying this folder already existed. Even though I had domain admin permissions. If you click the "Show Details" button you should see the 4.0 folder, but I didn't.

I checked another CRM installation's SSRS website, and copied the URL that takes you directly to the 4.0 reports folder: http://sqlreports/Reports/Pages/Folder.aspx?ItemPath=%2fMyOrganization_MSCRM%2f4.0&ViewMode=Detail and it took me to the folder, where, oddly, the folder had been checked to remain hidden in the folder view. I removed this checkmark, and then went to the security settings for the folder and added the Active Directory groups for CRM report users, as well as the NT Authority\NETWORK SERVICE account, and voila - CRM could now see the SQL Reports for 4.0.