Simplifying Data Export from Legacy ERP to Azure Synapse

Introduction

In today’s data-driven world, businesses often face the challenge of migrating data from legacy systems to modern analytics platforms. One common scenario involves exporting data from an older ERP system to Azure Synapse for advanced analytics.

This blog post addresses how to handle a full database backup file (.bak) from a legacy ERP system when Azure Synapse and Azure SQL Database only accept .bacpac files. The solution involves using a temporary SQL Server instance hosted via Docker to convert the .bak file to a .bacpac file, simplifying the process and reducing costs.

Challenge:

Exporting data from a legacy ERP system to Azure Synapse for analytics can be tricky. The ERP system doesn’t allow direct access to its data platform, so a full database backup file (.bak) is provided. However, Azure Synapse and Azure SQL Database only accept .bacpac
files, not .bak files. This means the .bak file must first be restored to a SQL Server database on an Azure Virtual Machine or an on-premises machine, from which the data can then be exported to Azure Synapse. This adds complexity and cost.

Solution:

To streamline this process, we can use a temporary SQL Server instance hosted via a Docker image. The .bak file will be read from Azure Data Lake Storage (ADLS) Gen2, converted to a .bacpac file, and then imported into Azure SQL Database or Synapse Warehouse. This approach simplifies the process and reduces costs.

Tools and Packages:

1. Docker
2. Azure SQL Database
3. Azure Data Lake Gen2
4. Azure Container App Jobs
5. Azure Container Registry
6. SQL Server Docker image
7. Azure CLI
8. mssql-tools
9. sqlpackage

Implementation Steps:

Step 1: Upload .bak File

Upload the .bak file to Azure Data Lake Storage Gen2.

Step 2: Build the Docker Image

1. Pull the Image:

FROM mcr.microsoft.com/mssql/server:2022-latest


2. Install Azure CLI:

RUN apt-get update && \
apt-get install -y ca-certificates curl apt-transport-https lsb release gnupg && \
mkdir -p /etc/apt/keyrings && \
curl -sLS https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor | tee /etc/apt/keyrings/microsoft.gpg > /dev/null && \ chmod go+r /etc/apt/keyrings/microsoft.gpg && \
AZ_DIST=$(lsb_release -cs) && \
echo "deb [arch=$(dpkg --print-architecture) signed by=/etc/apt/keyrings/microsoft.gpg]
https://packages.microsoft.com/repos/azure-cli/ jammy main" | tee /etc/apt/sources.list.d/azure-cli.list && \
apt-get -y update && \
apt-get install azure-cli


3. Install mssql-tools and sqlpackage:

RUN ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev && \ unzip /sqlpackage-linux-x64-en-162.1.172.1.zip -d /sqlpackage && \ chmod 777 -R /sqlpackage && \
rm /sqlpackage-linux-x64-en-162.1.172.1.zip


4. Set the Entrypoint:

RUN mkdir /backups && \
chown -R mssql:root /backups && \
chmod a+x ./create-bacpacs.sh
ENTRYPOINT /bin/bash ./entrypoint.sh


Step 3: Entrypoint Script

This script starts the SQL Server instance and executes the create-bacpac script
file. /opt/mssql/bin/sqlservr & ./create-bacpacs.sh


Step 4: Create Bacpac Script

5. Download the .bak File from Azure:

sleep 50
az storage blob download -f "./backups/AdventureWorks2022.bak" -c files -n "Test/AdventureWorks2022.bak" --account-name dltest --sas token "<sas_token>"


6. Restore the Database:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $MSSQL_SA_PASSWORD - Q "RESTORE DATABASE testdatabase FROM DISK =
'/backups/AdventureWorks2022.bak' WITH MOVE 'AdventureWorks2022' TO '/var/opt/mssql/data/AdventureWorks2022.mdf', MOVE
'AdventureWorks2022_log' TO
'/var/opt/mssql/data/AdventureWorks2022_log.ldf', REPLACE;"


7. Export to .bacpac:

/sqlpackage/sqlpackage /a:Export /ssn:localhost /sdn:testdatabase /su:sa /sp:$MSSQL_SA_PASSWORD /stsc:True
/tf:/backups/AdventureWorks2022.bacpac


8. Upload .bacpac to Azure:

az storage blob upload --file "/backups/AdventureWorks2022.bacpac" -- container-name files --name "Test/AdventureWorks2022.bacpac" -- account-name dltest --sas-token "<sas_token >"


9. Import to Azure SQL DB:

sqlpackage-win7-x64-en-162.1.172.1/sqlpackage.exe /Action:Import /TargetConnectionString:"Server=testserver.database.windows.net;Initia l Catalog=testdatabase;User ID=admin;Password="<password>"


Conclusion

By following these steps, you can efficiently convert and import your legacy ERP data into Azure Synapse, minimizing complexity and cost.

Contact us or write to info@gain-insights.com to explore a hands-on session or for quick discussion of your requirements.