Azure Synapse Serverless SQL pool – How to extract the database model for backup, migration, or analysis

In this blog post, I will demonstrate how to extract the database model of a Serverless SQL Pool from Azure Synapse Analytics to analyze it or extract information from it.

Extracting a database model from Azure Synapse Analytics Serverless SQL Pool

In a best practice environment, you have attached your Azure Synapse Analytics workspace to Azure DevOps or GitHub. Additionally, every database role, schema, external table, view – or any other script you used to create your Azure Lakehouse, should be backed up in your repository.

But what if you created some scripts on the fly, did a hot-fix, or didn’t have that information for any other reason and, e.g., need to find out which tables on your data lake are feeding the views your end-users are using (hint: data lineage)?

Out-of-the-box, there is no way to open, for example, SSMS to export a DACPAC of your Serverless SQL Pool or use the Azure Synapse Analytics GUI to extract the database model or single objects.

Prerequisites

The following solution uses different tools that you can make sure to install upfront.

• PowerShell 7.4.1 (I recommend installing it via the Microsoft Store, to keep it up-to-date)
• sqlpackage.exe (Can be downloaded here, or if you have SSMS installed, check your Program Files)
• Power BI (Also recommended to install it via the Microsoft Store, so you don’t have to update it manually)

Serverless SQL Pool View

Let’s say you have one or multiple views that can be used to query data from your Azure Data Lake Gen2. On the left side, you can see a script that covers a select statement that can be run on your Serverless SQL pool. As you can also see, it is the only script that resides within the workspace (and, therefore, your repository.
If the question arises: Where does the data come from? You could answer from the datalake – and if you’re lucky this will be enough. From my experience, it won’t be enough, so let’s figure out how to get an answer.

sqlpackage.exe – environment variable

In order to be able to use sqlpackage.exe with PowerShell, you need to add it as a Path variable to your system environment variables. Otherwise, the PowerShell code provided will not work.

Extract .dacpac via PowerShell from Serverless SQL Pool

Adjust the /TargetFile parameter, if you don’t want the current date as a prefix, just remove it from the code. Authentication uses Active Directory Interactive (so a window will pop up where you have to log in). And ultimately, add your data source, the Serverless Endpoint as well the database/catalog you want to extract.

Find Serverless SQL pool and endpoint information

To proceed, you also need some information from your Serverless SQL pool in Azure Synapse Analytics.

1. You can find your database/catalog below the SQL databases
2. Views, in this example, you can see the different Views I am going to extract from the model
3. Any query in the workspace you are running requires a database/catalog – you could also remove the information here

Further, you will need your Serverless SQL endpoint, which you can find in the Azure portal if you select your Azure Synapse Analytics workspace and jump to Overview.

Run sqlpackage.exe in PowerShell and create DACPAC as an output

After adjusting and running the PowerShell code, a DACPAC file is created in the specified location.

Unzip the DACPAC file to get database model

You need to follow these steps to dive deeper and extract the database model.

• Adjust the .dacpac file and replace .dacpac with .zip
• Unzip the .zip file to a location of your choice
• Check the output, and there should be a model.xml file available now

Analyze a database model with Power BI

Now, the only thing left to do is analyze the database model with e.g. Power BI and extract the necessary information. Open your Power BI desktop and get data from the model.xml that you extracted from the DACPAC.
In this case, I extracted the information on which views exist in the database and their definition. If you want to replicate these steps, copy the Power Query (M) code below and adjust the path to your model.

Demystifying Serverless SQL pool database models

Synapse Analytics Serverless Endpoints open the way to your Serverless SQL pools, giving your end users access to your Azure Lakehouse. Even if the scripts are not available (anymore), you can still extract all necessary information from an existing database to cover your data lineage needs.

Thanks for reading, and keep an eye out for my upcoming blog posts!