SQL Server → Azure SQL Database

📖 Overview & Scope

This SOP covers a one-time full copy (schema + data) of a SQL Server on-premises or VM database into an existing Azure SQL Database, using the Endrias Bridge Assessment and Migration tabs. Because source and target are the same engine family (T-SQL), most types map 1:1 — see the Type Conversions table for the few exceptions (deprecated types, FILESTREAM, temporal SysEnd clamping).

✅ Prerequisites — Source (SQL Server)

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

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

✅ Prerequisites — Target (Azure SQL Database)

Endrias Bridge does not create the Azure SQL Database — it must exist and be accessible before Step 1. Complete the following in the Azure Portal before connecting:

1. Add migration host firewall rule:

Navigate to Azure Portal → SQL Server (logical server) → Networking → Firewall rules. Add the public IP of your migration host as an allowed inbound rule.

2. Create contained database user on target:

-- Run on Azure SQL Database (EndriasBridgeTarget) via SSMS or Azure Portal Query Editor
CREATE USER MigrationUser WITH PASSWORD = 'your-password';
ALTER ROLE db_owner ADD MEMBER MigrationUser;

3. Verify tier capacity:

Open Endrias Bridge and navigate to Step 5 — DB Migration. Fill in both connection blocks as shown below. For the Azure SQL target, use the fully qualified server hostname from the Azure Portal (yourserver.database.windows.net) and port 1433.

Switch to the Assessment tab and click Run Assessment. The tool connects to the source only at this stage. Since source and target are the same engine family, most objects will show as compatible — look for deprecated type warnings (NTEXT, IMAGE, TEXT) and any FILESTREAM columns.

Switch to the Migration tab. Click Load Tables from Source to populate the table list. For a SQL Server → Azure SQL migration, all tables are generally safe to include — review the list for any tables that contain FILESTREAM data or rely on cross-database joins that will not work post-migration.

Click Start Migration. Watch the Migration Log update live. The log will show type upgrade notes for deprecated types and any temporal SysEnd clamping warnings — these warnings are informational and do not indicate data loss.

1. Click Verify Data Integrity — compares source vs. target row counts for every migrated table and logs any mismatches.
2. Click Export Report (enabled once migration finishes) to save an HTML/text summary of what was created, copied, and any warnings.
3. Spot-check key tables on the Azure SQL target using SSMS or Azure Portal Query Editor.

-- Run on Azure SQL target after migration to spot-check row counts
SELECT t.name AS TableName, p.rows AS RowCount
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
ORDER BY t.name;

Azure SQL Database (PaaS) does not support all SQL Server on-prem features. Review the following items after migration completes.

1. Recreate SQL Agent jobs as Azure Elastic Jobs:

SQL Server Agent is not available on Azure SQL Database (PaaS). Export all job definitions from the source using the Schema Objects tab, then recreate them as Azure Elastic Jobs via the Azure Portal or T-SQL elastic jobs API.

2. Review FILESTREAM / FILETABLE columns:

-- Identify FILESTREAM columns on target (will be VARBINARY(MAX))
SELECT OBJECT_NAME(c.object_id) AS TableName, c.name AS ColumnName, t.name AS TypeName
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE t.name = 'varbinary' AND c.max_length = -1
ORDER BY TableName, ColumnName;

FILESTREAM files were not migrated — binary column data is present in VARBINARY(MAX) but the file-system projection is gone. Migrate file payloads separately to Azure Blob Storage and update application connection strings accordingly.

3. Validate linked server references:

Linked servers are not available on Azure SQL Database. Replace with OPENDATASOURCE, elastic queries (external tables), or Azure Data Factory pipelines for any cross-database or cross-server references.

🔄 Live Migration — Full-Load-First Sequence

✅ Live Migration — Source Pre-flight Checks

Run all four blocks on the VM source before enabling CDC. All checks must pass.

USE YourSourceDB;
GO

-- 1. SQL Agent running? (CDC capture job requires it)
SELECT servicename, status_desc
FROM   sys.dm_server_services
WHERE  servicename LIKE 'SQL Server Agent%';
-- Expected: Running

-- 2. Recovery model FULL?
SELECT name, recovery_model_desc
FROM   sys.databases WHERE name = DB_NAME();
-- Expected: FULL

-- 3. Containment? (PARTIAL blocks CDC -- see note below)
SELECT name, containment_desc
FROM   sys.databases WHERE name = DB_NAME();
-- Expected: NONE

-- 4. CDC already enabled?
SELECT name, is_cdc_enabled
FROM   sys.databases WHERE name = DB_NAME();
-- Expected: 0 (will enable next) or 1 (already done)

-- 5. Permission check
SELECT IS_MEMBER('db_owner') AS is_db_owner, SUSER_SNAME() AS login;

📡 Round 1 — CDC Stream Setup (VM Source)

USE YourSourceDB;
GO
EXEC sys.sp_cdc_enable_db;
GO
GRANT SELECT ON SCHEMA::cdc TO [Migration];
GO

-- Set 30-day retention immediately (default is 3 days)
EXEC sys.sp_cdc_change_job @job_type='cleanup', @retention=43200;  -- 43200 min = 30 days
GO

-- Verify CDC on and jobs created
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME();
SELECT name, enabled FROM msdb.dbo.sysjobs WHERE name LIKE 'cdc.YourSourceDB%';
-- Expected: 2 rows (capture + cleanup)

USE YourSourceDB;
GO
EXEC sys.sp_cdc_enable_table
    @source_schema        = N'dbo',
    @source_name          = N'YourTable',
    @role_name            = NULL,
    @supports_net_changes = 1;
GO

-- Generate enable statements for ALL tables at once
SELECT
    'EXEC sys.sp_cdc_enable_table @source_schema=N''' + s.name + ''','
    + '@source_name=N''' + t.name + ''','
    + '@role_name=NULL,@supports_net_changes=1;' AS stmt
FROM   sys.tables t
JOIN   sys.schemas s        ON t.schema_id = s.schema_id
LEFT JOIN cdc.change_tables ct ON ct.source_object_id = t.object_id
WHERE  s.name NOT IN ('cdc','sys') AND ct.capture_instance IS NULL
ORDER  BY s.name, t.name;
-- Copy the output and execute the generated statements

1. Source: SQL Server → VM hostname/IP, YourSourceDB, MigrationUser
2. Target: Azure SQL Database → yourserver.database.windows.net, port 1433
3. Migrate tab → Sync Mode → CDC Stream (real-time, SQL Server)
4. Select tables → click Start Migration — full load runs, LSN anchor saved
5. Wait for full load to complete — watch Migration Log until all tables show row counts
6. Click Start CDC Stream — catch-up begins, then enters tail mode

-- Run on VM source
USE YourSourceDB;
UPDATE dbo.YourTable SET SomeColumn = 'CDC_TEST_1' WHERE ID = 1;

-- Run on Azure SQL target within ~1 second
SELECT SomeColumn FROM dbo.YourTable WHERE ID = 1;
-- Expected: 'CDC_TEST_1'

-- Also test UPDATE and DELETE
UPDATE dbo.YourTable SET SomeColumn = 'CDC_UPDATE' WHERE ID = 1;
DELETE FROM dbo.YourTable WHERE ID = 999;   -- use a real dispensable row

🔁 Round 2 — CT Sync Setup (Change Tracking)

Change Tracking requires no SQL Agent and is simpler to configure. Use this if CDC is unavailable or as a comparison test.

USE YourSourceDB;
GO

-- Database level — 30-day retention
ALTER DATABASE YourSourceDB
SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 30 DAYS, AUTO_CLEANUP = ON);
GO

-- Per table
ALTER TABLE dbo.YourTable
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
GO

-- Generate statements for all tables
SELECT 'ALTER TABLE ' + s.name + '.' + t.name
      + ' ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON);'
FROM   sys.tables t
JOIN   sys.schemas s ON t.schema_id = s.schema_id
WHERE  s.name NOT IN ('cdc','sys')
ORDER  BY s.name, t.name;

-- Verify
SELECT db_name, retention_period, retention_period_units_desc, is_auto_cleanup_on
FROM   sys.change_tracking_databases WHERE database_id = DB_ID();

SELECT t.name, ct.is_track_columns_updated_on
FROM   sys.change_tracking_tables ct
JOIN   sys.tables t ON ct.object_id = t.object_id;

1. Same Source + Target connections as Round 1
2. Sync Mode → CT Sync (watermark)
3. Click Start Migration → full load runs
4. After full load → click Start CT Sync stream

-- Run on VM source
UPDATE dbo.YourTable SET SomeColumn = 'CT_UPDATE' WHERE ID = 1;
DELETE FROM dbo.YourTable WHERE ID = 999;
INSERT INTO dbo.YourTable (ID, SomeColumn) VALUES (9999, 'CT_INSERT');

-- Verify each on Azure SQL target within ~1-5 seconds

⚖️ CDC Stream vs CT Sync — Comparison

🖥️ UI Busy / Not Responding During Migration

What you will see

How to tell if migration is still running

1. Open Task Manager → find python.exe or MigrationBridge.exe → confirm CPU % is above 0 and memory is active. If so, the migration is running — the UI is just catching up.
2. Open the latest log file directly: C:\MigrationT\migration_logs\ — the file updates in real time even when the UI is frozen.
3. For CDC Stream: check the source with SELECT sys.fn_cdc_get_max_lsn() AS source_lsn and compare it to the last LSN shown in the Migration Log. If they are close, the stream is healthy.

Best practices to reduce UI freeze frequency

• Run from source rather than the installed binary: py C:\MigrationT\Migrationtool\launch_gui.pyw — this uses the latest version which has reduced UI flush frequency on large tables.
• Avoid resizing or interacting with the window during high-throughput table copy — UI interactions queue additional render work.
• For very large databases (> 5 million rows), consider running the migration overnight and checking the log file the next morning rather than watching the UI live.
• For CDC Stream: once the full load is complete and streaming has started, the UI is mostly idle (polling every 250ms) and will not freeze under normal change volumes.

Get-Content "C:\MigrationT\migration_logs\migration_$(Get-Date -f yyyyMMdd)*.log" -Wait -Tail 20

🗺️ Known Issues

Temporal table SysEnd clamping: ODBC Driver 17 cannot write year-9999 datetime boundaries. Endrias Bridge automatically clamps SysEnd = 9999-12-31 to 9998-12-31. Log lines showing WARNING: SysEnd clamped are informational — data lands correctly on target.

FILESTREAM / FILETABLE: Not supported on Azure SQL Database. Columns are migrated as VARBINARY(MAX). Files stored in FILESTREAM must be migrated separately to Azure Blob Storage.

SQL Server Agent jobs: Not available on Azure SQL (PaaS). Recreate as Azure Elastic Jobs post-migration.

Linked servers: Not available on Azure SQL. Replace with OPENDATASOURCE, external tables, or Azure Data Factory pipelines.

CLR assemblies: Require explicit enablement on Azure SQL (sp_configure 'clr enabled' is not available — submit a support request for CLR).

Cross-database queries: Azure SQL does not support 3-part cross-database names. Use elastic queries instead.

TCP 10054 (idle connection drop): Azure SQL drops idle pooled connections after ~30 minutes. Endrias Bridge detects 10054/forcibly closed errors and automatically retries once after disposing the connection pool.

📊 Type Conversions Applied (SQL Server → Azure SQL)