Overview

This project involved migrating on-prem SQL Server to Azure SQL Server. There was a local docstore database where file data (PDFs, images, audio etc.) were held in memo fields. The file data was extracted and moved to blob storage. The metadata was then migrated to Azure SQL Server. The GP data was migrated using the Azure Sync Tool. The custom databases were migrated using bacpac files.

It demonstrates:

Data Lake creation and management
SQL Migrations
Azure Entra ID integration
Azure SQL Server usage
Azure Blob Storage usage

Architecture

Azure Blob Storage holds the file data
Azure SQL Server holds the metadata
Azure Sync Tool is used to migrate the GP data
Azure Entra ID is used for authentication
Azure Data Factory is used for data movement
Azure Synapse Analytics is used for data lake management

Status

This project completed on time and with very little headaches.

Challenges

Azure Sync Tool had compatibility issues with GP custom tables and stored procedures
Blob storage migration required custom scripts due to MEMO field size limitations
Azure SQL Server firewall rules and Entra ID authentication caused initial connection failures

Lessons Learned

Always test Azure Sync Tool compatibility with custom GP tables before full migration
Extract MEMO field data to blob storage before database migration to avoid size limitations
Configure Azure SQL Server firewall rules and Entra ID authentication early in the process

Related Services

Azure SQL Database migration and optimization
Azure Blob Storage integration and management
Azure Data Factory pipeline development
Azure Synapse Analytics data lake solutions
Azure Entra ID authentication and security

On-Prem SQL to Azure SQL Migration