Related sites:

Newsletter: Perspectives on Power Platform

Company: Niiranen Advisory Oy

Creating a copy of your production 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 XRM approach: 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 you follow 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.

Tags

Related Posts

41 Comments

  2. Hi Jukka,

    Great Article. I would like to add one more point over here and i.e. if your plugins, javacripts or any custom code is using a hard coded value of the production environment please change it accordingly.

    If it still refers to the production environment this would be a huge problem in future. Even better is document this process and keep it so you can refresh your sandbox every quarter the same way.

    • That’s an important addition, Maulik. If you’re unsure of any code that has been deployed to your production CRM environment (for example, if you’ve “inherited” an old deployment with several add-ons and custom components), setting up an isolated test server with it’s own domain & credentials is definitely a much safer route to take. After all, there are 90 day trial license of CRM and 180 day trials of Windows Server & SQL available for building a temporary test environment on a virtual machine that can run on Windows Azure or any other similar service.

      Just yesterday when using the Copy Database task (luckily on an internal development environment), I ran into a situation where the copy process failed and as a result the original CRM organization database was stuck in a single user mode. Even rebooting the database server or IISRESET didn’t allow me to change the settings, because a connection to the database was persistently open. After running sp_who2 on the SQL server I saw that it was the CRM async server that was holding onto the DB as the single allowed user, so I stopped the service and was able to revert the database back to multi user mode. The lesson here is that you just need to be prepared for some downtime while performing the database copy in a production environment.

  3. When creating a copy of production database especially for testing and development and it is kept on the same box (PROD CRM Server). Are they forced to use the same workflow? If not, then we could customize and test changes to workflow in test and not have production affected? Are there other or differant draw backs to the single server environment for production and testing environment?

    Great article and I look forward to your reply.

    • Ron, the single server will share the asynchronous service that executes the workflows, but the workflow process definitions and instances are specific to each CRM organization. Being a multi-tenant application, you can safely assume that anything which would remain separate between different customers sharing a common 3rd party hosting platform for running their Dynamics CRM organizations will also be isolated in the case of running dev/test/prod databases on the same server instance. Anything that is configured on the solution level is safe, as long as there are no hooks to external services or shared resources outside the CRM organization database.

      The main drawback is that with a single server you can’t test anything related to binary level changes of the actual CRM application itself. A common example would be Update Rollups, but now when CRM 2013 is about to be released soon, testing the version upgrade will not be possible if you don’t have a separate application server for it. Also any development related activities that either require IISRESETs or impose a potential performance hit on the server will affect the live system users when working on a single server with multiple CRM databases.

  4. Jukka

    We have a situation in which we have three separate environments, each on separate CRM application servers, with separate database servers. We want to copy production and overwrite the existing test environment.

    Should be fairly simple as all different org id’s etc, simple copy and reimport… but it’s IFD configured, with a bunch of scribe integrations pointing to the existing test organisation that we’d prefer to not have to repoint. Any ideas on how we could reimport over the existing organisation? Unfortunately it’s the default org, so I don’t think we can delete it either.

    Thanks

  5. Possibly – we don’t have easy access to those (partner, working on behalf of customer, hosted with third party) to check. I was hoping there was a way without messing with DNS.

    By the way, all your comments on here say they’re posted on 7th August 2012… not quite right 🙂

    • Yeah, the WP theme has been messed up for quite a while, I just haven’t bothered to check where I’d need to change the date parameter. Wasn’t really an issue before I started getting comments on the blog on a regular basis 😉

  6. Hello.

    I’m new to MS CRM. Started working on it from 6months. I work for an energy company in Holland.
    I finished customizing the Sales lifecycle but now im stuck in Services. Our company use a big asp.net application for Customer Services. The ides is to implement it in CRM Services. I don’t think we can do it in CRM. Is it something like, any kind of Customer service applications can be implemented in CRM Services?
    I don’t know how to get so much of data from my database to CRM and display in one form(CASE)
    (or) distribute the data into different forms (or) crete Custom Entities?
    Any references or suggestion for fresher like me would be appreciated!

    Thanks.

    • Manu, it sounds to me like this could be a possible use case for the Customer Care Accelerator for Dynamics CRM 2011. In short, it provides a User Interface Integration (UII) framework that allows you to build custom screens for call center agents, drawing data from several different back office applications into the CRM UI. It’s by no means a “click install, next, next, next” type of experience but rather a starting point for developing advanced UI customizations to meet the specific needs in service departments. Have a look at the following article for a quick summary of what the CCA is about: http://blogs.msdn.com/b/ukcrm/archive/2011/05/11/getting-started-with-cca-for-crm-2011.aspx

  7. Hi Jukka,

    Very good detialed article with multiple options and reasons while selecting an approach. I have question / challange in my case. We are having more than one Organization in our production server. As these are production servers when ever we deploy some change, we bring servers down (modifying web site bindings). After deployment granting access to end users(rollback the changes in bindings of IIS). So when ever we deploy something to Org1 other Organization is also getting affected. Do you have any Idea or approach to avoid such dependencies else we can’t use MS CRM as a platform (I guess).

  8. Hi Jukka,

    Thank you for your time to respond !
    By disabling the Org1 while we be able to deploy our solutions? (Please excuse if the question is too silly :-))

    • Praveen, disabling the organization will disable all access to CRM. I wonder what kind of solution modifications you are performing that absolutely require the environment to be made inaccessible? Also, how are you performing this currently through the IIS bindings for the end users while preserving admin access to the CRM application?

  9. Hello Jukka!

    Thank you for the detailed overview about the UII .I will know more about it and try to use it in my CRM.

    Thanks
    Manu 🙂

  10. Hi,

    I think I could use just the same procedure of the company importer, to set up a test environmenton on a different server, can I?

    I always read, that u should use a different domain for the test enviroment, is that realy necassary?
    Will the test environment still work, if I use the same Domain and Credentials as I do in the prod environment?

    Or will I encounter some troubles?

    • Florian, the process of taking a database backup and importing it onto another server is a typical process for setting up a test environment where you want not just the customizations but also the data. The process would be the same, except that if you’re working on a separate server there’s no need to necessarily run the script.

      As for isolating dev, test and production environments, it’s always going to be about balancing your resources and requirements. Sharing the same domain with your test and production environments is not something I would consider a big risk from a performance perspective (can’t speak for AD administration side of things as that’s not my area of expertise by any means). Then again, if you already have separate test boxes for the CRM application and database servers, then adding a test domain in there isn’t such a huge step to take for making the environments completely isolated.

      If you just need to have a working CRM server for testing application functionality and not performance scaling, then having everything on a single test box is a perfectly viable option when server resources are scarce. Have a look at this post for the minimum required steps for setting up a Dynamics CRM test environment on a single server that can be running on Azure or any other location of your choice.

      For a more in-depth discussion on the various considerations on building your development and test environments, I recommend you to read this article by Aaron Elder, the grandfather of Microsoft Dynamics CRM. Even though the article is back from CRM 4.0 days, the multi-tenant architecture of Dynamics CRM still follows much of the same principles as it did back in 2009.

  11. Hi,

    I built CRM Farm with 4 servers (BE, FE, SQL and SSRS). Now my company deployed a SQL Cluster and I needed to migrate from Single Database Server to SQL Cluster Instance and point CRM to use the new SQL Instance in SQL Cluster. Please advise
    Regards
    SR

  12. Hi Jukka,

    We have four separate instances for Dev, Test, UAT and Production. All of them have their own dedicated application server and DB servers. The process of replicating a Production database works fine (as there are separate servers) and we tend to refresh the environments once every 2-3 months or so.

    I am looking for an elegant solution to the problem I have with Workflows. We have a lot of them and a majority of them send emails (internal as well as external). As part of our refresh, we update each workflow to point to a test address instead of a “real” one and publish them again. This needs to be dnoe every time we refresh the data and I was looking for a solution that could allow us to put an IF condition in the workflow – to check something to determine if it is being run in Production or not (the organisation name…? as the org names for our instances are different)
    I could then code it to send emails to a hard-coded address in scenarios where it is not running in Production.

    This could potentially involve a Workflow Helper? as I am not sure about using any other entity storing this information to help determine the instance…

    hope the above makes sense… Surely, this has to be a common scenario and just wanted to put it out there to see what your thoughts were?

    cheers,
    Kay

    • Kay, perhaps you could add a reference to the record owner’s business unit and then rename these after the redeployment of the environment (“TEST HQ” instead of “HQ” etc.)? Quite often the non-production environments are not configured to send emails out into the real world (from what I’ve seen), but if you need to really be able to get the emails out from every environment instead of just validating the creation of the email activity record, then the workflow condition sounds like a good idea. With a custom workflow activity you could also reference some specific configuration entity that doesn’t have a direct relationship to the actual business entities, which would effectively give you a global switch for determining whether the workflow is running in a production environment or not.

  13. Hi Jukka,

    Thanks for the article.

    I am setting up a new server which will host a test CRM instance. The test DB’s will be hosted on the same server as the live DB’s.

    When installing CRM on the test server should I create a new deployment and then import a new organisation from the test DB’s.

    or

    Connect to the existing deployment (would this harm the live system?) and then import the new organisation?

    Thanks again

    • Didi, unfortunately you won’t be able to host databases from 2 different CRM deployments on a single SQL Server. There can be only one MSCRM_Config database per SQL instance, so pointing the test CRM server to the existing SQL Server won’t allow you to complete the installation. You can have as many organizations from a single CRM deployment on the same SQL Server as you want (in theory), but to test any deployment wide components like new Update Rollups you’ll need both a CRM test server and a SQL test server.

  14. Interesting article. The obvious question to ask is once the production database has been copied for testing. How would you refresh it with new data. I’m assuming you can’t keeping importing the database every couple of weeks and this would start to fill up the organisation table within MSCRM_CONFIG DB.

    Is there a way to refresh the data once cloned?

    • Steve, technically there shouldn’t be any limits on how often you can import a new database. You can always disable and remove old, redundant test organizations to conserve disk space, but I don’t think the MSCCRM_CONFIG database growth would be a concern very fast.

      If you don’t want to copy the whole database, then you would essentially need to set up an integration between your production and test organizations. For simple requirements you could achieve this with the free CRM Instance Adapter, which is a CRM-CRM variant of the Dynamics Connector originally designed for CRM-ERP data synchronization. Not all of the data types would necessarily flow through the Adapter (more complex records with many dependencies, such as activities or product/price related entities), but you could easily maintain a list of up-to-date accounts with their ID numbers, for example, if you need to have your test server connected with other test systems. However, for 1:1 parity between production and test, copying the database is the most reliable method.

  15. Thanks Jukka

    I’ll check out the CRM Instance Adapter, as if we go down this road of Production to Test then I would like the whole process automated, and removing an earlier database and reimporting would be a difficult ask.

    There is of course products like scribe (other products are available), but I like the idea of free so I’ll check out that tool.

    Many thanks and again great article.

    steve

  16. Hello,

    We are trying to import an organisation from a different domain and therefore we don’t have the users setup in AD in the destination environment. Therefore is it possible to get around the mapping step in the import wizard?

    • I’m not aware of a workaround that would allow you to skip the mapping of CRM users to valid AD user accounts. I recently had to map a large number of CRM users into dummy accounts generated into a development domain and the process was quite tedious (mostly because I suck at Powershell and had to try many workarounds to create the AD entries, but that’s another story).

  17. Hello

    I am new to CRM and using CRM2013. How to move chunks of customizations and modifications to Dev to Test and Test to Prod. And also how to get the current data into Dev and Test environments from prod.

    • Sari, the customizations and modifications should all move with the CRM solution file export & import process. As for moving the current data from production to test/dev, the method described in this article should still be a perfectly valid option, even with CRM 2015. Once you’ve released the latest modifications to your production environment, delete the existing dev/test organizations and create new ones by taking a database backup of your prod org.

  18. Hi Jukka. Are you sure your approach works when using managed solutions in Production? Because if (after a release to Prod) copy the Prod database to DEV/TEst you will end up with managed solutions on DEV which is not good. Because you can’t change these soluions and components anymore…

    Or at least I’m not aware how to do this.

    Thanks on any advise.
    Alen

    • Alen, if you’re using managed solutions then this approach wouldn’t be for you. In general, using managed solutions will require setting up such a carefully planned and strict development and release process that you’ll probably need to automate it anyway with some piece of software, like the Adxstudio ALM Toolkit, for example. Therefore you would also need to develop suitable scripts for populating dev and test organizations with data.

      If you don’t have such processes in place yet, you have a small team/project and don’t expect to do much custom code development, I would advise against using managed solutions and just going with unmanaged packages in all environments. ISVs and large enterprise projects with distributed development teams are of course a different story.

  19. Copying the database should be the same as creating a new database and restoring the backup right? I was able to try all the above steps. Our organisation uses IFD, when I try to login it says An error has occurred. I have updated the ADFS and shows the newly added organisation but still says the error message.

    The above setup doesn’t use IFD is that correct?

    • Sohc, when using IFD there might be some entries in the database that references the old URL, so I suggest re-running the IFD configuration wizard in CRM Deployment Manager to get things updated.

  21. Hi Jukka
    This is just what I was looking for. But I get an error when copying the database. Say CRMReader role is not in the new database. How do I fix that?

  22. Hi Jukka,

    I tried your sql command on Dynamics 365 on-prem (CRM 2016 with December 2016 Update).
    Inside the tables I see that the OrganizationID has been changed.
    I can import the organization without any issues inside Deployment Manager.
    But after the import is done (without errors or anything) and I try to browse the Org, I get the error “Invalid Authorization”

    Do you have any experience with duplicated organizations on CRM 2016?

    • Andreas, unfortunately I haven’t been working with on-prem environments for a while now and don’t have a CRM 2016 server where I could verify whether the script still works. In the original forum thread someone did say they’ve successfully used it in CRM 2016, though, so it might be an issue specific to your environment. If you’re using IFD then that might be one factor. I assume you’ve tried clearing browser cache and ensured it’s not a client machine or user account specific issue?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.