Microsoft Dynamics GP (Archived)

Migrate One Company Database to Another Instance of SQL with other company databases

(0) Share

Report

Posted on by Rennecia Barran

Scenario:

SQL Instance A has one company and one dynamics database

SQL Instance B has Six companies and One Dynamics Database

Desired Outcome:

All Companies on One Instance with One Dynamics Database so as to obtain consolidated financials.

Hence, I want to migrate the One Company from Instance A to Instance B and allow it to use the Dynamics Database on Instance B.

Questions:

1. Can this be done?

2. How can I achieve this?

3. I know currencies are shared between the databases so I am checking to confirm the currency IDs will be different. What else can I look out for that can cause issues?

*This post is locked for comments

All responses (9)

Answers (3)

Derek Albaugh on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

Correct, the account framework maximums are system settings, meaning that all company databases must fall under them or within.

For example, you could have a maximum setup of 10 segments each with a length of 7, but one company may only use 4 segments and another may only use 3. They just wouldn't be able to use more than 10 segments, in this example, or have a segment length more than 7.
Rennecia Barran on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

Ahh, I see.

From what I have been told. The maximums are the same across the companies. Only the structure is different.

Thanks for your expert advice!
Verified answer

Derek Albaugh on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

There's no KB article that has this list, again, it's more consulting/advisory, so we don't have a lot of information out on it.

ASIEXP81 is the only system table that gets setup as per the account framework maximums.

For the company databases, there are about 30+ tables in the core 'Microsoft Dynamics GP' module, then some in the following modules:

--Electronic Bank Reconcile (2)

--Canadian Payroll (16)

--Revenue Expense Deferrals (1)

--Project Accounting (1)

--Intercompany (1)

--Control Account Management (1)

--Analytical Accounting (2)

--Cashbook/Bank Management (30+)

You can use the SQL Maintenance window to drop and re-create these table dex procedures for these modules, per company database that you're changing, and you should be fine.

For indexes, triggers, etc., you can use the Database Maintenance Utility to drop and re-create those per module, per company database.

The biggest thing I was referencing, is that if you're changing tables in a company database to have/use more segments than what it was originally using, make sure the dexterity procedures (zDP_ ) know about those extra columns, otherwise you'll run into errors such as 'amount of columns provided doesn't match table definition' or something along those lines.

Thanks
Rennecia Barran on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

Thanks for your quick response Derek.

What indexes and dex procedures, etc do I need to be aware of?

Is there a KB article from Microsoft with all the details?
Derek Albaugh on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

Since we provide this as a advisory service, we don't have any scripts to change account framework maximums.

What you'll want to do, if the account framework maximums do not match, is look in the SY00300 table in each company database, which will show you how many segments that company actually does use, then plan out the account framework maximums accordingly, to accommodate all company databases from both environments.

Sometimes this requires a change to the company databases being added to the existing environment, sometimes it requires us to change the existing system to be larger......

While PSTL will help change tables to have a desired structure with data intact, don't forget all the related objects such as indexes, dex procedures, etc.
Rennecia Barran on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

One question Jeffrey/Derek,

I am using PSTL to make the account segments consistent. If i do have an issue where the account framework maximums do not match (I have enough space within the segment setup to get the accounts to the format i need), is there a script I need to run to make them the same?
David Musgrave MVP ... 14,036 Most Valuable Professional on at

Like (1)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

Hi Ali

GP Power Tools Database Validation feature can recreate all the logins and database users needed after you migrate, it can also update table structures to make the Account Frameworks match.

GP Power Tools Portal: http://WinthropDC.com/GPPT

David
Verified answer

Derek Albaugh on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

Jeffrey, you're correct.

The first thing we look at when consolidating Dynamics GP environments is first the SQL collation/sort order setup for each of the two environments as they must be identical.

Second, we check the account framework maximums (i.e. in the SY003001 and SY00302 system tables), as these must match identically as well.

Third, whichever DYNAMICS/system database that is going to be used, must be setup for whatever currencies are being used by each company database and the setup must match as well, such as the ISOCURRC values, currency ID and number values, etc.

If all three of these are correct, then you can look at combining the two Dynamics GP environments, but if any of these three are not correct, it'll need to be fixed before you can combine the two environments.

We would also recommend that both environments be on the exact same version/build of Dynamics GP, as well as have the same GP modules installed, whether they are used or not.

We see this type of process done frequently, it's just that it can involve a lot of work and setup depending on how different the two environments are.

Thank you,
Verified answer

twelvestrikes 3,657 on at

Like (0)

Report

RE: Migrate One Company Database to Another Instance of SQL with other company databases

Hi Ali

First thing to check is that the SQL sort order is the same on both instances. Run sp_helpsort. If they are different this will be harder as you may need to work with Microsoft Professional services to accomplish what you are trying to do. If the sort orders are the same then onto the next step.

Next thing to check is the account framework on both instances. If they match then you have cleared hurdle one. If they don't, you have a show stopper. Fortunately there is a good 3rd party product you can use called Account framework reformatter from CRG (Corporate Renaissance Group in Ottawa).

This product will allow you to change the account format of the company to be brought in.

I can't stress enough that before you do anything that you work in a test environment and go through all the steps from beginning to end.

You may need to modify GL accounts as well for the company coming in. So that will have to be done as well.

You can create the company for company being transferred in the larger GP instance. Restore the revised company database into the new Dynamics GP environment.

Assign currency as you noted that you may need to create new currency id's to match what you have in the other instance. Create your users and security settings.

You can start off with this.

If you are unsure you should engage the services of your partner for this