How to migrate your Dynamics GP data without losing history

A GP data migration is a rebuild, not a copy

The first thing to accept is that you cannot lift your GP databases and drop them into a new ERP. GP stores its data in SQL Server tables with its own structure: GL20000 and GL30000 for open and historical general ledger transactions, RM tables for receivables, PM tables for payables, IV tables for inventory, and a separate database per company plus the shared DYNAMICS system database that holds security, multicurrency setup, and the list of companies itself. Your new system has a different chart of accounts structure, different customer and vendor records, and a different transaction model entirely. There is no export button that solves this.

So a migration is really a rebuild of your data in a new shape, not a copy of it. That reframing matters because it changes the question from “how do we move everything” to “what does the new system need on day one, and where does everything else live.” Companies that ask the first question end up with bloated, expensive projects that try to force twenty years of GP history into a system that was never designed to hold it. Companies that ask the second one finish on time, with a system that is fast and clean from the first login.

The deadline pressure is real but manageable. Microsoft stopped selling new GP perpetual licenses on April 1, 2025, and will stop all new subscription sales on April 1, 2026, meaning no organization will be able to newly acquire Dynamics GP in any form after that date.1 Microsoft ends product enhancements, tax and regulatory updates, and standard support on December 31, 2029, and ends all security patching on April 30, 2031.2 Those are firm, Microsoft-published dates, not rumors. You have time to plan a migration properly. You do not have time to do it twice, and rushing a data migration is exactly how organizations end up in the group of projects that blow their budget and schedule, which research puts at a striking share of all ERP work.

Those numbers are not an argument against migrating off GP. They are an argument for treating the data work as its own discipline, with its own owner, its own timeline, and its own sign-off, rather than as a checkbox inside a broader software rollout.

What GP’s data actually looks like under the hood

Before you can decide what moves, it helps to know what you are actually looking at. Every GP company is its own SQL Server database, typically named something like TWO for a test company or a client-specific code for production, and every one of those databases shares a single DYNAMICS system database that stores company definitions, user security, posting setup, and multicurrency configuration. If you run five legal entities in GP, you have five company databases plus one system database, and migration planning has to account for all of them, including whatever intercompany relationships tie them together.

Inside each company database, the general ledger keeps open-year transactions in the GL20000 table and transactions from closed years in GL30000, which is why “how many years of GL history do we actually need” is a real design decision rather than a technicality. Receivables live across the RM series, with document-level detail and open balances that drive your aging reports. Payables mirror that in the PM series. Inventory quantities, costs, and item history sit in the IV tables. On top of the raw tables, most GP shops have layered SmartLists, Management Reporter or Jet Reports feeds, and sometimes a data warehouse, all of which pull from this same structure and all of which need to be re-pointed or rebuilt once the source system changes.

None of this is exotic engineering. It is just structure you need to respect, because every mapping decision in the migration, from chart of accounts to customer records to open documents, ultimately traces back to one of these table families.

What to migrate and what to archive

The dividing line is simple: the new system needs what is required to operate, and everything else is history you need to be able to look up, not history the new system needs to carry around forever.

One GP-specific note that trips up otherwise careful teams: GP distinguishes open from historical transactions internally, which is exactly why there are separate 20000-series and 30000-series tables for the general ledger and equivalents across the other modules. Run your paid transaction removal routine and reconcile GP itself before you extract anything, so that open-versus-historical split is clean at the source. Skip this step and your open AR and AP extracts will quietly include documents that are actually settled, and you will spend the reconciliation phase chasing balances that were never real to begin with.

Deciding how much history is enough

“How many years” is the question every finance leader asks and almost nobody answers with a clear rule. Use these four factors instead of a gut feeling.

Audit and tax retention requirements set a floor. Most jurisdictions expect supporting detail for several years back, so your archive, not necessarily your live system, needs to reach that far. Loan covenants and lender reporting sometimes require multi-year comparatives inside the operating system itself, which is a reason to bring in GL summary history even when you would otherwise leave it behind. Warranty and service obligations matter if you sell anything with a multi-year warranty tail; you may need transaction-level detail for those specific items even while archiving everything else. And simple usability matters too: nobody opens a new ERP wanting to scroll past a decade of closed invoices to find last month’s activity, so more history inside the live system is not automatically better for the people using it every day.

Weigh those four against each other, write down the answer as an explicit policy, a number of years for GL summary and a number of years for open-document detail, and get your controller and auditors to sign it before anyone starts mapping fields. A documented policy is also what protects you later, when someone asks why 2019 detail lives in an archive instead of the new system.

Choosing your extraction method

GP shops generally extract in one of three ways, and the right choice depends on volume and internal skill, not on which tool is fashionable. Direct SQL extraction, querying the GP tables and views yourself and staging the results, gives the most control and is usually the cheapest option if you already have someone who knows the schema. GP’s own integration tooling and third-party integration platforms built for GP can move data in bulk using GP’s documented business logic rather than raw table writes, which reduces the risk of creating records GP itself would consider invalid. And for smaller or simpler migrations, a consultant-run extract and load, done once, reviewed twice, is often faster and cheaper than building reusable tooling for a one-time event.

Whichever method you pick, the rule does not change: extract into a staging area first, validate there, and only then load into the destination. Never point a load process directly at GP’s live tables or at the new system’s production tables without a staging and validation step in between.

The migration steps in order

1. Inventory your data and volumes. List every company database, count records in the major tables (customers, vendors, items, GL accounts, open AR and AP documents, years of GL history), and note any multi-company or intercompany setup. This inventory drives scope and cost, and it surfaces surprises early, like a test company nobody remembers, a second currency, or a module nobody has used in years but still holds open documents.

2. Decide open versus historical. Set the policy explicitly: what moves as operating data, how many years of GL summary history move for comparatives, and what goes to the archive. Get your controller and your auditors to sign off on this before anyone writes a mapping, using the retention, covenant, and warranty factors above to justify the cutoff you choose.

3. Map master records. Define how each GP customer, vendor, item, and account maps to the new system’s structure. The chart of accounts mapping is the big one: this is where you collapse segments you no longer use, split ones you do, and document every old-to-new account pairing. That mapping document becomes the Rosetta Stone for reconciliation later, so keep it in a format everyone on the project can read, not buried in a developer’s spreadsheet.

4. Cleanse before you move. Merge duplicate customers and vendors, close out stale open documents, write off ancient pennies that nobody is ever going to collect, post or delete every unposted batch, and complete any pending year-end closes. Cleansing in GP, where you know the data and still have the system that created it, is far cheaper than cleansing after import, when every fix has to go through the new system’s validation rules instead.

5. Migrate balances and open documents. Load master records first, then opening GL balances as of a clean cutoff date (a month end, ideally a year end), then open AR and AP at document level so aging still works correctly from day one, then open purchase orders, sales orders, and inventory quantities. Do this in a test environment first, at least twice, before the real load, and treat each test load as a full dress rehearsal, not a quick sanity check.

6. Reconcile to the penny. Trial balance in GP versus trial balance in the new system, on the same date, mapped through your account mapping document. AR aging totals must match. AP aging totals must match. Inventory valuation must match, or the variance must be explained line by line and documented. “Close enough” is not a reconciliation, and a controller who signs off on an approximate number today is signing up to defend it to an auditor later.

7. Validate with the people who use the data. Have AR staff pull up their ten largest customers and confirm the open documents look right, not just the totals. Have AP confirm the next payment run matches what GP would have paid, down to the invoice. Run the first month end in parallel if the timeline allows. Sign-off should come from the people who will actually use the numbers every day, not from the migration team grading its own work.

Reconciliation is the whole game

The difference between a migration people trust and one they quietly work around is reconciliation discipline. Three habits make it work.

Reconcile at every stage, not just at the end. After master records load, count them against the source system record for record. After balances load, tie the trial balance before you move on to the next step. Finding a variance early means writing one correction script. Finding the same variance at cutover means forensic work under pressure, with a go-live date bearing down on you and finance asking why the numbers do not match.

Freeze the source during final extraction. Pick the cutover date, stop posting in GP, extract, load, reconcile, and only then let users into the new system. Companies that keep posting in GP “just one more batch” during the load are the ones that end up off by exactly one batch, and exactly one batch is enough to make everyone distrust the whole migration.

Keep the evidence. Save the extracts, the mapping document, and the signed reconciliations somewhere durable. Your auditors will ask about the year the numbers changed systems, and a folder that ties GP’s final trial balance to the new system’s opening one, with a name attached to the sign-off, ends that conversation in five minutes instead of a week.

Where these projects actually fail

The failure modes repeat across companies, so it is worth naming them plainly. This is also where the industry-wide research and the GP-specific reality line up: a widely cited Gartner benchmark found that 83 percent of data migration projects either fail outright or exceed their planned budget and schedule, a finding from 2009 that the data-management industry still treats as the standard warning about how often this specific kind of work goes wrong.3 More recent, independently surveyed evidence backs the same pattern at the ERP-project level: in Panorama Consulting’s 2024 ERP Report, data issues were named as a contributing cause by 34.9 percent of organizations that went over budget and 46.3 percent of organizations that went over schedule, making data problems one of the most commonly cited reasons ERP projects run long or run over.4

Trying to migrate everything. The project balloons, testing never finishes, and go-live slips past a fiscal year boundary, which makes everything harder, including the reconciliation you eventually have to do anyway.

Skipping cleanup. Duplicate vendors and dirty item records get faithfully migrated, and the new system starts life with GP’s accumulated mess plus a brand-new interface layered on top of it.

Ignoring unposted batches and half-done closes. Balances extracted from a GP company with unposted batches or an incomplete year-end close will not tie to anything, no matter how carefully the rest of the migration is executed.

Reconciling in totals only. The trial balance ties but AR aging is wrong at the document level, and collections finds out three weeks after go-live, when a customer disputes an invoice that migrated with the wrong due date.

No named owner for the numbers. Someone, usually the controller, has to own the statement “the opening balances are correct.” If nobody will sign that sentence, the migration is not done, whatever the project plan says.

None of this is exotic. It is careful, unglamorous work with a clear finish line: a new system whose opening numbers tie to GP’s closing numbers, and an archive that answers every question about the years before. Get those two things right and you have not lost any history at all. You have just stopped paying to keep it running.