I want to Merge Multiple Dynamics GP 2013 Instances (4 DYNAMICS DBs running in 4 different SQL Server Instances) in to one SQL Instance

(0) Share

Report

Posted on by Muhammad Imran Ali

455

Hi There,

We are using 4 Different Instances of Dynamics GP 2013 (Version 12.00.1538 SP2) with 4 Different Instances of SQL Server 2008 R2 (Because of 4 different DYNAMICS Databases) because i have upgraded 2010 to 2013 recently. I want to know that can I merged all the instances of Dynamics GP 2013 in to One SQL Server Instance as Microsoft provided a feature in Dynamics GP 2013 to Change the Dynamics GP Database Name. Can I Rename the existing DYNAMICS Database name and merged all instances in to one SQL Server Instance?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (1)

Verified answer

Tim Wappat 5,711 on at

Like (0)

Report

Assuming you have done an audit on the proposed new host instance, to ensure it has the capacity to drive the number of users and size of your companies, then I should think this is fine.

We've merged all companies and instances into one DYNAMICS database. After ensuring everything was at the same software version, for both in GP and SQL.

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (1)

Report

If the company account framework is the same amongst all 4 instances then the answer is Yes. If they are different you will need to use CR Group Rreformatter to make them all the same before you merge. After the merge you will also need to run the NoteFix SQL script to correct the note index value in the DYNAMICS database. You may also have to reset security to each company plus if you have different versions of modified reports and/or forms you may have overlap. You will need to pick which DYNAMCIS database will become the new master database and then use GP Utilities to create new companies using this database. Once the databases are created you can use SQL to restore the company databases over the recently created ones. It is not an easy process but it can be done. I would make sure you have backups in place before you start plus consult your GP reseller.

Was this reply helpful? Yes No
Muhammad Imran Ali 455 on at

Like (0)

Report

Tim Wappat,

I don't want to merge all companies into one DYNAMICS database. i just want to rename of DYNAMICS databases and want to merge all these DYNAMICS databases and companies databases in to one SQL Server Instance to save the SQL Licence Cost. Is is possible?

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Muhammad,

Richard's answer above is accurate. In order to merge all companies into one DYNAMICS system database, you would need to first ensure their account frameworks are compatible by checking the SY003001 and SY00302 tables in each DYNAMICS DB.

If they are compatible, it is typical to pick one of the DYNAMICS databases as the one DYNAMICS database you would keep. It is conceivable to create a new DYNAMICS database.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Just to be clear, when you put all the company databases onto one SQL Server instance you can only have a single DYNAMICS database.

Another point to clarify, might we change the wording from MERGE to MOVE - merge suggests a coming together of the databases in a different maner than that of MOVE

All in all the suggestions are rock solid and on the point.

Was this reply helpful? Yes No
Muhammad Imran Ali 455 on at

Like (0)

Report

Bill Campbell,

Yes i don't want to merge all companies in to one DYNAMICS database. I want to move 4 Instances of DYNAMICS databases in to ONE SQL Server Instance. I Have 4 Instances of DYNAMICS DBs from Which ONE DYNAMICS have 10 companies, 2nd have 7 companies, 3rd have 1 company and 4th have one company. I just want to move these four instances of DYNAMICS dbs in to ONE SQL Server Instance by Renaming of DYNAMICS DB Databases.

Was this reply helpful? Yes No
Suggested answer

Bill Campbell 12 on at

Like (0)

Report

Muhammad, the DYNAMICS DB does not contain company operational data - it is the control database for the overall management of the Dynamics Companies you have created.

Each instance has to have one and only one DYNAMICS db and then as many individual company databases as is needed for the operation.

If you want to put all 19 companies onto 1 SQL instance you still only need 1 Dynamics database. To accomplish this, you need to follow the advice provided by Richard and Harry on how to create a single DYNAMICS (control) Database.

If you are trying to consolidate your SQL Instances, then I would suggest as by others that you choose one of the instances (my suggestion would be the one with 17 companies) use that as your base. If that is the SQL Server Instance you want to keep, all the better.

With the DYNAMICS db selected, and the 17 COmpany DB's already in place, what you need to do now is create the other 6 (six) company databases using the Dynamics Utilities pointed at this instance. Once they are created, you would then restore the 6 databases from their last backups to the new SQL Instance.

You will likely have to look at the security settings for the 'new' companies once you have moved them since you will not be copying the logins and adjust them as needed.

Hope this helps and adds some clarification to what Harry and Richards (and others) have suggested above.

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Beginning with GP 2013 R2, you can have multiple system (DYNAMICS) databases in one SQL instance because you can choose a name for the system DB other than DYNAMICS when you install GP. I currently have two instances of GP running on my server - 2013 R2 and 2015. Their system databases are named DYN2013R2 and DYN_GP2015, respectively. I believe this is what you're trying to accomplish, correct Muhammad?

If it were me, rather than renaming the existing DYNAMICS database, I would leave it as named and create 3 other system DBs. It might not hurt to rename it, but I would respect the rule - "If it ain't broke, don't fix it".

Was this reply helpful? Yes No
Muhammad Imran Ali 455 on at

Like (0)

Report

Frank Hamelly,

Yes you are right, I want to do the same with GP 2013 SP2 (Version 12.00.1538) with already existing 4 DYNAMICS databases. If i rename these database like (DYN01, DYN02,DYN03 and DYN04) and move it in to one SQL Server Instance (Currently they are in 4 different SQL Instances because of same DYNAMICS database name). Is is possible to rename these already existing DYNAMICS database ? If i try to rename these databases then what challenges i might face during this process?

Thanks

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

Just to be clear, the end result of this work will be to take the 4 different DYNAMICS databases that exist on 4 different servers under 4 different instances of SQL and to have one server with one instance of SQL with 4 DYNAMICS databases? Is this correct?

Was this reply helpful? Yes No