SQL Server → Azure Synapse Analytics (Dedicated SQL Pool)

📖 Overview & Scope

Azure Synapse Analytics (SQL Pool) is registered in Endrias Bridge as a full member of the SQL Server family of engines — the same internal engine group as SQL Server, Azure SQL Database, Azure SQL Managed Instance, and AWS RDS for SQL Server. That grouping matters: it means Synapse is not a bolt-on, target-only connector (unlike, for example, Databricks). It gets the same mssql+pyodbc ODBC connection path, the same Encrypt=yes;TrustServerCertificate=no secure-by-default handling as Azure SQL Database, the full generic relational schema-and-data migration engine, and the full live DMF compatibility scan when Synapse is used as a source. No special or dedicated migration code was required to add Synapse support — it works through the exact same code path as any other SQL Server-family engine.

✅ Prerequisites — Source (SQL Server)

-- Verify source SQL Server version and compatibility level
SELECT @@VERSION;
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'eb_healthcare';

-- Grant minimum permissions on source
USE eb_healthcare;
CREATE USER MigrationUser FOR LOGIN MigrationUser;
ALTER ROLE db_datareader ADD MEMBER MigrationUser;
GRANT VIEW SERVER STATE TO MigrationUser;

✅ Prerequisites — Target (Azure Synapse Analytics)

Endrias Bridge does not provision the Synapse workspace or SQL pool — both must already exist and be reachable before Step 1. Complete the following in the Azure Portal before connecting:

1. Provision a dedicated SQL pool:

In your Synapse workspace, create (or confirm) a Dedicated SQL Pool (formerly SQL DW), sized to a DWU tier appropriate for your data volume. Do not point this migration at a serverless pool as the target — see the Dedicated vs Serverless table.

2. Add migration host firewall rule:

Navigate to Azure Portal → your Synapse workspace → Networking → Firewall rules. Add the public IP of your migration host as an allowed inbound rule, or enable Allow Azure services and resources to access this workspace if the migration host runs inside Azure. If the workspace is on a private endpoint/VNet instead, the migration host must be able to reach that endpoint (VPN/peering) before Step 1 will succeed.

3. Create a SQL login and contained database user on the target:

-- Run on the dedicated SQL pool (e.g. eb_healthcare_dw) via SSMS,
-- Azure Data Studio, or the Synapse Studio SQL script tool
CREATE USER MigrationUser WITH PASSWORD = 'your-password';
EXEC sp_addrolemember 'db_owner', 'MigrationUser';

4. Confirm the pool is online:

Open Endrias Bridge and navigate to Step 5 — DB Migration. Fill in both connection blocks as shown below. Connection setup for Synapse is exactly like Azure SQL Database — no field repurposing is needed. The only decision point is which SQL endpoint hostname to use.

Switch to the Assessment tab and click Run Assessment to discover source objects and check target compatibility. Then run the DMF (Data Manufacturing Factory) compatibility scan — available from the Run DMF Scan button in the app header, or the DMF card on the Tools tab.

Switch to the Migration tab. Click Load Tables from Source to populate the table list from eb_healthcare. All seven healthcare tables are safe to include in a standard migration.

Click Start Migration with Full Copy selected. Watch the Migration Log update live as each table's schema is created and its rows are copied into the dedicated SQL pool.

Switch to the Tools tab and run Data Quality Checks against the source tables already loaded on the Assessment tab. For a Synapse migration this step matters more than it does for a same-engine SQL Server → SQL Server or SQL Server → Azure SQL Database move.

Dedicated SQL pools have a different performance and operations model than SQL Server. Review the following items after migration completes.

1. Tune distribution strategy for large tables:

Endrias Bridge does not choose a distribution strategy for you — it issues generic CREATE TABLE DDL, which lands on Synapse's default (ROUND_ROBIN). For large fact-style tables such as lab_results (1,500,000 rows) or insurance_claims, ROUND_ROBIN is rarely optimal once you start joining against dimension tables like patients or providers at scale. Consider manually switching these tables to HASH distribution on a common join key:

-- Manual post-migration tuning step — not automated by Endrias Bridge
CREATE TABLE dbo.lab_results_hashed
WITH (
    DISTRIBUTION = HASH(patient_id),
    CLUSTERED COLUMNSTORE INDEX
)
AS SELECT * FROM dbo.lab_results;

-- Swap the tables once verified
RENAME OBJECT dbo.lab_results TO lab_results_roundrobin;
RENAME OBJECT dbo.lab_results_hashed TO lab_results;

-- Repeat the same pattern for insurance_claims, e.g. HASH(patient_id)

This is a manual T-SQL exercise performed on the target after migration — Endrias Bridge does not automate distribution key selection or execute CTAS swaps for you.

2. Decide whether to re-add constraints as unenforced metadata:

If your BI/reporting tools rely on declared PK/FK relationships for join elimination or semantic modeling, you can add them back as metadata-only constraints (they will not be enforced at write time, per Synapse's design):

ALTER TABLE dbo.patients
  ADD CONSTRAINT PK_patients PRIMARY KEY NONCLUSTERED (patient_id) NOT ENFORCED;

ALTER TABLE dbo.appointments
  ADD CONSTRAINT FK_appointments_patients
  FOREIGN KEY (patient_id) REFERENCES dbo.patients (patient_id) NOT ENFORCED;

3. Recreate SQL Agent jobs as Synapse pipelines/triggers:

Synapse has no SQL Server Agent. Export job definitions from the source using the Schema Objects tab, then recreate the equivalent scheduled logic as Synapse pipelines with scheduled or tumbling-window triggers in Synapse Studio. Endrias Bridge does not create pipelines or triggers for you — this is a manual rebuild step in Synapse Studio.

⚖️ Dedicated vs Serverless SQL Pool — Comparison

🗺️ Known Issues / Caveats

Constraint enforcement gap: Dedicated SQL pools accept PRIMARY KEY / FOREIGN KEY / UNIQUE declarations but do not enforce them at write time. Run Data Quality Checks before migrating and periodically after go-live if other processes write to the target.

Serverless pool as a target: This tool's INSERT-based approach does not work well against a serverless SQL pool. Use a dedicated SQL pool as the migration target; serverless is fine as a source or for post-migration ad hoc querying.

Distribution strategy not automated: Tables are created via generic CREATE TABLE DDL and land on the pool's default (effectively ROUND_ROBIN). Manually tune large fact tables to HASH distribution post-migration (Step 6).

Incremental / CDC sync not fully validated for Synapse: These modes are selectable because Synapse is grouped with the SQL Server family, but they have not been specifically validated against Synapse's DML restrictions in this release. Use Full Copy for a first migration.

SQL Server Agent jobs: Synapse has no SQL Agent. Recreate scheduled logic as Synapse pipelines with triggers — this is a manual rebuild in Synapse Studio, not automated by Endrias Bridge.

DMF checks on a Synapse source: A couple of DMF checks (notably the deprecated-features performance counter query) were designed with SQL Server on-prem/Azure SQL sources in mind and may not fully apply if Synapse itself is ever used as the DMF scan source — they degrade to a warning rather than a crash.

SQL authentication only: No Azure AD/OAuth login flow — a SQL login must be created in the Synapse workspace for the migration credentials.