Modernization Best Practices and Reusable Assets Blog articles

Memory-Optimized Table Variables: Performance Under the Microscope

David_Lyth — Wed, 17 Jun 2026 23:24:50 GMT

Introduction

Memory-optimized table variables (MOTVs) have been available since SQL Server 2014, but many workloads still use regular on-disk table variables. The common assumption is that MOTVs are "a bit faster" - but how much faster, and under what conditions? When does the difference justify the change?

To answer this, we ran a set of benchmarks comparing MOTVs against on-disk table variables across the dimensions that matter in production: caching scenarios, concurrency levels, row counts, and platforms. The results were surprising in several ways - sometimes MOTVs were barely faster, and in one scenario on-disk variables were actually quicker. But under conditions commonly seen in production, the difference was dramatic.

Throughout this post, we use the shorthand MOTV for memory-optimized table variables and DiskTV for regular on-disk table variables. Both are typed table variables created with CREATE TYPE ... AS TABLE; the only difference is that MOTVs include WITH (MEMORY_OPTIMIZED = ON) in the type definition. The query code that uses them - INSERT, SELECT, JOINs - stays the same. One practical difference is that MOTVs must use an explicitly created table type (DECLARE tv MyTableType), whereas DiskTVs can also be declared inline (DECLARE tv TABLE (col1 int, ...)). If your existing code uses inline declarations, switching to MOTVs requires defining a named type first.

MOTVs are available in SQL Server (2014 and later), Azure SQL Managed Instance, and Azure SQL Database. For brevity, we use Microsoft SQL throughout this post to refer to all three platforms collectively.

Note: Results will vary depending on hardware, Microsoft SQL version, database configuration, and workload patterns. We recommend validating performance in your own dev/test environment. All tests were run on SQL Server 2025 (8 logical processors) and Azure SQL Database Hyperscale (4 vCores).

Background: Temporary Data Options in Microsoft SQL

Microsoft SQL offers several ways to handle temporary data, each with different trade-offs:

Temporary tables are on-disk tables in tempdb. They can suffer from metadata latch contention and compilation overhead, though object caching and ongoing tempdb improvements have helped significantly.
Table variables were added in SQL Server 2000 to reduce compilation volumes compared to temp tables. SQL Server 2019 added deferred compilation for better cardinality estimates.
Memory-optimized table variables were introduced with the In-Memory OLTP engine (code-named Hekaton) in SQL Server 2014. They use in-memory structures that bypass tempdb entirely. See Memory Optimization for Faster Temp Table and Table Variables.
Schema-only in-memory OLTP tables are an alternative for scenarios like high-speed data ingestion, though long-running transactions can block garbage collection.
Memory-optimized tempdb metadata addresses tempdb system table contention, but has its own risks with long-running transactions.
Do without - many temporary objects exist because a query was originally broken into multiple statements that pass intermediate results through temp tables or table variables. This was often a performance workaround for older optimizer limitations. With intelligent query processing expanding steadily - adaptive joins, batch mode on rowstore, optimized plan forcing, and others - it is worth revisiting whether the multi-statement pattern is still necessary. A single well-written query that lets the optimizer work end-to-end can eliminate the temp object entirely, removing both the tempdb overhead and the maintenance burden of the staging logic.

This post focuses specifically on the comparison between MOTVs and DiskTVs.

Performance: The Full Picture

The headline from existing articles - "MOTVs are faster" - is correct but incomplete. The actual advantage depends heavily on three factors: whether tempdb object caching is active, how many concurrent sessions are running, and whether the workload is executing in a stored procedure or as ad-hoc SQL.

The Three Caching Scenarios

The most important variable is whether the Microsoft SQL engine can cache the tempdb object for DiskTVs between executions. There are three distinct scenarios:

Scenario	What happens	DiskTV µs	MOTV µs	MOTV speedup
Stored Procedure	Engine caches the tempdb object between calls - only a truncation is needed, not a full create/destroy	1,127	832	1.4x
WHILE Loop	The table variable persists across loop iterations within a single batch - neither type is recreated	1,768	907	1.9x
Fresh Batch	Each execution compiles a fresh batch and creates the tempdb object from scratch - the pattern for ad-hoc queries, ORMs, and dynamic SQL	19,912	2,248	8.9x

(50 rows, single session, local SQL Server 2025)

In each scenario, the workload is the same: declare a typed table variable, INSERT 50 rows, and run an aggregate SELECT query. What differs is how Microsoft SQL handles the lifecycle of the underlying tempdb object. In the Stored Procedure scenario, the procedure is called via EXEC and the engine caches the tempdb object between calls - on re-entry it only truncates the existing allocation rather than creating and destroying it. In the WHILE Loop scenario, a single batch declares the variable before the loop; each iteration inserts, queries, and deletes rows, but the variable itself persists across iterations so neither type incurs creation overhead. In the Fresh Batch scenario, each iteration passes the SQL text to EXEC(), forcing a fresh compile and a new tempdb object every time - this is the pattern produced by ORM frameworks, application-generated SQL, and dynamic SQL.

In stored procedures, tempdb object caching masks most of the DiskTV overhead, and MOTVs are only 1.4x faster. But a significant share of production table variable usage does not go through stored procedures with cached plans. ORM frameworks, application-generated SQL, and dynamic SQL all hit the fresh batch path where DiskTVs cost 20ms per iteration and MOTVs cost 2ms - nearly a 9x difference.

Concurrency Makes It Worse for DiskTVs

Under concurrent sessions, DiskTV performance degrades sharply because multiple sessions compete for tempdb allocation page latches. MOTVs allocate from per-core partitioned memory pools with no shared latches, so their performance stays relatively stable.

The three-phase breakdown below separates each iteration into DECLARE (object creation), INSERT (populating 50 rows), and SELECT (query). All tests use stored procedure execution (cached):

Sessions	Phase	DiskTV µs	MOTV µs	MOTV speedup
1	Create	0	4	-
1	Insert	406	377	1.1x
1	Query	54	65	0.8x
1	Total	461	447	1.0x
10	Create	4	5	-
10	Insert	1,757	1,335	1.3x
10	Query	186	185	1.0x
10	Total	1,949	1,525	1.3x
25	Create	4	4	-
25	Insert	1,903	993	1.9x
25	Query	186	135	1.4x
25	Total	2,094	1,133	1.8x

Three things stand out. First, the DECLARE phase is trivially fast for both types at all concurrency levels - object creation is not the bottleneck. Second, the INSERT phase accounts for 85-91% of total time across every combination. Third, the MOTV INSERT advantage grows steadily with concurrency: from 1.1x at 1 session to 1.9x at 25 sessions, as tempdb latch contention compounds.

With smaller row counts (5 rows instead of 50), the per-iteration timings drop proportionally but the pattern stays the same - the INSERT phase remains the dominant cost and the MOTV advantage at single session is similar (~1.1x). The difference becomes more pronounced under concurrency, as covered below.

The Worst Case: Uncached + Concurrent

Combining uncached (fresh batch) execution with concurrent sessions produces the most realistic worst case for typical production workloads:

Sessions	Cached DiskTV	Cached MOTV	MOTV speedup	Uncached DiskTV	Uncached MOTV	MOTV speedup
1	1,407	1,177	1.2x	11,763	1,495	7.9x
10	1,684	1,281	1.3x	9,020	1,542	5.9x
25	2,125	1,425	1.5x	68,772	1,196	57.5x

(50 rows, local, µs per iteration)

At 25 concurrent sessions with uncached execution, DiskTV takes 68.8ms per iteration while MOTV takes 1.2ms - a 57x difference. MOTV barely notices concurrency in uncached mode because it never touches tempdb regardless of caching state.

Tail Latency

Average numbers mask the production impact. The maximum latency per iteration shows the real risk:

Concurrency	DiskTV Max	MOTV Max
10 sessions	811 ms	81 ms
50 sessions	10.5 seconds	4.7 seconds

A single DiskTV iteration taking 10 seconds in a 50-session workload is a catastrophic tail event for any latency-sensitive application.

Small Row Counts (5 Rows)

With fewer rows, the INSERT phase shrinks - making the tempdb object creation overhead dominate even more in the uncached case:

Test	5-row DiskTV	5-row MOTV	MOTV speedup
Cached, 1 session	395 µs	316 µs	1.2x
Uncached, 1 session	6,627 µs	293 µs	22.6x
Uncached, 10 sessions	36,240 µs	404 µs	89.7x

Uncached 5-row DiskTV at 10 sessions (36ms) is actually worse than 50-row (9ms). Each session cycles through create/destroy faster with less INSERT work to do, which means the sessions hammer tempdb metadata contention at a higher rate.

Hyperscale Results

On a 4-vCore Azure SQL Hyperscale instance, single-session timings using the WHILE loop scenario confirmed that MOTV creation rates of 86K bare-declares per second are achievable. The MOTV populate advantage is also present on Hyperscale, though the uncached (fresh batch) scenario showed narrower differences because compilation overhead - which is the same for both types - dominates on Hyperscale where tempdb is local to the compute node and therefore faster than on a local developer instance.

Maximum Creation Rate

In stored procedure context with tempdb object caching active, DiskTV bare-declare rates actually exceeded MOTV rates (34K/sec vs 22K/sec) because the cached truncate is cheaper than MOTV's pool allocation and index creation. With single-row INSERTs, MOTVs took the lead at 8.4K/sec vs 5.0K/sec. In the uncached case, MOTVs were 27x faster for 1-row creates (7.9K/sec vs 294/sec), since DiskTVs pay the full tempdb creation penalty on every call.

The key finding is that raw creation rate is not the main differentiator - it is the data path (INSERT/query) under concurrency and without caching where MOTVs deliver transformative improvement.

Memory Usage

One common concern about MOTVs is their memory consumption. However, DiskTVs also use memory - they live in the buffer pool. The real question is the difference.

Rows	DiskTV (buffer pool)	MOTV NCI	MOTV Hash (15K buckets)
10	40 KB	64 KB (1.6x)	128 KB (3.2x)
1,000	680 KB	1,344 KB (2.0x)	-
10,000	5,528 KB	7,168 KB (1.3x)	2,368 KB (0.4x)

At small row counts (10 rows), the difference is just 24 KB - entirely irrelevant. At 10,000 rows with a hash index, MOTVs actually use less memory than DiskTVs (2,368 KB vs 5,528 KB). For the target use case of high-frequency, small tables, 1,000 concurrent sessions with 10-row MOTVs would use approximately 64 MB of memory - a modest amount on any modern server.

Hash indexes are significantly more memory-efficient than nonclustered indexes for larger row counts. The bucket array itself is small (8 bytes per bucket), so changing the bucket count has limited impact on total memory consumption. However, bucket count has a critical impact on performance: an undersized count causes long hash chains that severely degrade INSERT and lookup speed. Follow the sizing guidance - set the bucket count to 1-2x the expected number of distinct key values.

When to Use MOTVs

Following Paul White's guidance that table variables work best "when the data is small and when plan selection does not depend on the values present", the scenarios where MOTVs help most are:

Very high frequency execution. Stored procedures that execute thousands of times per second, each using a small table variable, benefit most from MOTV's faster population path.
Concurrent sessions sharing tempdb. MOTVs are decoupled from tempdb, so they do not contend on shared PFS/GAM allocation latches that slow DiskTVs under concurrency.
Resilience to tempdb stalls. A tempdb autogrow event pauses all tempdb operations. On an 80-vCore Azure SQL Database running 2,000 batch requests per second with table variables, that can exhaust the worker thread pool in seconds. MOTVs are unaffected.
Data ingestion buffers. Using MOTVs as table-valued parameter (TVP) buffers avoids the tempdb log throughput that on-disk TVPs generate, as described in this IoT ingestion case study.

Scenarios where MOTVs are not a good fit:

Plan quality depends on data shape. If the optimizer needs accurate cardinality estimates - large row counts, complex joins requiring column histograms - table variables (regular or memory-optimized) are the wrong tool. Use temp tables instead.
Low-frequency, large data loads. MOTVs do not support parallel INSERT, so bulk loading large volumes into a MOTV will be single-threaded. At high execution frequency, going parallel can cause more problems than it solves, but for infrequent large loads, DiskTVs or temp tables are a better choice.
DML on the table variable after population. MOTVs use the In-Memory OLTP versioning engine, which retains every version of every row. Garbage collection cannot reclaim these versions while the variable is in scope, so if code loads data into a MOTV and then updates rows - for example, calculating a scoring field - each UPDATE creates a new row version that persists until the variable is destroyed. Memory consumption can double or more depending on the number of update passes. This is a significant anti-pattern for MOTVs.
Long-running transactions. For the same reason, a MOTV used inside a long-running transaction accumulates row versions that cannot be reclaimed by garbage collection until the variable goes out of scope. A common example is a single-row "next ID" table that is updated on every call - while each individual update is fast, every version is retained, and memory will grow continuously.

When Does It Not Matter?

In cached mode (stored procedure) with 10 or fewer concurrent sessions, the MOTV advantage is 1.2-1.3x - meaningful but not dramatic. If your workload uses stored procedures exclusively and runs at low concurrency, the benefit of switching to MOTVs is modest.

The case for MOTVs is strongest when any of these conditions apply:

Execution is uncached - ad-hoc queries, ORM-generated SQL, or dynamic SQL
Concurrency is moderate to high - 10 or more concurrent sessions using table variables
Table variables are used at high frequency - hundreds or thousands of times per second
Tail latency matters - even occasional 10x spikes in DiskTV latency are unacceptable

Conclusion

MOTVs deliver a modest but real improvement in stored procedure workloads at low concurrency. Under conditions commonly seen in production - uncached execution, moderate concurrency, and high frequency - the improvement becomes dramatic, reaching up to 57x faster in our tests. The change requires no application code modification beyond the table type definition, and the memory overhead is manageable for the small-table workloads where MOTVs are most beneficial.

Test Scripts

The scripts used for all benchmarks in this post are included below. Each can be run independently on any Microsoft SQL instance (SQL Server 2019+, Azure SQL MI, or Azure SQL Database) with a database configured with a MEMORY_OPTIMIZED_DATA filegroup.

Setup: Table Types

-- Regular (DiskTV) table type CREATE TYPE dbo.BlogTest_Regular AS TABLE ( ID int NOT NULL PRIMARY KEY, ProductName nvarchar(50) NOT NULL, Quantity int NOT NULL, UnitPrice decimal(10,2) NOT NULL, OrderDate datetime2(0) NOT NULL ); GO -- Memory-optimized (MOTV) table type CREATE TYPE dbo.BlogTest_MemOpt AS TABLE ( ID int NOT NULL PRIMARY KEY NONCLUSTERED, ProductName nvarchar(50) NOT NULL, Quantity int NOT NULL, UnitPrice decimal(10,2) NOT NULL, OrderDate datetime2(0) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON); GO

Test: Three-Phase Breakdown (Create/Populate/Query)

-- 3-phase breakdown: single session, 1000 iterations -- Measures DECLARE, INSERT, and SELECT phases independently DECLARE @Iterations int = 1000, @Warmup int = 100; DECLARE RAW TABLE (TestName nvarchar(60), i int, CreateUS bigint, PopulateUS bigint, QueryUS bigint, TotalUS bigint); DECLARE @i int, T0 datetime2(7), @t1 datetime2(7), t2 datetime2(7), T3 datetime2(7), @sink decimal(18,2); -- Warmup SET @i=0; WHILE @i < @Warmup BEGIN DECLARE @w dbo.BlogTest_Regular; INSERT INTO @w (ID,ProductName,Quantity,UnitPrice,OrderDate) SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)), CONCAT('P-', ROW_NUMBER() OVER(ORDER BY(SELECT NULL))), ABS(CHECKSUM(NEWID())) % 100 + 1, CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME()) FROM sys.all_objects; SELECT @sink = SUM(Quantity * UnitPrice) FROM @w WHERE Quantity > 20; DELETE FROM @w; SET @i += 1; END; -- DiskTV measured run SET @i = 0; WHILE @i < @Iterations BEGIN SET T0 = SYSDATETIME(); DECLARE @r dbo.BlogTest_Regular; SET @t1 = SYSDATETIME(); INSERT INTO @r (ID,ProductName,Quantity,UnitPrice,OrderDate) SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)), CONCAT('P-', ROW_NUMBER() OVER(ORDER BY(SELECT NULL))), ABS(CHECKSUM(NEWID())) % 100 + 1, CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME()) FROM sys.all_objects; SET t2 = SYSDATETIME(); SELECT @sink = SUM(Quantity * UnitPrice) FROM @r WHERE Quantity > 20; SET T3 = SYSDATETIME(); DELETE FROM @r; INSERT INTO RAW VALUES('DiskTV', @i, DATEDIFF_BIG(MICROSECOND, T0, @t1), DATEDIFF_BIG(MICROSECOND, @t1, t2), DATEDIFF_BIG(MICROSECOND, t2, T3), DATEDIFF_BIG(MICROSECOND, T0, T3)); SET @i += 1; END; -- Repeat warmup + measured run for MOTV (replace BlogTest_Regular with BlogTest_MemOpt) -- ... (same pattern, change type name) -- Results SELECT TestName, AVG(CreateUS) AS Avg_Create_US, AVG(PopulateUS) AS Avg_Populate_US, AVG(QueryUS) AS Avg_Query_US, AVG(TotalUS) AS Avg_Total_US FROM RAW GROUP BY TestName ORDER BY TestName;

Test: Caching Scenarios (Cached vs Uncached)

-- Scenario A: Stored Procedure (cached) CREATE PROCEDURE dbo.usp_Test_DiskTV AS BEGIN SET NOCOUNT ON; DECLARE tv dbo.BlogTest_Regular; INSERT INTO tv (ID,ProductName,Quantity,UnitPrice,OrderDate) SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)), CONCAT('P-', ROW_NUMBER() OVER(ORDER BY(SELECT NULL))), ABS(CHECKSUM(NEWID())) % 100 + 1, CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME()) FROM sys.all_objects; DECLARE @sink decimal(18,2); SELECT @sink = SUM(Quantity * UnitPrice) FROM tv WHERE Quantity > 20; END; GO -- Scenario A (MOTV variant): same procedure using the memory-optimized type CREATE PROCEDURE dbo.usp_Test_MOTV AS BEGIN SET NOCOUNT ON; DECLARE tv dbo.BlogTest_MemOpt; INSERT INTO tv (ID,ProductName,Quantity,UnitPrice,OrderDate) SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)), CONCAT('P-', ROW_NUMBER() OVER(ORDER BY(SELECT NULL))), ABS(CHECKSUM(NEWID())) % 100 + 1, CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME()) FROM sys.all_objects; DECLARE @sink decimal(18,2); SELECT @sink = SUM(Quantity * UnitPrice) FROM tv WHERE Quantity > 20; END; GO -- Scenario C: Fresh batch (uncached) - run each iteration via EXEC() DECLARE @sql nvarchar(max) = N' DECLARE tv dbo.BlogTest_Regular; INSERT INTO tv (ID,ProductName,Quantity,UnitPrice,OrderDate) SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)), CONCAT(''P-'', ROW_NUMBER() OVER(ORDER BY(SELECT NULL))), ABS(CHECKSUM(NEWID())) % 100 + 1, CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME()) FROM sys.all_objects; DECLARE @sink decimal(18,2); SELECT @sink = SUM(Quantity*UnitPrice) FROM tv WHERE Quantity > 20;'; DECLARE @i int = 0, T0 datetime2(7), @t1 datetime2(7); SET T0 = SYSDATETIME(); WHILE @i < 1000 BEGIN EXEC(@sql); SET @i += 1; END; SET @t1 = SYSDATETIME(); SELECT DATEDIFF_BIG(MICROSECOND, T0, @t1) / 1000 AS Avg_US_Per_Iteration;

Test: Concurrent Sessions (PowerShell driver)

# Run N concurrent sessions, each executing a stored procedure $server = "YOUR-SERVER-INSTANCE" # e.g. "localhost\sql25" or "myserver.database.windows.net" $db = "YOUR-DATABASE" # database with MEMORY_OPTIMIZED_DATA filegroup foreach ($level in @(1, 10, 25)) { foreach ($type in @('DiskTV','MOTV')) { $proc = "dbo.usp_Test_$type" Write-Host "--- $type at $level sessions ---" $jobs = @() for ($s = 0; $s -lt $level; $s++) { $sql = "EXEC $proc @Iterations=500, @Warmup=50;" $jobs += Start-Job -ScriptBlock { param($svr, $database, $query) sqlcmd -S $svr -d $database -Q $query -t 600 } -ArgumentList $server, $db, $sql } $jobs | Wait-Job | Out-Null $jobs | Remove-Job -Force Write-Host " Done." } }

Test: Memory Usage Comparison

-- Helper function: current MEMORYCLERK_XTP total (KB) CREATE OR ALTER FUNCTION dbo.fn_XTP_MemoryKB() RETURNS bigint AS BEGIN DECLARE KB bigint; SELECT KB = SUM(pages_kb + virtual_memory_committed_kb + awe_allocated_kb + shared_memory_committed_kb) FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_XTP'; RETURN ISNULL(@kb, 0); END; GO -- Measure MOTV memory: capture XTP clerk before and after populating DECLARE Baseline bigint = dbo.fn_XTP_MemoryKB(); DECLARE tv dbo.BlogTest_MemOpt; INSERT INTO tv (ID, ProductName, Quantity, UnitPrice, OrderDate) SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)), CONCAT('P-', ROW_NUMBER() OVER(ORDER BY(SELECT NULL))), ABS(CHECKSUM(NEWID())) % 100 + 1, CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME()) FROM sys.all_objects; DECLARE @after bigint = dbo.fn_XTP_MemoryKB(); SELECT @after - Baseline AS MOTV_Memory_KB; GO -- Measure DiskTV memory: count buffer pool pages in tempdb -- Run this in a stored proc so the TV is alive during measurement CREATE OR ALTER PROCEDURE dbo.usp_MeasureDiskTV @RowCount int AS BEGIN SET NOCOUNT ON; DECLARE tv dbo.BlogTest_Regular; INSERT INTO tv (ID, ProductName, Quantity, UnitPrice, OrderDate) SELECT TOP(@RowCount) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)), CONCAT('P-', ROW_NUMBER() OVER(ORDER BY(SELECT NULL))), ABS(CHECKSUM(NEWID())) % 100 + 1, CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME()) FROM sys.all_objects a CROSS JOIN sys.all_objects b; -- Count tempdb pages allocated to this session's internal objects SELECT (internal_objects_alloc_page_count - internal_objects_dealloc_page_count) * 8 AS DiskTV_Memory_KB FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID; END; GO EXEC dbo.usp_MeasureDiskTV @RowCount = 50;

Note: DiskTV memory measurement via dm_db_task_space_usage can return 0 for small row counts when pages are cached. For DiskTV, the buffer pool query from dm_os_buffer_descriptors filtered to tempdb is the most reliable method, but requires a second session to query while the first holds the TV alive. The MOTV measurement via MEMORYCLERK_XTP differencing works reliably on a quiet instance.

Feedback and Suggestions

If you have feedback or suggestions, please contact the Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com).

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Microsoft Options to Migrate SQL Server Databases

Mitch_van_Huuksloot — Wed, 17 Jun 2026 15:04:50 GMT

Overview

This blog supplements the Migrate to Azure SQL documentation and tries to summarize the Microsoft first party ways to copy or migrate SQL Server databases from on‑premises environments to one of the flavours of SQL Server running in Azure. It is organized first by target platform (Azure SQL Database/Hyperscale, Azure SQL Managed Instance and Azure SQL Virtual Machine) and then by tool (for example Azure DMS, Managed Instance Link, Distributed Availability Groups, SqlPackage, and data copy utilities). For each tool, you’ll find supported targets, prerequisites, whether it can run online (continuous change replication) or requires offline downtime, and the common limitations that influence tool choice.

Azure SQL Target Platforms

Azure SQL Database (including Hyperscale) – Fully managed by Microsoft running as Platform as a Service (PaaS), the service provides database scoped access with no visibility between databases on a logical server. Some SQL Server features are not available (i.e. backup/restore) so migrations need to use logical transfer or export/import of data.
Azure SQL Managed Instance (MI) – Fully managed by Microsoft running as Platform as a Service (PaaS), the service provides SQL Server instance scoped access and near-full SQL Server surface area. MI supports specialized migration/replication options such as Managed Instance Link as well as backup/restore based (Log Replay Service (LRS)) approaches.
Azure SQL Virtual Machine (SQL Server on Azure VM) – This option uses Azure Infrastructure as a Service (IaaS). You manage the OS (Windows or Linux) and SQL Server. Since you are installing the full box product of SQL Server, this target supports all native SQL Server features on that OS platform.

SQL Server everywhere else – For completeness, this includes SQL Server running on-premises, another VM, or other cloud providers. Tools such as SqlPackage, SmartBulkCopy, and SqlDBCopy can copy data to any SQL Server targets when connectivity between the servers is available.

Key Decision Factors

Downtime tolerance: Do you need an online migration (continuous replication) with a brief cutover phase or is an offline data migration approach acceptable?
Scope: One database, many databases, or full instance and feature usage (replication, CLR, agent jobs, cross database query, linked servers, SSIS, SSAS, SSRS etc.)?
Size and throughput: The PaaS offerings have maximum databases sizes that vary across each service. Ensure your growth and performance requirements are met by the service you select. For migrations, large databases may favor backup/restore and log shipping type approaches (if available) over row-by-row copy.
Network connectivity: VPN/ExpressRoute, firewall rules, and whether you can deploy a self-hosted Integration Runtime (IR) or agents.
Schema compatibility: Azure SQL Database/Hyperscale has the most feature differences; MI and SQL VM are closer to SQL Server.
Operational constraints: Change control on production, ability to install agents, and security requirements for credentials and encryption.
Cost model: Some approaches incur ongoing compute costs (for example ADF pipeline runs, IRs, interim storage, or extra replicas during migration).

Migration Assessments (SSMS 22+ and Azure Arc)

Before moving an on‑premises SQL Server database to Azure SQL Database/Hyperscale or Azure SQL Managed Instance, you should run a migration assessment. Assessments identify feature incompatibilities, behavior differences, and configuration gaps that are easy to miss until late in the project (for example unsupported instance features in Azure SQL Database, cross-database dependencies, SQL Agent usage, CLR/extended procedures, or deprecated syntax). Running an assessment early reduces rework, helps you choose the right PaaS target (SQL DB vs. MI), and informs remediation tasks and sizing/performance planning.

Starting with SSMS 22+, assessment capabilities are built into the primary management tool many DBAs already use, making this a low-friction, repeatable step. The assessment results typically highlight blocking issues, warnings, and recommended changes, along with links to guidance so teams can prioritize remediation. Treat this as a gating step for PaaS migrations: run it for every candidate database, track findings as work items, and re-run after remediation and again before cutover.

For organizations managing multiple on‑premises and edge SQL Servers, Azure Arc-enabled SQL Server can extend Azure management capabilities to your existing estate. Arc can provide centralized inventory, performance monitoring, policy-based governance, and (where configured) automatic/recurring assessments so you can continuously measure readiness for Azure SQL PaaS.

When to assess: Run an assessment as soon as a database is a candidate for Azure SQL DB/HS or MI, then re-run after fixes and before cutover.
What you learn: Compatibility blockers, feature gaps, and refactoring guidance; inputs to target selection (SQL DB vs. MI) and to migration method choice.
SSMS 22+ workflow: Use the built-in assessment experience to generate findings you can track and share with dev/app teams.
Arc value-add: Enable Arc on on‑premises SQL Server to get centralized management, performance monitoring, and ongoing assessments across your estate.
Planning integrations: Use assessment outputs to drive remediation backlogs and to inform Azure DMS-based migration projects (and associated connectivity/identity planning). For larger estates, Azure Migrate assessments can complement SSMS/Arc by providing discovery at scale plus readiness, sizing, and cost estimates for Azure SQL targets.

Tooling by Target (at a glance)

Target	Online (minimal downtime) options	Offline (requires downtime) options
Azure SQL Database / Hyperscale	ADF with CDC/CT, Transactional Replication (push subscription required), SqlDBCopy with CT	Azure Migrate (discovery/assessment), Azure Arc SQL Migration Tooling (Arc-enabled source assessment/orchestration), Azure DMS offline, ADF copy, SqlDBCopy, SmartBulkCopy, SSIS, SqlPackage (BACPAC)
Azure SQL Managed Instance	Managed Instance Link, Azure DMS online, Log Replay Service (LRS), Transactional Replication, ADF copy with CDC/CT, BlobShipper, SqlDBCopy with CT	Azure Migrate (discovery/assessment), Azure Arc SQL Migration Tooling (Arc-enabled source assessment/orchestration), LRS, Azure DMS (offline), ADF copy, SqlDBCopy, SmartBulkCopy, SSIS, SqlPackage (BACPAC)
Azure SQL VM	Distributed Availability Groups (DAG), Transactional Replication, BlobShipper, Log Shipping, ADF with CDC/CT, SqlDBCopy with CT	Azure Migrate (discovery/assessment), Azure Arc SQL Migration Tooling (Arc-enabled source assessment/orchestration), Backup/restore, Azure DMS (offline), Transactional Replication, ADF copy with CDC/CT, SqlDBCopy, SmartBulkCopy, SSIS, SqlPackage (BACPAC)
SQL Server anywhere	Possibly Distributed AG, Transactional Replication, SqlDBCopy with CT	Backup/Restore, SqlDBCopy, SmartBulkCopy, SSIS, SqlPackage (BACPAC)

Note: Exact capabilities depend on SQL Server version/edition, database features in use, and target service limitations. Some approaches copy data only (table-level) while others move a full database or instance. Always validate feature compatibility and run a proof-of-concept with production-like data volumes.

Tool Deep Dives

Azure Migrate

What it is: Azure Migrate is the Azure hub for discovery, assessment, migration planning, and lift and shift migrations across servers, apps, and data platforms. For SQL Server specifically, Azure Migrate can discover SQL instances and databases (via an appliance or other supported discovery methods) and produce Azure SQL assessments that estimate readiness, right-size recommendations, and cost for targets such as SQL Server on Azure VM, Azure SQL Managed Instance, and Azure SQL Database.

Where it fits: Use Azure Migrate early to build an inventory, group related workloads, understand dependencies, and generate sizing/cost guidance before you choose an execution tool (for example Azure DMS, MI Link, backup/restore, replication, or table-level copy).
Discovery approach: Typically uses an Azure Migrate appliance for agentless discovery of servers and workloads (including SQL Server instances/databases) and ongoing performance collection; assessment quality improves when you collect representative performance data over time.
Assessment outputs: Readiness (ready/conditional/not ready), recommended target (SQL VM vs. MI vs. SQL DB where applicable), right-sized SKU/tier, and cost estimates based on configuration-only or performance-based sizing.
Strengths: Works well at estate scale; provides a consistent, repeatable assessment methodology and helps prioritize migration waves and modernization candidates.
Limitations / considerations: Azure Migrate is primarily a planning and assessment tool for SQL; the actual database move is typically executed with tools like Azure DMS, MI Link, backup/restore-based approaches, replication, or data copy utilities. Treat Azure Migrate recommendations as inputs—validate with workload-specific testing and SQL feature compatibility checks (for example SSMS assessment results) before cutover.

Azure Database Migration Service (Azure DMS)

What it is: A managed Azure service that orchestrates database migrations using guided workflows. Depending on the source/target combination, it can perform offline (one-time move) or online (continuous sync + cutover) migrations.

Typical targets: Azure SQL Database/Hyperscale (offline), Azure SQL Managed Instance (online), and SQL Server on Azure VM.
Prerequisites: Network connectivity from DMS to source/target (often via VPN/ExpressRoute), required firewall openings, and appropriate permissions on both ends.
Strengths: Guided experience, assessment integration, and support for multiple database moves with monitoring.
Limitations / considerations: Feature support varies by scenario; some migrations are offline only. DMS orchestrates the process but still depends on connectivity and may have constraints around file placement for restore-based workflows. Plan for validation, cutover steps, and potential reconfiguration tasks (logins, jobs, SSIS, etc.) outside the database move itself.

Azure SQL Managed Instance Link (MI Link)

What it is: A managed replication capability designed specifically for migrating from SQL Server to Azure SQL Managed Instance with near-real-time synchronization and a controlled cutover experience.

Supported targets: Azure SQL Managed Instance only.
Online/offline: Online synchronization with a short cutover window.
Prerequisites: Supported SQL Server versions/editions and configurations, network connectivity to the MI endpoint, and permissions to configure the link. (Review current product documentation for the exact supported matrix.)
Strengths: Designed for MI migrations; reduces the need for custom change-capture pipelines; supports validation and coordinated cutover.
Limitations: Not usable for Azure SQL VM or Azure SQL Database targets. Feature support and prerequisites are scenario-specific; plan for testing and for migrating instance-level objects and application connectivity during cutover.

Log Replay Service (LRS)

What it is: LRS is a managed Azure SQL Managed Instance migration service that restores native SQL Server full, differential, and log backups from Azure Blob Storage to keep a target database catching up until cutover. It is useful when you want a backup/restore-based migration pattern with more control over orchestration, or when other guided migration tooling cannot be used because of environment, network, or operational constraints.

Supported targets: Azure SQL Managed Instance only.
Online/offline: Commonly used as a minimal-downtime migration method in continuous mode by repeatedly restoring new log backups until cutover. It can also be used in an autocomplete pattern when the full backup chain is already available and you simply want the restore to run to completion.
Prerequisites: Source databases must use the full recovery model, and you need a valid backup chain (full, optional differential, and log backups) accessible in Azure Blob Storage. Plan storage layout carefully—each database should use its own folder path—and validate backup integrity (CHECKSUM recommended) before migration.
Strengths: Supports migrations from SQL Server hosted almost anywhere, including on-premises, VMs, and other clouds; supports differential backups; and gives you direct control through PowerShell, Azure CLI, or APIs for custom orchestration and hybrid migration patterns.
Limitations / considerations: Databases being restored through LRS are not usable until migration finishes (no read-only access during restore). After cutover, the migration is final and can’t be resumed with additional differential backups. Review current service-tier and feature limitations—especially for Business Critical—and schedule maintenance windows and cutover steps carefully.

Azure Arc SQL Migration Tooling

What it is: Azure Arc-enabled SQL Server adds an Azure portal experience for continuous migration assessment and, for supported scenarios, integrated migration workflows. It helps you discover Arc-enabled SQL Server instances, evaluate readiness for Azure SQL targets, compare recommended destinations and sizing, and then move into guided migration execution without stitching together separate tools by hand.

Where it fits: Best for organizations that have already Azure Arc-enabled their SQL Server estate and want a more centralized migration journey covering discovery, readiness, target recommendation, and guided execution from the Azure portal.
Assessment outputs: Arc can continuously generate migration readiness results, identify risks and mitigation guidance, recommend a target type (Azure SQL Managed Instance, Azure SQL Database, or SQL Server on Azure VM), suggest sizing/service tier, and provide retail cost estimates to support migration planning.
Supported migration direction: Arc-based SQL migration tooling is centered on Arc-enabled source SQL Server instances. Current portal experiences focus on integrated migration to Azure SQL targets, including Azure SQL Managed Instance, with expanding support for SQL Server on Azure VM scenarios.
Strengths: Consolidates assessment and migration activities into one operational surface, reduces context switching, helps compare modernization-vs.-cost strategies, and keeps assessments refreshed on a schedule so you can track readiness over time.
Limitations / considerations: Requires the source SQL Server to be enabled by Azure Arc, and some migration experiences are still scenario- or platform-specific and at the time of this article limited to 10 databases per migration workflow. Treat Arc guidance as a planning and orchestration layer—validate the recommended target, chosen migration method, feature compatibility, and operational prerequisites with workload-specific testing before production cutover.

Transactional Replication

What it is: A SQL Server replication technology that continuously streams committed changes from a Publisher to one or more Subscribers via a Distributor. For migrations, it is commonly used to keep a target synchronized with minimal downtime, then cut over applications to the subscriber.

Supported targets: SQL Server on Azure VM, Azure SQL Managed Instance, Azure SQL Database/Hyperscale, and SQL Server anywhere - subject to replication feature support and version compatibility.
PaaS subscription model: When the subscriber is a PaaS target (Azure SQL Database or Azure SQL Managed Instance), you typically use a push subscription so that the on‑premises publisher/distributor (or an IaaS SQL Server acting as distributor) connects outbound to the PaaS subscriber.
Distributor placement: You can host the Distributor on‑premises (common when outbound connectivity to Azure is allowed and you want to keep replication infrastructure local), or on a SQL Server on Azure VM (common when you want replication infrastructure closer to the cloud target, to simplify network paths, or to avoid routing large snapshot/distribution traffic across the WAN). In both cases, ensure the Distributor can reach the Publisher and can make outbound connections to the PaaS subscriber for push subscriptions.
Online/offline: Online data movement after initialization; cutover requires a brief downtime window to quiesce writes, allow the subscriber to catch up, and switch application connections.
Prerequisites: A configured Distributor, reliable network connectivity, and appropriate permissions. Articles generally need a primary key (or a defined unique row identifier) for updatable patterns; initialization uses a snapshot that must be delivered to the subscriber.
Strengths: Proven low-latency replication for many OLTP workloads; supports one-to-many topologies; useful when you want to keep a cloud target warm for validation and phased cutover.
Limitations: Operational overhead (monitoring agents, distribution database growth, troubleshooting latency). Not a full instance migration (logins, jobs, etc. are separate). Some schema changes require careful coordination and may force reinitialization. Large objects, high churn, or very large initial snapshots can increase time and storage requirements. Plan identity range management, constraints, and replication of DDL based on your workload and target.

Distributed Availability Groups (DAG)

What it is: A SQL Server feature that replicates databases between two separate Availability Groups. It can be used for migration by establishing asynchronous data movement to a secondary AG and then performing a controlled cutover.

Supported targets: SQL Server only targets—commonly SQL Server on Azure VM. (Not applicable to Azure SQL Database or Managed Instance.)
Online/offline: Online replication with a short cutover window; can provide very low data loss when configured appropriately.
Prerequisites: Availability Groups and underlying clustering infrastructure (WSFC on Windows or Pacemaker on Linux), compatible SQL Server editions, and network connectivity between replicas.
Strengths: Mature HA/DR technology with robust monitoring; suitable for larger databases and for keeping a warm secondary environment.
Limitations: Higher complexity and operational overhead (clusters, AG configuration, certificates/endpoints). Not a lightweight option when change control or infrastructure constraints are tight.

BlobShipper Service (log shipping via Azure Blob Storage)

What it is: The BlobShipper application is a Windows service that automates SQL Server full and log backups to Azure Blob Storage and restores them to a target SQL Server (commonly on an Azure SQL VM). It is a backup-driven approach intended to minimize production changes while enabling low-downtime cutover.

Supported targets: SQL Server on Azure VM (primary), and generally SQL Server-to-SQL Server scenarios where backup/restore is available.
Online/offline: Online up to cutover (continuous log shipping), then a short downtime window for the final log backup/restore and application cutover.
Prerequisites: Ability to take full + log backups, Azure Storage account access, and connectivity from the target to read backups from Blob Storage.
Strengths: Minimal change to production beyond taking ownership of the backup/log chain; controlled file placement on the target; can optionally apply logs with a delay to provide a read-only fallback window.
Limitations: Target must support restoring native SQL Server backups (therefore not Azure SQL Database). Log chain control means you typically stop other backup jobs for the migrated databases while the service runs.

Log Shipping (native SQL Server)

What it is: A built-in SQL Server feature that keeps a secondary database synchronized by repeatedly backing up transaction log backups on the primary, copying them to a secondary location, and restoring them on the secondary. For migrations to Azure, Log Shipping is commonly used from on‑premises SQL Server to SQL Server on Azure VM to achieve low downtime cutover.

Supported targets: SQL Server only - including SQL Server on Azure VM. (Not applicable to Azure SQL Database or Azure SQL Managed Instance.)
Online/offline: Online log restore cycles up to cutover; downtime is typically limited to the final log backup/restore and application connection switch.
Prerequisites: The source database must be in full recovery model with a healthy log chain, and you need a mechanism to copy log backups from on‑premises to the Azure VM (file share, storage account, or other transfer path). Agent jobs run on both ends to schedule backup/copy/restore.
Strengths: Simple, well-understood, and works with very large databases; provides a warm standby you can validate before cutover.
Limitations: Asynchronous by design (some data loss is possible if you cut over before the last logs are applied). Requires ongoing management of backup files, schedules, and retention. You must coordinate other backup jobs so they don’t break the log chain and ensure you can meet RPO/RTO.
Relationship to BlobShipper: BlobShipper automates a similar log-shipping pattern using Azure Blob Storage as the transfer medium and adds operational conveniences (for example, controlled restore timing and simplified blob-based transport).

Azure Data Factory (ADF) copy pipelines

What it is: A cloud data integration service that can copy data from on‑premises SQL Server to Azure data stores (including Azure SQL Database, Managed Instance, and SQL Server on Azure VM) using configurable pipelines.

Prerequisites: For on‑premises sources you typically deploy a Self-hosted Integration Runtime (IR) in your network (or a connected network) to enable secure connectivity. You also configure linked services, datasets, and credentials.
Online/offline: ADF copy itself is often offline for consistent point-in-time results, but you can implement incremental patterns (for example, watermark columns, Change Tracking/CDC-based extracts, or time-window batching) to reduce cutover downtime. Our team has a custom tool to make this process much easier - if you are interested, reach out to us.
Strengths: Works across many sources/targets; good for selective table moves, transformations, and repeatable pipeline operations.
Limitations / considerations: Throughput is influenced by IR sizing, source/target performance, network bandwidth, and parallelism settings. Pipeline executions incur costs (activity runs, data movement, and IR compute if you use Azure-hosted resources). You are responsible for schema management and for handling constraints/ordering, as well as for designing incremental logic and validation.

SSIS (SQL Server Integration Services)

What it is: A data integration and ETL technology in SQL Server that can extract data from an on‑premises SQL Server and load it into many targets. SSIS is often used as an offline copy mechanism for selected tables (or full database data via multiple packages) when you already have SSIS skills/infrastructure or when you need transformations during the move.

Supported targets: SQL Server on Azure VM, Azure SQL Managed Instance, Azure SQL Database/Hyperscale, and many other destinations via connectors/drivers.
Online/offline: Most SSIS-driven copies are offline point-in-time loads. Incremental loads are possible but require package design (watermarks, CDC/CT patterns, staging, and merge logic).
Prerequisites: An execution environment for SSIS packages (for example SQL Server Integration Services on-prem, or an Azure-hosted SSIS runtime where applicable), connectivity from the runtime to source and target, and appropriate drivers/connectors and credentials.
Strengths: Mature tooling, rich transformation support, and strong control over batching, error handling, and staging.
Limitations / considerations: Package development and operations can be complex at scale. Performance depends on runtime sizing and network throughput. Like other table-level copy approaches, SSIS does not automatically migrate schema/instance objects unless you build that into the process. For cloud-first pipelines, ADF may be preferred for managed scheduling/monitoring, but SSIS remains a solid choice when you already have packages and patterns.

SqlDBCopy (offline copy and Change Tracking–based incremental copy)

What it is: SqlDBCopy is a utility that can copy tables between SQL Server endpoints either as a one-time offline operation or incrementally by leveraging SQL Server Change Tracking to move only changed rows.

Supported targets: SQL Server, Azure SQL VM, Azure SQL Managed Instance, and Azure SQL Database (depending on connectivity).
Offline mode: Copies a static snapshot of data; best when you can stop writes or when the data set is reference/static.
Incremental mode (Change Tracking): Can continuously or periodically apply changes for tables where Change Tracking is enabled and where tables have a primary key. This enables lower-downtime migration patterns for selected tables.
Prerequisites: Target schema and keys must exist; Change Tracking must be enabled on the source database and tables used for incremental copy; plan retention/cleanup so changes aren’t lost before they’re applied.
Limitations: Change Tracking does not capture all DDL changes—schema evolution must be managed separately. Tables without primary keys are not suitable for Change Tracking–based incremental copy. Large change volumes may require tuning batch sizes and scheduling to keep up.

SmartBulkCopy (table-level bulk copy utility)

What it is: A table-level bulk copy approach for moving data between SQL Server-compatible endpoints using high-throughput bulk operations. It is useful when you want to copy selected tables (not a whole database) or when backup/restore-based methods aren’t available.

Supported targets: SQL Server, Azure SQL VM, Azure SQL Managed Instance, and Azure SQL Database (connectivity and permissions permitting).
Online/offline: Commonly offline for consistent results unless you implement your own change capture and replay pattern.
Prerequisites: The target schema must already exist (tables, indexes as needed). You also need appropriate permissions for bulk insert/copy and sufficient target throughput.
Strengths: Fast for large table copies; you can scope to a subset of tables and parallelize work.
Limitations: Does not automatically migrate schema, users/logins, or instance-level objects. Maintaining referential integrity and identity values may require careful ordering and settings. If you need minimal downtime, you must add a separate mechanism to capture and apply ongoing changes.

SqlPackage (BACPAC export/import and DAC deployment)

What it is: A command-line utility used to export a database to a BACPAC (schema + data) and import it into a target, or to publish schema changes using a DACPAC. It is commonly used to move databases to Azure SQL Database or to create a portable logical copy for SQL Server targets.

Supported targets: Azure SQL Database/Hyperscale, Azure SQL Managed Instance, and SQL Server (including SQL Server on Azure VM) depending on options used.
Online/offline: Typically offline for a full data move—export from the source represents a point-in-time snapshot. To avoid data drift, you usually stop writes or accept a reconciliation step.
Strengths: Simple packaging model; good for smaller databases, dev/test cloning, and scenarios where logical export is preferred over backup/restore.
Limitations: Can be slow for large databases (export/import is largely single-threaded and sensitive to network and target performance). Requires enough local or intermediate storage for the BACPAC. It is not a minimal downtime option. Some objects/features may not be supported on the target platform (especially Azure SQL Database), and you may need to resolve compatibility issues before import.

Security Considerations

Security planning is a critical part of any on‑premises to cloud migration. In addition to securing network paths (VPN/ExpressRoute, firewall rules, Private Link where applicable) and protecting credentials used by migration tools, you should plan for how identity and access will work on the target platform after cutover.

For Azure SQL PaaS targets (Azure SQL Database and Azure SQL Managed Instance), many organizations choose to standardize on Microsoft Entra ID (formerly Azure AD) authentication. This can require translating existing on‑premises Active Directory logins and database users into Entra-based identities and groups and then remapping database permissions, so applications, users and administrators retain the correct access.

Inventory and map identities: List server logins, database users, role memberships, and explicit GRANT/DENY permissions on the source. Decide which should become Entra users vs. Entra groups (recommended for manageability) and confirm synchronization/hybrid identity requirements with your identity team.
MoveLogins: Use this PowerShell script to help script existing logins, users, roles and permissions and use the AD lookup function to map the AD logins to Entra UPNs. The script can then be reviewed and applied to the target server. (Contact our team if you need this script).
ScriptLoginsAndUsers: Use this utility to script out Entra and SQL logins, users, roles and permission so you can recreate the security structure on the target. This is especially helpful when you are moving PaaS databases from one Tenant to another.

Dev/Test with Obfuscated Data

Many migration projects benefit from creating a development or test copy of production data to validate application behavior, performance, and migration tooling. When production contains sensitive information (PII/PHI/PCI or confidential business data), you may need to obfuscate that data before it can be used outside tightly controlled production environments.

Typical use cases: Building realistic dev/test environments, rehearsing migrations and cutovers, enabling troubleshooting by broader teams, and sharing data with partners/vendors where raw production data is not permitted.
Obfuscate tool: Use the Obfuscate utility to transform sensitive columns (for example names, emails, phone numbers, identifiers, free-text fields) into non-sensitive substitutes while preserving useful characteristics (such as format, referential integrity, and distribution patterns) as required by testing scenarios.
Plan the masking strategy: Define which tables/columns are sensitive, which must remain consistent across tables (keys/lookup values), and what level of realism is needed (synthetic values vs. deterministic masking). Document the rules so results are repeatable for refreshes.
Sequence and constraints: Obfuscation often needs to run in a controlled order to preserve foreign keys and uniqueness constraints. Consider disabling/re-enabling constraints or using staging tables if your process requires it.
Security hygiene: Treat the obfuscation process and outputs as sensitive until validated. Restrict access to the obfuscation configuration, logs, and any intermediate exports that may contain raw values.
Verify effectiveness: Add validation steps to confirm that sensitive values are masked, that key relationships still hold, and that the application behaves correctly with the obfuscated dataset.

Data validation

Regardless of which copy or migration mechanism you choose, you may want to plan for data validation immediately before cutover. Validation reduces the risk of silent data drift and missed objects, and it helps you quantify exactly what is left to reconcile before switching applications to the new target.

Database Compare Utility

The Database Compare Utility can validate that source and target have the same schema objects and contain identical data with the option to generate a change script to align the target to the source. It supports comparisons between other heterogeneous database platforms, but it fully supports any SQL Server to SQL Server comparison assuming there is connectivity between the two. The latest version includes a GUI or it can also be executed in batch/console mode. The tool is multi-threaded (configurable parallelism) but can generate significant load on databases and the network. It can do row count verification, table hash verification or row by row verification. For tables with primary keys, it can also generate change scripts. Advanced scenarios can be driven by an Excel input that maps different schema/table/column names, supplies filters to split large tables into parallel tasks, and defines alternate keys or column exclusions.

CPQR Utility

The Compare Query Performance and Result sets (CPQR) utility’s purpose is to compare the execution times and result sets (i.e. every column value for every row) generated by running two queries against two different data sources; one any OLEDB data source and the other a SQL Server data source. Note that although the performance of queries is measured, since multiple queries are being run by the application concurrently, the performance may be affected by other queries that are being run in parallel (to mitigate this you can turn off parallelism).

How to choose an approach

If your target is Azure SQL VM and you need minimal downtime: prefer log/replication approaches such as BlobShipper (log shipping), Transactional Replication, or Distributed Availability Groups, depending on how much infrastructure and change you can accept.
If your target is Azure SQL Managed Instance and you need minimal downtime: start with Managed Instance Link (when supported) or DMS online migrations. Transactional Replication (push subscription) can also work for many patterns when you can manage distributor operations and table/article constraints; use table-level incremental copy (SqlDBCopy/ADF patterns) only when a full database move isn’t feasible.
If your target is Azure SQL Database/Hyperscale: prioritize compatibility assessment early. For smaller databases, SqlPackage (BACPAC) may be acceptable. For larger or lower-downtime needs, consider DMS online (supported scenarios), Transactional Replication (push subscription) for suitable table/article sets, or ADF incremental patterns.
If you only need certain tables or you’re consolidating data: table-level tools (ADF, SmartBulkCopy, SqlDBCopy) can be simpler than full database migrations—just plan schema creation and data consistency.
Always plan the non-data work: logins/users, SQL Agent jobs, maintenance plans, SSIS/SSRS, linked servers, certificates/keys, and application connection changes often dominate the overall cutover effort.

Conclusion

There is no single best migration method for every SQL Server workload. The right choice depends primarily on the Azure target, downtime tolerance, and how closely you need to preserve SQL Server instance features. Use the matrix above to narrow options by target, then validate prerequisites and limitations in a proof-of-concept. For production migrations, include performance testing, data validation, cutover rehearsal, and a rollback plan appropriate to your business risk.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com).

Thank you for your support!

Unlocking the Power of Azure Resource Graph Explorer for Comprehensive Database Inventory

akumaranchath — Fri, 29 May 2026 16:03:38 GMT

Why Taking Inventory is Crucial

Taking inventory of databases in Azure is a critical practice for any organization managing cloud resources. It ensures that all databases are accounted for, providing a comprehensive overview of the organization’s data assets. This visibility is essential for effective management and optimization of resources, as it allows for better planning and allocation based on actual usage and requirements.

An accurate inventory also plays a key role in identifying potential vulnerabilities and compliance issues. By knowing exactly what databases exist and where they reside, organizations can take proactive measures to secure sensitive data and adhere to regulatory standards.

Additionally, maintaining an up-to-date inventory facilitates efficient troubleshooting and maintenance. When issues arise, having detailed insights into the configuration and status of each database significantly reduces resolution time.

In short, keeping an accurate inventory of Azure databases supports operational efficiency, security, and compliance.

Who Does This Matter To

For roles such as product owners or operations managers, this task becomes even more critical. You may need to query the entire data estate to consolidate information for governance, reporting, or strategic planning. Without a centralized view, managing large-scale environments becomes challenging and prone to errors.

How Azure Resource Graph Explorer Helps

Azure Resource Graph Explorer is a powerful tool designed to extend Azure Resource Management by enabling efficient and performant resource exploration across multiple subscriptions. It allows users to:

Query resources with complex filtering, grouping, and sorting by resource properties.
Explore resources at scale across multiple subscriptions.
Iterate queries based on governance requirements.

This makes ARG an ideal solution for managing and governing large-scale environments where visibility and control are paramount.

An Azure Resource Graph query like this one can help users to get an inventory of Azure SQL installations from data estates spanning across multiple subscriptions.

resources | where type =~ "microsoft.sqlvirtualmachine/sqlvirtualmachines" | project SubscriptionId = subscriptionId, ResourceGroup = resourceGroup, Name = name, type, Location = location, tags, elasticPoolId = "", ServiceType = "SQL VM", Edition=tostring(properties.sqlImageSku), Environment=tostring(tags.opEnvironment), PricingModel="vCores", ServiceTier=tostring(properties.sqlImageOffer), IsAHB = iff(tostring(properties.sqlServerLicenseType) =~ "AHUB", "Yes", iff(tostring(properties.sqlServerLicenseType) =~ "PAYG" and tostring(tolower(properties.sqlImageSku)) =~ "Developer", "Yes", "No")) | join kind=inner (resources | where ['type'] =~ 'microsoft.compute/virtualmachines' | project SubscriptionId = subscriptionId, ResourceGroup = resourceGroup, Name = name, Location = location, Num=tostring(properties.hardwareProfile.vmSize)) on Name, ResourceGroup, Location, SubscriptionId | project-away SubscriptionId1,ResourceGroup1,Name1,Location1 | union ( Resources | project SubscriptionId = subscriptionId, ResourceGroup = resourceGroup, Name = name, type, Location = location, tags, elasticPoolId = tolower(tostring(properties.elasticPoolId)), ServiceType = case(type =~ "microsoft.sql/managedinstances", "Azure SQL MI", type =~ "microsoft.sql/servers/databases", iff(tolower(properties.currentSku.tier)=~"datawarehouse", "Synapse", "Azure SQL DB"), type =~ "microsoft.sql/servers/elasticpools", "Azure SQL DB Elastic Pools", "Azure SQL VM"), Edition = case(type in~ ("microsoft.sql/managedinstances","microsoft.sql/servers/databases","microsoft.sql/servers/elasticpools"), iff(tolower(properties.currentSku.tier)=~"datawarehouse", "Dedicated Pools", "Azure SQL") , "SQL VM"), Environment=tostring(tags.opEnvironment), Num = tostring(case(type =~ "microsoft.sql/managedinstances", toint(sku.capacity), type =~ "microsoft.sql/servers/databases", toint(properties.currentSku.capacity), type =~ "microsoft.sql/servers/elasticpools", toint(properties.perDatabaseSettings.maxCapacity), 0)), PricingModel = case(type =~ "microsoft.sql/managedinstances", "vCores", type =~ "microsoft.sql/servers/databases", iff(tolower(properties.currentSku.tier)=~"datawarehouse", "DWU", iff((tolower(sku.tier) in ("basic","standard","premium")), "DTU", "vCores")), type =~ "microsoft.sql/servers/elasticpools", iff((tolower(sku.tier) in ("basic","standard","premium")), "DTU", "vCores"), "vCores"), ServiceTier = case(type =~ "microsoft.sql/managedinstances", sku.tier, tolower(properties.currentSku.tier)), MaxDatabaseSizeGB = iff(tostring(properties.currentSku.tier)=~"hyperscale", 1000,properties.maxSizeBytes/(1024 * 1024 * 1024)), IsAHB = case(properties.licenseType == 'BasePrice', "Yes", "No"), Is_Replica=iff(tostring(properties.secondaryType) in("Geo","Standby"), "Yes","No"), Is_Standby_Replica=iff(tostring(properties.secondaryType)=~ "Standby", "Yes","No") | where (type =~ 'Microsoft.Sql/servers/databases' or type =~ "microsoft.sql/managedinstances" or type =~ "microsoft.sql/servers/elasticpools") and ServiceTier !in~ ("system") | join kind=leftouter ( Resources | where type =~ 'microsoft.sql/servers/elasticpools' | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolSize=tostring(properties.perDatabaseSettings.maxCapacity)) on elasticPoolId | project-away elasticPoolId1 ) | order by ['Name'] asc

This process can be further bolstered by automating the running of Azure Resource Graph queries and even acting on the results.

Let us know if any of these resonate with you or if have more suggestions!

Feedback

If you have feedback or suggestions for improving this data migration asset, please comment here or contact the Azure Databases SQL Customer Success Engineering Team (datasqlninja@microsoft.com) directly. Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

The Odd Couple - Latches and KEEP_NULLS

David_Lyth — Fri, 29 May 2026 15:58:10 GMT

Introduction

Bulk loading is one of the most heavily optimised data paths in SQL Server. When it works well, hundreds of concurrent sessions can push millions of rows per minute into large tables with minimal CPU overhead. But when it doesn't, the symptoms can be baffling: CPU sits almost idle while every session waits, and throughput collapses by orders of magnitude.

We recently worked with a customer running a large-scale data processing platform on Azure SQL Hyperscale Database. The workload used Spark to bulk-insert data from hundreds of concurrent sessions into multi-billion-row tables, each using SEQUENCE objects to generate surrogate keys. After the team switched from row-by-row inserts to parallel bulk loading - a change that should have been a clear win - job durations went from minutes to over an hour. CPU utilisation sat at 2–5% while latch waits consumed over 98% of elapsed time.

The root cause turned out to be a surprising interaction between two seemingly unrelated SQL Server features: SEQUENCE object latch modes and the KEEP_NULLS bulk insert option. Each is individually well-documented, but their combined effect under high-concurrency bulk insert workloads produced catastrophic serialisation that was not obvious from the documentation alone.

This post distils the lessons learnt from that engagement into a reusable guide. We explain the mechanism, demonstrate the issue with reproducible tests on SQL Server 2025, and provide four independent fixes - the simplest of which is a single client-side configuration change that requires no schema modifications, no table rebuilds, and no downtime.

Note: Results may vary depending on hardware, SQL Server version, and workload patterns. All tests in this post were run on SQL Server 2025 (CU3). We recommend validating in your own dev/test environment.

Background: The Ingredients

The workload used a common pattern for generating surrogate keys during bulk inserts:

SEQUENCE objects - CREATE SEQUENCE ... AS NUMERIC(28,0) - provided unique IDs
DEFAULT constraints - ALTER TABLE ... ADD CONSTRAINT ... DEFAULT (NEXT VALUE FOR dbo.MySequence) - auto-populated IDs for rows that didn't supply them
INSERT BULK - Spark's JDBC SQLServerBulkCopy loaded data in parallel from ~350 concurrent sessions
The application called sp_sequence_get_range to pre-allocate ID ranges and supplied IDs with every row - the DEFAULT was there as a safety net, not the primary path

Each component is sensible on its own. The problem emerges from their interaction.

The Problem: Two Layers of Serialisation

Layer 1: INSERT BULK Fires the DEFAULT - Even When You Supply Values

When INSERT BULK (the TDS protocol used by BCP, BULK INSERT, and JDBC SQLServerBulkCopy) runs without the KEEP_NULLS option, the engine sets an internal flag (HNT_KEEPDEFAULT) that wraps every DEFAULT-constrained column as:

ISNULL(source_value, default_expression)

The default_expression - in this case NEXT VALUE FOR - is evaluated by a Sequence Project iterator that runs unconditionally per row, before the ISNULL decides whether to use it. Even though every row in our workload supplied a valid non-NULL ID, the sequence latch was still acquired, the counter was incremented, and the generated value was discarded.

This is fundamentally different from a regular INSERT ... SELECT statement, where NEXT VALUE FOR only fires if the column is actually omitted from the column list.

Layer 2: NUMERIC Sequences Take Exclusive Latches

SQL Server's sequence generator uses different synchronisation strategies depending on the sequence data type:

Sequence Type	Latch Mode	Concurrency
`NUMERIC` / `DECIMAL`	Exclusive (EX) per row	Fully serialised - one thread at a time
`BIGINT` / `INT`	Shared (SH) + atomic CPU increment	Concurrent - multiple threads progress

The difference is not in the sequence cache (which only reduces disk I/O) but in the in-memory latch that protects the counter. With a NUMERIC(28,0) sequence, every single row - across all 350 concurrent sessions - must wait in line for one exclusive latch.

The Combined Effect

Combining both layers produces catastrophic serialisation:

350 sessions × 50,000 rows each = 17.5 million unnecessary exclusive latch acquisitions per bulk load cycle
Each acquisition blocks all other sessions
CPU sits idle while threads wait in the latch queue
With NUMERIC(28,0) + no KEEP_NULLS, the workload was 98% wait time and 2% CPU time

The diagnostic signature is unmistakable: LATCH_EX dominant in wait stats, low CPU utilisation, and all waiting sessions blocked on METADATA_SEQUENCE_GENERATOR.

Proving It: The KEEP_NULLS Experiment

The simplest proof is to BCP 10,000 rows - all with application-supplied IDs - and check whether the sequence counter advances.

Setup

CREATE SEQUENCE dbo.Seq_Orders AS NUMERIC(28, 0) START WITH 1 INCREMENT BY 1 CACHE 50000000; GO CREATE TABLE dbo.Orders ( OrderID NUMERIC(28, 0) NOT NULL CONSTRAINT DF_Orders_ID DEFAULT (NEXT VALUE FOR dbo.Seq_Orders), CustomerID INT NOT NULL, OrderDate DATE NOT NULL, Amount DECIMAL(10, 2) NOT NULL, Filler CHAR(80) NOT NULL DEFAULT ('x'), CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID) ); GO

Generate a BCP data file with IDs 1-10,000 pre-assigned by the application.

Test 1: BCP Without KEEP_NULLS (Default Behaviour)

bcp MyDB.dbo.Orders in orders.dat -c -t"," -S localhost\sql25 -T

Result:

Metric	Value
Rows stored	10,000
Stored OrderIDs	1-10,000 (app-supplied - correct)
Sequence current_value	20,000 (advanced from 1)

The sequence advanced by at least 10,000 values, even though the stored IDs are entirely from the application. The NEXT VALUE FOR default fired per-row, consumed sequence values, and discarded them all.

Test 2: BCP With KEEP_NULLS

bcp MyDB.dbo.Orders in orders.dat -c -t"," -S localhost\sql25 -T -k

Result:

Metric	Value
Rows stored	10,000
Stored OrderIDs	1-10,000 (app-supplied - correct)
Sequence current_value	1 (unchanged)

With -k (KEEP_NULLS), the sequence did not advance at all. The ISNULL wrapper was not applied, the Sequence Project iterator was not added to the plan, and all 10,000 latch acquisitions were eliminated.

Test 3: Table Without DEFAULT (Control)

bcp MyDB.dbo.Orders_NoDefault in orders.dat -c -t"," -S localhost\sql25 -T

Result: Sequence unchanged at 1. No DEFAULT means no Sequence Project, regardless of KEEP_NULLS.

Summary

Scenario	DEFAULT Exists	KEEP_NULLS	Sequence Advances?	Latch Per Row?
BCP (default behaviour)	Yes	No	Yes - wasted	Yes - serialised
BCP with `-k`	Yes	Yes	No	No
BCP, no DEFAULT	No	N/A	No	No
Regular INSERT...SELECT	Yes	N/A	No (column supplied)	No

The fourth row is important: a standard INSERT ... SELECT that includes the OrderID column does not fire the DEFAULT. This behaviour is specific to the INSERT BULK protocol.

Proving It: NUMERIC vs BIGINT Latch Contention

Even when the DEFAULT must fire (for example, when the application genuinely relies on it), the sequence data type determines the severity of the contention.

We tested three configurations at 32 concurrent sessions, each inserting 20,000 rows via INSERT ... DEFAULT VALUES:

Metric	NUMERIC(28,0)	BIGINT→NUMERIC	BIGINT
Latch wait requests	137,321	7	23
Latch total wait (ms)	37,661	210	384
Latch max single wait (ms)	53	54	18

(32 sessions × 20,000 rows, SQL Server 2025, localhost)

The "BIGINT→NUMERIC" column uses a BIGINT sequence feeding a NUMERIC(28,0) column. This proves that the latch mode follows the SEQUENCE type, not the column type. Changing the sequence from NUMERIC(28,0) to BIGINT is a metadata-only operation (DROP SEQUENCE + CREATE SEQUENCE AS BIGINT) that delivers a 99.4% reduction in latch waits without requiring any table rebuild.

IDENTITY Columns Have the Same Issue

The NUMERIC-vs-BIGINT latch behaviour is not limited to SEQUENCE objects - it applies equally to IDENTITY columns. An IDENTITY column defined as NUMERIC(28,0) or DECIMAL(28,0) takes an exclusive latch on the identity counter for every inserted row, while BIGINT IDENTITY uses a shared latch with atomic increments. The identity counter latch class (IDENTITYVALUE_GENERATOR) follows the same exclusive-vs-shared pattern as METADATA_SEQUENCE_GENERATOR.

For tables that use NUMERIC IDENTITY under high-concurrency insert workloads, changing the column type to BIGINT eliminates the serialisation. Unlike sequences, changing an identity column's data type requires ALTER TABLE ... ALTER COLUMN, which triggers a table rebuild for large tables. Plan this as a maintenance window operation for billion-row tables.

The Fixes

Four independent fixes address different layers of the problem. Any one of them helps; the combination eliminates the issue entirely.

Fix 1: Enable KEEP_NULLS in the Bulk Copy Client

This is the single highest-impact fix. When the application supplies all column values, enabling KEEP_NULLS prevents the engine from evaluating any DEFAULT constraint during INSERT BULK.

Spark JDBC:

.option("bulkCopyForBatchInsertKeepNulls", "true")

JDBC directly:

SQLServerBulkCopyOptions opts = new SQLServerBulkCopyOptions(); opts.setKeepNulls(true);

BCP:

bcp ... -k

BULK INSERT:

BULK INSERT dbo.Orders FROM 'data.dat' WITH (KEEPNULLS);

Impact: Eliminates 100% of unnecessary sequence latch acquisitions during bulk insert. If the application already supplies the ID column, this fix has zero behavioural side effects.

Warning: KEEP_NULLS applies to all columns, not just the sequence column. When enabled, any column where the source data contains NULL will store NULL rather than the column's DEFAULT value. If your table has other DEFAULT constraints that the application relies on - for example, CreatedDate DATE DEFAULT GETDATE() or Status VARCHAR(20) DEFAULT 'Pending' - those defaults will not fire for NULL values when KEEP_NULLS is active. Before enabling this option, verify that the application supplies values for every column, or that NULL is acceptable for columns the application omits. If some columns genuinely need their defaults, Fix 2 (dropping only the sequence DEFAULT) is the safer server-side alternative.

Fix 2: Drop the DEFAULT Constraint

If every insert path supplies the ID column, the DEFAULT constraint serves no purpose. Dropping it is an instant metadata-only DDL operation:

ALTER TABLE dbo.Orders DROP CONSTRAINT DF_Orders_ID;

Impact: Same as Fix 1, but enforced server-side. No client configuration required. Consider this if multiple applications insert into the table and you cannot guarantee all of them will set KEEP_NULLS.

Fix 3: Change NUMERIC Sequences to BIGINT

For scenarios where the DEFAULT must fire - for example, ad-hoc inserts, SSMS, or stored procedures that omit the ID column - changing the sequence type from NUMERIC(28,0) to BIGINT switches from exclusive to shared latching:

-- Wrap in a transaction so the workload never sees a moment without the DEFAULT. -- Without the transaction, inserts that rely on the DEFAULT would fail between -- the DROP CONSTRAINT and the ADD CONSTRAINT. BEGIN TRANSACTION; -- Step 1: Record current value DECLARE @current BIGINT; SELECT @current = CAST(current_value AS BIGINT) FROM sys.sequences WHERE name = 'Seq_Orders'; -- Step 2: Drop DEFAULT, drop sequence, recreate as BIGINT, re-add DEFAULT ALTER TABLE dbo.Orders DROP CONSTRAINT DF_Orders_ID; DROP SEQUENCE dbo.Seq_Orders; CREATE SEQUENCE dbo.Seq_Orders AS BIGINT START WITH @current INCREMENT BY 1 CACHE 50000000; ALTER TABLE dbo.Orders ADD CONSTRAINT DF_Orders_ID DEFAULT (NEXT VALUE FOR dbo.Seq_Orders) FOR OrderID; COMMIT TRANSACTION;

Wrapping the four statements in a single transaction ensures that concurrent inserts never see a state where the DEFAULT constraint is missing. Without the transaction, any insert that relies on the DEFAULT would fail with an error between the DROP CONSTRAINT and the ADD CONSTRAINT. All four operations are metadata-only and the transaction completes in under a second. The column type (NUMERIC(28,0)) does not need to change - SQL Server implicitly converts BIGINT to NUMERIC on insert.

Impact: 99.4% reduction in latch wait time when the DEFAULT does fire. BIGINT covers values up to 9.2 × 10^18, which is sufficient for virtually all surrogate key scenarios.

Fix 4: Right-Size Sequence Cache

The sequence CACHE setting controls how many values are pre-allocated in memory before the next disk write to the system catalog. The default cache is 50, which is far too small for high-volume bulk inserts:

ALTER SEQUENCE dbo.Seq_Orders CACHE 50000000;

Impact: Reduces disk I/O during sequence value generation. This does not change the latch mode - it only reduces how often the latch must also perform a catalog write. Fix 3 addresses the latch mode; this fix addresses the I/O.

When Each Fix Applies

Scenario	Fix 1 (KEEP_NULLS)	Fix 2 (Drop DEFAULT)	Fix 3 (BIGINT)	Fix 4 (Cache)
App supplies IDs via bulk insert	Primary fix	Backup/server-side	Reduces residual contention	Helpful
App relies on DEFAULT for IDs	N/A	N/A	Primary fix	Required
Mixed: some paths supply, some don't	For bulk paths	N/A	For DEFAULT paths	Required
SEQUENCE used in sp_sequence_get_range only	Not needed	Drop it	Helpful	Helpful

How to Detect This Pattern

Wait Stats

Query sys.dm_os_wait_stats or sys.dm_os_latch_stats for the signature:

-- Check for sequence latch contention SELECT latch_class, waiting_requests_count, wait_time_ms, max_wait_time_ms FROM sys.dm_os_latch_stats WHERE latch_class = 'METADATA_SEQUENCE_GENERATOR' AND waiting_requests_count > 0;

If METADATA_SEQUENCE_GENERATOR appears with high waiting_requests_count alongside LATCH_EX dominating sys.dm_os_wait_stats, this pattern is likely active.

Sequence Consumption Rate

Compare the sequence advance rate against actual row insertions:

-- If sequence advances much faster than rows are inserted, -- DEFAULT is firing unnecessarily during bulk inserts SELECT s.name AS sequence_name, CAST(s.current_value AS BIGINT) AS current_value, s.data_type, s.cache_size FROM sys.sequences s WHERE s.data_type IN ('numeric', 'decimal');

Sequences typed as NUMERIC or DECIMAL under high-concurrency workloads are the risk profile.

Execution Plans

Look for a Sequence Project iterator in the execution plan of INSERT BULK operations. If the application supplies the ID column, a Sequence Project indicates the DEFAULT is being evaluated unnecessarily.

Test Scripts

The scripts used for all benchmarks in this post are included below. Each can be run on any SQL Server 2019+ instance.

Setup: Database, Sequences, and Tables

USE master; GO IF DB_ID('LatchKeepNullsDemo') IS NOT NULL BEGIN ALTER DATABASE LatchKeepNullsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE LatchKeepNullsDemo; END GO CREATE DATABASE LatchKeepNullsDemo; GO USE LatchKeepNullsDemo; GO -- Table WITH NEXT VALUE FOR DEFAULT (demonstrates the problem) CREATE SEQUENCE dbo.Seq_Orders_Numeric AS NUMERIC(28, 0) START WITH 1 INCREMENT BY 1 CACHE 50000000; GO CREATE TABLE dbo.Orders_WithDefault ( OrderID NUMERIC(28, 0) NOT NULL CONSTRAINT DF_Orders_ID DEFAULT (NEXT VALUE FOR dbo.Seq_Orders_Numeric), CustomerID INT NOT NULL, OrderDate DATE NOT NULL, Amount DECIMAL(10, 2) NOT NULL, Filler CHAR(80) NOT NULL DEFAULT ('x'), CONSTRAINT PK_Orders_WithDefault PRIMARY KEY CLUSTERED (OrderID) ); GO -- Table WITHOUT DEFAULT (control) CREATE TABLE dbo.Orders_NoDefault ( OrderID NUMERIC(28, 0) NOT NULL, CustomerID INT NOT NULL, OrderDate DATE NOT NULL, Amount DECIMAL(10, 2) NOT NULL, Filler CHAR(80) NOT NULL DEFAULT ('x'), CONSTRAINT PK_Orders_NoDefault PRIMARY KEY CLUSTERED (OrderID) ); GO -- NUMERIC(28,0) sequence for latch test CREATE SEQUENCE dbo.Seq_Latch_Numeric AS NUMERIC(28, 0) START WITH 1 INCREMENT BY 1 CACHE 50000000; GO CREATE TABLE dbo.Inserts_Numeric ( ID NUMERIC(28, 0) NOT NULL DEFAULT (NEXT VALUE FOR dbo.Seq_Latch_Numeric), Filler CHAR(100) NOT NULL DEFAULT ('x'), CONSTRAINT PK_Inserts_Numeric PRIMARY KEY CLUSTERED (ID) ); GO -- BIGINT sequence for latch test CREATE SEQUENCE dbo.Seq_Latch_BigInt AS BIGINT START WITH 1 INCREMENT BY 1 CACHE 50000000; GO CREATE TABLE dbo.Inserts_BigInt ( ID BIGINT NOT NULL DEFAULT (NEXT VALUE FOR dbo.Seq_Latch_BigInt), Filler CHAR(100) NOT NULL DEFAULT ('x'), CONSTRAINT PK_Inserts_BigInt PRIMARY KEY CLUSTERED (ID) ); GO -- BIGINT sequence + NUMERIC column (proves latch follows sequence type) CREATE SEQUENCE dbo.Seq_Latch_Mixed AS BIGINT START WITH 1 INCREMENT BY 1 CACHE 50000000; GO CREATE TABLE dbo.Inserts_Mixed ( ID NUMERIC(28, 0) NOT NULL DEFAULT (NEXT VALUE FOR dbo.Seq_Latch_Mixed), Filler CHAR(100) NOT NULL DEFAULT ('x'), CONSTRAINT PK_Inserts_Mixed PRIMARY KEY CLUSTERED (ID) ); GO

-- Helper: Generate BCP test data with app-supplied IDs CREATE OR ALTER PROCEDURE dbo.GenerateBCPData @RowCount INT = 10000 AS BEGIN SET NOCOUNT ON; ;WITH Nums AS ( SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b ) SELECT CAST(n AS NUMERIC(28,0)) AS OrderID, ABS(CHECKSUM(NEWID())) % 10000 + 1 AS CustomerID, CAST(DATEADD(DAY, -(n % 365), '2026-03-31') AS DATE) AS OrderDate, CAST(ABS(CHECKSUM(NEWID())) % 100000 / 100.0 AS DECIMAL(10,2)) AS Amount, REPLICATE('x', 80) AS Filler FROM Nums; END GO

Test: BCP KEEP_NULLS Comparison

# Step 1: Export test data bcp "EXEC LatchKeepNullsDemo.dbo.GenerateBCPData @RowCount=10000" \ queryout orders.dat -c -t"," -S localhost\sql25 -T # Step 2: Record sequence starting value sqlcmd -S localhost\sql25 -d LatchKeepNullsDemo -Q \ "SELECT name, CAST(current_value AS VARCHAR(30)) AS val FROM sys.sequences WHERE name = 'Seq_Orders_Numeric';" # Step 3a: BCP WITHOUT KEEP_NULLS (DEFAULT fires per-row) bcp LatchKeepNullsDemo.dbo.Orders_WithDefault in orders.dat \ -c -t"," -S localhost\sql25 -T # Check: sequence should have advanced (values wasted) sqlcmd -S localhost\sql25 -d LatchKeepNullsDemo -Q \ "SELECT name, CAST(current_value AS VARCHAR(30)) AS val FROM sys.sequences WHERE name = 'Seq_Orders_Numeric';" # Step 3b: Reset, then BCP WITH KEEP_NULLS (DEFAULT suppressed) sqlcmd -S localhost\sql25 -d LatchKeepNullsDemo -Q \ "TRUNCATE TABLE dbo.Orders_WithDefault; ALTER SEQUENCE dbo.Seq_Orders_Numeric RESTART WITH 1;" bcp LatchKeepNullsDemo.dbo.Orders_WithDefault in orders.dat \ -c -t"," -S localhost\sql25 -T -k # Check: sequence should NOT have advanced sqlcmd -S localhost\sql25 -d LatchKeepNullsDemo -Q \ "SELECT name, CAST(current_value AS VARCHAR(30)) AS val FROM sys.sequences WHERE name = 'Seq_Orders_Numeric';"

Test: Concurrent Latch Comparison (PowerShell)

# Compare NUMERIC vs BIGINT sequence latch contention # under N concurrent sessions, each inserting via INSERT...DEFAULT VALUES param( [int]$Sessions = 16, [int]$RowsPerSession = 10000, [string]$Server = "localhost\sql25" ) $db = "LatchKeepNullsDemo" function Invoke-ConcurrentInserts { param([string]$TableName, [string]$Label) sqlcmd -S $Server -E -d $db -Q "TRUNCATE TABLE dbo.$TableName;" -b 2>&1 | Out-Null sqlcmd -S $Server -E -d $db ` -Q "DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR) WITH NO_INFOMSGS;" ` -b 2>&1 | Out-Null $sql = "SET NOCOUNT ON; DECLARE @i INT = 0; " + "WHILE @i < $RowsPerSession BEGIN " + "INSERT INTO dbo.$TableName DEFAULT VALUES; SET @i += 1; END" $sw = [System.Diagnostics.Stopwatch]::StartNew() $jobs = @() for ($s = 0; $s -lt $Sessions; $s++) { $jobs += Start-Job -ScriptBlock { param($srv, $database, $query) sqlcmd -S $srv -E -d $database -Q $query -b 2>&1 | Out-Null } -ArgumentList $Server, $db, $sql } $jobs | Wait-Job | Out-Null $sw.Stop() $jobs | Remove-Job -Force # Capture latch stats $raw = sqlcmd -S $Server -E -d $db ` -Q "SELECT latch_class, waiting_requests_count, wait_time_ms, max_wait_time_ms FROM sys.dm_os_latch_stats WHERE latch_class = 'METADATA_SEQUENCE_GENERATOR';" ` -W -s "," -h -1 2>&1 $line = $raw | Where-Object { $_ -match 'METADATA' } | Select-Object -First 1 $requests = 0; $waitMs = 0; $maxMs = 0 if ($line -match 'METADATA_SEQUENCE_GENERATOR\s*,\s*(\d+)\s*,\s*(\d+)\s*,\s*(\d+)') { $requests = [long]$Matches[1]; $waitMs = [long]$Matches[2]; $maxMs = [long]$Matches[3] } Write-Host (" {0,-20} Elapsed: {1,6} ms | Latch waits: {2,8} | Wait: {3,6} ms" ` -f $Label, $sw.ElapsedMilliseconds, $requests, $waitMs) return @{ Label = $Label; Elapsed = $sw.ElapsedMilliseconds; Requests = $requests; WaitMs = $waitMs; MaxMs = $maxMs } } Write-Host "Sessions: $Sessions | Rows/session: $RowsPerSession" $rNum = Invoke-ConcurrentInserts -TableName "Inserts_Numeric" -Label "NUMERIC(28,0)" $rMix = Invoke-ConcurrentInserts -TableName "Inserts_Mixed" -Label "BIGINT->NUMERIC" $rBig = Invoke-ConcurrentInserts -TableName "Inserts_BigInt" -Label "BIGINT" if ($rNum.WaitMs -gt 0) { $mixPct = [math]::Round((1 - $rMix.WaitMs / $rNum.WaitMs) * 100, 1) $bigPct = [math]::Round((1 - $rBig.WaitMs / $rNum.WaitMs) * 100, 1) Write-Host "`nBIGINT->NUMERIC latch reduction: $mixPct%" Write-Host "BIGINT latch reduction: $bigPct%" }

Key Takeaways

INSERT BULK without KEEP_NULLS evaluates DEFAULT expressions per-row - even when the source data supplies non-NULL values. For NEXT VALUE FOR defaults, this means one latch acquisition per row, per session, regardless of whether the value is used. This is documented behaviour, but the performance implications for sequence-based defaults under concurrency are rarely discussed.
NUMERIC / DECIMAL sequences use exclusive latches; BIGINT / INT use shared latches with atomic increments. The sequence cache setting reduces disk I/O only - it does not change the latch mode. The latch mode is determined by the sequence type, not the column type.
The combination creates a force multiplier. Each factor is tolerable in isolation. Exclusive latching with a few sessions is fine. Unnecessary DEFAULT evaluation at low concurrency has negligible cost. But 350 sessions × exclusive latch × per-row evaluation = complete serialisation.
The diagnostic signature is clear. LATCH_EX dominant in wait stats + METADATA_SEQUENCE_GENERATOR in sys.dm_os_latch_stats + low CPU utilisation = this pattern. Low CPU under high concurrency means serialisation, not low demand.
The fix is simple. Setting KeepNulls = true in the bulk copy options eliminates the issue entirely when the application supplies all values. No schema changes required, no table rebuilds, no downtime.

Documentation References

BULK INSERT (Transact-SQL) - KEEPNULLS - Documents the KEEPNULLS behaviour: "Specifies that empty columns should retain a null value during the bulk import operation, instead of having any default values for the columns inserted."
bcp Utility -k flag - BCP command-line equivalent of KEEPNULLS.
SQLServerBulkCopyOptions.setKeepNulls - JDBC bulk copy configuration for the keepNulls option.
Sequence Numbers (SQL Server) - Overview of SEQUENCE objects, caching behaviour, and usage patterns.
sp_sequence_get_range - Pre-allocating contiguous ID ranges with a single latch acquisition.
CREATE SEQUENCE (Transact-SQL) - Sequence data type options and cache configuration.
sys.dm_os_latch_stats - DMV for diagnosing latch contention by class.
IDENTITY (Property) - IDENTITY column behaviour and data type considerations.

Feedback and Suggestions

If you have feedback or suggestions, please contact the Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com).

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Redefining Database Maintenance after Migrating from Db2 on Mainframe to Azure SQL DB Hyperscale

Sandip-Khandelwal — Wed, 25 Mar 2026 17:05:12 GMT

Introduction

Migrating from Db2 z/OS to Azure SQL Database Hyperscale is a major step toward modernizing your mainframe relational data infrastructure to Azure Data SQL managed relational database offering. But what happens to all those daily and periodic Db2 database maintenance tasks you used to perform on Mainframe?

In this post, to make migration easier we have provided the recommended mapping between Db2 zOS maintenance and Azure SQL DB Hyperscale maintenance tasks that shows:

Tasks you must still schedule or trigger yourself
Tasks that are only needed after specific events (like large data loads)
Tasks that are now fully handled by Azure SQL Database PaaS or are simply not applicable anymore
Best practices that, while not mandatory, are strongly recommended

Mainframe Db2 vs Azure SQL Database Hyperscale Database Maintenance

For each maintenance activity, you will find actionable guidance on how to perform it in Azure SQL Database Hyperscale, helping you streamline operations and focus on what matters most.

Db2 z/OS Task / Concept

Purpose on Db2

Azure SQL DB Hyperscale Equivalent

Responsibility

Recommended Frequency (Post‑Migration)

Remark

Full Image COPY (Backup)

Recoverability

Note: Full image copy needs to be taken for data objects like Table space, index space etc.

Azure SQL DB Hyperscale utilizes storage snapshot technology to capture a full, complete copy of the database's data files. The transaction logs generated since the last snapshot are kept unchanged ("as is") for the set retention period to ensure point-in-time recovery.

Azure

Continuous

In Azure SQL DB HS default short term retention period of database backup is 7 days which can be configured till 35 days. You can configure retain backup for up to 10 years by configuring Long Term Retention Policy through Azure Portal / Azure CLI / PowerShell

Incremental / Delta COPY

Reduce backup window

No incremental backup in HS

Azure

N/A

Log Archive / Dual Logging

Point-in-time recovery

Transaction log backups automatic

Azure

Continuous

RUNSTATS (Table/Index)

Optimizer statistics

Auto Create/Auto Update Statistics

For specific cases schedule STATS update

Platform

+ You

Continuous

DBCC SHOW_STATISTICS command displays current query optimization statistics for a table or indexed view.

The Query Optimizer determines when statistics might be out-of-date and then updates them when they're needed for a query plan when AUTO_UPDATE_STATISTICS ON or AUTO_UPDATE_STATISTICS_ASYNC is enabled.

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY allows for updates of statistics asynchronously which can wait for the schema modification lock on a low priority queue. This improves concurrency for workloads with frequent query plan (re)compilations.

Asynchronous Auto Update Statistics

Leave it OFF (default) for most OLTP/reporting hybrids unless you see blocking on STATMAN operations. Consider enabling async if:

You have latency-sensitive OLTP queries that frequently block on synchronous stat refresh.
Trade-off: First executions after threshold may use stale stats until async update finishes.

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Statistics update for partitioned table.

Option INCREMENTAL = { ON | OFF } allows for creation of statistics per partition of the table.

If only some partitions are changed recently use below statistics update command for specific partition stats update.

UPDATE STATISTICS dbo.Fact PARTITION = n

Updating Statistics manually:

UPDATE STATISTICS schema.table WITH FULLSCAN / SAMPLE N ROWS / SAMPLE N PERCENT

Update Stats after:

· Bulk load (insert millions of rows) into existing large table

· Large data purge (delete/archival)

· Partition switch-in / switch-out

· Parameter sniffing + big, estimated vs actual row mismatch repeatedly

Identifying Which Stats Need Attention

Query to rank stats by modification ratio:

Pick those with (general guidance tune according to specific workload and performance expectations):

modification_pct > 15% on large fact tables
or absolute modification_counter very high (e.g., > 100K changes) even if pct smaller (extreme scale tables)

Simple Maintenance Script (DMV-Driven)

This updates only stats with >15% modification and >100K row changes:

DECLARE @sql nvarchar(max) = N'';
WITH c AS (
  SELECT
    s.object_id, s.stats_id,
    QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) AS full_table_name,
    QUOTENAME(s.name) AS stat_name,
    sp.rows, sp.modification_counter,
    1.0 * sp.modification_counter / NULLIF(sp.rows,0) AS mod_ratio
  FROM sys.stats s
  CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
  WHERE sp.modification_counter IS NOT NULL
    AND OBJECTPROPERTY(s.object_id,'IsMsShipped') = 0
    AND sp.rows >= 100000
    AND sp.modification_counter >= 100000
    AND (1.0 * sp.modification_counter / NULLIF(sp.rows,0)) >= 0.15
)
SELECT @sql = STRING_AGG(
   N'UPDATE STATISTICS ' + full_table_name + N' ' + stat_name + N' WITH SAMPLE 30 PERCENT;',' ')
FROM c;
PRINT @sql;  -- Review first
EXEC sp_executesql @sql;

Decision Matrix

Question	Answer
Should I keep AUTO_CREATE/UPDATE ON?	Often Yes. For big tables with millions of records with sizes 100 GB+ if auto create / update / asynchronous stats update is having performance impact, you can disable this and schedule daily / weekly process to perform STATAS UPDATE.
Do I need a nightly job?	Often no; maybe lightweight sp_updatestats for very volatile workloads.
After heavy ETL?	Yes, targeted stats refresh (especially dimensions/facts touched).
After index rebuild?	No extra stats on that index; other column stats unaffected may still need update.
Use FULLSCAN often?	Only for a handful of skewed, performance-critical tables with proven cardinality issues.

REORG TABLE (Tablespace)

Eliminate overflow / reclaim space

- Azure SQL does not have REORG TABLE command

- Similar result can achieved by using REBUILD of the clustered index

Not usually required. Should be done if there is large (>30 %) index defragmentation

Note: 30% is just guidance.

N/A

Db2 has table level REORG Option, Azure SQL DB HS has index level REORG options.

REORG INDEX / REBUILD INDEX

Defragment indexes

REBUILD / REORGANIZE index

You (Needed / Conditional)

Weekly or when fragmentation > thresholds

Check current fragmentation: SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats;

Reorganize (5–30%): ALTER INDEX ix ON tbl REORGANIZE

Rebuild (>30%): ALTER INDEX ix ON tbl REBUILD WITH (ONLINE=ON , RESUMABLE = ON, MAXDOP=1/4/8);

Above numbers (5 % to 30 % REORG / > 30 % REBUILD) are just guidelines in many cases you may not need index REBUILD due to latest storage technologies or you may need to perform REBUILD after > 60-70 % fragmentation. Also, you may not need REORG. STATS update needs to be done diligently for query performance improvement. Perform through test compare before and after execution plans to derive conclusion on this.

Large Index Rebuild special handling:

ALTER INDEX ix ON tbl REBUILD WITH (ONLINE=ON , RESUMABLE = ON, MAX_DURATION = 60,MAXDOP=1/4/8);

-- Pause after current batch

ALTER INDEX ix ON tbl PAUSE;

-- Resume with lower DOP

ALTER INDEX ix ON tbl RESUME WITH (MAXDOP = 2, MAX_DURATION = 60);

-- Abort and roll back if required

ALTER INDEX ix ON tbl ABORT;

Monitor progress of Index rebuild operation using below query:

SELECT r.session_id, r.command, r.percent_complete, r.start_time, r.estimated_completion_time

FROM sys.dm_exec_requests r

WHERE r.command LIKE '%INDEX%';

Perform Index Rebuild using example below approaches:

a) Ola Hallengren maintenance Scripts : link

b) Adaptive Index Defragmentation: link

c) How to maintain Azure SQL Indexes and Statistics : link

Automating Azure SQL DB index and statistics maintenance using Azure Automation : link

COPY / QUIESCE utilities

Consistent copy state

Not needed (transactionally consistent snapshots)

Not Applicable

N/A

CHECK DATA / CHECK INDEX

Structural consistency

DBCC CHECKDB

You (Best Practice)

Monthly or Weekly (off-peak)

Azure SQL Database automatically runs internal consistency checks, but you may still run it manually if you want:

T-SQL Query: DBCC CHECKDB(DatabaseName);

For large DB: run on geo-secondary or named replica then review

RUN QUERY EXPLAIN SNAPSHOT

Capture access paths

Query Store captures execution plans

Platform + You (Analysis)

Ongoing

Ensure Query Store ON with Operation Mode DEFAULT i.e. READ_WRITE (collects and persists query stats).
Easy analysis of Query store details using SSMS

REBIND Packages

Refresh access plans

Not needed (no static package binding)

Not Applicable

N/A

Catalog Statistics Maintenance

Optimizer health

System metadata auto maintained

Platform

N/A

Buffer Pool Sizing (BP0/BP32K etc.)

Memory tuning

Managed by service tier

Platform

N/A

Storage Space Preallocation

Avoid space issues

Storage allocated dynamically (i.e. auto-grow page servers)

Platform

N/A

Monitor size: sys.database_files; optionally purge/archive data

Archive Log Space Monitoring

Prevent log fill

Log scaling

Platform (capacity) + You (workload)

Monitor during bursts

sys.dm_db_resource_stats; watch log_write_percent

Partition Maintenance (ROLL-IN/OUT)

Lifecycle management

Table partitioning (if used)

You (Conditional)

During data lifecycle events

Switching Partitions (Fast Load/Unload) : Move data in/out of a partitioned table without large DELETE/INSERT operations:

ALTER TABLE [PartitionedTable] SWITCH

PARTITION N TO [StagingTable];

Rebuilding/Defragmenting Indexes per Partition

Instead of rebuilding the entire table, you can rebuild indexes on a single partition, which is faster and less blocking:

ALTER INDEX [IX_YourIndex] ON [YourPartitionedTable] REBUILD PARTITION = N

WITH (ONLINE = ON);

Compression (ROW/PAGE) MGMT

Space and IO

ROW / PAGE / ColumnStore compression (if enabled)

You (Optional)

At design / periodic review

Row compression stores fixed-length data as variable-length, ideal for CHAR, INT, etc.

Page compression adds prefix and dictionary compression for repeated values in large tables.

Columnstore compression stores data column-wise for huge analytical tables with millions of rows.

Security: RACF/External Auth Integration

Access control

Microsoft Entra ID Auth, Managed Identities, Microsoft Entra Service Principal

You (Needed)

At onboarding + periodic review

Example authentication using Microsoft Entra ID:

DECLARE @EntraIDUser NVARCHAR(128) = 'john@contoso.com';

CREATE USER [@EntraIDUser] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [@EntraIDUser];

ALTER ROLE db_datawriter ADD MEMBER [@EntraIDUser];

Encryption (Dataset / Log)

Compliance

TDE auto-enabled

Platform

N/A

Optionally manage customer-managed keys (CMK) via Key Vault

Auditing / SMF / IFI

Compliance logging

Azure SQL Auditing / Defender

You (Needed)

Enable once; review monthly

Azure SQL Database / Managed Instance supports auditing at the server or database level. Audits can be sent to Log Analytics, Storage Account, or Event Hub.

Performance Trace (IFCID)

Problem diagnostics

Query Store + Extended Events, Database Watcher, DMVs, Azure Portal Monitoring

You (Conditional)

When diagnosing issues

Example: Use SSMS to browse Query Store view; Use Azure Portal to monitor performance; or use DMVs for detailed troubleshooting.

Job Scheduling (JCL)

Orchestrate utilities

Azure Automation /

Elastic Jobs in Azure SQL DB/

ADF Pipelines /

Azure Logic Apps / Azure Functions

You (As per need)

Per task (daily/weekly)

Azure Automation – Serverless runbooks or scripts run in the cloud using managed identities or credentials.
Elastic Job in Azure SQL DB - You can create and schedule elastic jobs that could be periodically executed against one or many Azure SQL databases to run Transact-SQL (T-SQL) queries and perform maintenance tasks.
ADF / Fabric Pipelines – Orchestrate queries and maintenance as scheduled or triggered pipeline activities.
Logic Apps / Functions – Event-driven query execution in response to timers or Azure events.

Index Design Advisor

Workload tuning

Automatic Tuning (create/drop indexes)

Platform (optional)

Continuous

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (CREATE_INDEX=ON, DROP_INDEX=ON)

Plan Regression Detection

Stability

Automatic Plan Correction

Platform (if enabled)

Continuous

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN=ON)

HA / DR (Dual site, GDPS)

Availability

Built-in HA + Geo-Replication / Failover Groups

Platform + You (DR config)

Configure once; drills semi-annual

You can create up to 4 HA replicas. HA replica uses same page servers as the primary replica, so no data copy is required to add an HA replica. Information for High Availability replica is present at documentation: link
Named replica just like an HA uses the same page servers as the primary replica. Named replica can have their own SLO and you can create up to 30 named replicas for read scale out purpose. Create Hyperscale named replica by following documentation: link
For DR replica easily configure geo replication / failover group for Azure SQL Database as explained at location : link

Backup Retention / Offsite Vault

Long-term retention

LTR (Long-Term Retention)

You (Optional)

Configure at migration; review annually

Set LTR via

Azure Portal or
Azure CLI : az sql db ltr-policy set or
Azure Powershell : Set-AzSqlDatabaseBackupLongTermRetentionPolicy

Monitoring (OMEGAMON, Tivoli)

Health & capacity

Azure Monitor / Log Analytics / Database Watcher

You (Needed)

Daily dashboard; alert reaction

Using Azure Monitor create Alerts on CPU %, Memory %, Transaction Log throughput %, Storage %, blocking, failed logins etc.
Database Watcher collects in-depth workload monitoring data to give you a detailed view of database performance, configuration, and health.

Deadlock / Lock Escalation Review

Concurrency tuning

Extended Events + DMVs

You (Conditional)

Investigate alerts

sys.dm_tran_locks: Current (transient) lock inventory. Useful for spotting patterns leading to deadlocks (e.g., two sessions holding incompatible locks in different order) and for capturing blocking chains before a deadlock forms. It does NOT retain history; deadlocks often resolve in milliseconds, so you rarely see the actual deadlock moment here.
system_health Extended Event: Always running; captures xml_deadlock_report events with a full deadlock graph (processes, resource nodes, lock modes, victim). Gives you post‑mortem detail even if you missed it live.

Batch Window Management

Avoid contention

Scale compute / workload smoothing

You (Best Practice)

Before large ETL

If required, scale up database resources before batch execution to shorten batch execution:

ALTER DATABASE ... MODIFY (SERVICE_OBJECTIVE='HS_Gen5_8');

scale down database resources after batch completion

ETL Load

Faster bulk load

Minimal logging depends on model (Always FULL)

You (Conditional)

During large loads

On large data load to a given table (> 100 GB)

Preferably:

Keep Clustered Column Store Indexes on table as it is. Use batch size of >102, 400 rows for better performance.
Drop Clustered Row store and non-clustered indexes before load if possible and recreate them after load.
While recreating indexes on large tables use these options : ONLINE = ON, RESUMABLE = ON, MAXDOP = 4 / 8
Monitor the progress of index creation using:

SELECT object_name(object_id) AS TableName, index_id, percent_complete, state_desc FROM sys.index_resumable_operations

WHERE state_desc = 'IN_PROGRESS';

Object Ownership / Schema Sync

Governance

VS Code extension

You (Needed)

On deployment

Schema compare between Db2 and SQL

ADF based solution for Db2 and SQL Schema comparison: link

Schema compare between Azure SQL databases

The schema comparison tooling enables you to compare two database definitions:link

Database Comparison

Governance

Database Compare Utility

You

On-demand

If you want to compare data between Db2 and Azure SQL DB HS you can use Database Compare Utility which is available for download at location : link

Minimal Practical Post-Migration Maintenance Set (Typical Cadence)

Daily:

Review alerts (performance, error rates, failed logins)
Optional lightweight index fragmentation check if workload highly write-intensive
Monitor Query Store for top regressions

After Bulk Loads:

UPDATE STATISTICS table WITH FULLSCAN (only for large changes)
Consider index rebuild if fragmentation spiked

Weekly:

Targeted index maintenance (only fragmented ones)
Targeted statistics maintenance (only for stale stats)
Query Store review: force known good plan if auto correction off

Monthly (or quarterly for very large DB):

DBCC CHECKDB (prefer off-peak or on a geo-secondary). If database size is large, perform this operation on business-critical tables or use specific options like PHYSICAL_ONLY.
Security/audit review
Cost/compute tier right-sizing

Annual / Semi-annual:

DR failover test (Failover Group)
LTR retention policy review
Compression strategy review
Automatic Tuning effectiveness assessment

Migration Mindset Tips

Don’t lift-and-shift Db2 utility cadence; you might over-maintain and waste resources.
Replace "daily job list" with "monitor + exception-based actions."
For very large Hyperscale DBs, consider named replicas for offloading DBCC CHECKDB or reporting.

Disclaimer: The guidance, recommendations, and examples provided in this blog are based on our experience with migrating Db2 from Mainframe to Azure SQL Database Hyperscale and may not be universally applicable to all environments. Every customer’s workload, configuration, and performance characteristics are unique. You should thoroughly test and validate these recommendations in your own development or staging environment before applying them in production. We do not assume any responsibility or liability for any issues, downtime, or impacts resulting from the use of the information in this blog.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please send an email to Database Platform Engineering Team.

Azure SQL’s Native JSON Type: Optimized for Performance

ShrustiKolsur — Mon, 09 Feb 2026 09:00:00 GMT

Introduction

JSON has become the de-facto format for modern applications from web APIs to microservices and event-driven systems. Azure SQL has supported JSON for years, but JSON was treated just like text (stored as nvarchar or varchar). That meant every query involving JSON required parsing, which could get expensive as data volume grew.

The new native JSON binary type changes that story. Instead of saving JSON as raw text, Azure SQL can store it in a binary representation that’s optimized for fast reads, efficient in-place updates, and compact storage. You get the flexibility of JSON with performance that behaves more like a structured column.

Learn more about the JSON data type in the documentation –

Textual data format - https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver17
Native binary format - https://learn.microsoft.com/en-us/sql/t-sql/data-types/json-data-type?view=sql-server-ver17

A few useful things to know upfront:

JSON data is stored in a native binary format, not as plain text
Reads are faster because the JSON is already parsed
Improved write efficiency, since queries can update individual values without accessing the entire document.
Storage is more compact, optimized for compression
Existing JSON functions continue to work so app changes are minimal
Internally, JSON is stored using UTF-8 encoding (Latin1_General_100_BIN2_UTF8)

This blog shares the performance gains observed after migrating JSON from nvarchar/varchar to the native JSON binary type. Results will vary across JSON structures and workloads, so consider this a guide rather than a universal benchmark.

Note: The purpose of this blog is to introduce the native JSON binary data type. We are not covering JSON indexes or JSON functions at this time in order to maintain clarity and focus.

Test Environment Details:

To measure the performance impact of migrating JSON from nvarchar/varchar to the native JSON binary type, a test environment was set up with six tables on Azure SQL Database (General Purpose, Gen5, 2 vCores).

Note: The dataset used in the testing was generated using AI for demonstration purposes.

JSON data stored as nvarchar/varchar data types:

Table Name	Number of Records	Size (GB)
InventoryTrackingJSON	400,003	4.21
OrderDetailsJSON	554,153	1.29
CustomerProfileJSON	55,001	0.16
ProductCatalogJSON	100,001	0.10
SalesAnalyticsJSON	10,000	0.08
EmployeeRecordsJSON	5,000	0.02

Total database size: 5.94 GB (59.43% used), based on a maximum configured size of 10 GB, with JSON stored as nvarchar/varchar.

Example schema (OrderDetailsJSON)

One of the core tables used in testing:

CREATE TABLE JSONTest.OrderDetailsJSON ( OrderDetailID INT IDENTITY(1,1) PRIMARY KEY, OrderMetadata NVARCHAR(MAX), -- JSON: order info,source, salesperson ShippingDetails NVARCHAR(MAX), -- JSON: carrier, priority, addresses CustomizationOptions NVARCHAR(MAX), -- JSON: customizations and add-ons CreatedDate DATETIME2 DEFAULT SYSDATETIME(), ModifiedDate DATETIME2 DEFAULT SYSDATETIME() );

Each JSON column simulated realistic business structure - for example:

OrderMetadata

{ "orderSource": "Mobile App", "salesPerson": "Jane Smith", "orderDate": "2025-11-14T10:30:00Z", "customerType": "Premium" }

ShippingDetails

{ "carrier": "FedEx", "priority": "standard", "address": { "city": "Anytown", "state": "CA" } }

CustomizationOptions

{ "color": "Green", "size": "Medium", "giftWrap": true }

Performance before migration:

To measure performance differences accurately, a continuous 12-minute test session was run. The load sizes referenced in the results (500, 1K, 2.5K, 5K, 10K, and 25K) represent the number of records read, and each record goes through the following operations:

Multiple JSON_VALUE extractions
JSON validation using ISJSON
Safe type conversions using TRY_CONVERT
Aggregation logic

During the 12-minute continuous workload, JSON stored as nvarchar/varchar showed consistent resource pressure, primarily on CPU and storage IO. The monitoring tools reported:

Disclaimer: These results are for illustration purposes only. Actual performance will vary depending on system hardware (CPU cores, memory, disk I/O), database configurations, network latency, and table structures. We recommend validating performance in dev/test to establish a baseline.

Data migration to native JSON binary data type

For testing, native JSON columns were added to the existing tables, and JSON data stored in nvarchar/varchar columns was migrated to the new native JSON binary columns using the CAST function.

Migration Script (example used for all tables)

Add native JSON columns
ALTER TABLE JSONTest.OrderDetailsJSON ADD OrderMetadata_native JSON, ShippingDetails_native JSON, CustomizationOptions_native JSON;
Migrate existing NVARCHAR/VARCHAR JSON into native JSON
UPDATE JSONTest.OrderDetailsJSON SET OrderMetadata_native = CAST(OrderMetadata AS JSON), ShippingDetails_native = CAST(ShippingDetails AS JSON), CustomizationOptions_native = CAST(CustomizationOptions AS JSON);

Note: After validating that the migrated data was consistent, the original nvarchar/varchar JSON columns were dropped. A rebuild index operation was then performed to remove fragmentation and reclaim space, ensuring that the subsequent storage comparison reflected the true storage footprint of the native JSON binary type.

The same pattern was repeated for all tables.

Storage footprint after migration:

Table Name	Number of Records	Size_Before (GB)	Size_After (GB)
InventoryTrackingJSON	400,003	4.21	0.60
OrderDetailsJSON	554,153	1.29	0.27
ProductCatalogJSON	100,001	0.16	0.11
SalesAnalyticsJSON	10,000	0.10	0.04
CustomerProfileJSON	55,001	0.08	0.01
EmployeeRecordsJSON	5,000	0.02	0.00

Total database size: 1.06 GB (10.64% used), based on a maximum configured size of 10 GB, with JSON in native binary data type.

After migrating all JSON columns from nvarchar/varchar to the native JSON type, the total database size dropped from 5.94 GB to 1.06 GB - an ~82% reduction in storage.

Performance after migration

After moving all JSON columns from nvarchar/varchar to native JSON, the exact same 12-minute workload was rerun - same query patterns, same workload distribution. Only the JSON storage format was different. Here are the results:

Key Metrics (Before vs. After)

The migration didn’t just shrink storage - it made JSON workloads easier for the engine to process. With the native JSON type, the same workload completed with ~27% lower CPU and ~80% lower Data IO.

Query duration, Throughput, & Logical Reads

Query duration

A comparison was conducted using the same workload, dataset, indexes, and query patterns - with the only variable being the JSON storage format. The outcome showed a clear trend in query duration.

Across every single load level, native JSON cut query duration by 2.5x - 4x. Even more interesting: as the workload scaled 50x, native JSON latency stayed almost flat, while text JSON steadily slowed down.

Note: The duration values shown represent the average across multiple runs within the performance test described earlier.

Throughput improvement

The benefits also translated directly into throughput. Overall, native JSON enabled 20x to 40x more records processed per second (rps). For example:

Load	Throughput Before (rps)	Throughput After (rps)
Small load	~60	~240
High load	~690	~2300
Peak load	~1360	~4700

Logical reads improvement

Native JSON significantly reduced I/O work as well:

Logical reads per run dropped from ~168,507 → ~33,880
An ~80% reduction in pages read

Lower logical reads directly correlate with improved scalability - fewer pages scanned means less work required to serve each request, especially under increasing load.

Sample results:

JSON (nvarchar/varchar) JSON (native binary)

Cache management

To ensure the performance improvement was not simply a result of native JSON fitting more easily in memory, the test cleared the cache at regular intervals using DBCC DROPCLEANBUFFERS, forcing repeated cold-start execution. As expected, query duration increased immediately after each cache clear for both text JSON and native JSON, yet the relative benefit remained consistent: native JSON continued to show a 2.5x–4x reduction in duration across all load levels. This confirms that the gains are not due to buffer pool residency alone, but from reduced JSON parsing work during execution.

For example, in the chart below for the small load, runs 3 and 6 were executed right after clearing cache. Although both formats show higher duration, the relative performance advantage remains unchanged.

Conclusion

Native JSON storage in Azure SQL isn’t just a new way to store semi-structured data - it delivers tangible performance and efficiency gains. In our case, migrating JSON from NVARCHAR to the new binary JSON type resulted in:

If your workload involves frequent reading or updating of JSON documents - especially large or deeply nested ones, the native JSON type is worth evaluating. Your gains may vary based on JSON structure, indexing strategy, and workload patterns - but the benefits of eliminating repeated text parsing + reducing storage cost are difficult to ignore.

As SQL workloads continue to blend structured and semi-structured data, native JSON brings Azure SQL more in line with modern application design while preserving the maturity and stability of the relational engine.

Feedback and Suggestions

If you have feedback or suggestions, please contact the Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com).

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Handling Sybase BIGTIME Data Type During Migration to Azure SQL

saikat_dey — Mon, 12 Jan 2026 09:00:00 GMT

Introduction

Migrating databases from Sybase to SQL Server or Azure SQL is a common modernization scenario. However, not all Sybase data types have direct equivalents in SQL Server, and one such challenge is the BIGTIME data type. The BIGTIME data type in Sybase stores time-of-day values with microsecond precision (format: hh:mm:ss.SSSSSS). It is commonly used in applications that require high-precision time tracking.

To unblock and accelerate this conversion, we have developed an script (sybase_bigtime_migration.sh) that provides automation to migrate schemas from Sybase ASE to SQL Server specifically where tables contain the BIGTIME datatype. It systematically discovers affected tables, then generates ALTER statements to convert BIGTIME columns to SQL Server’s TIME (6) with a controlled, auditable flow.

General Guidelines

The purpose of this blog is to provide end‑to‑end flow for discovering BIGTIME columns in Sybase and converting them to SQL Server’s TIME (6). Run the scripts on a host that has Sybase ASE installed and running and SQL Server tools ("sqlcmd") installed and available on the PATH. Provide accurate connection details, passwords are read securely without echoing to the terminal.

Functionality of the scripts

The script (sybase_bigtime_migration.sh) validates and sources the Sybase environment, then locates "isql" to query system catalogs for tables with BIGTIME columns. It writes a clean, header-free list to "tablist.txt", ensuring a usable input for the next steps. For each table, it generates an individual ALTER script converting BIGTIME → TIME (6) so you can review or apply changes per object. When SQL migration is enabled, it detects "sqlcmd", tests connectivity, executes each ALTER script, and saves rich logs for verification and troubleshooting.

Prerequisites

The script (sybase_bigtime_migration.sh) must be executed from the same host where Sybase ASE is installed and running, to ensure reliable access to system catalogs and local client utilities. The schema conversion of all tables must be performed using SQL Server Migration Assistant (SSMA) prior to running this script, ensuring that all non-BIGTIME columns are properly migrated and aligned with Azure SQL standards.

Ensure access to Sybase ASE instance with permissions to query metadata in "sysobjects", "syscolumns", and "systypes". If you plan to apply changes, you must have SQL Server client tools installed and permissions to run "ALTER TABLE" on the target database objects. Network connectivity from the host to both Sybase and SQL Server is required.

If you want to run the script only for a specific set of BIGTIME tables in Sybase, create a file named tablist.txt in the same directory as the script. This file should contain the list of BIGTIME tables (one table name per line) that the script should process.

Sybase datatype:

Schema conversion using SSMA:

Azure SQL datatype after schema conversion using SSMA:

How to Use

Run the script (sybase_bigtime_migration.sh) and provide Sybase server, username, password, and database when prompted. Choose whether to perform migration against SQL Server; if yes, supply SQL Server host, credentials, and database. After the detection step, confirm whether to proceed with all tables that have BIGTIME in the specified Sybase database. Selecting “yes” triggers script generation and optional application, selecting “no” exits after guidance, letting you tailor "tablist.txt" before rerunning.

Output Files

"tablist_Final.txt" output file contains the clean list of tables with BIGTIME columns and is regenerated on each run to reflect the current database. Each run writes an overall validation report, including per-table status and counts to "validation_summary_timestamp.log" where valid=tables with BIGTIME columns, missing=tables not found in DB, no_bigtime=tables without BIGTIME columns, unverified=validations errors, total_tablist_count=total tables checked from "tablist.txt". Per table ALTER scripts are created as "alter_<SYB_DB>_<TABLE>.sql", enabling fine-grained review and targeted application. When executing against SQL Server, output logs are saved under "sql_outputs/alter_<SYB_DB>_<TABLE>.out". These logs assist with validating results, identifying failures.

Final Azure SQL datatype output:

Data Migration Strategy

After the schema conversion and BIGTIME data type handling are completed, the data migration should be performed as a separate activity. The migration can be executed using Azure Data Factory (ADF) or a custom BCP-based export and import process, based on factors such as data volume, performance requirements, and operational considerations. Separating schema preparation from data movement provides greater flexibility, improved control, and reduced risk during the data migration phase.

Steps to Download the script

Please send an email to the alias datasqlninja@microsoft.com, and we will share the download link along with instructions.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com). Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Implementing Oracle Autonomous Transactions in Azure SQL for Seamless Logging

Vijay_Kumar — Mon, 05 Jan 2026 12:00:00 GMT

In Oracle PL/SQL, the directive PRAGMA AUTONOMOUS_TRANSACTION allows a block of code such as a procedure, function, or trigger to run in its own independent transaction. This means:

It can commit or rollback changes without affecting the main transaction.
It is fully isolated from the calling transactions, no shared locks or dependencies.
It is ideal for logging, auditing, and error tracking, where you want to preserve data even if the main transaction fails.

Example in Oracle PL/SQL:

CREATE OR REPLACE PROCEDURE log_error(p_message VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO error_log (log_time, message) VALUES (SYSDATE, p_message); COMMIT; END;

This ensures that the error log is saved even if the main transaction rolls back.

How to Implement Autonomous Transactions in Azure SQL

If you need logs that persist through rollbacks in Azure SQL, you can recreate the behavior of Oracle’s PRAGMA AUTONOMOUS_TRANSACTION using external logging via Azure Function

External logging via sp_invoke_external_rest_endpoint to an Azure Function

This blog post shows concrete code for Oracle PL/SQL vs. T‑SQL, and provides a secure, sample Azure Function example.

There’s no direct PRAGMA AUTONOMOUS_TRANSACTION in Azure SQL as on the date when this blog is written.
To persist logs even if the current transaction rolls back, consider calling an external logger (e.g., Azure Function) using sp_invoke_external_rest_endpoint.

Oracle vs. Azure SQL at a glance

Feature	Oracle (PRAGMA AUTONOMOUS_TRANSACTION)	Azure SQL (workarounds)
Native support	Yes	No
Isolation from caller txn	True	Partial (via external logging)
Rollback independence	Yes	Yes (external logging only)
Complexity	Low	Moderate (extra components)
Logging persistence	Immediate	External or deferred
Common use cases	Audit, error logging, notifications	Audit, error logging, compliance

External logging via Azure Function and sp_invoke_external_rest_endpoint

In SQL use this when the application might wrap your stored procedure in its own transaction. If the app rolls back after your proc finishes, any in-database transactions (including your logs) also roll back. An external logger runs outside the database transaction and remains durable.

Prerequisites and notes

sp_invoke_external_rest_endpoint is available in Azure SQL Database and Azure SQL Managed Instance. Ensure outbound network access to your Function endpoint.
Prefer Azure AD (Managed Identity) auth for your Function endpoint. If you must use function keys, pass them via header (x-functions-key), not query strings.
Set reasonable timeouts and add retry/backoff for transient failures.

Minimal SQL prerequisites for Function App Managed Identity

Grant your Function App’s managed identity access to the database and tables:

-- In your Azure SQL Database (connected as an Entra admin) CREATE USER [<FUNCTION_APP_MI_NAME>] FROM EXTERNAL PROVIDER; ALTER ROLE db_datawriter ADD MEMBER [<FUNCTION_APP_MI_NAME>]; -- Or grant explicit permissions GRANT INSERT ON dbo.ErrorLogs TO [<FUNCTION_APP_MI_NAME>];

T‑SQL: emit a log via REST

CREATE OR ALTER PROCEDURE dbo.add_numbers @x INT, @y INT, INT OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRY -- Log process start INSERT INTO dbo.ProcessLogs (LogMessage, ProcedureName) VALUES (CONCAT('Started adding numbers: ', @x, ' + ', @y), 'add_numbers'); -- Add numbers SET = @x + @y; -- Log process success INSERT INTO dbo.ProcessLogs (LogMessage, ProcedureName) VALUES (CONCAT('Successfully calculated sum = ', ), 'add_numbers'); END TRY BEGIN CATCH -- Log error details locally INSERT INTO dbo.ErrorLogs (ErrorMessage, ErrorProcedure, ErrorLine) VALUES (ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE()); -- Prepare JSON payload DECLARE @jsonPayload NVARCHAR(MAX) = N'{ "LogMessage": "Some error occurred", "ProcedureName": "add_numbers", "ErrorMessage": "' + ERROR_MESSAGE() + N'" }'; -- Call external REST endpoint (Azure Function) EXEC sp_invoke_external_rest_endpoint @method = 'POST', @url = 'https://yourfunctionapp.azurewebsites.net/api/WriteLog', @headers = '{"Content-Type": "application/json", "x-functions-key": "<FUNCTION_KEY>"}', = @jsonPayload; -- Re-throw original error to caller THROW; END CATCH; END; GO

Azure Function: HTTP-triggered logger (C#)

Auth: use AuthorizationLevel.Function (or Azure AD), avoid Anonymous in production.
DB access: use Managed Identity User to request an access token for Azure SQL. Once the request token is generated, next step is to insert logs/messages into a permanent log/error table.

Below is an end-to-end Function App code in C# which demonstrates above.

using Azure.Core; using Azure.Identity; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using Microsoft.Azure.WebJobs; using Microsoft.Azure.WebJobs.Extensions.Http; using Microsoft.Data.SqlClient; using Microsoft.Extensions.Logging; using System; using System.IO; using System.Text.Json; using System.Threading; using System.Threading.Tasks; namespace FunctionApp_MI_HTTPRequest { public static class ManagedInstance_Http_Request { private static readonly string SqlConnectionString = Environment.GetEnvironmentVariable("SqlConnectionString"); public class ProcedureLog { public string ProcedureName { get; set; } public string LogMessage { get; set; } } [FunctionName("WriteLog")] public static async Task<IActionResult> Run( [HttpTrigger(AuthorizationLevel.Function, "post")] HttpRequest req, ILogger log) { log.LogInformation("WriteLog invoked."); string requestBody = await new StreamReader(req.Body).ReadToEndAsync(); log.LogInformation($"Received Log Request: {requestBody}"); ProcedureLog logData; try { logData = JsonSerializer.Deserialize<ProcedureLog>(requestBody, new JsonSerializerOptions { PropertyNameCaseInsensitive = true }); if (logData == null || string.IsNullOrWhiteSpace(logData.ProcedureName)) { return new BadRequestObjectResult(new { status = "Error", message = "Invalid log data." }); } } catch (JsonException) { return new BadRequestObjectResult(new { status = "Error", message = "Invalid JSON format." }); } try { // Acquire access token for Azure SQL using Managed Identity var tokenCredential = new DefaultAzureCredential(); var accessToken = await tokenCredential.GetTokenAsync( new TokenRequestContext(new[] { "https://database.windows.net/.default" }), CancellationToken.None); using var conn = new SqlConnection(SqlConnectionString) { AccessToken = accessToken.Token }; await conn.OpenAsync(); using var transaction = conn.BeginTransaction(); var query = @" INSERT INTO dbo.ErrorLogs (ErrorMessage, ErrorProcedure) VALUES (@LogMessage, @ProcedureName);"; try { using var cmd = new SqlCommand(query, conn, transaction); cmd.Parameters.AddWithValue("@LogMessage", (object?)logData.LogMessage ?? string.Empty); cmd.Parameters.AddWithValue("@ProcedureName", logData.ProcedureName); await cmd.ExecuteNonQueryAsync(); transaction.Commit(); log.LogInformation($"Log inserted: {logData.ProcedureName} | {logData.LogMessage}"); return new OkObjectResult(new { status = "Success", message = "Log inserted successfully." }); } catch (SqlException ex) { transaction.Rollback(); log.LogError(ex, "Database transaction failed."); return new ObjectResult(new { status = "Error", message = "Database transaction failed." }) { StatusCode = 500 }; } } catch (Exception ex) { log.LogError(ex, "Internal Server Error"); return new ObjectResult(new { status = "Error", message = "Internal Server Error" }) { StatusCode = 500 }; } } } }

Calling the Function from T‑SQL

Header-based key (preferred):

DECLARE @jsonPayload NVARCHAR(MAX) = N'{ "LogMessage": "Some error occurred", "ProcedureName": "ProcessData" }'; EXEC sp_invoke_external_rest_endpoint @method = 'POST', @url = 'https://yourfunctionapp.azurewebsites.net/api/WriteLog', @headers = '{"Content-Type": "application/json", "x-functions-key": "<FUNCTION_KEY>"}', = @jsonPayload;

CREATE TABLE dbo.ErrorLogs ( Id INT IDENTITY(1,1) PRIMARY KEY, ErrorMessage NVARCHAR(MAX), ErrorProcedure NVARCHAR(255), ErrorLine INT NULL, ErrorDateTime DATETIME2 DEFAULT SYSDATETIME() ); GO CREATE TABLE dbo.ProcessLogs ( Id INT IDENTITY(1,1) PRIMARY KEY, LogMessage NVARCHAR(MAX), ProcedureName NVARCHAR(255), LogDateTime DATETIME2 DEFAULT SYSDATETIME() ); GO

Architecture diagram

PL/SQL reference (Oracle)

For comparison, here’s the autonomous transaction pattern in Oracle. Note the autonomous pragma on the logger procedure.

-- ========================================= -- Table: ERRORLOGS -- ========================================= CREATE TABLE errorlogs ( log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, procedure_nm VARCHAR2(100), error_msg VARCHAR2(4000), log_time TIMESTAMP DEFAULT SYSTIMESTAMP ); / -- ========================================= -- Procedure: LOG_ERROR (Autonomous Transaction Logger) -- ========================================= CREATE OR REPLACE PROCEDURE log_error ( p_procedure VARCHAR2, p_error VARCHAR2 ) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO errorlogs (procedure_nm, error_msg) VALUES (p_procedure, p_error); COMMIT; END; / -- ========================================= -- Procedure: ADD_NUMBERS (Sample Procedure) -- ========================================= CREATE OR REPLACE PROCEDURE add_numbers ( p_x NUMBER, p_y NUMBER, p_sum OUT NUMBER ) IS BEGIN p_sum := p_x + p_y; EXCEPTION WHEN OTHERS THEN log_error('add_numbers', SQLERRM); RAISE; END; /

Troubleshooting and FAQ -

Why aren’t my logs saved after a rollback in Azure SQL?

Inserts performed inside the same transaction are rolled back together. Use external logging via 'sp_invoke_external_rest_endpoint' to guarantee persistence regardless of the caller’s transaction.

Can I use CLR or linked servers for autonomous transactions in Azure SQL?

No. CLR integration and linked servers for DML aren’t supported in Azure SQL Database. Prefer external REST endpoints.

What are the security considerations for external logging?

Avoid 'Anonymous' Functions. Prefer Azure AD; second-best is 'AuthorizationLevel.Function' with keys in headers. Validate payloads and avoid logging sensitive data unless encrypted. Store secrets in Key Vault.

How do I monitor and validate the logging pipeline?

Use Application Insights in your Function. Emit structured logs for both success and failure. Configure alerts on failure rates and latency.

What performance impact should I expect?

External calls add network latency. For high-volume scenarios, batch logs, use async fire-and-forget patterns on the app side, and set sensible timeouts.

Summary

In summary, external logging offers durable visibility across services, making it ideal for distributed systems. Be sure to implement retries and validation, set up monitoring and alerts, and document the behavior clearly for your team to ensure everyone understands the trade-offs involved.

If you have any feedback or suggestion, please reach out to us at datasqlninja@microsoft.com

Data Migration Strategies for Large-Scale Sybase to SQL Migrations Using SSMA, SSIS and ADF- Part 1

Ankur_Sinha — Fri, 12 Dec 2025 16:33:49 GMT

Introduction

In today’s data-driven landscape, the migration of databases is a crucial task that requires meticulous planning and execution. Our recent project for migrating data from Sybase ASE to MSSQL set out to illuminate this process, using tools like Microsoft SQL Server Migration Assistant (SSMA), Microsoft SQL Server Integration Service (SSIS) packages, and Azure Data Factory (ADF).

We carefully designed our tests to cover a range of database sizes: 1GB, 10GB, 100GB, and 500GB. Each size brought its own set of challenges and valuable insights, guiding our migration strategies for a smooth transition.

For the smaller databases (1GB and 10GB), we utilized SSMA, which demonstrated strong efficiency and reliability in handling straightforward migrations. SSMA was particularly effective in converting database schemas and moving data with minimal complication. As we scaled up larger datasets (100GB and 500GB), we incorporated SSIS packages alongside Azure Data Factory to address the increased complexity and ensure robust performance throughout the migration process.

Our exercises yielded important findings related to performance metrics such as data throughput, error rates during transfer, and overall execution times for each migration approach. These insights helped us refine our methodologies and underscored the necessity of selecting the right tools for each migration scenario when transitioning from Sybase to MSSQL.

Ultimately, our experience highlighted that thorough analysis is essential for identifying potential bottlenecks and optimizing workflows, enabling successful and efficient migrations across databases of all sizes. The results reassure stakeholders that, with a well-considered approach and comprehensive testing, migrations can be executed seamlessly while maintaining the integrity of all datasets involved.

There are 2 parts in the Strategy, the 1st part covers the overview, source database environment setup for Sybase, target database environment setup for Azure SQL, migration steps, time consumed and key learnings for SSMA and SSIS, the remaining sections are covered in Part 2 here :- Part 2

Objectives of the Evaluation

Assess the migration duration for various database sizes.
Evaluate the capabilities and limitations of each tool.
Identify optimal patterns for bulk data movement.
Recommend the best tool or parameter combination for various scenarios which gives the best results for different sample datasets.

Overview of Tools

SQL Server Migration Assistant (SSMA) for SAP ASE

Microsoft SQL Server Migration Assistant (SSMA) for Sybase Adaptive Server Enterprise (ASE) is a tool for migrating SAP ASE databases to SQL Server 2012 (11.x) through SQL Server 2022 (16.x) on Windows and Linux, Azure SQL Database or Azure SQL Managed Instance.  It supports schema conversion, data migration, and limited post-migration testing. SSMA converts Sybase ASE database objects (tables, views, stored procedures, etc.) to Azure SQL-compatible formats and migrates data using a client-side or server-side engine.

SQL Server Integration Services (SSIS)

SQL Server Integration Services is a platform for building enterprise-level data integration and data transformations solutions. It offers flexibility in handling non-compatible objects, custom transformations, and large-scale data transfers through its pipeline architecture. SSIS is particularly useful when SSMA cannot migrate certain objects or when additional data transformation is required like Unparsed SQL, SET option conversion issues, Identifier conversion and issues, date format conversion and with NON-ANSI joins

Azure Data Factory (ADF)

ADF is a fully managed, serverless, cloud-based data integration service for orchestrating and automating data movement and transformation across on-premises and cloud environments. It is well-suited for hybrid migrations, large-scale data pipelines, and integration with Azure services like Azure SQL. ADF excels in scenarios requiring scalability and parallel processing.

Environment Setup

For testing, we used the following setup:

Source Environment

Sybase ASE Version: 16.0 SP03
OS: SUSE Linux Enterprise Server 15 SP6
VM Size: Standard B12ms (12 vcpus, 48 GiB memory)

Target Environment

SQL Server 2022: hosted on Azure VM
OS: Windows Server 2022
VM Size: Standard D16as v4 (16 vcpus, 64 GiB memory)

Network

Both VMs hosted in same Azure region
Premium SSD LRS disks for source and target

Metrics

We evaluated the tools based on:

Data Migration Time: Time to migrate 1 GB, 10 GB, 100 GB (1 x 100 GB table) and 500 GB (5 x 100 GB tables) of data.
Scalability: Performance with increased data volumes (up to 500 GB).

SQL Server Migration Assistant (SSMA)

Migration steps:

Install SSMA for SAP ASE 10.3 or above and required drivers (Sybase ASE ODBC/ADO.NET providers).
Create an SSMA project, configure source (Sybase ASE) and target (Azure SQL) connections.
Assess database compatibility, customize data type mappings (e.g., Sybase TEXT to SQL Server NVARCHAR(MAX)), and convert schema.
Migrate data using SSMA’s client-side or server-side engine.
Validate migrated objects and data.

Test Results:

Test Scenario	Data Size	Time Taken	Throughput	Threads	Notes
Single Copy	1 GB	28 seconds	~36 Mbps	1	Single Threaded
Single Copy	10 GB	4 minutes 36 seconds	~37 Mbps	1	Single Threaded
Parallel Copy	100 GB	44 minutes	~38 Mbps	40	Parallel Threads - Using project level setting changes, like Migration engine, batch size, parallel data migration mode and multi loading
Scalability Test	500 GB	3 hours 44 minutes	~38 Mbps	40	Parallel Threads - Same performance as of sequential nature of SSMA’s processing engine, which limits parallelism

Key learnings:

Excellent for schema conversion and small data sets
Not scalable beyond 100 GB, memory issues and slow single-threaded loads
Limited configuration for tuning bulk inserts

Performance Tuning Insights:

Migration Engine: Configured server-side data migration to optimize performance and reduce processing overhead. Client-side data migration refers to SSMA client retrieving the data from the source and bulk inserting that data into Azure SQL. Server-side data migration refers to SSMA data migration engine (bulk copy program) running on the Azure SQL box as a SQL Agent job retrieving data from the source and inserting directly into Azure SQL thus avoiding an extra client-hop (better performance). When choosing this method, you will need to specify which version of the BCP is intended to use (32bit or 64bit):

Batch Size: Data is migrated in batches from the source tables into Azure SQL tables within transactions. The batch size option determines how many rows are loaded into Azure SQL per transaction. By default, it is 10,000, but we increased it to 270,000 since our dataset contained 26,214,400 rows (around 100 GB).

Parallel Data Migration Mode: This option is available only when using the Client Side Data Migration Engine mode. It defines the number of parallel threads to be used during migration. By default, it is set to Auto (10 threads). To modify it, select Custom and specify the desired number of parallel threads. We changed this to 40 to get the best results.

Multi loading: With Multi-Loading enabled, SSMA uses multiple parallel threads to load data batches at the same time, which can significantly speed up migration for large tables. It essentially breaks the source data into chunks (based on your batch size setting) and loads them concurrently.

SQL Server Integration Services (SSIS)

Migration steps:

Create an SSIS project in SQL Server Data Tools (SSDT).
Configure ODBC / ADO .NET Source (Sybase ASE) and ADO .NET/OLE DB Destination (Azure SQL) with appropriate drivers.
Build Control flow and data flow tasks for each table, applying transformations for incompatible data types or business logic.
Execute packages in parallel for large tables, optimizing buffer size and commit intervals.
Monitor and log errors for troubleshooting.

Test Results:

Test Scenario	Data Size	Time Taken	Throughput	Threads	Notes
Single Copy	1 GB	31 seconds	~33 Mbps	1	Single Threaded
Single Copy	10 GB	4 minutes 16 seconds	~40 Mbps	1	Single Threaded
Parallel Copy	100 GB	38 minutes	~44 Mbps	5	Parallel Threads – Regulating MaxConcurrentExecutable, DefaultBufferMaxRows, Engine threads and Azure SQL maximum server memory
Scalability Test	500 GB	3 hours 12 minutes	~44 Mbps	5	Parallel Threads - Performance improved with larger buffer sizes (100 MB) and SSD I/O.

Key learnings:

Very fast for large data volumes with tuning
Requires development time (package design, error handling)
Bottleneck: network throughput on 500 GB run
Best suited for 10–500 GB on-prem or IaaS migrations

Performance Tuning Insights:

DefaultBufferSize: The task’s buffer settings can be configured using the DefaultBufferSize property, which defines the buffer size, and the DefaultBufferMaxRows property, which specifies the maximum number of rows per buffer. By default, the buffer size is 10 MB (with a maximum of 100 MB), and the default maximum number of rows is 10,000.

DefaultBufferMaxRows: The data flow engine begins the task of sizing its buffers by calculating the estimated size of a single row of data. It then multiplies the estimated size of a row by the value of DefaultBufferMaxRows to obtain a preliminary value for the buffer size.

If the result is greater than the value of DefaultBufferSize, the engine reduces the number of rows.
If the result is less than the minimum buffer size calculated inside the engine increases the number of rows.
If the result falls between the minimum buffer size and the value of DefaultBufferSize, the engine sizes the buffer as close as possible to the estimated row size times the value of DefaultBufferMaxRows.

If sufficient memory is available, it is better to use fewer large buffers instead of many small ones. In other words, performance improves when the number of buffers is minimized, and each buffer holds as many rows as possible.

IsSorted: Sorting using a SSIS task is a slow operation by definition. Avoiding unnecessary sorting can enhance the performance of data flow in the package. Set the IsSorted property of a component in the output data flow upstream to True.

ADO .NET Source: When retrieving data from a view using an OLE DB data source, choose SQL command as the data access mode and provide a SELECT statement. Using a SELECT statement ensures that the view is accessed in the most efficient way.

OLEDB Destination: Several OLE DB Destination settings can significantly impact data transfer performance:

Data Access Mode – This setting offers the Fast Load option, which internally uses a BULK INSERT statement to load data into the destination table, instead of executing individual INSERT statements for each row. Unless you have a specific reason to change it, keep the default Fast Load option enabled. When using Fast Load, additional performance-related settings become available (listed below).
Keep Identity – By default, this is unchecked. If the destination table has an identity column, SQL Server generates identity values automatically. Checking this option ensures that identity values from the source are preserved and inserted into the destination.
Keep Nulls – By default, this is unchecked. If a NULL value is encountered in the source and the target column has a default constraint, the default value will be inserted. Enabling this option preserves the NULL values from the source instead of applying the default constraint.
Table Lock – This is checked by default, meaning a table-level lock is acquired during data load instead of multiple row-level locks. This prevents lock escalation issues and generally improves performance. Keep this enabled unless the table is actively being used by other processes at the same time.
Check Constraints – Checked by default. This validates incoming data against the destination table’s constraints. If you are confident the data will not violate constraints, unchecking this option can improve performance by skipping the validation step.
Rows per Batch – The default value is -1, which means all incoming rows are treated as a single batch. You can change this to a positive integer to divide the incoming rows into multiple batches, where the value specifies the maximum number of rows per batch.
Maximum Insert Commit Size – The default value is 2147483647 (the maximum for a 4-byte integer), which commits all rows in a single transaction once the load completes successfully. You can set this to a positive integer to commit data in smaller chunks. While committing more frequently does add overhead to the data flow engine, it helps reduce pressure on the transaction log and tempdb, preventing excessive growth during high-volume data loads.

DelayValidation: SSIS uses validation to determine if the package could fail at runtime. SSIS uses two types of validation. First is package validation (early validation) which validates the package and all its components before starting the execution of the package. Second SSIS uses component validation (late validation), which validates the components of the package once started. If you set it to TRUE, early validation will be skipped and the component will be validated only at the component level (late validation) which is during package execution.

MaxConcurrentExecutables: It's the property of the SSIS package and specifies the number of executables (different tasks inside the package) that can run in parallel within a package or in other words, the number of threads SSIS runtime engine can create to execute the executables of the package in parallel.

EngineThreads: This property specifies the number of source threads (does data pull from source) and worker thread (does transformation and upload into the destination) that can be created by data flow pipeline engine to manage the flow of data and data transformation inside a data flow task, it means if the EngineThreads has value 5 then up to 5 source threads and also up to 5 worker threads can be created. Please note, this property is just a suggestion to the data flow pipeline engine, the pipeline engine may create less or more threads if required.

IsolationLevel: In SQL Server Integration Services (SSIS), IsolationLevel is a property that defines how a transaction within a package interacts with other concurrent transactions in the database. It determines the degree to which one transaction is isolated from the effects of other transactions. Default is ‘Serializable’ which Locks the entire data set being read and keeps the lock until the transaction completes. Instead set it to ‘ReadUncommited’ or ‘ReadCommited’. ‘ReadUncommited’ Reads data without waiting for other transactions to finish. Can read rows that are not yet committed (a.k.a. dirty reads). While ‘ReadCommited’ only reads committed rows. Prevents dirty reads by waiting until other transactions commit or roll back. Use ReadUncommited when you need speed and can tolerate inaccurate data else use ReadCommited but it is slower than Read Uncommitted because it must wait for locks to release.

TransactionOption: The TransactionOption property in SQL Server Integration Services (SSIS) is used to control how tasks, containers, or the entire package participate in transactions to ensure data integrity. It determines whether a task or container starts a transaction, joins an existing one, or does not participate in any transaction. The property is available at the package level, container level (e.g., For Loop, Foreach Loop, Sequence), and for individual Control Flow tasks (e.g., Execute SQL Task, Data Flow Task). Set this to ‘NotSupported’ by which the task or container does not participate in any transaction, even if a parent container or package has started one. If a transaction exists at a higher level (e.g., package or parent container), the task or container operates outside of it. It is the fastest because there’s no transaction overhead.

Database Recovery Model: The source database is configured to use the Bulk-logged recovery model, which aims to minimally log bulk operations. This should be significantly more performant than the Full recovery model, assuming the bulk insert meets the criteria required to be minimally logged. The criteria for the target table are as follows –

The target table is not being replicated.
Table locking is specified (using TABLOCK).
If the table has no indexes, data pages are minimally logged.
If the table does not have a clustered index but has one or more non-clustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty –
If the table is empty, index pages are minimally logged.
If table is non-empty, index pages are fully logged.
If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.

Pulling High Volumes of Data: To enhance the speed and efficiency of the data migration process, we devised an optimized approach that involves transforming the target table into a heap by dropping all its indexes at the outset, thereby eliminating the overhead of index maintenance during data insertion. Following this, we transfer the data to the heap table, which is significantly faster due to the absence of indexes. Once the data transfer is complete, we recreate the indexes on the target table to restore its original structure and optimize query performance. This streamlined method of dropping indexes, transferring data to a heap, and then recreating indexes substantially accelerates the overall migration process compared to maintaining indexes throughout.

Feedback and suggestions

We hope this post has helped you configure your migration solution and choose the right options to successfully migrate your databases. The remaining steps are covered in Part 2 here :- Part 2

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Data Migration Strategies for Large-Scale Sybase to SQL Migrations Using SSMA, SSIS and ADF- Part 2

Ankur_Sinha — Fri, 12 Dec 2025 16:29:05 GMT

Introduction

There are 2 parts in the solution. Part 1 covers SSMA and SSIS. Part 2 covers the Azure Data Factory (ADF), tests results, performance improvement guidelines and conclusion.

Azure Data Factory (ADF)

Migration steps:

Set up an Azure Data Factory instance and Integration Runtime (IR) for on-premises connectivity.
Create pipelines with Copy data activities, mapping Sybase ASE tables to SQL Server/Azure SQL.
Configure parallel copy settings and staging storage (Azure Blob Storage) for large datasets (if required).
Monitor pipeline execution and retry failed activities.

Test Results:

Test Scenario	Data Size	Time Taken	Throughput	Threads	Notes
Single Copy	1 GB	40 seconds	~25 Mbps	1	Single Threaded
Single Copy	10 GB	4 minutes 22 seconds	~40 Mbps	1	Single Threaded
Parallel Copy	100 GB	39 minutes	~44 Mbps	16	Parallel Threads
Scalability Test	500 GB	3 hours 10 minutes	~45 Mbps	16	Parallel Threads - ADF scaled better due to cloud elasticity

Key learnings:

Cloud-native, scalable: handled 500 GB with ease
Parallel copy and batch tuning make it faster as volume increases
Auto-scaling prevents many typical OOM (Out of Memory) errors seen with SSMA
Recommended for cloud targets (Azure SQL MI, Azure SQL DB)

Performance Tuning Insights:

In our setup we added a lookup, a foreach and one copy data within our pipeline.

Lookup: In the Settings tab, select the SQL dataset as the source. Instead of choosing the entire table, go to the “Use query” section and provide the query shown below. This query uses a recursive Common Table Expression (CTE) to dynamically generate partition ranges across a large integer sequence (representing the total number of rows).

Here is a sample output of this query for top 20 rows–

ForEach: Use this query in the Pipeline expression builder which is a reference to the Lookup output in our pipeline.

Copydata: Use below query at source which allows you to dynamically query different partitions of a large table (like Table_1_100GB) in parallel or sequentially.

On Sink, change “write batch size” and “Max concurrent connections as per the rowset and assigned memory. Optimizing these values can improve performance and reduce overhead during data transfers. For small rows, increase writeBatchSize to reduce batch overhead and improve throughput. For large rows, use a smaller value to avoid memory or database overload. If the source data exceeds the specified batch size, ADF processes data in multiple batches automatically.

Additionally adjust “Maximum data integration unit” and “Degree of copy parallelism”. A Data Integration Unit (DIU) is a measure that represents the power of a single unit in Azure Data Factory and Synapse pipelines. Power is a combination of CPU, memory, and network resource allocation. DIU only applies to Azure integration runtime. DIU doesn't apply to self-hosted integration runtime. While Degree of copy parallelism determines how many parallel copy activities can run simultaneously, optimizing the data transfer process.

Cumulative Tests Result

Disclaimer: All test results published herein are provided solely for reference purposes and reflect performance under ideal conditions within our controlled environment. Actual performance in the user's environment may vary significantly due to factors including, but not limited to, network speed, system bottlenecks, hardware limitations, CPU cores, memory, disk I/O, firewall configurations, and other environmental variables. On the source and target databases also multiple performance optimization and configuration adjustments have been implemented to enhance the migration efficiency. We strongly recommend that users conduct their own testing to determine performance under their specific conditions.

Data Size	Best Performer	Observation
1 GB	All tools performed efficiently	Minimal overhead; no significant performance difference.
10 GB	SSIS	Optimized batch processing led to better performance.
100 GB	SSIS and ADF	Both benefited from parallelism.
500 GB	ADF and SSIS	ADF's scalability and retry mechanisms proved valuable. SSIS was equivalent with tuned data flow components.

Data Volume	Row Count	SSMA Time / Speed	SSIS Time / Speed	ADF Time / Speed
1 GB	262,144	28 sec / ⚡ 36 MB/s	31 sec / ⚡33 MB/s	40 sec / ⚡25 MB/s
10 GB	2,621,440	4 min 36 sec / ⚡37 MB/s	4 min 16 sec / ⚡40 MB/s	4 min 22 sec / ⚡40 MB/s
100 GB	26,214,400	44 min / ⚡38 MB/s	38 min / ⚡44 MB/s	39 min / ⚡44 MB/s
500 GB	26,214,400 x 5	3 hr 44 min / ⚡38 MB/s	3 hr 12 min / ⚡44 MB/s	3 hr 10 min / ⚡45 MB/s

Performance Improvement Guidelines

Use SSMA for Schema Conversion: SSMA is the primary tool for automating schema conversion. Customize data type mappings (e.g., Sybase DATETIME to SQL Server DATETIME2) and handle case-sensitive databases carefully.
Leverage SSIS for Complex Data Transformations: Use SSIS for tables with non-compatible data types or when business logic requires transformation. Optimize performance with parallel tasks and appropriate buffer settings.
Use ADF for Large-Scale or Hybrid Migrations: ADF is ideal for large datasets or migrations to Azure SQL Database. Use staging storage and parallel copy to maximize throughput. Ensure stable network connectivity for on-premises to cloud transfers.
Tips to improve ADF performance:
- Use staging areas (e.g., Azure Blob Storage) to offload source systems and speed up data transfers.
- Enable parallel copy in Copy Activity to increase throughput.
- Use the Integration Runtime closest to your data source to reduce network latency.
- Enable data partitioning on large tables to parallelize read/write operations.
- Adjust degree of parallelism to match your compute capacity.
- Use Self-Hosted IR or Azure IR with higher compute for large or complex migrations.
- Enable Auto-Scaling where supported to handle spikes efficiently.
- Monitor IR utilization to avoid under-provisioning or over-provisioning.
Please refer below links for more details related to ADF –

Sybase ASE to Azure SQL full and incremental data copy using ASE Transfer Table Tool and ADF

Copy activity performance optimization features - Azure Data Factory & Azure Synapse | Microsoft Learn

Db2 to Azure SQL fast data copy using ADF
Migration Readiness Testing: Conduct performance testing on production-scale environments prior to the actual migration to obtain an accurate baseline of system behavior and identify potential bottlenecks under real workload conditions.
Hybrid Approach: Combine SSMA for schema conversion, SSIS for complex data migrations, and ADF for orchestration in large-scale or cloud-based scenarios. For example, use SSMA to convert schemas, SSIS to migrate problematic tables, and ADF to orchestrate the overall pipeline.
Validation: Post-migration, validate data integrity using checksums or row counts and test stored procedures for functional equivalence. Use SQL Server Management Studio (SSMS) for debugging. In the end we can use Microsoft Database Compare Utility that allows comparison of multiple source and target databases.

Challenges and Mitigations

Sybase-Specific Syntax: SSMA may fail to convert complex stored procedures with Sybase-specific T-SQL. Manually rewrite these using SQL Server T-SQL.
LOB Data: Large Object (LOB) data types (e.g., TEXT, IMAGE) may cause truncation errors. Map to NVARCHAR(MAX) or VARBINARY(MAX) and validate data post-migration.
Network Latency in ADF: For on-premises to Azure migrations, ensure high-bandwidth connectivity or use Azure ExpressRoute to minimize latency.
Case Sensitivity: Sybase ASE databases may be case-sensitive, while SQL Server defaults to case-insensitive. Configure SQL Server collations (e.g. SQL_Latin1_General_CP1_CS_AS) to match source behavior.

Conclusion

SSMA, SSIS, and ADF each offer unique strengths for migrating Sybase ASE to SQL Server, Azure SQL Database or Azure SQL Managed Instance. SSMA excels in schema conversion, SSIS in complex data transformations, and ADF in scalability and cloud integration. A hybrid approach, leveraging SSMA for schema conversion, SSIS for problematic data, and ADF for orchestration, often yields the best results. Evaluation shows ADF’s superior scalability for large datasets, while SSIS provides flexibility for complex migrations.

Proper planning, including schema assessment, data type mapping, and performance tuning, is critical for a successful migration. For further details refer to Microsoft’s official documentation:

SSMA for Sybase: SQL Server Migration Assistant for Sybase (SybaseToSQL) - SQL Server | Microsoft Learn

SSMA Project Settings: Project Settings (Migration) (SybaseToSQL) - SQL Server | Microsoft Learn

SSIS: SQL Server Integration Services - SQL Server Integration Services (SSIS) | Microsoft Learn

ADF: Azure Data Factory - Data Integration Service | Microsoft Azure

Feedback and suggestions

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Faster Data Copy between Source and target for partitioned table using Partition Switch in ADF

Ramanath_Nayak — Fri, 05 Dec 2025 01:08:02 GMT

Introduction

This blog post presents a comprehensive Azure Data Factory (ADF) solution for automating the migration of partitioned tables from IBM Db2 z/OS to Azure SQL Database. The solution consists of two main components: a Partition Discovery & Preparation Pipeline and a Parallel Copy Pipeline with partition switching capabilities. This approach significantly reduces migration time and ensures data integrity while maintaining partition structure in the target environment. As we use one separate table per partition for data copy, it divides the workload into multiple different tables hence reducing the complexity in data migration. For few partitioned/non-partitioned tables, there is another approach where parallel copy activities can be implemented.

Challenges in migrating of large, partitioned tables

Migrating large, partitioned tables from IBM Db2 z/OS to Azure SQL Database presents unique challenges:

Complex Partition Structures: Db2 z/OS supports various partitioning schemes (range, hash, list) that need to be properly mapped
Large Data Volumes: Enterprise tables can contain billions of rows across hundreds of partitions
Minimal Downtime Requirements: Business-critical applications require near-zero downtime migrations
Data Integrity: Ensuring consistency across all partitions during migration
Performance Optimization: Maximizing throughput while managing resource consumption

This approach addresses most of the challenges above in an intelligent, automated approach:

Automatically discovers partition metadata from Db2 system tables
Creates optimized migration plans based on partition characteristics
Create one temporary persistent table per partition(Which can be dropped after the process is complete) with the same partition function and scheme to help in migration(Check constraint is another way to implement this, however for the sake of simplicity, we are cloning the base table)
Executes parallel data transfer with partition-level granularity
Implements partition switching for minimal downtime
Provides comprehensive monitoring and error handling

Solution Overview

The solution has two phases both implemented using an automated ADF Pipeline.

Phase 1: Discovery & Preparation

Using a Db2 copy activity, extract necessary data from Db2 System tables.
Extracts partition metadata (boundaries, row counts, sizes)
Creates migration control tables in Azure SQL Database
Generates optimized copy strategies per partition

Phase 2: Copy and Partition switch

Executes partition-level data transfers in parallel
Implements partition switching for seamless integration
Provides real-time status tracking and error handling
Supports restart capabilities for failed partitions

Prerequisites

Azure data factory instance
Self-Hosted Integration Runtime (SHIR) with Db2 connectivity
Access to System tables on Db2.
Schema of Db2 Source Partitioned table should be migrated to SQL(SQL table will be used to clone temporary persistent tables per partition)

ADF Pipeline Phase 1: Partition Discovery & Preparation Pipeline

Fig 1.1 Partition Preparation Pipeline

The diagram above illustrates the initial phase of the solution, which sets up the necessary tables and extracts essential information from Db2 into Azure SQL. Here’s a breakdown of each step:

The first script establishes a control table in SQL called Db2_PARTITION_STATUS.
The second script adds indexes to Db2_PARTITION_STATUS to improve access speed.
The third step uses copy activities to retrieve partition details from Db2 system tables and populate the Db2_PARTITION_STATUS table.
The fourth step creates a stored procedure named ClonePartitionedTable, which automates the cloning of SQL tables—one for each partition—making it easier to handle hundreds of partitions.
The fifth step fetches relevant rows from Db2_PARTITION_STATUS. Users can decide which tables to migrate by updating the MIGRATE column in this table.
The final step generates a clone of the source table for each partition, supporting the overall migration process.

ADF Pipeline Phase 2: Data Copy & Partition Switch

Fig 1.2 Parallel copy and Switch pipeline

The next phase involves the actual data copy between partitions

First step gets the table and partition information for the rows which need to be migrated using a lookup activity in ADF.
For each row that is retrieved from step1 above, there are a series of activities which are executed in parallel. Details of it are provided below.

Fig 1.3 ForEach Activity Details

As there are multiple rows which come into the foreach activity, each instance of foreach represents on partition of the source table. So the activities highlighted below will be performed for each and every partition in parallel that has been marked for migration.

First we update the Status table that the data copy has started for this particular partition using a script activity.
Next is a copy activity, which copies the particular partition data information from source to the same partition on a cloned SQL table.
If the Copy activity was successfully, the status table is updated with a status as Start Switching.
If the copy activity fails, it updates the status as Copy Failed. This helps in tracking which partition data was copied and which failed. It also helps in restarting copy of failed partitions.
Then there is a script activity which actually does the partition switch using an Alter table command.
Again the status of the partition switch is updated as success/Failure in the status table, helping in exactly tracking which partitions were successful and which were not.

Conclusion

This comprehensive Azure Data Factory solution provides a robust, scalable approach to migrating partitioned tables from IBM Db2 z/OS to Azure SQL Database. However it is not restricted to Db2 as source. The same architecture can be used for other partitioned sources with slight modifications. The two-pipeline architecture ensures:

Key Benefits Achieved

Automated Discovery: Eliminates manual partition mapping and reduces human error
Intelligent Optimization: Applies data-driven strategies for optimal performance
Parallel Processing: Maximizes throughput through partition-level parallelism
Minimal Downtime: Uses partition switching for near-instantaneous data integration
Comprehensive Monitoring: Provides real-time visibility into migration progress
Error Resilience: Isolates failures and enables selective retry operations

Business Impact

Reduced Migration Time: From weeks to days through automation and parallelization
Lower Risk: Comprehensive testing and rollback capabilities
Cost Efficiency: Optimal resource utilization and reduced manual effort
Maintainability: Standardized approach applicable across multiple migration projects

Future Enhancements

The solution foundation supports several potential enhancements:

Machine Learning Integration: Predictive optimization of batch sizes and thread counts
Advanced Monitoring: Integration with Azure Monitor and custom dashboards
Cross-Platform Support: Extension to other database platforms beyond Db2
Automated Testing: Built-in data validation and integrity checking
Cloud-Native Optimization: Leverage Azure SQL Database elastic pools and serverless compute

This solution represents a best-practice approach to enterprise data migration, combining the power of Azure Data Factory with intelligent partition management strategies. Organizations implementing this solution can expect significant reductions in migration time, risk, and cost while achieving reliable, repeatable results across their data migration initiatives.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please send an email to Database Platform Engineering Team.

AI-Powered Db2 LUW to Azure Database for PostgreSQL Schema Converter

naruldoss — Wed, 12 Nov 2025 14:56:30 GMT

Introduction

This blog introduces a custom-built tool designed to migrate database objects from IBM Db2 LUW (Linux, Unix, Windows) to Azure Database for PostgreSQL. While SSMA for Db2 supports migrations to SQL Server, this tool specifically assists with migration to Azure Database for PostgreSQL, helping customers transition smoothly to the Azure ecosystem.

Db2 LUW to Azure database for PostgreSQL

As organizations work to migrate Db2 systems, tools that assist with database migrations are increasingly relevant. The Db2 LUW to Azure PostgreSQL Database Converter is designed to facilitate the migration of IBM Db2 LUW (Linux, Unix, Windows) databases to Azure database for PostgreSQL. Created by the Microsoft Azure SQL CSE/Ninja Team, this tool provides an interface intended to support migration workflows.

With the appropriate drivers and prerequisites, the tool currently supports the conversion of Db2 LUW to Azure database for PostgreSQL. Future versions of this may include enhancements to existing object conversion and support for other Db2 platforms like z/OS and Db2 for i (AS400).

System Requirements

▪ Supports Db2 on LUW version 9.8, 10.1, and later versions

▪ Windows 10, Windows 11

▪ .NET 8.0 Desktop Runtime

▪ Microsoft OLEDB Provider for DB2 is required to access the IBM Db2 Databases.

▪ Azure OpenAI models (For converting Triggers, Functions and Stored Procedures).

Database Object Conversion

The Db2 LUW to Azure PostgreSQL Database Converter streamlines enterprise-scale database migrations with a segmented workflow. Users are self-guided through connection setup, schema selection and object conversion via intuitive tabs and input fields. With Azure OpenAI integration Db2 LUW triggers, functions and procedures are converted seamlessly. The tool also features built-in conversion log and statistics, a robust design for reliable use by engineers and architects during database migrations.

The tool effectively extracts metadata from the source Db2 database and transforms the relevant objects into PostgreSQL formats. All Db2 metadata is retained locally, enabling efficient reuse during subsequent offline conversion process. Error and warning notifications are issued as appropriate, with detailed error log files generated to record any issues arising throughout execution and conversion. Furthermore, a comprehensive telemetry report detailing object conversions is provided upon completion of the process.

Looking to migrate your Db2 LUW workloads to Azure Database for PostgreSQL? Contact the Azure SQL Engineering Team at datasqlninja@microsoft.com to get started with the tool and user guide.

If you have feedback or suggestions for improving this data migration asset, please contact the Database Platform Engineering Team. Thanks for your support!

Note: Db2 editions on z/OS, iSeries, and Linux/UNIX/Windows (LUW) differ in their subsystems, databases, and object definitions/functions. This tool currently supports only Db2 LUW; Db2 z/OS and Db2 iSeries are not yet supported.

Future Road Map: Multiple significant enhancements are scheduled for the current tool, including expanded support for Db2 for z/OS and Db2 for iSeries. Aimed at strengthening compatibility, increasing performance, and introducing additional features to facilitate enterprise-level Db2 database migrations to Azure PostgreSQL.

Enforcing SQL PaaS backup retention with Azure Policy

David_Lyth — Mon, 08 Sep 2025 15:56:52 GMT

Implementation for SQL DB PITR using the portal

Azure policy covers much more than SQL; here we are using a small portion of its capabilities. The bits we are using are

A policy definition, on what the policy checks for, and what to do about issues
A policy assignment, with the scope to check the definition across, and parameter values
A remediation task, that makes the required changes

The requirement in this example is to ensure that all Azure SQL Databases have a short-term (PITR) backup retention of at least 9 days.

Any database created without specifying the retention period will have this added
Any update made with a shorter period will have that modified to be 9 days
Modifications or database creation that explicitly set the retention period to more than 9 days will have that value honoured

All these are built under “Policy” in the portal

The definition

Open Policy | Authoring | Definitions, and on that blade, use the “+ Policy definition” to create a new one

Definition Location: the subscription to hold this (there’s a pull-down menu of valid items)
Name: for example, “Enforce SQL DB PITR”
Category: for example “Backup”
Role Definitions: “Contributor” for this example, but in general this should be the minimum needed for the updates that the definition will make
Policy rule:

{ "mode": "All", "policyRule": { "if": { "anyOf": [ { "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays", "exists": false }, { "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays", "less": "[parameters('Minimum_PITR')]" } ] }, "then": { "effect": "modify", "details": { "roleDefinitionIds": [ "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c" ], "operations": [ { "operation": "addOrReplace", "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays", "value": "[parameters('Minimum_PITR')]" } ] } } }, "parameters": { "Minimum_PITR": { "type": "Integer", "metadata": { "displayName": "Min PITR", "description": "Min PITR retention days" } } } }

In this code

Field is what we want to check and/or change; get the list of field names using PowerShell

$aliases = Get-AzPolicyAlias -ListAvailable -NamespaceMatch 'Microsoft.Sql' | where ResourceType -like 'retentionpol' | Select-Object -ExpandProperty 'Aliases' $aliases | select Name

For the list of fields that can be modified/updated, look at the Modifiable attribute

$aliases | Where-Object { $_.DefaultMetadata.Attributes -eq 'Modifiable' } | select Name

Minimum_PITR is the name of the parameter the assignment (next step) will pass in. You choose the name of the parameter
roleDefinitionIds are the full GUID path of the roles that the update needs. The policy remediation docs talk about this, but we can get the GUID with PowerShell

Get-AzRoleDefinition -name 'contributor' # replace contributor with the role needed

This definition is saying that if the PITR retention isn’t set, or is less than the parameter value, then make it (via addOrReplace) the parameter value.

The Assignment

Once you save the definition, use “Assign policy” on the screen that appears

For this, there are several tabs

Basics:
- Scope and exclusions let you work on less than the entire subscription
- enable “policy enforcement”
Parameters
- Enter 9 for Min_PITR (to have policy apply 9 days as the minimum)
Remediation
- Tick “create remediation task”
- Default is to use a system managed identity

Then create this assignment

Initial Remediation

Once the assignment is created, look at the compliance blade to see it; Azure policy is asynchronous, so for a newly created assignment, it takes a little while before it begins checking resources in its scope.

Similarly, “remediation tasks” on the remediation blade shows the task pending to begin with.

Once the initial remediation scan completes, you can look at the backup retention policies (in Data Management | backups) on the logical server(s) and see that the PITR retention periods have been increased to a minimum of 9 days.

Ongoing operation

With the initial remediation complete, the policy will now intercept non-compliant changes, and refactor them on the fly. For example, if we use PowerShell to set the retention to 2 days

$DB_PITR = get-azsqldatabasebackupshorttermretentionpolicy -ResourceGroupName mylittlestarter-rg -ServerName mylittlesql -DatabaseName oppo $DB_PITR | Set-AzSqlDatabaseBackupShortTermRetentionPolicy -RetentionDays 2 ResourceGroupName : mylittlestarter-rg ServerName : mylittlesql DatabaseName : oppo RetentionDays : 9 DiffBackupIntervalInHours : 12

The update completes, but the summary shows that the retention stays as 9 days

The experience on the portal is the same; we can change the retention to 1 day in the GUI, and the operation succeeds, but with the retention remaining at 9 days. In the activity log of either the logical server or the database, this shows up as a modify, with the JSON detail of the modify showing the policy name and the effect.

Tricky bits

A few challenges that can cause delays:

Retention policies are separate resources – Both short-term and long-term backup retention aren’t direct attributes of the database resource. Instead, they exist as their own resources (e.g., with retentionDays) tied to the database.
Keep policies simple – Focusing each policy on a single resource (like SQL DB PITR) proved more effective than trying to create one large, all-encompassing policy.
Case sensitivity matters – The policy definition code is case-sensitive, which can easily trip you up if not handled carefully.
The definitionRoleID is just the GUID of the security role that the policy is going to need, not anything to do with the identity that’s created for the remediation task…but the GUID is potentially different for each subscription, hence the PowerShell to figure out this GUID
Writing the definitions in PowerShell means that they are just plain-text, and don’t have any syntax helpers; syntax issues in the definition tend to appear as strange “error converting to JSON” messages.
Waiting patiently for the initial policy remediation cycle to finish; I haven’t found any “make it so” options

References

The posts mentioned in the introduction are

Automatically Enable LTR and PITR Policy upon a Database creation on Azure SQL Managed Instance | Microsoft Community Hub using audits and runbooks
Azure custom policy to prevent backup retention period to be below X number - Azure SQL | Microsoft Community Hub which uses ‘Deny’ to fail attempts that don’t meet the requirements.

Expanding this using PowerShell

With a working example for SQL DB PITR, we now want to expand this to have policies that cover both short and long term retention for both SQL DB and SQL MI.

The code below isn’t exhaustive, and being a sample, doesn’t have error checking; note that the code uses “less” for the policy test, but operators like “equals” and “greater” (https://learn.microsoft.com/en-us/azure/governance/policy/concepts/definition-structure-policy-rule#conditions ) are available to build more complex tests, depending on the policy requirements. The document Programmatically create policies - Azure Policy | Microsoft Learn covers using powershell with Azure policy.

Other wrinkles that this sample doesn’t explicitly cater for include

LTR retentions are held in ISO 8601 format (eg, ‘P8D’ for 8 days), so it’s not trivial to do less than tests; in theory ARM template functions could be used to convert these into the number of days, but this example just does an equality check, and enforces the policy, without any understanding that P4W is a longer period than P20D
LTR isn’t available for serverless databases with autopause enabled (https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose#auto-pause ); this would need some form of scope control, potentially either using resource groups, or a more complex test in the policy definition to look at the database attributes
A few service levels, for example the Basic database SLO, have different limits for their short term retention
PITR for databases that could be offline (stopped managed instances, auto-paused serverless databases, etc) hasn’t been explicitly tested.
Remediation tasks just run to completion, with no rescheduling; to ensure that all existing databases are made compliant, this could be expanded to have a loop to check the count of resources needing remediation, and start a task if the relevant existing ones are complete

<# /***This Artifact belongs to the Data SQL Ninja Engineering Team***/ Name: Enforce_SQL_PaaS_backup_retention.ps1 Author: Databases SQL CSE/Ninja, Microsoft Corporation Date: August 2025 Version: 1.0 Purpose: This is a sample to create the Azure policy defintions, assignment and remediation tasks to enfore organisational policies for minimum short-term (PITR) and weekly long-term (LTR) backup retention. Prerequisities: - connect to your azure environment using Connect-AzAccount - Register the resource provider (may already be done in your environment) using Register-AzResourceProvider -ProviderNamespace 'Microsoft.PolicyInsights' - if needed to modify/update this script, this can be used to find field names: Get-AzPolicyAlias -ListAvailable -NamespaceMatch 'Microsoft.Sql' | where ResourceType -like '*retentionpol*' | Select-Object -ExpandProperty 'Aliases' | select Name Warranty: This script is provided on as "AS IS" basis and there are no warranties, express or implied, including, but not limited to implied warranties of merchantability or fitness for a particular purpose. USE AT YOUR OWN RISK. Feedback: Please provide comments and feedback to the author at datasqlninja@microsoft.com #> # parameters to modify $Location = 'EastUS' # the region to create the managed identities used by the remediation tasks $subscriptionID = (Get-AzContext).Subscription.id # by default use the current Subscription as the scope; change if needed # the policies to create; PITR can do a less than comparison, but LTR has dates, so uses string equalities [array]$policies = @() $policies += @{type = 'DB'; backups='PITR'; name = 'Enforce SQL DB PITR retention'; ParameterName = 'Minimum_PITR'; ParameterValue = 9; Role = 'contributor'; Category='Backup'} $policies += @{type = 'MI'; backups='PITR'; name = 'Enforce SQL MI PITR retention'; ParameterName = 'Minimum_PITR'; ParameterValue = 9; Role = 'contributor'; Category='Backup'} # LTR retention is in ISO8601 format, eg P2W = 2 weeks, P70D = 70 days; 'PT0S' = no retention $policies += @{type = 'DB'; backups='LTR';name = 'Enforce SQL DB LTR retention'; Weekly = 'P4W'; Monthly = 'PT0S'; Yearly = 'PT0S'; WeekofYear = 1; Role = 'contributor'; Category='Backup'} $policies += @{type = 'MI'; backups='LTR';name = 'Enforce SQL MI LTR retention'; Weekly = 'P4W'; Monthly = 'PT0S'; Yearly = 'PT0S'; WeekofYear = 1; Role = 'contributor'; Category='Backup'} # templates for the Policy definition code; this has placeholders that are replaced in the loop $Policy_definition_template_PITR = @' { "mode": "All", "policyRule": { "if": { "anyOf": [ { "field": "Microsoft.Sql/<Type>/databases/backupShortTermRetentionPolicies/retentionDays", "exists": false }, { "field": "Microsoft.Sql/<Type>/databases/backupShortTermRetentionPolicies/retentionDays", "less": "[parameters('<ParameterName>')]" } ] }, "then": { "effect": "modify", "details": { "roleDefinitionIds": [ "/providers/Microsoft.Authorization/roleDefinitions/<RoleGUID>" ], "operations": [ { "operation": "addOrReplace", "field": "Microsoft.Sql/<Type>/databases/backupShortTermRetentionPolicies/retentionDays", "value": "[parameters('<ParameterName>')]" } ] } } }, "parameters": { "<ParameterName>": { "type": "Integer" } } } '@ # LTR, look for any of the weekly/monthly/yearly retention settings not matching $Policy_definition_template_LTR = @' { "mode": "All", "policyRule": { "if": { "anyOf": [ { "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weeklyRetention", "exists": false }, { "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weeklyRetention", "notEquals": "[parameters('Weekly_retention')]" }, { "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/monthlyRetention", "notEquals": "[parameters('Monthly_retention')]" }, { "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/yearlyRetention", "notEquals": "[parameters('Yearly_retention')]" } ] }, "then": { "effect": "modify", "details": { "roleDefinitionIds": [ "/providers/Microsoft.Authorization/roleDefinitions/<RoleGUID>" ], "operations": [ { "operation": "addOrReplace", "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weeklyRetention", "value": "[parameters('Weekly_retention')]" }, { "operation": "addOrReplace", "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/monthlyRetention", "value": "[parameters('Monthly_retention')]" }, { "operation": "addOrReplace", "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/yearlyRetention", "value": "[parameters('Yearly_retention')]" }, { "operation": "addOrReplace", "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weekOfYear", "value": "[parameters('WeekofYear')]" } ] } } }, "parameters": { "Weekly_retention": { "type": "String" }, "Monthly_retention": { "type": "String" }, "Yearly_retention": { "type": "String" }, "WeekofYear": { "type": "Integer" } } } '@ # main loop foreach ($policy in $policies) { # translate the Role name into its GUID $Role = Get-AzRoleDefinition -name $($policy.Role) $type = $policy.type -replace 'MI','managedInstances' -replace 'DB','servers' $template = if ($policy.backups -eq 'PITR') {$Policy_definition_template_PITR} else {$Policy_definition_template_LTR} # generate the definition code for this policy $policy_definition = $template -replace '<Type>',$type -replace '<RoleGUID>',$($Role.Id) -replace '<ParameterName>',$policy.ParameterName # create the policy definition $PolicyDefinition = new-AzPolicyDefinition -Name $($policy.name) -Policy $policy_definition -Metadata "{'category':'$($policy.Category)'}" # create the assignment if ($policy.backups -eq 'PITR') { $PolicyParameters = @{$($policy.ParameterName)=($($policy.ParameterValue))} } else { $PolicyParameters = @{"Weekly_retention"=($($policy.Weekly)); "Monthly_retention"=($($policy.Monthly)); "Yearly_retention"=($($policy.Yearly)); "WeekofYear"=($($policy.WeekofYear));} } $PolicyAssignment = New-AzPolicyAssignment -Name $($policy.name) -PolicyDefinition $PolicyDefinition -PolicyParameterObject $PolicyParameters -IdentityType 'SystemAssigned' -Location $Location # now follow the docs page to wait for the ID to be created, and assign the roles required to it; https://learn.microsoft.com/en-us/azure/governance/policy/how-to/remediate-resources?tabs=azure-powershell # include a loop to wait until the managed identity created as part of the assignment creation is available do { $ManagedIdentity = Get-AzADServicePrincipal -ObjectId $PolicyAssignment.IdentityPrincipalId -erroraction SilentlyContinue if (!($ManagedIdentity)) {start-sleep -Seconds 1} # wait for a bit... } until ($ManagedIdentity) $roleDefinitionIds = $PolicyDefinition.PolicyRule.then.details.roleDefinitionIds if ($roleDefinitionIds.Count -gt 0) { $roleDefinitionIds | ForEach-Object { $roleDefId = $_.Split("/") | Select-Object -Last 1 $roleAssigned = New-AzRoleAssignment -ObjectId $PolicyAssignment.IdentityPrincipalId -RoleDefinitionId $roleDefId -Scope "/subscriptions/$($subscriptionID)" } } # lastly create the remediation task $RemediationTask = Start-AzPolicyRemediation -Name $($policy.name) -PolicyAssignmentId $PolicyAssignment.Id } # confirm that the policies have been set up Get-AzPolicyDefinition | where name -In $policies.name | format-table Name, PolicyType Get-AzPolicyAssignment | where name -In $policies.name | format-table Name, Parameter

Feedback and suggestions

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Migrating Oracle Partitioned Tables to Azure PostgreSQL Without Altering Partition Keys

VenkatMR — Thu, 21 Aug 2025 19:17:06 GMT

Introduction

When migrating partitioned tables from Oracle to Azure PostgreSQL Flexible Server, many customers prefer to preserve their existing Oracle table design exactly as defined in the original DDLs. Specifically, they want to avoid altering the partition key structure, especially by not adding the partition key to any primary or unique constraints, because doing so would change the table’s original design integrity.

The challenge arises because PostgreSQL enforces a rule: any primary key or unique constraint on a partitioned table must include the partition key. This difference in constraint handling creates a migration roadblock for customers aiming for a like-for-like move from Oracle without schema changes.

To bridge this gap and emulate Oracle’s partitioning behavior, the pg_partman extension offers a practical solution. It supports declarative partitioning in PostgreSQL while eliminating the need to modify primary or unique constraints to include the partition key. This enables successful migrations while preserving complete compatibility with Oracle’s partitioning model and eliminating the need for schema changes.

Background

For example, consider the following Oracle “Orders” table partitioned by the order_date column.

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER NOT NULL, order_date DATE NOT NULL, status TEXT, total_amount NUMERIC(10,2) ) PARTITION BY RANGE (order_date); CREATE TABLE orders_2025_m1 PARTITION OF orders FOR VALUES FROM ('2024-12-01') TO ('2025-01-01'); CREATE TABLE orders_2025_m2 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'); CREATE TABLE orders_2025_m3 PARTITION OF orders FOR VALUES FROM ('2025-02-01') TO ('2025-03-01'); CREATE TABLE orders_2025_m4 PARTITION OF orders FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

In Oracle, it’s valid to define a primary key only on order_id without including the partition key (order_date). Many customers want to preserve this design when migrating to Azure PostgreSQL Flexible Server. However, Azure PostgreSQL Flexible Server requires that any primary or unique constraint on a partitioned table must also include the partition key. Attempting to keep a primary key solely on order_id will result in an error.

To replicate the Oracle’s behavior, the pg_partman extension along with the table template can be used. It allows partition management without forcing the partition key into primary or unique constraints, enabling the migration to retain the original table structure.

CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, order_date DATE NOT NULL, status VARCHAR(20), total_amount NUMERIC(10,2) ) PARTITION BY RANGE (order_date); unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "orders" lacks column "order_date" which is part of the partition key.

Enable the Server Level Parameters for PG_PARTMAN

To configure the server-level parameter, go to the Azure portal, open the left-hand panel, and search for ‘Server Parameters’ under the Settings section. Then, search for azure.extensions, check the box for PG_PARTMAN in the value field, and click Save

Once the above is completed search for “shared_preload_libraries” and in the value section click the checkbox for PG_PARTMAN_BGW and then click SAVE.

The above step would prompt the restart of the server.

Prerequisites at Database level

Once the server is restarted login to the database either by using PgAdmin or through psql. And set up the role and following permissions.

CREATE ROLE partman_role WITH LOGIN; CREATE SCHEMA partman; CREATE EXTENSION pg_partman SCHEMA partman; --- Create extension if not already created GRANT ALL ON SCHEMA partman TO partman_role; GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role; GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role; GRANT ALL ON SCHEMA public TO partman_role; GRANT TEMPORARY ON DATABASE postgres to partman_role;

And if you have the partition table not part of partman_role ensure that usage on the schema granted. Example:- GRANT USAGE ON SCHEMA partman TO partman_role;

Create partition table without Primary Key

Create the parent table including the partition key without including the Primary key.

CREATE TABLE orders ( order_id BIGINT, customer_id BIGINT NOT NULL, order_date DATE NOT NULL, status VARCHAR(20), total_amount NUMERIC(10,2) ) PARTITION BY RANGE (order_date);

Create a Template Table

In order to use the Primary Key part of the table and not to include it in the partition key use the template as shown below. Notice that it’s the same structure as parent table and included primary key for the column order_id.

CREATE TABLE orders_template ( order_id BIGINT , customer_id BIGINT NOT NULL, order_date DATE NOT NULL, status VARCHAR(20), total_amount NUMERIC(10,2), PRIMARY KEY (order_id) );

Create parent table using Pg_Partman

Once the above tables are completed, the next step is to invoke the create_parent function as shown below.

SELECT partman.create_parent( p_parent_table := 'public.orders', p_control := 'order_date', p_type := 'native', p_interval := 'monthly', p_template_table := 'public.orders_template' );

Notice that, the above script included orders_template table as a parameter for template table, this would ensure that partitions are created with the primary keys automatically.

Validate the partition table

After inserting the records validate the partitions created

SELECT tableoid::regclass AS partition, * FROM orders;

EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01';

EXPLAIN SELECT * FROM orders WHERE order_id > 100;

The query plan above shows that the partition key (order_date) is primarily used for date-range queries, independent of the primary key. In contrast, queries filtering by order_id rely on the primary key, which is defined separately from the partition key.

Feedback and Suggestions

If you have feedback or suggestions for improving this asset, please contact the Data SQL Ninja Team (datasqlninja@microsoft.com).
Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Thank you for your support!

Key Considerations to avoid Implicit Conversion issues in Oracle to Azure SQL Modernization

Nitish_reddy_kotha — Tue, 09 Sep 2025 05:27:35 GMT

Overview

This blog dives into the mechanics of implicit data type conversions and their impact during the post-migration performance optimization phase of heterogeneous database migrations. Drawing from our observed Engineering field patterns across diverse application architectures, this blog explores why certain platforms like ORMs, JDBC drivers, and cross-platform data models are more prone to implicit conversions than others and how they result in performance issues, break indexes, or cause query regressions. You'll gain actionable strategies to detect, mitigate, and design around these pitfalls to ensure a successful and performant data migration to platforms like Azure SQL.

Understanding Implicit Conversions in Database Migrations

When an application communicates with a database whether through JDBC, ODBC, ADO.NET, or any other data access API it sends parameters and query values in a specific data type format. However, if the type of the value provided by the application does not exactly match the data type of the target column in the database, the database engine attempts to reconcile the mismatch automatically. This automatic adjustment is known as implicit conversion. For instance, a value passed as a string from the application may be compared against a numeric or date column in the database. This occurs because many front-end systems and APIs transmit values as strings by default, even if the underlying business logic expects numbers or dates. Unless the application explicitly parses or casts these values to match the expected types, the database engine must decide how to handle the type mismatch during query execution. In such cases, the engine applies type conversion internally, either to the parameter or the column, based on its own rules. While this feature can simplify application development by allowing flexible data handling, it often introduces engine-specific behavior that becomes more visible during cross engine database migrations, where assumptions built into one system may not hold true in another.

Impact of Implicit Conversions

Implicit conversions can adversely affect database performance and functionality in several ways, some of which are discussed below:

Performance Degradation: When a database performs an implicit conversion, it may bypass indexes, resulting in slower query execution. For example, comparing a VARCHAR column to an INT value in SQL Server can trigger a table scan instead of an index seek, significantly increasing query time.

Migration-Specific Issues and Data Integrity Risks: Implicit conversions can cause data loss or incorrect results during a few instances and one such example is, when a column defined as VARCHAR2 in Oracle, which can store Unicode characters by default is mapped to a VARCHAR column in SQL Server, non-ASCII characters such as Chinese, Russian, or Korean may be silently replaced with incorrect characters/symbols. One example of scenario when this can happen:

Oracle VARCHAR2 stores Unicode if the database character set is UTF-8 (AL32UTF8), which is common in modern Oracle installations.

SQL Server VARCHAR is ANSI/code-page based, so non-ASCII characters are stored differently, unless the column is explicitly declared as NVARCHAR.

-- In Real World this can happen on any other data types

Maintenance Challenges: Queries relying on implicit conversions are harder to debug and optimize, as these conversions are not explicitly visible in the code and may only surface during performance regressions. These queries forces the optimizer to compile an execution plan containing scans of large clustered indexes, or tables, instead of a seek resulting in degraded performance

Execution Overhead and Resource Consumption: Implicit conversions increase execution times for both queries and API calls, as the engine must perform runtime casting operations. This can lead to higher CPU usage, increased logical reads, and memory pressure.

Detection Methods

Detecting implicit conversions is crucial for optimizing database performance post-migration. The following methods can be employed to detect:

Query Store (QDS): Use QDS post-migration during load testing to track expensive queries based on cost and surface performance regressions caused by type mismatches. Review execution plans captured in QDS for conversion-related patterns. You can also use custom script like below to query the QDS:

USE <[Replace_with_actual_DB_name]> -- Replace with the actual database name GO SELECT TOP (100) DB_NAME() AS [Database], qt.query_sql_text AS [Consulta], rs.last_execution_time AS [Last Execution Time], rs.avg_cpu_time AS [Avg Worker Time], rs.max_cpu_time AS [Max Worker Time], rs.avg_duration AS [Avg Elapsed Time], rs.max_duration AS [Max Elapsed Time], rs.avg_logical_io_reads AS [Avg Logical Reads], rs.max_logical_io_reads AS [Max Logical Reads], rs.count_executions AS [Execution Count], q.last_compile_start_time AS [Creation Time], CAST(p.query_plan AS XML) AS [Query Plan] FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id WHERE CAST(p.query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%' AND qt.query_sql_text NOT LIKE '%sys.query_store%' ORDER BY rs.avg_cpu_time DESC;

Execution Plans: For the expensive queries, in SSMS, hover over operators like Index Scan to inspect the Predicate. If implicit conversion exists, the plan includes something like “CONVERT_IMPLICIT(<data_type>, ..”

XML Plan: For a confirmation of above, reviewing the underlying XML execution plan confirms whether implicit conversion is occurring and on which side of the comparison. This technique is particularly valuable when working with parameterized queries or when graphical plan warnings are insufficient. Look for elements like below in the XML plan:

Plan Cache Inspection: Custom scripts can be written to scan the Azure SQL plan cache for any instances of CONVERT_IMPLICIT operations. Below is one such script that can be used to find.

SELECT TOP (100) DB_NAME(B.[dbid]) AS [Database], B.[text] AS [SQL_text], A.total_worker_time AS [Total Worker Time], A.total_worker_time / A.execution_count AS [Avg Worker Time], A.max_worker_time AS [Max Worker Time], A.total_elapsed_time / A.execution_count AS [Avg Elapsed Time], A.max_elapsed_time AS [Max Elapsed Time], A.total_logical_reads / A.execution_count AS [Avg Logical Reads], A.max_logical_reads AS [Max Logical Reads], A.execution_count AS [Execution Count], A.creation_time AS [Creation Time], C.query_plan AS [Query Plan] FROM sys.dm_exec_query_stats AS A WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(A.plan_handle) AS B CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS C WHERE CAST(C.query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%' AND B.[dbid] = DB_ID() AND B.[text] NOT LIKE '%sys.dm_exec_sql_text%' ORDER BY A.total_worker_time DESC

XE event: Extended Events (XE) is valuable in support scenarios when Query Store or telemetry data alone can't pinpoint issues like implicit conversions, especially if plans aren't cached or historical data lacks detail. XE provides real-time capture of plan-affecting convert events, offering granular insights into query behavior that QS might miss during short-lived or dynamic workloads. However, use it sparingly due to overhead, as a targeted diagnostic tool rather than a broad solution. You can use below script to turn it off. Remember to stop and drop the event immediately when you are done collecting.

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'Detect_Conversion_Performance_Issues') DROP EVENT SESSION [Detect_Conversion_Performance_Issues] ON SERVER; GO CREATE EVENT SESSION [Detect_Conversion_Performance_Issues] ON SERVER ADD EVENT sqlserver.plan_affecting_convert( ACTION(sqlserver.database_name, sqlserver.sql_text) WHERE ([sqlserver].[database_name] = N'<Replace_with_your_DB_name>') -- Replace your DB name ) ADD TARGET package0.ring_buffer WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF ); GO ALTER EVENT SESSION [Detect_Conversion_Performance_Issues] ON SERVER STATE = START; -- View the raw Extended Events buffer SELECT s.name AS session_name, t.target_name, CAST(t.target_data AS XML) AS raw_buffer_xml FROM sys.dm_xe_sessions s JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address WHERE s.name = 'Detect_Conversion_Performance_Issues';

Documentation Reference: Microsoft docs on conversion and precedence help explain engine behavior and mappings around implicit conversion triggers. This close look at them along with app developers during the schema/code conversion phase can help better understanding and mitigation.

Implicit Conversion: Real-World Example

To evaluate the impact of implicit conversions in Azure SQL during post-migration scenarios, we created a synthetic workload example using a table named dbo.Customers. It contains one million rows and includes columns such as AccountNumber, CustomerName, PhoneNumber, and JoinDate. The AccountNumber, CustomerName, and PhoneNumber columns were initially defined as VARCHAR, and Nonclustered indexes were created on these fields to enable efficient lookups. From the application layer, parameters were passed using NVARCHAR, which mirrors typical real-world ORM behavior particularly in Java-based applications or when migrating from Oracle, where VARCHAR2 frequently stores Unicode characters. This deliberate mismatch allows us to study the real performance consequences of implicit conversions in Azure SQL’s query execution engine. Although enabling SET STATISTICS XML ON can expose implicit conversions during query execution, our approach tries to reflect how these issues are usually uncovered in real-world scenarios where customers are less aware of this issue. In this case, we used Query Store and execution plan XML inspection

Problem: Implicit Conversion Due to Type Mismatch:

A NVARCHAR parameter from the application is compared against a VARCHAR column in the database. This scenario highlights a silent performance regression that can go unnoticed post-migration without detailed plan inspection.

Query Used: DECLARE ACC NVARCHAR(20) = N’ACC000500’; SELECT CustomerID, AccountNumber, CustomerName, PhoneNumber FROM dbo.Customers WHERE AccountNumber = ACC;

Execution Plan Behavior:

Fig: 1

SQL Server applies an implicit CONVERT_IMPLICIT(nvarchar, AccountNumber) on the column side as we can see from Fig 1 when you hover to Index Scan and see the Predicate

This disables the use of the nonclustered index on AccountNumber, leading to an Index Scan

The XML plan includes a <Warning> tag under <PlanAffectingConvert>

Extended Events monitoring consistently shows "plan_affecting_convert" warnings indicating suboptimal query plans caused by these conversions

What's Missing:

Type alignment between the query parameter and the column.

Awareness that even matching string lengths won’t help if encoding mismatches exist.

Impact:

Index Seek is lost, and full scans are triggered.

Higher Exec times and Overall costs observed.

Mitigation via Explicit CAST – Matching the Column’s Type:

In some cases, especially during post-migration tuning, application teams may not be able to change the database schema, but developers can update the query to explicitly align data types. This scenario simulates such a mitigation where an NVARCHAR parameter is explicitly cast to VARCHAR to match the column’s data type and avoid implicit conversions.

Query Used: DECLARE ACC NVARCHAR(20) = N'ACC000500'; SELECT CustomerID, AccountNumber, CustomerName, PhoneNumber FROM dbo.Customers WHERE AccountNumber = CAST(@acc AS VARCHAR(20)); -- Explicit use of CAST

Execution Plan Behavior:

Fig: 2

The CAST operation ensures that the parameter side matches the VARCHAR column type.

SQL performs an Index Seek on the IX_AccountNumber index. The Seek Predicates as seen in Fig 2 confirms this showing Scalar “Operator(CONVERT..”

No <Warning> tag appears in the XML execution plan indicating the absence of implicit conversions.

What's Fixed:

Type mismatch is resolved on the query side without altering the database schema.

The query is now SARGable, enabling index usage.

Impact:

Index Seek is lost, and full scans are triggered.

Higher Exec times and Overall costs observed.

What's Still Missing:

This still creates a long-term maintainability concern, especially when many queries or columns are affected.

Developers must remember to manually CAST in every affected query, increasing code complexity and the chance of inconsistency.

Missed CASTs in other queries can still cause implicit conversions, so the issue isn’t eliminated just patched locally.

Fix at DB end – Parameter Usage aligned Schema Column type

This fix involves altering the column type to NVARCHAR, aligning it with the NVARCHAR parameter passed from the application. It eliminates implicit conversions and enables index seeks, improving performance. However, it’s a database-side adjustment, the ideal long-term fix lies in ensuring the application sends parameters matching the original column type.

Query Used: DECLARE ACC NVARCHAR(20) = N'ACC000500'; SELECT CustomerID, AccountNumber, CustomerName, PhoneNumber FROM dbo.Customers WHERE AccountNumber = CAST(@acc AS VARCHAR(20));

Execution Plan Behavior:

Fig: 3

The CAST operation ensures that the parameter side matches the VARCHAR column type.

As seen in Fig 3 an Index Seek is performed on the updated IX_AccountNumber index. The Seek Predicates confirm this showing “Scalar Operator(..”

No <Warning> tag appears in the XML execution plan indicating the absence of implicit conversions.

What's Fixed:

The fix is schema-driven and works universally, ensuring consistent performance across tools and interfaces.

Encoding alignment between the parameter and column removes conversion logic entirely, making query plans stable and predictable.

Impact:

Indexes remain fully usable without manual intervention in queries.

Application code stays clean, no casts or workarounds are needed.

This is the most sustainable fix but may require coordination with application and DB teams.

What's Still Missing:

Some of implications around data type changes will be around data types consuming additional storage, for example NVARCHAR consumes 2 bytes/char, increasing storage when compared to VARCHAR.

Implicit vs Explicit vs Aligned: Execution Plan Behavior Comparison

Scenario	Predicate Expression in Exec Plan	Implicit Conversion	Index Seek Used	XML Plan Warning Shown
Data Type Mismatch	CONVERT_IMPLICIT(nvarchar, AccountNumber) = ACC	Yes	No (results in scan)	PlanAffectingConvert
Explicit Cast in Query	AccountNumber = CONVERT(varchar, ACC)	No	Yes	No
Matching Data Types (NVARCHAR)	AccountNumber = ACC	No	Yes	No

Best Practices for Managing Implicit Conversions

Refactoring Application: Legacy systems, especially those using dynamic SQL or lacking strict type enforcement, are prone to implicit conversion issues. Refactor your application code to leverage strongly typed variables and parameter declarations to ensure data type consistency at the source, minimizing implicit conversions during query execution.

Explicit Data Type Casting: Use CAST or CONVERT functions to explicitly define conversions, reducing reliance on implicit behavior. In our example we have used CAST, but a CONVERT function would have worked equally well. Both approaches explicitly align the parameter type to the column and avoid implicit conversions, enabling index seek.

Data Type Alignment: When you are performing heterogenous migrations which involve different database engines, ensure data types are consistent between source and target DB engines. Check official documents thoroughly to know and see the nuances around your data and application convertibility and know the implications like additional storage, collation changes etc. that can negatively affect your business.

Indexing: Create indexes on columns frequently involved in WHERE filters and JOIN predicates with matching data types to avoid implicit conversions that would cause index seeks to degrade into scans and ensures optimal index utilization by the optimizer.

Early Testing: Conduct thorough post-migration testing using QDS to identify, then drill down on execution plans and performance metrics to identify and resolve conversion-related issues. Early collaboration between Developer and DBA teams will be crucial.

Tools and Scripts: Utilize SQL Server Migration Assistant (SSMA) for Oracle to identify and change mappings early when you know your application needs. Additionally, use can use custom scripts or third-party tools if necessary to detect implicit conversions in the plan cache.

References

https://learn.microsoft.com/sql/t-sql/data-types/data-type-conversion-database-engine

https://learn.microsoft.com/sql/t-sql/data-types/data-type-precedence-transact-sql

Final Thoughts

We hope that this post has helped you gain actionable strategies to detect, mitigate, and design around implicit conversions in order to ensure a successful and performant data migration to platforms such as SQL Server or Azure SQL. If you have feedback or suggestions for improving this post, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!

Optimized Data Transfer from Sybase ASE to Azure SQL via Chunked BCP Processing

Manish_Kumar_Pandey — Tue, 07 Oct 2025 14:33:30 GMT

Introduction

Enterprises upgrading legacy databases often face challenges in migrating complex schemas and efficiently transferring large volumes of data. Transitioning from SAP ASE (Sybase ASE) to Azure SQL Database is a common strategy to take advantage of enhanced features, improved scalability, and seamless integration with Microsoft services. With business growth, the limitations of the legacy system become apparent, performance bottlenecks, high maintenance costs, and difficulty in integrating with modern cloud solutions.

SQL Server Migration Assistant for SAP Adaptive Server Enterprise (SSMA) Automates migration from SAP ASE to SQL Server, Azure SQL Database and Azure SQL Managed Instance. While SSMA provides a complete end-to-end migration solution, the custom BCP script (ASEtoSQLdataloadusingbcp.sh) enhances this process by enabling parallel data transfers, making it especially effective for migrating large databases with minimal downtime.

Script Workflow

One of the most common challenges we hear from customers migrating from Sybase ASE to SQL Server is: “How can we speed up data transfer for large tables without overwhelming the system?” When you are dealing with hundreds of tables or millions of rows, serial data loads can quickly become a bottleneck.

To tackle this, we created a script called ASEtoSQLdataloadusingbcp.sh that automates and accelerates the data migration process using parallelism. It starts by reading configuration settings from external files and retrieves a list of tables, either from the source database or from a user-provided file. For each table, the script checks if it meets criteria for chunking based on available indexes. If it does, the table is split into multiple views, and each view is processed in parallel using BCP, significantly reducing the overall transfer time. If chunking is not possible, the script performs a standard full-table transfer.

Throughout the entire process, detailed logging ensures everything is traceable and easy to monitor. This approach gives users both speed and control , helping migrations finish faster without sacrificing reliability.

Prerequisites

Before running the script, ensure the following prerequisites are met:

Database schema is converted and deployed using SQL Server Migration Assistant (SSMA).
Both the source (SAP ASE) and target (Azure SQL DB) databases are accessible from the host system running the script.
Source ASE database should be hosted on Unix or Linux.
The target SQL Server can be hosted on Windows, Linux, or as an Azure.

Configuration Files

The configuration aspect of the solution is designed for clarity and reuse. All operational parameters are defined in external files, this script will use following external config files during

bcp_config.env

The primary configuration file, bcp_config.env, contains connection settings and control flags. In the screenshot below you can see the format of the file.

chunking_config.txt

The chunking_config.txt file defines the tables to be partitioned, identifies the primary key column for chunking, and specifies the number of chunks into which the data should be divided.

table_list.txt

Use table_list.txt as the input if you want a specific list of tables.

Steps to run the script

Script Execution Log

The script log records tables copied, timestamps, and process stages.

Performance Baseline

A test was run on a 32-core system with a 10 GB table (262,1440 rows) for ASE and SQL. Migration using SSMA took about 3 minutes.

Using the BCP script with 10 chunks, the entire export and import finished in 1 minute 7 seconds. This demonstrates how parallelism and chunk-based processing greatly boost efficiency for large datasets.

Disclaimer: These results are for illustration purposes only. Actual performance will vary depending on system hardware (CPU cores, memory, disk I/O), database configurations, network latency, and table structures. We recommend validating performance in dev/test to establish a baseline.

General Recommendation

Larger batch sizes (e.g., 10K–50K) can boost throughput if disk IOPS and memory are sufficient, as they lower commit overhead.
More chunks increase parallelism and throughput if CPU resources are available; otherwise, they may cause contention when CPU usage is high.

Monitor system’s CPU and IOPS:

When the system has high idle CPU and low I/O wait, increasing both the number of chunks and the batch size is appropriate.
If CPU load or I/O wait is high, reduce batch size or chunk count to avoid exhausting resources.
This method aligns BCP operations with your system's existing capacity and performance characteristics.

Steps to Download the script

Please send an email to the alias: datasqlninja@microsoft.com and we will send you the download link with instructions.

What’s Next: Upcoming Enhancements to the Script

Smart Chunking for Tables Without Unique Clustered Indexes

Enable chunk-based export using any unique key column, even if the table lacks a unique clustered index.
This will extend chunking capabilities to a broader range of tables, ensuring better parallelization.

Multi-Table Parallel BCP with Intelligent Chunking

Introduce full parallel execution across multiple tables.
If a table qualifies for chunking, its export/import will also run in parallel internally, delivering two-tier parallelism: across and within tables.

LOB Column Handling (TEXT, IMAGE, BINARY)

Add robust support for large object data types.
Include optimized handling strategies for exporting and importing tables with TEXT, IMAGE, or BINARY columns, ensuring data fidelity, and avoiding performance bottlenecks.

Feedback and Suggestions

Thank you for your support!

Temporal Table Replication in SQL Server: Common Barriers and Solutions

Sonali_Solanki — Fri, 18 Jul 2025 18:06:14 GMT

Introduction

Transactional replication is a SQL Server feature that copies and synchronizes data and database objects across servers. It generally begins with a snapshot of the publication database objects and data. After this initial snapshot, any data changes and schema modifications made at the Publisher are delivered to the Subscriber as they occur, typically in near real time. These data changes are applied to the Subscriber in the same order and within the same transaction boundaries as at the Publisher, maintaining transactional consistency within a publication

Standard transactional replication in SQL Server does not provide support for system-versioned temporal tables. This constraint presents difficulties for organizations aiming to replicate historical data maintained in temporal columns, such as ValidFrom and ValidTo. The challenge persists even when system versioning is disabled, yet there remains a requirement to retain the original values within the target database.

Understanding Temporal Tables

System-versioned temporal tables are a specialized form of user table designed to retain a comprehensive record of all data modifications. These tables facilitate point-in-time analysis by automatically recording historical changes. Each temporal table contains two datetime2 period columns that specify the validity duration for each row. In addition to the current table, an associated history table preserves previous versions of rows whenever updates or deletions take place.

Scenario & Challenge

In one of the migration scenarios, the customer faced an issue where system versioning was disabled, but there was still a requirement to replicate data from the ValidFrom and ValidTo columns to the target database without modification. Although temporal tables are commonly used for auditing and historical analysis, replicating them within a transactional replication setup can present specific technical challenges:

System managed period columns complicate schema compliance.
Mismatch in ValidFrom and ValidTo columns across environments can compromise audit reliability.

As transactional replication currently does not support temporal columns, we devised the following solution to address this requirement.

Common Error Example

When configuring replication for an article that includes a system-versioned temporal table, the setup process may encounter failures due to SQL Server limitations related to system-generated columns.

In certain situations where system versioning is disabled, it may still be necessary to replicate the initial values of the ValidFrom and ValidTo period columns on the target system. However, during the configuration of transactional replication, the snapshot application process can fail on these columns, resulting in the following error:

Error message:

This issue arises because SQL Server considers these columns system-generated and restricts direct inserts, including during replication. The following workaround addresses this situation.

The Workaround

To successfully replicate temporal tables, follow these steps:

Note: This approach will work in case of scenarios when there is a scope of minimal downtime.

Predefine Table Schema on Target: Ensure that the source table schema exists on the target and matches with the source schema.
Disable System Versioning Temporarily: Before configuring replication, disable system versioning on the temporal table. This allows replication to treat it like a regular table.

ALTER TABLE [dbo].[Department] SET (SYSTEM_VERSIONING = OFF);

3. When you set SYSTEM_VERSIONING = OFF and don't drop the SYSTEM_TIME period, the system continues to update the period columns for every insert and update operation. Use the below script to remove the period for system time.

ALTER TABLE dbo.Department DROP PERIOD FOR SYSTEM_TIME;

4. After this step, we can use the below script step by step to configure replication.

Replication Setup Steps

Set a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

use master GO exec sp_replicationdboption @dbname = N'SourceDBNAme', @optname = N'publish', @value = N'true' GO

Create a transactional publication. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName] GO exec sp_addpublication @publication = N'PublicationName', @description = N'Transactional Replication publication of database', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @SnapShot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' GO

Create the Snapshot Agent for the specified publication. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName] GO exec sp_addpublication_snapshot @publication = N'PublicationName', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @publisher_security_mode = 0, @job_login = N'', @job_password = N'', @publisher_login = N'', @publisher_password = N''

Create an article and add it to a publication. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName] GO exec sp_addarticle @publication = N'PublicationName', @article = N'ArticleName', @source_owner = N'Source Schema Name', @source_object = N'SourceTableName', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'truncate', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Destination Table Name', @destination_owner = N'Destination Schema Name', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboEmployee', @del_cmd = N'CALL sp_MSdel_dboEmployee', @upd_cmd = N'SCALL sp_MSupd_dboEmployee' GO

Add a subscription to a publication and set the Subscriber status. This stored procedure is executed at the Publisher on the publication database.

use [SourceDBName] GO exec sp_addsubscription @publication = N'PublicationNAme', @subscriber = N'Azure SQL DB Server NAme', @destination_db = N'Target DB Name', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 GO

Add a new scheduled agent job used to synchronize a push subscription to a transactional publication. This stored procedure is executed at the Publisher on the publication database.

Use [SourceDBNAme] GO exec sp_addpushsubscription_agent @publication = N'PublicationNAme', @subscriber = N'Azure SQL DB Server NAme', @subscriber_db = N'Target DB Name', @job_login = N'', @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'', @subscriber_password = null, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor' GO

5. Once you have performed all the above steps and completed the data migration on target database you need to stop/delete the replication and again add period for system_time on the target table and enable system versioning.

ALTER TABLE dbo.Department ADD PERIOD FOR SYSTEM_TIME(<ValidFrom>,<ValidTo>); ALTER TABLE [dbo].[Department] SET (SYSTEM_VERSIONING = ON);

Note:

The <ValidFrom> and <ValidTo> columns are datetime2 columns defined as PERIOD FOR SYSTEM_TIME, using GENERATED ALWAYS AS ROW START and ROW END. Request to refer the period column names you have created while creating the temporal table and use the same while adding the period columns in the above script.

Conclusion

Migrating temporal tables within a transactional replication environment involves managing system-versioned features appropriately. Temporarily disabling system versioning and removing the SYSTEM_TIME period allows for adherence to schema requirements and facilitates data replication. After completing replication on the target platform, re-enabling system versioning reinstates temporal table functionality while maintaining data integrity.

This workaround ensures that your replication strategy remains robust while preserving the audit trail and historical insights offered by temporal tables.

Seamless Online Homogeneous SQL Family Migration via Azure Data Factory using SQL CDC

Vinod_Kumar_MSFT — Thu, 06 Nov 2025 05:20:45 GMT

Migrating data across SQL platforms, be it SQL Server, Azure SQL Database, Managed Instance, or SQL Server on IaaS, often involves operational complexity and potential downtime. Azure Data Factory (ADF) removes those barriers by enabling seamless, logical data movement across these services in either direction. Whether using SQL Change Data Capture (CDC) for near-zero downtime or traditional batch-based strategies, ADF ensures data consistency and operational continuity throughout the process.

While physical data migration strategies remain valuable in many scenarios, this blog focuses on how ADF delivers a unified, scalable approach to logical database migration, in modernizing the database environments with minimal downtime.

Prerequisites

NOTE:

Please make sure to go through the limitations of CDC as this blog doesn't cover those.

SQL CDC Limitations

Known Issues with CDC

Before proceeding, please ensure you have the following prerequisites:

An Azure subscription.
Access to Azure Data Factory.
Source and target databases, such as SQL Server, Azure SQL Database, Azure SQL MI etc.
Enable Change Data Capture (CDC) on the source database for online migration.
- CDC captures changes like insert, update, and delete (DML) operations in the source database, allowing near real-time replication to a target database with minimal latency. To enable CDC, run:

-- Enable CDC on the database EXEC sys.sp_cdc_enable_db; -- Enable CDC on the source table EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'SourceTable', @role_name = NULL;

Azure Data Factory Provisioning
- ADF should be provisioned to provide a runtime environment for executing the pipeline.
Self-hosted Integration Runtime (SHIR)
- SHIR is required to connect to the data source or destination which is not natively reachable by Azure (e.g., on-premises, private VNET, behind firewall).
Linked Services
- These should be created to connect to the source and target.
Datasets
- Datasets identify data within different data stores, such as tables, files, folders, and documents.
Performance Optimization
- To speed up the process, primary keys, non-clustered indexes and constraints should be dropped on the target to reduce blocking/deadlocks and minimize resource contention.
Script Components
- Adf_source.sql
  - This script should be deployed on the source SQL Server. It will populate information in the dbo.data_extraction_config_adf table to run Change Data Capture (CDC) and the initial load pipeline.
- Adf_target.sql
  - This script should be deployed on the target SQL server. It will create stored procedures to help merge CDC changes and create objects necessary for running pipelines smoothly.
- Master tables
  - dbo.cdc__watermark__adf contains information about the CDC tables for the last watermark.
  - dbo.data__extraction__config__adf contains information about the heap tables for initial load and CDC tables.
  - dbo.sqlqueries contains information about the clustered tables for initial load.

Let's deep dive into pipelines to handle different scenarios

Pipeline 1: ClusteredTableMigration_Initial
- This pipeline migrates data only from clustered tables.
- The dbo.sqlqueries table automatically populates with clustered table info via the pipeline (Stored Procedure Activity).
- Ensure the source table schema matches the target table schema. To run the pipeline for specific tables, set the IsActive flag to 0 (inactive) or 1 (active) in the sqlqueries table or add the table name in the Lookup activity.

Pipeline 2: HeapTableMigration_Initial
- This pipeline is designated for migrating heap tables. Prior to executing this pipeline, ensure that the heap table information has been added to the dbo.data__extraction__config__adf table.
- The source table schema should be synchronized with the target table schema.
- To execute the pipeline for a set of tables, the IsActive flag may be set to 0 (inactive) or 1 (active) in the dbo.data__extraction__config__adf table.

Pipeline 2: Heap Table Migration

Pipeline 3: CDCTableMigration
- This pipeline facilitates the migration of clustered tables with Change Data Capture (CDC) enabled. Prior to execution, please ensure that the relevant information for these clustered tables is entered into the dbo.data__extraction__config__adf table.
- Ensure the table schema is synchronized with the target schema, and that all tables intended for CDC synchronization possess a primary key and matching schema definition on the target system (excluding constraints and non-clustered indexes).
- To execute the pipeline for specific tables, the IsActive flag can be set to 0 (inactive) or 1 (active) in the dbo.data__extraction__config__adf table.

Pipeline 3: CDC Table Migration

Schedule the Pipeline - For CDC load only

Create a trigger: Create a trigger to schedule the pipeline to run at regular intervals (e.g., every 5-30 minutes based on application requirements) to capture and apply changes incrementally.

Monitor the pipeline: Monitor the pipeline runs to verify that the data is being migrated and synchronized accurately.

Cutover and cleanup

Once the delta changes are synchronized fully on source and target database, cutover can be initiated by setting the source database to read-only and then changing the connection string of the application (or all apps, agent jobs etc. that are impacted) to use the new target database and perform cleanup by deleting the SPs in target database and stop the CDC, remove tables, and SPs in source database.

Conclusion

Using Azure Data Factory allows for both online and offline data migration with minimal downtime, ensuring consistency between source and target databases. Change Data Capture enables near real-time data migration, suitable for environments requiring continuous data synchronization.

Note - To get ADF Pipelines and T-SQL Queries mentioned in this blog please reach out to our team alias : datasqlninja@microsoft.com

Hidden pitfalls of Temporary Tables in Oracle to PostgreSQL Migrations

KapilSamant — Thu, 26 Jun 2025 00:59:58 GMT

If you have been relying on Oracle Database as your primary system for analytics and the generation of MIS reports, you are probably familiar with the use of temporary tables within stored procedures. These temporary tables play an important role in managing intermediate data, performing complex calculations, and streamlining the overall data processing workflow. Temporary tables help in handling large volumes of data, break down queries into manageable steps, and produce complex analytical reports efficiently.

However, when organizations migrate these systems to Azure PostgreSQL, most automated code converters simply translate Oracle temporary tables into Azure PostgreSQL temporary tables without highlighting the key difference.

Understand the misconception

In Oracle the Global Temporary Table is a persistent schema object whose structure is permanent, but data is temporary. Internally, Oracle stores all data inserted into a GTT in the temporary tablespace, isolating it per session by using temporary segments that are dynamically allocated and cleaned up at the end of the session or transaction, depending on whether the table is defined with ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS. While the table metadata remains in the data dictionary, the data itself is never written to the redo logs.

Oracle also introduced Private Temporary table in version 18C which has an extra option of ON COMMIT DROP DEFINITION which drops the table at transaction commit.

Azure PostgreSQL too has a temporary table object that supports all the three commit clauses available in Oracle i.e. ON COMMIT DELETE ROWS, ON COMMIT PRESERVE ROWS and ON COMMIT DROP but the object is private to the session that created it with both its structure and data completely invisible to other sessions and the table itself is dropped automatically when the session ends.

Oracle Global Temporary Tables have a permanent table definition accessible by all sessions but store data privately per session or transaction, whereas Azure PostgreSQL temporary tables exist only for the duration of a session and are dropped automatically at session end.

At first glance, this difference might seem trivial, after all, you can simply add a CREATE TABLE statement in your code to recreate the temporary table at the start of every session. But what appears to be a small tweak can quickly spiral into a performance nightmare, overloading your system in ways you wouldn’t expect if not managed carefully.

Whole Azure PostgreSQL is built on an MVCC architecture, which means even its internal tables and system catalogue tables retain deleted rows of dropped objects. If you relook at the key difference between temp tables, you will understand that every time a temp table is created and drop per session, it adds few rows and deletes it from many system catalogue tables. See example below

Following is the output of Pgstattuple for three of the system tables.

Now I run a function a few times sequentially that joins multiple tables and writes the data into a temp table and return the response. You can see that there is a slight increase but nothing to be concerned about.

But if the same function is called by 500 sessions concurrently, you can see that the increase is dramatic.

There is also a marked increase in IOPS consumption as shown below

Understand the impact

As seen above, system catalogue tables like pg_class, pg_attribute, and pg_type can grow rapidly in size as each session that creates and drops temporary tables leaves behind dead tuples in these catalogues, contributing to significant bloat. This accumulation happens because Azure PostgreSQL records metadata for every temporary table in the system catalogues, and when the tables are dropped (typically at session end), their metadata is simply marked as dead rather than immediately removed.

In highly transactional environments, this bloat can escalate dramatically, sometimes increasing by hundreds or even thousands of times within just a few hours. Azure PostgreSQL relies heavily on its system catalogue during parsing, planning, and execution phases of every SQL statement.

Also, every temp table created will try to utilize temp buffer to store the data but if the data is large and temp buffer is small then naturally the data is stored on disk. This frequent creation and deletion of files adds a lot of disk IO. Under normal conditions, this will be taken care of by the file management. However, when the system is under heavy load, this process can become a bottleneck and slow down even normal select statements.

This catalogue bloat and frequent file and buffer management under heavy or repeated use of temporary tables will lead to high CPU consumption and will slow down existing users which will intern add more CPU load and quickly the system will get inundated and possibly crash.

Below example shows almost 3 times increase in planning time with bloated system table as compared to without bloat

Conclusion

It's important to recognize that Azure PostgreSQL and Oracle implement temporary tables differently: Oracle's global temporary tables are persistent schema objects that do not add significant system load whereas Azure PostgreSQL's temporary tables are always session-specific and are dropped at session, this along with its MVCC architecture adds significant load on the system in certain situations. This fundamental difference means that if not handled properly it can cause database to crash.

When migrating workloads from Oracle to Azure PostgreSQL, developers should carefully consider whether a temporary table is truly necessary, or if the requirement can be addressed more elegantly using alternatives like CTEs or views.

In some scenarios, temporary tables are indispensable, for example, they provide an efficient way to store intermediate results, for simplifying complex query logic or for collecting data from ref-cursor and no workarounds fully match the flexibility of the temp tables for these use cases.

If you can’t get rid of temp tables, then it’s absolutely necessary to have a robust alerting on system table bloat and having a custom job that does frequent vacuuming on these tables.

Feedback and suggestions

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.

Ingesting Mainframe File System Data (EBCDIC) into SQL DB on Fabric Using OSS Cobrix

Ramanath_Nayak — Fri, 06 Jun 2025 15:18:39 GMT

Introduction

Mainframe/Midrange data is often stored in fixed-length format, where each record has a predetermined length, or variable-length format, where each record’s length may vary. The data is stored in binary format, using Extended Binary Coded Decimal Interchange Code (EBCDIC) encoding and the metadata for the EBCDIC files is stored in a copybook file. These EBCDIC encoded files store data uniquely based on its data type, which is vital Mainframe file system data optimal storage and performance.

However, this presents a challenge when migrating data from Mainframe or Midrange systems to distributed systems. The data, originally stored in a format specific to Mainframe or Midrange systems, is not directly readable upon transfer to distributed systems. As distributed systems only understand code pages like American Standard Code for Information Interchange (ASCII)

To make this data readable on a distributed system, we would need to do an EBCDIC to ASCII code page conversion. This conversion can be achieved in many ways. Few of them are

Microsoft Host Integration Server, Host File client
Logic app IBM host File connector. Our detailed blog about it is here.
Open Source (OSS) Libraries.
Third-party ISV solutions.

Microsoft Host Intergration server

Microsoft Host Integration server (HIS) has a component named Host File Client (HFC). This particular component helps in converting Mainframe EBCDIC files to ASCII using a custom developed C# solution. More details on this solution is provided in HIS documentation page.

Logic App Converter.

If you prefer to choose a cloud native solution, then you can try to use the Host File Connector in Azure Logic Apps. The detailed process has been documented in this blog post.

Fabric (with Open-Source Libraries)

Microsoft Fabric is an enterprise-ready, end-to-end analytics platform. It unifies data movement, data processing, ingestion, transformation, real-time event routing, and report building. It supports these capabilities with integrated services like Data Engineering, Data Factory, Data Science, Real-Time Intelligence, Data Warehouse, and Databases.

There are many open-source solutions which can help in achieving conversion of mainframe data to ASCII. This will help in converting files using Fabric, Databricks, Synapse on Azure.

This blog will focus on the OSS option.

Data Ingestion Architecture

Using OSS on Fabric

There are multiple Open-source libraries that can be utilized for this data conversion. In this article we will dive deeper into one of these solutions - Cobrix

COBRIX is an open-source library built using scala and leverages the multithreaded process powered framework of spark. This helps in converting the file faster than compared to other single threaded processes. As this is multithreaded, it will need a pool of compute resources to achieve the conversion. Cobrix can run on spark environments like Azure Synapse, Databricks and Microsoft Fabric. We will dive deeper into how we can set up Cobrix on Microsoft Fabric.

Download required Cobrix packages
- We will have to first download the required Cobrix packages from the right sources. As Fabric has a particular runtime dependency, please make sure your download the right build for Scala as per the fabric environment that you setup. You will have to download two jars named Cobol-Parser_xx.xx.jar and Spark-cobol_xxx.xx.jar.
Setup the Fabric Environment.
- Login to fabric.microsoft.com.
- Create a Fabric workspace
- Create an Environment in the workspace
- Open the Environment and click on custom Libraries.
- Upload the two jars which were downloaded earlier.Once you have uploaded your custom library setup should look something like this.
- Create a new Lakehouse. Upload the cobol copybook file as well as the Mainframe Datafile in Binary to a particular location in the lakehouse. At the end of this step your lakehouse setup should look something of this kind.
- For both these files, copy the Azure Blob File System Secure (ABFSS) path by right clicking on the files. This link can be used to point to the file from the Spark notebook.
Create a new Fabric pipeline.
- This pipeline will have two components, the first component will be a notebook, which will call the Cobrix framework to convert the file from EBCDIC to ASCII. Second piece of it will be a copy activity to copy the contents of the output file created in the notebook to a SQL DB on Fabric.
- Create a new Notebook . Attach the environment which you had created earlier to this notebook.

In the notebook cell, use can use this piece of code.

//Blob access var CopyBookName = "abfss://file1.cpy" var DataFileName = "abfss://file1.dat" var outputFileName = "abfss://output.txt" //Cobrix Converter Execution val cobolDataframe = spark .read .format("za.co.absa.cobrix.spark.cobol.source") .option("copybook", CopyBookName) .load(DataFileName) //Display DataFrame to view conversion results cobolDataframe.printSchema() cobolDataframe.show()

- Once you have set the configuration properly, you are all set to run the notebook. And this will convert the file from EBCDIC to ASCII and store it to the Lakehouse.
- Add a Copy activity to the pipeline with File as Source and SQL server as destination.
- At this point in time, your pipeline should look something like this

- Once you run this pipeline, the Mainframe EBCDIC file will be converted to ASCII and then loaded into Fabric Native SQL DB table.

Third-party ISV solutions.

- There are many third-party ISV solutions which are available for EBCDIC to ASCII conversions. Please get int touch with us to help you get the right solution for your requirements.

Summary

EBCDIC to ASCII conversion is a critical piece of work during the data migration/modernization journey. Being able to do this with ease and accuracy will drive the success of data migration. With this feature enabled in fabric, this opens up a new set of use cases like Mainframe report generation etc kind of use cases which are predominantly data warehouse driven.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please send an email to Database Platform Engineering Team.