Creating  a copy of your production MS Dynamics CRM database:

 
If you have an on-premises CRM server and you’re running Dynamics CRM 2011, then you’re eligible to having more than one database in your CRM environment. Back when the multi-tenancy concept was introduced in CRM 4.0, there was a distinction between Workgroup, Professional and Enterprise editions. Only the Enterprise version granted you the right to run several CRM databases on a single server. With CRM 2011 the Professional and Enterprise editions were merged, which means that almost all Dynamics CRM customers can now enjoy the benefits of multi-tenancy. (The Workgroup edition still has a limitation of max 5 users, which in this age of cloud computing means hardly anyone would choose that version and put up a server for it, so let’s ignore that one.)

Why would you need more than one CRM database? One possible reason could be the XRMapproach: for no additional license or hardware costs you could be using your Dynamics CRM environment for also managing other processes than sales, marketing and service that traditionally are the focus area of CRM systems, while keeping it separate from your main customer database (HR, IT service desk, project management etc.). In case you’re happy with focusing on customer relationship management for now, then a typical reason for needing another database is that you want to test some customizations in an environment that won’t mess with your live system settings.

How would you go about managing multiple databases then? In Dynamics CRM terminology, each database represents a distinct organization in CRM. Adding new organizations into your CRM server can be easily achieved through the CRM Deployment Manager. If all you need is a blank new test organization, then just start the New Organization wizard and click through the process, which will create a new database, configure language and currency settings etc. Click Finish and you’re all set!

Oh, you wanted your production system entities, fields and other customizations in there, too? No problem, just go into your production CRM settings area, pick a solution that contains the elements you need and export it. If you don’t have such a solution, then simply create a new one, as it’s not going to affect how your live environment operates in any way. After all, solutions are just pointers to the components like entities, processes or plugins, until you export them, at which time they become physical containers to all those bits that make your CRM environment different from the plain vanilla Dynamics CRM organization. Don’t export your transport solution as managed, unless you have a good reason for it (you’ll know once you do). Just take the unmanaged solution zip file from your current CRM organization, open the new one, import it there and publish all customizations. Now we’re done!

Huh? What’s that you say about data? Would you prefer to have not just the schema of the database but also the contents of your CRM database in that new test organization of yours? In that case, let’s forget the previous steps and use another approach, shall we? Instead of the New Organization wizard you’ll want to use the Import Organization option. Before you click on it, though, we’re going to need to create a copy of your database, because the CRM Deployment Manager does not have a “Copy Organization” feature.

Typically the Import Organization functionality is used when taking a database from some other environment, like when establishing a whole separate development box or test server. Also the actual upgrade process for turning a CRM 4.0 database into a CRM 2011 database is handled through the same import wizard. When you’re in the process of planning your Dynamics CRM upgrade, this is a handy way to update the old CRM 4.0 customizations into new CRM 2011 solutions. In these scenarios you’d first take a backup of the original database on your old SQL Server, then copy it over to the new environment, import it into SQL and finally into CRM. However, as we’re simply creating a replica of the database inside the same environment, we don’t need to necessarily go through the backup stage.

 On your SQL Server machine, open up SQL Server Management Studio, right click on the database which has the name ending with “_MSCRM”, then select Tasks – Copy Database. This helpful Copy Database Wizard will step you through the process of creating an exact copy of your CRM organization database. Just give it a different name than the original database and point it to the same server. You don’t even necessarily need to schedule a maintenance break for your production CRM environment, since the wizard can create the copy without the need to detach and attach the original database. This wizard actually builds an SSIS (SQL Server Integration Services) package that takes care of the copy operation.

Before returning to the CRM Deployment Manager we’ll need to pause for a while and think about how the Dynamics CRM server operates. Just like records in CRM, also the actual organization itself has a unique ID in addition to the name and display name visible in Deployment Manager. Although the Import Organization wizard does attempt to handle this, it doesn’t perform it in the most graceful way. The two known side effects from from having overlapping organization ID’s prior to the import are that: A) the import will fail if you’ve customized the business unit entity and B) email router will not work for the new organizations. There might even be other nasty surprises hiding deep inside the database, so ultimately we’d like to have a situation where the organization ID’s are 100% unique.

There are no official tools for this operation, but luckily the Microsoft Dynamics community has come up with a solution. In the CRM Forum thread “CRM 2011 Import Organization on the same server (or how do you create a development sandboxes)” you can find a script that you can execute on your new database copy. This script will generate a new ID and update it to all the relevant tables, including the PrincipalObjectAccess table where the OrganizationID goes by the name PrincipalID. If you’re not familiar with working on SQL Server then it may look scary, but the process itself is quite simple. Select your new database (not the old one!), right click, New Query. Copy the script created by Frenkie Smart found in the CRM Forum post and paste it into the query window. Pause for a minute amd check that you have fresh backup copies of anything that’s valuable to you on that SQL Server. Got it? Good, then just click the Execute button to run the script. You’ll see in the message window below the query how many records the script has changed in each table it processes.

Now we’ve got the new database in such a condition that we can proceed to the Import Organization wizard in the CRM Deployment Manager. Select the new database as the one you want to import, give the organization a unique display name and database name, accept the user mappings, and off you go (see detailed process instructions in this Technet article). The Deployment Manager will build a new organization for you from the copy of the existing database, which you can then access by replacing the organization name in your existing CRM URL (in my case from http://server/demo1 to http://server/demo2). If you want to use friendly URL’s or IFD for accessing CRM then you’ll need to know which DNS entries and settings to modify for the new organization to be available.

That’s it, for real. Here’s a quick recap of the process steps:

1.    Copy your production database

2.    Take backups!

3.    Run the script on the new database

4.    Import the database as a new CRM Organization with a unique name

The typical scenario for performing this process would be the need to generate several development organizations that contain identical data and customizations as a starting point. If you just want to maintain your own test organization alongside your production CRM environment then there’s a few things you should take into consideration. First of all, the chance for human error. If you have two identical CRM organizations that are separated only by a few characters in the URL, the chances of mixing them up can be high. Second, you won’t be able to test anything related to Update Rollups and other components that are shared by all the organizations on the server. Third, if you’re unsure about what you’re doing, then don’t do it on your live CRM server!

With all this in mind, it might be a good idea to investigate the possibility of having a separate test server after all, don’t you think? If you don’t have any suitable hardware lying around, then signing up for a virtual machine straight from the cloud is a valid option these days. With its latest improvements, Windows Azure offers a convenient service for provisioning persistent virtual machines as needed. Building a VHD image with CRM 2011 is not a very difficult process if youfollow the instructions (and know the few gotchas about SQL or VM size settings). Also, if you don’t need to keep the server up & running on a continuous basis, you can always delete the Azure virtual machine and still keep a copy of the VHD image, available for booting it back up again when the time comes.