Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Move CRM Data to anoth...
Microsoft Dynamics CRM (Archived)

Move CRM Data to another Server. Keep Database User Accounts.

(0) ShareShare
ReportReport
Posted on by Artemy 285

I'm trying to move production CRM MSSQL data to another server (server 2). Server 2 already has most of the data after a restore I did from the production data backup a few months ago. The problem is that I don't want the SQL users to be overwritten, since I made changes to them on server 2. I just want the data in the tables. I think this rules out a backup and restore approach.

I was thinking of using the "generate scripts" option for just the tables, but the script seems to fail with a syntax error, or pretty much all of the commands get skipped because of foreign and primary key constraints. I'm executing the script using sqlcmd.

I tried to save the sql users from server 2 using the script generation, but when I import them back into server 2 after restoring production data, they don't seem to work, since the CRM starts giving user-related errors.

CRM Data migration tool crashes the server when I try to export the data, likely because there's too much data for it to handle.

Both servers use SQL2012.

Do you have any suggestions on how to migrate the production data into the sql database on server 2, while keeping the sql users at the destination untouched?

*This post is locked for comments

  • Artemy Profile Picture
    Artemy 285 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    @David

    I believe this will indeed solve the problem. I realized that the issue isn't on the SQL-AD side, but on the CRM-AD side. I just didn't remember that an organization import does the user remap as well.

    One thing I'm curious to find out is what will happen if I import a copy of the production AD data into the server 2 stack, and then do the CRM organization import. Will it break the services that are run by CRM? Will it break the Administrator account? I was thinking of doing the AD data import using the NTDS.dit extraction and replacement procedure.

    Would it be better to just use the current AD on server 2 for the organization import and ignore any inconsistencies it may have with the production AD (or just manually create the missing users)?

  • David Jennaway Profile Picture
    David Jennaway 14,063 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    The import organisation process does 2 things that are relevant here:

    1. It should ensure the SQL Users for the AD groups (e.g. SQL Access Group) in the organisation database map to the correct SQL Logins
    2. Map the CRM systemuser records in the organisation database to the corresponding systemuser record in the MSCRM_Config database
  • Artemy Profile Picture
    Artemy 285 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    I still haven't figured this out. Any more suggestions?

  • Artemy Profile Picture
    Artemy 285 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    The "server 2" stack servers are all in the same domain. I also have no issues with logging into the domain servers using a domain account. The CRM application worked fine before the database was restored into the server 2 stack.

    Is there anything in particular I should try to diagnose or solve the issue? "fix an issue with Active Directory Trusts" is too generic of an instruction.

    I tried restarting ADFS and CRM services and resetting IIS, but it didn't seem to solve anything.

  • Suggested answer
    Adrian Begovich Profile Picture
    Adrian Begovich 21,009 Super User 2024 Season 2 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    Hi Artemy,

    There is a problem reading a user name, group name or computer name from Active Directory. You will need to fix an issue with Active Directory Trusts.

  • Artemy Profile Picture
    Artemy 285 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    Yes, I'm referring to the SQL user objects. However, your insight on the users not needing to be in the CRM database because they get their access and permissions from the AD groups created by the CRM installation is helpful.

    This leads me to believe that the problem here is that I did a clean installation of the CRM software on the server 2 stack, but did a backup and restore of the production CRM database into server 2. This means that the AD groups and SQL group logins generated by the server 2 stack CRM installation will not match the ones in the SQL user objects, since they are referring to the groups created by the initial production CRM installation (Hopefully my point is clear). This means that the connection between the SQL server logins and user objects will be broken, which means that the CRM will not work. 

    Do you mean to say that the import organization process after the backup restore will fix the mismatch in SQL users/logins and AD groups?

    I've tried using "alter user with login" queries, but they say "Cannot alter the user 'Domain\SQLAccessGroup {449dc3c1-9a0c-4bc4-ad9d-f32a04a16963}', because it does not exist or you do not have permission." I bypassed this by having a use crm database statement before the query, but the user group is no longer accessible in the object explorer because of the error: "Database user name is not valid". The CRM then shows this error:

    crm-user-error.PNG

    What would be the recommended solution here?

  • Verified answer
    David Jennaway Profile Picture
    David Jennaway 14,063 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    What are you referring to re: 'I don't want the SQL users to be overwritten' ? If you're referring to SQL User objects, then you shouldn't have any in the MSCRM database, as users get SQL access via the ReportingGroup AD group. Or are you referring to the CRM user (systemuser) entity - if so, then you can restore the MSCRM database, then use CRM Deployment Manager to import the organisation database, which will allow you to map the users correctly.

    Overall, your best option is almost certainly to use SQL backup/restore and import the organisation, and maybe then apply any other changes you've made to the users. The alternatives are to either try and transfer data at the SQL level, which is unsupported, and will almost certainly break CRM in some way (it is very hard to manage the dependencies), or use the CRM API to import data (either directly, or using tools like the CRM Data Migration tool), which is at least supported, but takes a lot of effort to get all the data dependencies in the right order, and takes a long time to run

  • Artemy Profile Picture
    Artemy 285 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    I'm not familiar with SSIS. Is there a specific process you would recommend to migrate the data? The constraint is that I need the data to be exported to a file and then imported into the database on server 2. There can't be a direct connection from production sql server to server 2.

    Also, why would something like SSM's "generate scripts" won't work to export the data?

  • Suggested answer
    Adrian Begovich Profile Picture
    Adrian Begovich 21,009 Super User 2024 Season 2 on at
    RE: Move CRM Data to another Server. Keep Database User Accounts.

    Hi Artemy,

    I recommend using SQL Server Integration Services (SSIS) for this task. This will allow you to migrate the production data into the SQL database on server 2, while keeping the SQL users at the destination untouched.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans