Zero downtime migration from SQL Server to Azure SQL Database

For many enterprises, moving SQL Server workloads to Azure SQL Database is a critical step in their journey to cloud modernization. Yet, migrating mission-critical databases without disrupting operations can be challenging. Downtime, data drift, and performance validation often introduce unnecessary risk and complexity.

With Striim and Microsoft Azure, those challenges become manageable. Striim’s log-based Change Data Capture (CDC) continuously streams every SQL Server transaction into Azure SQL Database in real time to deliver zero-data-loss migration, continuous validation, and minimal impact on live applications.

As part of the Microsoft Unlimited Database Migration Program, Striim is optimized to help organizations accelerate and de-risk their journey to Azure by combining enterprise-grade CDC technology, architectural best practices, and hands-on partner expertise.

This tutorial walks through the key setup steps and configurations for migrating from SQL Server (on-premises or in another cloud) to all SKUs of Azure SQL Database including Hyperscale using Striim.

Why Use Striim for Continuous Migration

In our previous blogpost about Microsoft's unlimited data migration program, we talked about how unlimited partnership between Microsoft and Striim enables continuous data replication from existing databases into Azure in real time, enabling online migrations with zero downtime. Through this Microsoft Unlimited Database Migration Program, customers gain unlimited Striim licenses to migrate as many databases as they need at no additional cost.

Program highlights include:

• Online migration capabilities from SQL Server to Azure SQL DB, Zero-downtime, zero-data-loss migrations across SQL Server, Oracle, MySQL, PostgreSQL, Azure SQL and more.
• Support for heterogeneous, mission-critical workloads across relational and open-source systems.
• Real-time, AI-ready data pipelines, preparing workloads for analytics and ML once migrated.

With Striim, data moves continuously via log-based CDC, reading directly from SQL Server transaction logs and replicating every insert, update, and delete to Azure in real time. This minimizes impact on production systems while maintaining full data consistency during migration.

Architecture Overview

A typical SQL Server → Azure SQL Database setup includes three components:

• Source: an existing SQL Server instance (on-premises or hosted in another environment).
• Processing Layer: Striim, deployed in Azure for low-latency, secure data movement.
• Target: Azure SQL Database (Hyperscale, Business Critical and General Purpose SKUs).
  
  

Data flows securely from SQL Server → Striim → Azure SQL Database over port 1433. For production environments, Azure Private Link or private endpoints are recommended to avoid public internet exposure.

Preparing the SQL Server Source

Before replication can begin, ensure Change Data Capture (CDC) is enabled for your database and tables. Run the following commands as a user with sysadmin privileges:

USE master;
EXEC sys.sp_cdc_enable_db;
GO

USE <your_database>;
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'<table_name>',
  @role_name     = NULL;
GO

-- Then, create a user account for Striim with access to the transaction logs:

CREATE LOGIN striim_user WITH PASSWORD = 'strongpassword';
CREATE USER striim_user FOR LOGIN striim_user;
GRANT SELECT, VIEW DATABASE STATE, VIEW SERVER STATE TO striim_user;

Within Striim, configure the MS SQL Reader or MSJet Reader component:

• MS SQL Reader – uses Microsoft’s native CDC implementation (shadow tables). Easiest to deploy and supported out of the box.
• MSJet Reader – Striim’s proprietary high-performance log reader. Reads directly from transaction log files on disk for minimal overhead.

This log-based approach ensures a low single-digit CPU overhead on the source system while continuously streaming every change event.

Building the Migration Pipeline

A complete migration typically includes three coordinated stages:

1. Schema Migration – create equivalent tables in Azure SQL Database.
2. Initial Load – bulk-load historical data from SQL Server to Azure.
3. Change Data Capture (CDC) – continuously stream real-time transactions until the systems are fully synchronized.

Schema Migration

Before starting the data pipeline, ensure your target Azure SQL Database has the required schema.

To migrate the schema from SQL Server to Azure SQL DB you can use either Striim or Azure Database Migrate Service (DMS).

Striim provides two schema creation methods:

1. Schema Conversion Utility (CLI): automatically generates DDL statements from the source.
2. Wizard-based creation: directly builds tables and schemas in the Striim UI.

Azure Database Migration Service provides two options for schema migration:

1. In the DMS portal, while you select the database tables to migrate, select the checkbox against Migrate missing schema option. This will automatically migrate the schema of the table and the dependent objects like indexes, triggers, views etc.
2. You can use Azure Database Migrate Service PowerShell and Azcmdlets to migrate schema.

Please note, with both these options you need db_owner permissions on the target database to perform schema migration.

Next, create a SQL user with write access to the target database:

CREATE LOGIN striim_user WITH PASSWORD = 'strongpassword';
CREATE USER striim_user FOR LOGIN striim_user;
ALTER ROLE db_datareader ADD MEMBER striim_user;
ALTER ROLE db_datawriter ADD MEMBER striim_user;

Ensure Striim can connect to Azure SQL Database over port 1433. For security, use private endpoints and configure appropriate firewall and VNet rules.

Initial Load & Continuous stream

During the initial load, Striim uses a Database Reader to extract full tables and a Database Writer to load them into Azure SQL Database. Once complete, switch to the CDC pipeline, which applies new transactions in real time until cutover. To ensure zero data loss, follow the sequencing guidelines in Striim’s switch-over documentation.

Cutover

When ready to cut over:

1. Pause writes to SQL Server.
2. Validate record counts between source and target.
3. Redirect application traffic to Azure SQL Database.

Building migration pipelines in Striim

Striim provides several ways to build migration pipelines:

• Wizards: pre-built templates for SQL Server → Azure SQL Database.
• Visual Designer: a drag-and-drop interface for customized flows.
• TQL Scripts: programmatic creation of applications for CI/CD pipelines.
  
  

Each pipeline is defined by a TQL file, which can be version-controlled and deployed through REST APIs for repeatable infrastructure-as-code workflows.

Adding Transformations and Smart Data Pipelines

Beyond one-to-one replication, Striim allows you to enrich, transform, and validate data in flight using continuous SQL queries or custom Java processors. For example, you can append metadata for auditing:

SELECT *, CURRENT_TIMESTAMP() AS event_time, OpType() AS operation FROM SQLServerStream;

These Smart Data Pipelines enable in-stream transformations (e.g., deduplication, validation, or enrichment) without separate ETL jobs, streamlining modernization into a single continuous flow.

Performance Expectations

In joint Striim–Microsoft testing:

• 1 TB historical load typically completed in 4–6 hours.
• CDC replication latency averages sub-second for inserts, updates, and deletes.
  
  

Performance depends on schema complexity, hardware, and network configuration. For optimal results:

• Deploy Striim in the same Azure region as the target.
• Use private networking.
• Allocate adequate CPU and memory to handle peak CDC throughput.

Support and Enablement

The Microsoft Unlimited Database Migration Program is designed specifically to provide customers direct access to Striim’s field expertise throughout the migration process.

From end-to-end, you can expect:

• Onboarding and ongoing support, including installation kits and walkthroughs.
• Higher-tier service packages are available as well.
• Direct escalation paths to Striim for issue resolution and continuous assistance during migration and replication.
• Professional services and funding flexibility, such as ECIF coverage for partner engagements, cutover or weekend go-live standby, and pre-approved service blocks to simplify SOW approvals.
  
  

Together, these resources ensure migrations from SQL Server On-Premise to Azure SQL DB are fully supported from initial enablement through post-cutover operations, backed by Microsoft and Striim’s combined field teams.

Whether your goal is one-time migration or continuous hybrid replication, Striim’s CDC engine, combined with Azure’s SQL Database, ensures every transaction lands with integrity. Start your modernization journey today by connecting with your Microsoft representative.