I recently had the responsibility of moving an existing, in-use Microsoft CRM 3.0 database from it's current location/domain to an entirely new server on a different domain.  Not only was the target domain different but so was the licensing in that we were upgrading our product code and not using a Small Business Server license anymore.  Oh, and I should also mention that the organization name was also changing within the database.

This process is pretty painless, all-in-all, provided you're careful and pay attention to the goings-on of the process.

First of all, I backed-up the SQL Server databases (company_METABASE and company_MSCRM), archived them, and restored them into the new domain.  I also took the time to remove any schemas and logins from the databases that didn't apply in the new domain.

Then, I followed the instruction as provided by the Microsoft CRM Redeployment Tool (thankfully this existed to facilitate this process) on the CRM 3.0 disc 1 (also available via download here) to map users from the old system into the new domain.

When it came time to associate the new CRM installation with the original databases, however, I received the following error "The existing databases are not compatible with this installation of Microsoft CRM".  This was easily fixed by following the recommendations in a KB article and doing some spelunking on my own.  As it turns out, it wasn't liking the new databases because of an organization name change in CRM.  I also received an error because of the license key because we were moving off of the Small Business Server to another non-Small Business Server and the license keys could not be used together.

The following SQL commands fixed the issues:

UPDATE BuildVersion SET Revision = X (where X == the 3.0.5400.X of the SetupServer.exe product version)
UPDATE OrganizationBase SET Name = 'new organization name'
DELETE FROM License

After that, the installation was smooth as glass and without a hitch.

One thing of note as well once we were up and running.  CRM was operating correctly, but anytime we'd access the Reports section, we were greeted with the error "Report.config has invalid schema, and could not be loaded".  Likewise, when we'd go into the Calendar section we'd get an "Unexpected Error".

The fix for these were to make sure that the CRM 3.0 website and the SQL Reporting Services virtual directories were using the proper ASP.NET Version (1.1.4322).

There are times where you may need to dig into the details of a SQL Server database to retrieve information / metadata about the database itself.  There are many built-in stored procedures that facilitate this (e.g. sp_helpdb), but they don't always provide the information in a SQL-friendly form.  In the case of sp_helpdb, much of the information is returned in a comma-delimited form and parsing it out is a headache, and and unnecessary headache at that.

Suppose you need to retrieve the collation order of the database.  This is quite easily accomplished by calling the built-in function DATABASEPROPERTYEX.  In fact, each of the settings found in the status column returned by sp_helpdb are individually retrievable via the DATABASEPROPERTYEX function.

SELECT DATABASEPROPERTYEX('DbName', 'Collation')

Just a few days ago Microsoft released SP2 of it's SQL Server product.  Download it here.  If you're running Vista you'll want this update as pre-SP2 isn't supported on Vista.  Now I'm waiting for one more update (the VS.NET 2005 SP1 for Vista) before I officially make the switch on my primary development machine.

I've been battling a peculiar issue for a couple of days regarding Data Transformation Services (DTS) with SQL Server 2000 and SQL Server 2005.

I wrote a small C# application that utilizes DTS to pull a database from a server down to a client machine.  All in all, the application runs beautifully and the information synchronizes seamlessly.  Occasionally, however an odd error was reported: Unable to connect to source server for Transfer.  This was odd because all of my connections were succeeding.  It was also only on this computer that the error was occurring - I could not duplicate the error on other computers so I began delving into the differences between the machines.

As it turns out, the error has to do with mismatched database versions which I've whittled down to the following matrix:

As you can tell, the issue arises when the client database engine is not at least as high as the server version.  I'd long suspected that a version mismatch was the root of the problem but hadn't ascertained it until today.  After troubleshooting this for some time to make sure that there wasn't something else I was missing, I ran a trace on the SQL Server to detect whether something was happening there. (I had actually run the same trace before but failed to see the key piece of information).

The DTS transfer objects apparently have baked into them a version check.  When run from an MSDE client it performs the following query:

if (object_id('master.dbo.sp_MSSQLDMO80_version') is not null) exec master.dbo.sp_MSSQLDMO80_version else if
(object_id('master.dbo.sp_MSSQLDMO70_version') is not null) exec master.dbo.sp_MSSQLDMO70_version else select 0

SQL Server will always return a valid result set for the current version (e.g. 2005 returns valid results if the sp_MSSQLDMO90_version sproc is called and 2000 returns correctly if the sp_MSSQLDMO80_version sproc is called), but will throw and exception on any other version's sproc.  The issue is that the MSDE will check for sp_MSSQLDMO80_version on a SQL2005 box which returns an error which ultimately cascades down and cancels the DTS package.

From a SQL Express client, the query is this:

if (object_id('master.dbo.sp_MSSQLDMO90_version') is not null) exec master.dbo.sp_MSSQLDMO90_version else if
(object_id('master.dbo.sp_MSSQLDMO80_version') is not null) exec master.dbo.sp_MSSQLDMO80_version else if
(object_id('master.dbo.sp_MSSQLDMO70_version') is not null) exec master.dbo.sp_MSSQLDMO70_version else select 0

In this case, the query will continue to work against a SQL2005 server or a SQL2000 server.

In other words, SQL is throwing the 'cannot connect' error correctly.  Internally it reports "To connect to this server you must use SQL Server Management Studio or SQL Server management Objects (SMO)".

Solution:

My application must maintain full compatibility with the MSDE -> 2000 environment.  The only solution I could come to was that in order for my application to run against a SQL Server 2005 server the client computer would have to be upgraded to SQL Express.  I hate to have to do that, but I could see no other viable solution.

Are there other thoughts out there?