Phase 03 — Data Migration

Migrating 20.5 Million Rows

I built a custom Python ETL pipeline to move every row from PostgreSQL into the new SQL Server schema — and had to solve a real problem first: the audit triggers I'd just built would have fired on every one of the 10.8 million rows in the two audited tables.

Python ETL psycopg2 pyodbc fast_executemany Trigger Disable IDENTITY Reseed

Overview

What I Did in Phase 3

The Problem I Solved First

Before writing any load logic, I realized the audit triggers from Phase 2 would fire on every row inserted into cms.providers and cms.provider_services — roughly 10.8 million rows combined. Left unhandled, that would have flooded audit.data_access_log with migration noise and slowed the load dramatically from row-by-row trigger overhead.

-- Disable before bulk load, re-enable in a finally block
-- so triggers come back even if migration fails partway through
ALTER TABLE cms.providers DISABLE TRIGGER trg_audit_providers;
ALTER TABLE cms.provider_services DISABLE TRIGGER trg_audit_provider_services;
-- ... bulk load happens here ...
ALTER TABLE cms.providers ENABLE TRIGGER trg_audit_providers;
ALTER TABLE cms.provider_services ENABLE TRIGGER trg_audit_provider_services;

~20.5M

Rows Migrated

Tables Loaded

~15 min

Total Duration

Audit Rows From Migration

Real Troubleshooting

What Actually Went Wrong

Getting the ETL script to connect took several real fixes — documented here rather than smoothed over, since this is realistic DBA troubleshooting, not just script bugs.

Problem	Cause	Fix
Login timeout / error 1326	TCP/IP disabled by default in SQL Server Configuration Manager	Enabled TCP/IP, restarted the SQL Server service
Still couldn't connect	No Windows Firewall rule for port 1433	Created an inbound rule for TCP 1433
Login failed for etl_service	Password typo at the interactive prompt	Confirmed credentials worked via SSMS, re-ran carefully
Cannot find object "providers"	db_datawriter/db_datareader lack ALTER permission needed to disable triggers	Granted ALTER on just the two tables that needed it
DBCC CHECKIDENT permission denied	Identity reseeding needs elevated permission beyond routine ETL access	Ran the one-time reseed manually under my own admin account

Migration Results

Every Table, Migrated and Verified

Table	Rows	Time	Throughput
cms.providers	1,175,281	50.8s	~23,141 rows/sec
cms.provider_services	9,660,647	482.4s	~20,026 rows/sec
cms.user_state_access	2	<0.1s	—
cms.staging_raw	9,660,647	357.4s	~27,031 rows/sec

Screenshot — ETL Migration Output

Terminal output showing all four tables migrated with row counts and throughput

The custom Python ETL pipeline migrating all four tables end to end

I reseeded the IDENTITY counter on cms.provider_services after loading with IDENTITY_INSERT on, confirming the current identity value matched the last row loaded (9,660,647) so future inserts continue correctly.

Phase 4 — Validation