Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Suggested answer

Converting All Data from One Environment to Another

Posted on by Microsoft Employee

Hi:

A GP 18.2 client upgrading from GP 2016 wants their data migration conducted unconventionally. 

They have GP 18.2 installed in a a test environment with a data import tool, and they want all fourteen years of their transaction based and master data imported into this new environment.  Notice that I said "imported" rather simply upgraded as a part of the GP Utilities process.

First, would some sort of direct table to table import make the most sense?

Secondly their main company database is 280GB and the list below shows the number of records within the major SQL tables not counting third-party tables.

So, would the amount of time required to convert this data be more like weeks or even months rather than on a weekend?

Thanks!

John

Table ID Table Name # Records
GL10000 GL Transaction Work 16
GL20000 Year to Date Transaction Open 1,121,821
GL30000 Account Transaction History 10,886,276
PM00200 PM Vendor Master 7,761
PM10000 PM Transaction Work 6
PM20000 PM Transaction Open 182
PM30200 PM Paid Transaction History 1,029,050
PM30300 PM Apply to History 896,341
RM00101 RM Customer Master 35,346
RM10301 RM Sales Work 0
RM10201 RM Cash Receipts Work 171
RM20101 RM Open File 924,317
RM20201 RM Apply Open  515,878
RM30101 RM History File 1,076,141
IV00101 Item Master 1,514
IV10000 Inventory Transaction Work 1
IV30200 Inventory Transaction History 27,495
IV30300 Inventory Transaction Amounts History 9,481,291
CO00101 Document Attachment Master 48,395
SOP30200 Sales Transaction History 22,229,024
POP30300 Purchase Receipt History 14,266
FA00100 Fixed Assets Master 60,657
FA00200 Fixed Assets Book Master 60,656
FA00902 Asset Financial Detail Master 4,537,350
CN00100 Collections - Notes 650,494
  • RE: Converting All Data from One Environment to Another

    Hello John, 

    It looks like Gerald and Richard have already hit all of the high points.

    Basically it is highly recommended that you never copy GP data between versions unless you are fully aware of every single table and data change that would be required. This is usually just not feasible. Plus there are other benefits to using GP Utilities to do the upgrade, such as some data validation and the fact that we (The GP Support Team) will be onboard to assist. We would be unlikely to be able to assist if you are just moving the data since this is not really supported. 

    Let us know if you have further questions. 

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Converting All Data from One Environment to Another

    Sounds like they are trying to clean up old data. I would suggest they try scrubbing the data first. Look for old customers and vendors that are no longer used. Then only transfer data for the customers and vendors they want to keep. This will be a labor intensive process but if the budget is there then go for it. If it just all old data they want removed there is the Company Data Archive product from Professional Advantage. There are several ways to tackle this project. As previously suggested having virtual servers is a good start. Once you know which data will be kept do a sample move and then a sample upgrade. This will give you an idea how long the process will take. Do not try to copy old data to a new version of GP. Data structure changes will make that difficult. You can also drop me a private message and we can discuss this offline. It can't hurt to solicit as many opinions as possible.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Converting All Data from One Environment to Another

    Oh, yes.  I know these steps very well.

    I'm just trying to accommodate the client's request.  :)

    John

  • Suggested answer
    Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Converting All Data from One Environment to Another

    John,

    Here is the link on how to move the data to a new SQL Server.

    support.microsoft.com/.../how-to-transfer-an-existing-microsoft-dynamics-gp-microsoft-small-busi

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Converting All Data from One Environment to Another

    Thank you, for the advice, Gerald!  I'll discuss this with my supervisors!

    John

  • Suggested answer
    Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Converting All Data from One Environment to Another

    Hi John,

    Copying Data has a big risk because its not only transactions that you are going to worry but it includes all the setup. Report Options and so on.

    Suggestions is, move the data to the new Instance of SQL Server (GP) and from there you do your GP Upgrade to the latest GP.  I have done 103 databases and was able to upgrade in 32hours for just the GP data not including third Party.  It also depends to the resource of the new sql server.  So before you upgrade, if it is a virtual server, beefed up the server so it will be faster.

  • Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Converting All Data from One Environment to Another

    Zero. :)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Converting All Data from One Environment to Another

    Gerald:

    How much would it cost to discuss?

    John

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Converting All Data from One Environment to Another

    Hi Gerald:

    That's a good question.  I'm coming in "after the fact", so I do not know the reasoning behind not doing it this way.

    They don't want any data removed.  

    And, since they're going from one GP instance to another that's why I was wondering about doing a direct table to table conversion, though I know that differences in version could make some of this prohibitive.

    Thanks!

    John

  • Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Converting All Data from One Environment to Another

    Hi,

    Why not upgrade them from GP 16 to GP 2018.2? Is it the volume of Data or they want to remove to remove some data?

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,556 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,625 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans