Thursday, June 5, 2014

Consolidating multiple Dynamics GP Companies into single DYNAMICS database

Recently I had an interesting project with one of my customers, they had the following environment:

Server I:

  • Dynamics GP 9 RTM with 14 active companies.
  • SQL Server 2005 (Collation is Arabic_CI_AS).
  • Windows Server 2003 & FRx

Server II:

  • Dynamics GP 11 RTM with 3 active companies.
  • SQL Server 2008 R2 (Collation is Latin)
  • Windows Server 2008 & FRx

The customer simply wanted to upgrade to Dynamics GP 2013 SP2 and to SQL 2012, and also wanted to consolidate the databases into one single DYNAMIMCS database.


Now we have few challenges detailed below:

  1. Dynamics GP 2013 does not support upgrading from GP 9.0
  2. Dynamics GP 2013 does not support SQL 2005 and GP 9.0 does not support SQL 2012.
  3. Database collation differences between companies.
  4. Data stored in DYNAMICS database like currencies, users and BP objects.
  5. Changes that will occur on columns like “Company ID” and “Currency Index” which will be changed due to DYNAMICS data consolidation.
  6. Security migration from GP 9.0 to GP 2013.
  7. FRx migration to Management Reporter.
  8. Minimum downtime need to be maintained! 17 companies across multiple countries cannot stay down forever!


Well we believe that nothing is impossible in the IT field, we have planned for everything and scheduled the upgrade process, below the steps we followed:

Server I:

  • Took a backup for all databases.
  • Upgraded from version (GP 9.00.0114) to 9.00.0371 that supports the upgrade to GP 2010 (we need GP2010 because it supports both SQL 2005 and SQL 2012!)
  • Upgrade from (GP 9.00.0114) to GP 2010 (GP 11.00.2320) that supports direct upgrade to GP 2013.
  • Moved upgraded databases from SQL 2005 to SQL 2012 with moving all users and configurations.
  • Upgraded to GP 2013 SP2.
  • Checked System stability.
  • Updated Users permissions and passwords.
  • Installed Dynamics GP 2013 with SP2 on all clients
  • Installed Management Reporter that replaces the FRx on the server and on the clients.
  • Migrated existing FRx Reports to Management Reporter
  • Activated SQL Reporting Services
  • Installed and activated Dynamics GP Web Client
  • Started the data entry!

This operation took the first weekend!

Server II:

  • Took a backup for all databases.
  • Upgraded from your current version GP to the service pack that supports the upgrade to GP 2013.
  • Upgraded to GP 2013 SP2.
  • Checked System stability
  • Created new companies on the new server
  • Restored databases from old server to PROCCOSQL over newly created databases
  • Update databases collation to match old server collation (I used the tool that updates the SQL database collation by dropping all functions, constraints and indexes that is collation dependence)
  • Updated CURRENCY ID in all databases columns to match the newly added currencies, it was indexed as “100X” and changed to “10XX”
  • Update Company ID in all databases columns to match the newly added companies.
  • Add newly created companies to Management Reporter
  • Activate SQL Reporting Services

This operation took the second weekend! Till now everything is working properly and this is how their GP looks like right now:



Mohammad R. Daoud MVP - MCT
+962 - 79 - 999 65 85


James Darmanovich said...

How did you handle the merge?
were there no conflicts with primary keys?

Beat BUCHER said...

Hi Mohammad,
Thanks for that great summary post on the GP merger... Could you delve at little more on the Collation process change when you moved the DB's from the old server to the new one ? what utility did you use for the index recreation ?
Thanks a lot.

Lee said...

Congratulation! Great information about loan. Now a days payday loan consolidation is an issue. thank you very much

Rondha Pratt said...

This article shows nicely aobut debt. we can check payday loan reviews to get payday loan. thanks

Chris Wess said...

Thanks for sharing such a useful information about server consolidation. Keep updating such kind of interested post here.

Related Posts:

Related Posts with Thumbnails