SQL Server to PostgreSQL: The Deep Migration War Story

01 · BUSINESS PROBLEM

The license bill said migrate; the code said otherwise

A regional property & casualty insurer ran core policy administration, claims, and billing on SQL Server — an estate grown over more than a decade of acquisitions, custom development, and "temporary" integrations that became permanent. The economics pointed one way: the SQL Server licensing position was getting worse with every core added, and PostgreSQL was the strategic target. The code pointed the other way: this was not a lift-and-shift estate. It was a procedural estate — stored procedures carrying real business logic, cursor-driven adjudication rules, dynamic SQL built at runtime, and a halo of SSIS packages, SSRS reports, and SQL Agent jobs nobody had fully catalogued. The migration tooling would happily convert the easy majority. The engagement was about the remainder — the part that decides whether cutover night ends in celebration or in a rollback.

02 · INDUSTRY

Anonymized

Industry

A regional property & casualty insurer — policy administration, claims processing, and billing. A different anonymized organization from the national-bank program story; this account goes deeper on the conversion war itself, not the program wrapper.

Anonymization

Client name, brands, and identifying details withheld. Presented as experience informing AnovaCloud's methodology, not as an AnovaCloud delivery claim.

03 · SCALE

Orders of magnitude that shaped the design

Estate

Dozens of SQL Server instances across production, DR, and non-production — core transactional systems plus reporting replicas, each with its own job schedules and linked-server webs.

Code surface

A large procedural footprint: stored procedures, functions, and triggers numbering in the thousands, many carrying claims and billing business rules accreted over years.

Satellite systems

A substantial SSIS package inventory, an SSRS report catalog in active daily use, and SQL Agent jobs running everything from ETL triggers to maintenance to business alerts.

Recovery bar

Claims and billing are revenue-adjacent and regulator-visible. Downtime windows were short, rollback had to be real, and "we'll fix it Monday" was not an available strategy.

04 · ESTATE INVENTORY

How the real inventory was built — and what the tools missed

The engagement started the way these always start: with the vendor assessment tool's report — object counts, conversion-complexity scores, a comforting percentage marked "automatically convertible." That report was treated as a floor, not an inventory. The real inventory was built in three layers:

What the automated tools missed — the list that became the project's actual risk register:

• Dynamic SQL: unparseable at rest; only runtime capture revealed the real query shapes.
• Application-side SQL: hand-written and ORM-generated T-SQL embedded in application code — never in any database catalog, never in the tool's scope.
• Linked-server hops inside procedures: the tool sees the linked server; it doesn't trace the multi-hop chain a procedure walks in the small hours.
• Jobs owned by the departed: SQL Agent jobs whose owners left years ago, running on borrowed credentials nobody dared rotate.
• Shadow scheduling: SSIS packages launched from Windows Task Scheduler and third-party schedulers, invisible to msdb.
• Reports with embedded SQL: SSRS datasets carrying their own T-SQL — each a mini-migration of its own.
• OLE Automation and xp_cmdshell: procedures reaching outside the database entirely — into the filesystem, into COM objects. These don't convert; they get redesigned.

05 · THE MIGRATION PIPELINE

Inventory first, conversion second

The pipeline reads left to right, but the work ran in loops: the inventory fed risk triage, which reprioritized the inventory; the test harness sent failures back into conversion; rehearsals sent timing data back into the plan. Two ideas matter most: nothing converts before it is inventoried and triaged, and the cutover is rehearsed with the same seriousness as the conversion — including the rollback path, kept warm and never needed.

06 · T-SQL CONVERSION BATTLE SCARS

The patterns that fought back

Automated conversion handled the straightforward DDL and simple CRUD procedures. Then came the patterns carrying a decade of business intent — the ones where "convert the syntax" was the wrong job and "understand the rule, then re-express it" was the right one.

07 · DATATYPE TRAPS

Where silent wrongness lives

Datatype issues don't throw errors on cutover night. They throw wrong answers weeks later. Each of these was hardened deliberately, with a dedicated test:

datetime2 precision

datetime2(7) stores 100-nanosecond ticks; PostgreSQL timestamp tops out at microseconds. The seventh digit truncates silently, and boundary comparisons and ordering on sub-microsecond timestamps change meaning — caught only by tests built specifically for it.

datetime rounding

Legacy datetime rounds to 3.33 ms increments — data stored for years carries SQL Server's rounding baked in. Converted values had to preserve the stored (rounded) values exactly, not "correct" them.

money vs numeric

money is fixed 4-decimal with display rounding baked into its behavior; aggregates and divisions behave differently than numeric. Converted to numeric(19,4) with financial reconciliation proving penny-level behavior matched.

hierarchyid

No PostgreSQL equivalent. The account and organizational hierarchy trees became materialized paths (ltree), and the tree-walk procedures — the real cost — were rewritten against the new representation and validated level by level.

Collation surprises

Case-insensitive SQL_Latin1_General_CP1_CI_AS to PostgreSQL's case-sensitive default changes LIKE, unique constraints, GROUP BY, and join matching. Decided up front per workload — citext or normalized columns — never discovered mid-migration. Case-variant key tests ran in the harness permanently.

uniqueidentifier

NEWID() vs gen_random_uuid() generation semantics differ; applications assuming anything about GUID ordering or generation had to be found and fixed.

xml and sql_variant

FOR XML queries were rewritten; sql_variant columns — the type system's escape hatch — had to be concretely typed, which meant finding every value they had ever held.

rowversion

Not a time at all — an optimistic-concurrency token. No equivalent exists; the concurrency pattern was redesigned around explicit version columns.

08 · SATELLITE DISPOSITION

SSIS, SSRS, and SQL Agent: convert nothing by default

The rule for satellite systems: disposition before conversion. Every package, report, and job got one of four verdicts — rebuild, replace, retire, or (rarely) convert — decided before a single procedure was converted, because the satellites set the real timeline.

09 · TESTING

The testing that actually caught bugs

Row-count reconciliation is the industry's favorite security blanket: it passes when the data is wrong in all the interesting ways. The harness that caught real bugs had layers, each aimed at a different failure mode:

• Schema-diff gate: every source object converted, dispositioned, or explicitly retired — nothing merely "forgotten." The gate failed the build on unaccounted objects.
• Row-count + row-hash reconciliation: counts plus a hash over the full row, so type changes, precision truncation, and collation shifts showed up as hash mismatches even when counts matched.
• Semantic sampling: domain experts — claims and billing staff, not engineers — validated that converted procedures still meant the right business thing. Engineers verify logic; only the business verifies meaning.
• Parallel-run reconciliation: a shadow period with both systems running and report outputs diffed. This caught the bugs no static test could: timing-dependent logic, tie behavior, rounding at the edges.
• Error-path testing: every rewritten TRY/CATCH got a deliberate failure injected. The happy-path tests had all passed; the error paths were where the semantic differences lived.
• Collation and case-variant tests: keys differing only by case, case-folding edge cases, LIKE patterns — permanent residents of the harness, not one-time checks.
• Datetime boundary tests: datetime2(7) truncation cases, DST transitions, the 3.33 ms rounding legacy — the temporal edge cases that only bite against production data.
• Performance parity: baselines captured before conversion; the set-based rewrites were benchmarked, because "cleaner" code with a worse plan is a production incident wearing a disguise.

10 · CUTOVER REHEARSAL

Discipline, not heroics

Cutover night succeeds or fails months earlier, in rehearsal. The discipline:

11 · ROLLBACK PLAN

Rehearsed twice, never invoked

The rollback plan was real: the source estate kept warm through the cutover window with replication lag monitored, application connection strings switchable back by configuration, and the rollback runbook rehearsed and timed like the cutover itself. Trigger criteria were defined in advance — which gate failures meant "fix forward" and which meant "roll back" — so the decision on the night would be recognition, not deliberation.

It was never invoked. The cutover completed inside the planned window, validation gates passed in sequence, and the source estate was stood down on schedule. The rollback plan's value wasn't in being used — it was in making the go decision calm. Teams that know they can retreat advance faster.

12 · WHAT WE'D DO DIFFERENTLY

The honest list

• Start runtime capture earlier. Dynamic SQL shapes and real execution patterns should have been captured from week one; the late discoveries were all in code that existed only at runtime.
• Treat collation as a day-one decision. It was nearly a migration-week surprise. Collation strategy belongs in the first design review, not the last.
• Disposition satellites before converting anything. The SSIS / SSRS / Agent decisions set the true timeline; converting procedures first felt like progress while the critical path sat elsewhere.
• Budget more for the last 10%. The unusual procedures — the ones stacking multiple scar patterns — consumed a disproportionate share of senior-engineer time. Plan for it explicitly.
• Bring application teams in from week one. Application-side SQL was the biggest blind spot, and it belonged to teams who weren't in the room early enough.
• Freeze scope earlier. Every "while we're in there" addition to the migration scope was paid for in rehearsal time.

13 · RESULTS

Outcomes, described without invented metrics

• The cutover completed inside the planned window, with validation gates passing in sequence — no rollback, no data-loss incidents, no Monday-morning surprises.
• The procedures everyone feared — the cursor-driven adjudication logic, the dynamic SQL, the cross-database chains — ran correctly against production data, verified by parallel-run reconciliation before the switch.
• Datatype hardening held: financial reconciliation matched to the penny, temporal edge cases behaved, and the collation strategy survived contact with real case-variant data.
• The satellite estate shrank: dead packages, unused reports, and zombie jobs retired; what remained ran natively on the target stack instead of as converted legacy.
• The licensing position moved as intended — and more durably, the team now operates and extends the PostgreSQL estate natively rather than maintaining a converted artifact.

14 · HOW ANOVACLOUD APPROACHES THIS

The methodology this experience built

Everything above is now procedure. When AnovaCloud approaches a SQL Server → PostgreSQL migration, it starts with the Database Migration Assessment — and the assessment runs the war-story playbook, not the vendor-tool playbook:

• Real estate inventory first: scripted catalog crawl plus runtime capture plus owner interrogation — the three layers from chapter 04 — so the plan is built on what actually executes, not what a tool can count.
• Conversion-risk triage before conversion: procedures scored against the scar patterns in chapter 06, so the hardest code gets senior engineers and test budget up front.
• Datatype hardening as a design decision: the chapter 07 traps — datetime2 precision, hierarchyid, money, collation — decided and tested before migration week, never discovered during it.
• Satellite disposition before code conversion: SSIS, SSRS, and SQL Agent each get rebuild / replace / retire verdicts first, because they set the real timeline.
• A test harness designed like a product: row-hash reconciliation, parallel runs, error-path injection, collation and temporal edge cases — the layers from chapter 09.
• Rehearsed cutover with a real rollback: timed dress rehearsals, go/no-go gates with named owners, a point of no return defined in advance, and a rollback plan that has been executed — in rehearsal — before the night it might be needed.

If your estate looks like the one in this story — procedural, satellite-heavy, and frightening in exactly the ways tools can't see — the assessment is where the frightening parts get mapped. Start with the Database Migration Assessment →

15 · RELATED

Pattern, toolkit, service