Manish_Kumar_Pandey presents a practical approach to migrating from SAP ASE (Sybase ASE) to Azure SQL Database, featuring a parallel BCP script that streamlines large-scale data transfers and optimizes performance.

Accelerating SAP Sybase ASE to Azure SQL Migration Using SSMA and Parallel BCP

Introduction

Enterprises upgrading legacy databases face hurdles when moving complex schemas and transferring high data volumes. Migrating from SAP ASE (Sybase ASE) to Azure SQL Database is a frequent modernization path, allowing organizations to adopt advanced features, lower maintenance, and integrate with broader Microsoft services. As businesses expand, legacy constraints such as performance bottlenecks and limited cloud integration prompt the need for modern cloud solutions.

Migration Tools and Their Roles

  • SQL Server Migration Assistant (SSMA): Automates schema and data migration from SAP ASE to SQL Server, Azure SQL Database, or Azure SQL Managed Instance. Handles end-to-end migration but may encounter limitations with massive datasets under tight downtimes.
  • ASEtoSQLdataloadusingbcp.sh Script: A custom shell script written to accelerate large data migrations with parallel BCP. The script leverages configuration-driven chunking and parallelism to minimize the overall transfer window.

Script Workflow at a Glance

  1. Config Setup: Reads connection strings and migration options from bcp_config.env.
  2. Table Identification: Acquires table lists from the source DB or table_list.txt.
  3. Chunking: For tables supporting partitioning (with indexes or unique keys), the script splits tables into multiple chunks/views.
  4. Parallel Processing: Each view is transferred concurrently via BCP, massively reducing elapsed time vs. traditional serial loads.
  5. Fallback: For tables not eligible for chunking, the script falls back to standard full-table BCP transfer.
  6. Logging: Every migration run is fully logged for traceability, aiding monitoring and troubleshooting.

Pre-Migration Prerequisites

  • Schema conversion and deployment must be performed via SSMA.
  • Both the source (SAP ASE on Unix/Linux) and the target (Azure SQL DB, SQL Server on Windows/Linux/Azure) must be network-accessible from the migration host.
  • Configurations for BCP, chunking strategy, and table lists are provided in external files for clarity and reusability.

Configuration Files

  • bcp_config.env: Specifies all connection and operational parameters.
  • chunking_config.txt: Maps tables to primary keys and defines chunk counts for parallelization.
  • table_list.txt: Optionally restricts which tables are migrated.

Performance Analysis

  • On a 32-core migration host transferring a 10GB (2.6 million row) table:
    • SSMA: ~3 minutes
    • BCP script (10 parallel chunks): 1 minute 7 seconds
  • Parallel and chunk-based BCP approaches can more than halve migration durations under optimal conditions.

Disclaimer: Actual results depend on hardware, database configurations, and network parameters. Always validate performance in a test environment to establish realistic baselines.

Operational Recommendations

  • Use larger batch sizes (10K–50K) to maximize throughput when sufficient disk IOPS and RAM are available.
  • Increase parallel chunk counts only as far as the hardware allows; excessive parallelism can hinder performance under high CPU loads.
  • Monitor CPU and IOPS: Scale up batches/chunks when system is underutilized, and scale down to mitigate resource contention.

Future Enhancements

  • Smart Chunking: Enable chunk-based migration even for tables lacking a unique clustered index by using any unique key.
  • Two-Tier Parallelism: Parallelize both across different tables and within each table, compounding throughput gains.
  • Enhanced LOB Support: Add robust handling of TEXT, IMAGE, and BINARY SQL columns.

Getting the Script and Contributing

Last updated: Aug 04, 2025 – Version 1.0

Authored by: Manish_Kumar_Pandey

Feedback

For improvements or suggestions, please email the Data SQL Ninja Team at datasqlninja@microsoft.com.

References

This post appeared first on “Microsoft Tech Community”. Read the entire article here