Data migration

Existing System is where you hand over what your current system knows. Data migration is where its records follow. If the new app replaces something that has been running for years, the records are usually the part that matters most, and moving them is the part people are most afraid of. This page exists to make it boring.

It is three jobs, not one

Moving data between two different systems is three separate problems, and mixing them up is how migrations go wrong.

1. Reading it out. Getting the rows out of the old system, whatever it runs on.
2. Working out what becomes what. Your old tblCustomer is not the new customers. A single old record might become a customer and an address. CustType = 3 might mean “internal”. This is the whole job, and it is the part nobody but you can confirm.
3. Putting it in safely. Nothing goes into your Live data unchecked.

The page is a row of steps down the left. Every step but the last describes the job; only the last one runs it:

1. Who looks after it? — your team, or an expert.
2. Where is the old data? — an export, a dump, or a read-only connection.
3. What it holds — its tables, their columns and how many rows each has.
4. What to bring — leave tables out, and narrow the rest to the rows worth keeping.
5. What becomes what — how each old table fits the new system, decision by decision.
6. Run it — read the old data, load it, and check what landed. As often as you like.

A step opens once the one before it has given it something to work on, and the page opens on the first step that is waiting for you.

Who looks after it

Moving data out of an old system goes best with someone who reads SQL and knows what the old data means watching it — they check the decisions and read each rehearsal’s report. If nobody on your team does, Ask an expert on the first step. It sends a request that already says what the page knows about your source; the expert quotes first, and works on the same steps with you.

Where the old data comes from

Add a source and we read what it holds — its tables, their columns, how many rows each has and a sample of what the values look like. None of the data is copied: it stays where it is until a run reads it. A large table is counted roughly rather than held up for an exact count, and the page marks those numbers with ≈.

What we can read today:

• Files exported from the old system — CSV, Excel (.xlsx), JSON, or a .zip of them. This covers Salesforce, HubSpot, Airtable, Bubble, Notion, spreadsheets, and the “export to CSV” button in almost any business system. One file becomes one table.
• A database dump — a Postgres dump file, plain or compressed.
• A read-only connection to a Postgres, MySQL / MariaDB, SQL Server or Oracle database.

Prefer the export. Opening a firewall to your database is a ticket, a security review and a week. A file uploaded on a Friday evening is none of those, and nothing about the rest of the migration changes.

A SQL Server backup (.bak), a MySQL dump or an Oracle Data Pump file can’t be opened yet: connect to the database instead, or export the tables you need as CSV or Excel. For MongoDB, export each collection as JSON.

Connecting to MySQL, SQL Server or Oracle

Pick A read-only database connection, then the database. Fill in the host, port, database (for Oracle, the service name) and the read-only account. Or paste the connection string you already have: a URL, the Server=…;Database=… line from the old app’s web.config, or an Oracle host:1521/SERVICE.

• Which versions. MySQL 5.6 or later and MariaDB 10, SQL Server 2012 or later (and Azure SQL, with a SQL login or a Windows account), Oracle 12.1 or later. Older Oracle databases, and ones that insist on Oracle’s own network encryption, need the export path for now.
• The account. The dialog shows the one script your DBA runs. For MySQL, SELECT on the database. For SQL Server, membership of db_datareader. For Oracle, READ on the tables’ owner. Test connection tells you if the account can also change data. Sapilon only ever reads, but it is worth asking for one that can’t.
• Getting through the firewall. The database has to accept connections from Sapilon. The dialog shows the address to allow in. If there isn’t one yet, ask support.
• One schema per source. For SQL Server that is dbo unless you name another. For Oracle it is the tables’ owner. Tables in a second schema are a second source.
• It never holds your database. Reading the structure asks short questions with a time limit. A run reads every table from one snapshot where the database offers one (MySQL, Oracle, and SQL Server with snapshot isolation on). A SQL Server without it takes brief read locks as it goes, and the structure step warns you, so you can run it out of hours.
• Conditions are in the old database’s SQL. On What to bring, a condition runs on the old database, so write it the way that database does. DATEADD works for SQL Server, ADD_MONTHS for Oracle. Use the old table’s own column names.
• Names are simplified. tblCustomer.CustID arrives as tblcustomer.custid. The structure step shows the old spelling beside it.
• Every value arrives exactly as the old system holds it, dates and all. The quirks each database has are dealt with where the mapping is written, and each one is a decision you can read:
  • text keys that SQL Server and MySQL compared regardless of case and trailing spaces;
  • Oracle’s empty text that is really “nothing”;
  • MySQL’s 0000-00-00 dates;
  • CHAR columns padded with spaces.

What happens to the file

An export of a live system is sensitive from the first byte. It is stored encrypted, readable only by the job that reads it in, and deleted when you close the migration out or after 30 days, whichever comes first. The page shows you that date. A connection string is stored encrypted, never shown again, and deleted with the source.

What to bring

Not all of the old data is worth moving. On What to bring every old table has a tick box and a condition:

• Untick a table and no run brings it. The mapping will not be asked to place it.
• Write a condition to keep only some of its rows — created_at >= '2018-01-01', status <> 'deleted'. It is SQL on the old table’s own columns. Columns read from uploaded files are text, so compare dates written the way the file writes them.

A condition is checked the moment you save it, so a misspelled column is caught there rather than in the middle of a run. For a live connection the page also shows how many rows each condition keeps.

What you choose here is part of the migration: change it after a rehearsal and you rehearse again before going Live, just as you would after changing the mapping.

What becomes what

Once the structure is read, ask for a proposed mapping. Your project’s agent reads the old tables you bring and the new schema, and writes one card for each table in the new system. A card is a list of decisions and nothing else:

• where its rows come from — “Each record of old.tblcustomer becomes a row of customers”,
• what identifies a record in the old system,
• the assumptions it had to make, in plain language — “CustType 1/2/3 read as retail, trade, internal”,
• which old columns are not carried, and which new ones are left empty.

Point at a line and two buttons appear: accept it (green), or disagree (orange) and say what is wrong. A table is confirmed once every line on it is accepted. Anything you disagreed with can go back to the agent with Ask AI to fix, or be fixed by hand with Resolve; either way the changed lines come back for you to accept. Old tables that nothing reads get a card of their own — leaving a table behind is a decision too.

Values the new system doesn’t have

Old systems have their own words for things — a step can be Canceled where the new app only knows planned, unplanned, overdue and done. The agent sees both lists while it writes the mapping. When an old value has no match, it picks the closest one and says so in a decision — “Canceled has no match in the new system — mapped to unplanned” — so you can accept it, or disagree and ask for something else. If the answer is that the new app should have a Canceled status too, that is a change to your app: ask your project’s agent for it, then fix the mapping.

Each card also checks the values for you before anything runs. Under the decisions, a small table shows what every value the old column holds becomes — Canceled → unplanned — and a value the new system would refuse is flagged as Values the new system doesn’t have, with Fix with AI beside it. The check looks at the values it saw when it read the structure — the common ones, not necessarily every rare one; the run checks all of them.

A table can also start empty: don’t carry it, and say why.

That last one is a real answer. Nine years of audit log that nobody reads does not have to come with you, and writing down why means the decision is still findable when somebody asks in two years.

The mapping is kept with your code, so every change to it is a version you can look back at.

Rehearse until it is boring

Run it is the only step that touches data. Pick a server — the Build Server while you are still designing, the Rehearsal Server to drill it, the Live Server when it is time — and press Run it. Each run reads the old data fresh from the source — the tables and rows you chose to bring — loads it through the mapping, and checks what landed. It is safe, it is repeatable, and each run replaces what the one before it loaded. Run it as many times as you like.

Before a run writes anything, it checks every row it read against the new schema: a value the new system does not have, a value that is not a date or a number where one is needed, text too long for its column, an empty value where one is required. If anything does not fit, the run stops there — nothing is backed up, nothing is written — and its report lists each problem with the values and how many rows carry them.

Nothing of the old data is kept between runs: when a run finishes, the copy it read is thrown away, and the next run reads the source again.

Every run produces a report, in the order that matters:

• How many records went in, and what was left out and why. Records being left out is not automatically wrong; records being left out for a reason nobody can explain is.
• Do the totals match — for money and quantity columns, the old total against the new one. “Orders 2019–2025: €4,182,336.20 in both” is the most persuasive line in the report.
• Records pointing at nothing — an order whose customer did not come across. This fails a run outright.
• Every code, and what it became — each distinct value of a status or type column, and what it turned into. A value that became nothing is the most common real problem, and the easiest to see.
• Columns that changed shape — anything that got shorter or emptier on the way in.

If the report comes back worth a look, read it and either fix the mapping and rehearse again, or accept it — which records that a person looked at the warnings and was not surprised.

When a run fails

Open the run and What went wrong says whose problem it is, and offers the way out that fits:

• Your data or your old system — values that do not fit, or the old database refusing the connection. Fix with AI changes the tables the problems are in so their values fit; then run it again. If you would rather hand it over, Ask an expert — an expert looks at your data, and you get a quote before anything is charged.
• Sapilon — SQL that does not run, a step that broke, one of our servers not answering. Report to Sapilon sends us the run with everything it knows. It is free: you are never charged for a problem in Sapilon.

The other choice is always there too. If you think a problem we put down to your data is really ours, report it — a bug report is free whatever it turns out to be.

Going Live

A run into the Live Server is picked in Run it, like any other; the Go Live step is where it meets the freeze, the sign-off and the web address, because those things happen together.

If your project has a Rehearsal Server, the rule the product enforces is simple: the Live Server only ever runs a migration that has already been rehearsed cleanly, with exactly the same mapping. Change a single transform after your last rehearsal and you rehearse again. This is the one practice that separates migrations that work from migrations that make the news.

Without a Rehearsal Server there is nowhere to drill, and your production data is never copied into the Build Server to stand in for one. A Live run then goes ahead on its own safeguards: it backs the Live database up first, checks every row before writing, and takes back what it loaded if it fails.

Going Live also asks you three things worth deciding in advance:

• How will the old system be frozen? Read-only overnight is enough for most businesses. If it cannot be frozen at all, say so — that is an expert job.
• What happens to the old accounts? Usually: invite people to set a new password. Their records are already theirs, because the migration keeps track of which old record became which new one.
• How long does the old system stay reachable, and who decides whether to roll back? Two weeks and a named person is the usual answer.

Running it twice is safe

Every record carries its old identifier, and the migration remembers which new record each old one became — on each server separately, so the same customer has one identifier on the Rehearsal Server and another on the Live Server. Until the Live Server is open, a run replaces what the run before it loaded: it takes those records out and loads them again from the fixed mapping, so nothing stale is left behind.

Finding a record

Under the runs, Find a record takes an old key, or a name from the new record, and a server. It shows the record as it is there now, which run brought it and which run last changed it — and, when the old system is a live connection, the old record beside it.

Two things you can do from any record:

• This is wrong — say what is wrong in your own words. It lands as a comment on that table in What becomes what, with the record quoted, so Fix with AI has the evidence. If records like it should not have come at all, go to What to bring instead.
• Pin — every run after this one shows the pinned record again, old beside new, at the top of its report, until you unpin it. A mistake you found once stays in front of you, so a fix to another table can’t quietly bring it back.

There is no separate “verify” step: checking what landed is what every run’s report does. A mistake always goes back to the step that caused it, and the next run carries the fix.

Fixing a mistake after going Live

Once your project is Live, people are working in the Live Server: new orders point at migrated customers, and links to records are out there. Replacing migrated records now would delete people’s work or break those links, so the product refuses it. A run into the Live Server becomes a correction instead:

• it reruns only the tables whose mapping changed since the last run into Live;
• every record keeps its identifier;
• records someone edited in the app since they were migrated are left alone and listed — you can choose to overwrite a table’s edited records, but it is never the default;
• records the fixed mapping no longer brings are listed, not deleted. You can remove them only when nothing people created in the app still uses them.

Preview correction comes first and writes nothing: it shows, per table, what would be added, updated and left alone. When it looks right, Apply it — typed confirmation, a backup of the Live database first, and all tables in one go, so if anything fails nothing changes. With a Rehearsal Server, the fixed mapping still has to run cleanly there first.

Seeing what is on each server

The Database page reads the Build Server by default. Once your project has reached its servers, a picker at the top switches the whole page — tables, columns and rows — to the Rehearsal or Live Server, so you never see one server’s rows under another’s columns. If that server is behind the Build Server, the page says which changes it is missing. Other servers are read-only, sensitive columns are masked (the owner can show one, and that is recorded), and every look at Live data is recorded.

Closing out

When the old system is finally retired, close out the source. That drops what is left of the migration’s working area, deletes the uploaded files and removes the stored connection, so the old data stops living anywhere but the new system.