Moving CRM 4.0 Databases (Including MSCRM_CONFIG) to a new SQL Server

Sometimes, you just have to move your databases. While the Microsoft Dynamics CRM 4.0 Implementation Guide has guidance on moving an organization's _MSCRM database, there is no mention to what to do if you want to move the MSCRM_Config database also. Luckily, Microsoft has published  Knowledge Base Article 952934 describing three possible scenarios:

1. You want to move the Microsoft Dynamics CRM databases to another Microsoft SQL Server and Microsoft SQL Server Reporting Services server in the same domain. Additionally, you want to leave the Microsoft Dynamics CRM Server on the existing server.
2. You want to redeploy the Microsoft Dynamics CRM deployment that includes the Microsoft Dynamics CRM server within the same domain or to another domain.
3. You want to move the Microsoft Dynamics CRM Server or one of the Microsoft Dynamics CRM server roles. However, you want to leave the SQL Server and SQL Server Reporting Services server intact.

The scenario I'm more interested in is #1 - Moving just the databases to a new SQL Server. Here's how my experience went down.

Getting the Databases Moved

This part of the procedure is pretty simple. Just disable your organization(s) in the CRM Deployment Manager and then move the databases over to the new SQL instance. You could do this either by restoring from a backup, or using the detach/attach method - how to specifically do this is beyond the scope of this post.

Modify the Registry

On the CRM application server, you'll need to make some registry changes. What you'll be doing is modifying three entries in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM hive that reference the old SQL server to point to the new SQL server:

1. configdb
2. database
3. metabase

You'll have to do this on every server that has the CRM App Server role.

Edit The Organization

Next, Open the Deployment Manager and disable the organization(s) that you just moved. Right-click the organization(s) and select Edit. You'll then have to type in the new SQL server name, click Next then Apply.

Finally

Once this is all complete, you'll want to do an iisreset on the CRM server. In my case, I then checked the Deployment Manager and expanded the Servers folder and found that the original SQL server was still listed instead of the new SQL server. I don't know if this is a bug or what, but it appears to be a pretty benign issue.

Comments

[Matt Richardson]

Great Post! You might find this information complementary to this post:

http://www.iccohio.com/blogs/mrichardson/archive/2008/07/17/changing-sql-server-name-for-dynamics-crm-4-0.aspx

Thank you,
Matt

[Frank Eersels]

...and expanded the Servers folder and found that the original SQL server...

The field Name in table Server in database MSCRM_CONFIG is not updated. You can update this manually, replace the old value with the new value of the new SQL-server. Run Deployment Manager and it will display the new SQL-server.

Cheers,

Frank

[Jack Casas]

After doing this, the workflows have stopped working. We had IIS and SQL in the same machine. I used this guide to move the SQL databases to a new server.

Now CRM is working ok, but workflows are not. I think it has something to do with the roles defined in Deployment manager. Now the old server is IIS and the new server is SQL, but in the SERVER table, there is only one server record for the old server, and 'roles' field is set to all. I think a new record must be created, and roles defined, because right now, if I go to deplyment manager and click on enable server, I get error '"Enable Server" failed to complete.' I guess it's because now the server hasn't go all the roles.

Any ideas on how to sovle this??

[Assaf Lev]

@Jack:

Look for hotfix 950680, this should fix your problem.

Assaf Lev

[buy viagra]

I am working on two SQL servers at work... testing them out. One is SQL 2005 standard and the other being 2005 SQL express. I am trying to use import/export utility on the standard edition to move a database to the express addition. I was getting errors at first but then researched and troubleshooted to find that on the express server tcp/ip and pipe naming was disabled. I stopped and restarted the service. Now when i try the import/export on the standard server it does not list the express server to choose as it did before. It is no longer on the list of servers....any suggestions???

[Cheap Computers Canada]

Interesting information is what we look in a website and keep coming for more.

[albenza 400 mg]

Now CRM is working ok, but workflows are not. I think it has something to do with the roles defined in Deployment manager. Now the old server is IIS and the new server is SQL, but in the SERVER table, there is only one server record for the old server, and 'roles' field is set to all. I think a new record must be created, and roles defined, because right now, if I go to deplyment manager and click on enable server, I get error '"Enable Server" failed to complete.' I guess it's because now the server hasn't go all the roles.

Any ideas on how to sovle this??

[Smboardman215]

When i did this CRM reports no longer work for some users (myself included). When I run a report it immediately returns rsExecutionNotFound error every time. My co-worker is able to run reports just fine, we are both system admins in CRM... any ideas?

[Joel Lindstrom]

Smboardman215,
Microsoft has a hotfix that is supposed to fix this error. Not sure if it is exactly the same as yours, but probably worth a try
http://support.microsoft.com/kb/983630