Standard Operating Procedure

AdventureWorks2019 (SQL Server) → PostgreSQL

Step-by-step procedure for migrating the AdventureWorks2019 sample database to a PostgreSQL target using the Endrias Bridge Endrias Bridge, including one-time prerequisites and the table exclusions required for a clean first run.

Tool: Endrias Bridge Endrias Bridge · Source: SQL Server (AdventureWorks2019) · Target: PostgreSQL

📖 Overview & Scope

This SOP covers a one-time full copy (schema + data) of AdventureWorks2019 from SQL Server into an existing PostgreSQL database, using the GUI's Assessment and Migration tabs. All 71 tables migrate, including the four with hierarchyid/geography columns — those columns are converted to text automatically (see Spatial & hierarchyid columns).

ℹ️

Estimated time: ~10–15 minutes for setup + assessment, plus the time for the actual data copy (depends on database size — AdventureWorks2019 is small, typically under a minute for the included tables).

✅ Prerequisites — Source (SQL Server)

Requirement	Status
ODBC Driver 17 for SQL Server installed	Done
SQL Server allows mixed-mode (SQL login) authentication, or use Windows Auth	Done
Login (e.g. `bbpulse`) mapped to a database user in `AdventureWorks2019` with at least `db_datareader` (this SOP assumes `db_owner`)	Done

✅ Prerequisites — Target (PostgreSQL)

Endrias Bridge connects directly to postgresql+psycopg2://user:pass@host:port/dbname — it does not create the database itself, only the tables inside it. Run the following once on the PostgreSQL server (as a superuser, e.g. psql -U postgres) before Step 1:

-- 1. Create the target database (must exist before Step 5 connects to it)
CREATE DATABASE adventureworks;

-- 2. Create/confirm the migration user
CREATE USER bbpulse WITH PASSWORD 'your-password';

-- 3. Make it the owner so it can CREATE/DROP/INSERT freely
--    (avoids "permission denied for schema public" on PostgreSQL 15+)
ALTER DATABASE adventureworks OWNER TO bbpulse;
\connect adventureworks
ALTER SCHEMA public OWNER TO bbpulse;

💡

psycopg2 is already installed (v2.9.12) — no Python-side setup needed.

⚠️

"Run Assessment" does not check the target. It only connects to the source. The first real connection to PostgreSQL happens when you click Start Migration in Step 4 — if the database, user, or permissions above aren't set up, the migration log will show a connection error at that point.

Configure Connections (Wizard Step 5)

Open Endrias Bridge via the Desktop/Start Menu shortcut, navigate to Step 5 — DB Migration, and fill in both connection blocks:

Endrias Bridge — Step 5: Source & Target Configuration

SOURCE DATABASE

DB Type

SQL Server

Host/IP

localhost

Port

(blank)

Database

AdventureWorks2019

Username

bbpulse

Password

••••••••••••

TARGET DATABASE

DB Type

PostgreSQL

Host/IP

<pg-host>

Port

5432

Database

adventureworks

Username

bbpulse

Password

••••••••••••

Open Endrias Bridge

💡

Leaving Port blank for SQL Server lets the driver use the default instance port — this is the configuration already confirmed working for AdventureWorks2019.

Run Assessment

In the Endrias Bridge, stay on the Assessment tab and click Run Assessment.

Endrias Bridge

Assessment

Migration

Jobs

Schema Objects

Tools

Database Assessment

Discovers source database objects and checks compatibility with target.

Run Assessment

SOURCE OBJECTS

                📁 Tables (71)

                  AWBuildVersion  3 rows

                  DatabaseLog  0 rows

                  HumanResources.Employee  290 rows

                  Person.Address  19614 rows

                  … (66 more)

COMPATIBILITY ISSUES

• AWBuildVersion.SystemInformationID: TINYINT → SMALLINT
• DatabaseLog.Event: XML → TEXT
• Sales.SalesOrderHeader.SubTotal: MONEY → NUMERIC(19,4)
• Person.Person.rowguid: UNIQUEIDENTIFIER → CHAR(36)
• … (full list per scanned table)

ℹ️

This panel lists the automatic type conversions Endrias Bridge will apply during schema creation (e.g. TINYINT → SMALLINT, MONEY → NUMERIC(19,4), XML → TEXT). It does not flag the hierarchyid/geography columns — those are handled manually in the next step.

Select Tables (exclude 4 known-issue tables)

Switch to the Migration tab. Confirm options, click Load Tables from Source, then uncheck the 4 tables listed below.

Endrias Bridge

Assessment

Migration

Jobs

Schema Objects

Tools

Migrate Schema & Data

Select tables and copy schema / data to target database.

Migrate schema (CREATE TABLE)

Migrate data (INSERT rows)

Sync mode:

Full copy

Incremental (watermark column)

SQL Server CDC

Load Tables from Source

Start Migration

Verify Data Integrity

Export Report

AWBuildVersion

DatabaseLog

ErrorLog

HumanResources.Employee

Person.Address

Production.Document

Production.ProductDocument

Sales.SalesOrderHeader

… (all 71 tables, checked)

ℹ️

Leave all 71 tables checked. The four with hierarchyid/geography columns (HumanResources.Employee, Person.Address, Production.Document, Production.ProductDocument) now migrate automatically — those columns are read via .ToString() and stored as TEXT on the target. See Spatial & hierarchyid columns.

Start Migration & Monitor

Click Start Migration. Watch the Migration Log and progress bar update live as each table's schema is created and its rows copied in chunks of 1,000.

Endrias Bridge — Migration Log

14:02:11 --- Data type conversions (SQL Server -> PostgreSQL) ---

14:02:11 AWBuildVersion.SystemInformationID: TINYINT -> SMALLINT (no 1-byte integer type on most other engines)

14:02:11 AWBuildVersion.Database Version: NVARCHAR -> VARCHAR (no national-character type on target)

14:02:11 Person.Person.rowguid: UNIQUEIDENTIFIER -> UUID (native PostgreSQL type)

14:02:11 ProductPhoto.ThumbNailPhoto: VARBINARY -> BLOB/BYTEA

14:02:11 Employee.OrganizationNode: Unrecognized source column type -> TEXT (hierarchyid; read via .ToString())

14:02:12 Schema: created 71 table(s)

14:02:12 [1/71] AWBuildVersion ... 3 row(s)

14:02:12 [2/71] DatabaseLog ... 0 row(s)

14:02:13 [3/71] ErrorLog ... 0 row(s)

14:02:18 ...

14:02:45 --- Full copy: 250,000+ row(s) in 35s ---

14:02:41 --- Done ---

⚠️

If the log shows a connection error here (not on the Assessment tab), double-check the PostgreSQL prerequisites — this is the first step that actually connects to the target.

Verify & Export Report

Click Verify Data Integrity — compares source vs. target row counts for every migrated table and logs any mismatches.
Click Export Report (enabled once migration finishes) to save an HTML/text summary of what was created, copied, and any warnings (truncated strings, rounded decimals, etc.).

Schema Objects Optional

AdventureWorks2019 has stored procedures, views, functions, user-defined table types, temporal tables, and SQL Agent jobs that a data-only migration doesn't touch. On the Schema Objects tab:

Endrias Bridge

Assessment

Migration

Jobs

Schema Objects

Tools

Schema Objects

Procedures, views, triggers, functions, table types, temporal tables and SQL Agent jobs on the source database.

Scan Source Objects

Export All to .sql

Apply to Target

Click Scan Source Objects.
Click Export All to .sql to dump every routine/table-type/temporal-table definition and SQL Agent job to a .sql file for manual review.

ℹ️

Apply to Target will report "Nothing applicable" for this migration — it only applies routines when source and target are the same platform (T-SQL ≠ PL/pgSQL) and table types when the target is SQL Server. For SQL Server → PostgreSQL, everything needs manual conversion via the exported .sql file.

🗺️ Spatial & hierarchyid columns

These 4 columns reflect as an unrecognized NullType in SQLAlchemy, and the SQL Server CLR types behind them (hierarchyid, geography) can't be fetched as-is by the driver (ODBC SQL type -151 is not yet supported). Endrias Bridge now handles them automatically — no need to exclude these tables.

Schema	Table	Column	SQL Type	Migrated as
HumanResources	Employee	OrganizationNode	hierarchyid	`TEXT` on the target. Read server-side via `[col].ToString()` — hierarchyid becomes its `/1/2/` path string, geography/geometry becomes WKT.
Person	Address	SpatialLocation	geography
Production	Document	DocumentNode	hierarchyid
Production	ProductDocument	DocumentNode	hierarchyid

⚠️

The conversion is lossy: the target stores a text representation, not a native spatial/hierarchy type. If you need true spatial types on PostgreSQL, post-process these TEXT columns into PostGIS geometry/geography after the migration.

💡

Schema creation is also resilient: tables are created as one batch, and if any single table's DDL is rejected by the target, Endrias Bridge retries table-by-table so one unmapped type can't roll back the whole schema.

📊 Type Conversions Applied (SQL Server → PostgreSQL)

Source Type	Target Type	Note
UNIQUEIDENTIFIER	UUID	Native PostgreSQL UUID type (CHAR(36) on engines without one)
MONEY	NUMERIC(19,4)
SMALLMONEY	NUMERIC(10,4)
DATETIMEOFFSET	TIMESTAMP WITH TIME ZONE
SMALLDATETIME / DATETIME2 / DATETIME	TIMESTAMP
NVARCHAR / NCHAR	VARCHAR / CHAR	No national-character type; length preserved
NTEXT	TEXT	Deprecated SQL Server type
IMAGE / VARBINARY / BINARY	BYTEA	No VARBINARY type on PostgreSQL
XML	TEXT	No portable XML type; stored as text (lossless)
SQL_VARIANT	TEXT	Stored as string representation
ROWVERSION / TIMESTAMP	BYTEA	Auto-increment-on-write behaviour is lost
BIT	BOOLEAN
TINYINT	SMALLINT	No 1-byte integer type on most other engines
hierarchyid / geography / geometry	TEXT	Read via `.ToString()`; lossy (see above)