Refreshing your development or QA environment with production data

In a multi-server environment (Development, QA/Staging/Testing, Production), you occasionally run into an issue where you need to have the latest production data in the lower environments.

Here is a set of steps that will help you move the Production CRM database into Development or QA.  PowerShell is your friend here so much of this process can be automated.

Unfortunately, I don’t have any code to share because this was a customer project. But, this is not that hard to reproduce yourself.

We will review the task of moving to the QA environment, but the steps will apply to any other environment as well.

Step 1: QA Environment – SQL Server

The first step is to save any data that is specific to that environment, including:

• SharePoint configuration
• Custom configuration data, such as is used by plugins

Step 2: Production Environment

Make or get the latest full backup of the production CRM database.

Step 3: QA Environment – CRM Server

Turn off the following services:

• Email Router service.
• Asynchronous Processing Services.
• Sandbox service.
• Unzip service

Use Get-Service and Stop-service

Using the CRM Deployment Manager:

• Disable the QA organization.
• Delete the QA organization.

Use Disable-CrmOrganization and Remove-CrmOrganization

Step 4: QA Environment – SQL Server

From the SQL Management Studio, perform the following tasks:

1. Detach QA_MSCRM database.
2. Move QA_MSCRM.MDF and QA_MSCRM.LDF to a separate file system as backup. This is optional, but I’m a little on the paranoid side.
3. Delete the QA_MSCRM database.
4. Restore the Production database backup to QA_MSCRM.

This can be automated using PowerShell using at least two techniques: SMO commands or raw SQL commands, with the latter being the easiest.

Step 5: QA Environment – CRM Server

Using the CRM Deployment Manager, import the QA database.

From the Operating System, perform these tasks:

1. Run IISRESET.
2. Turn on Email Router service.
3. Turn on Sandbox service.
4. Turn on Unzip service
5. Turn on Asynchronous Processing Services.

Use Get-Service and Start-service

Step 6: QA Environment – SQL Server

Perform any PHI cleanup to remove or obfuscate sensitive information including:

• Phone/Fax numbers
• Email addresses
• Contact first and last names
• Address information

I would recommend the SSIS Integration Toolkit for Microsoft Dynamics CRM from KingswaySoft.

You may also wish to review the ALM Toolkit from AdxStudio for additional application life cycle management capabilities.

So that is about it. As you can see, it’s not rocket science, just a bunch of steps.

By the way, one assumption is that the servers are all in the same domain. If your environments are in different domains, you’ll have a bit more work to handle mapping users from one domain to the other, but that is something that is totally doable.