For this months T-SQL Tuesday contribution I cover managing Database/SQL Pool/Data Warehouse code.
To clarify, this post focuses on managing code for SQL Server databases, Azure Synapse SQL Pools and Microsoft Fabric Data Warehouses. Since they all use T-SQL and this is a T-SQL Tuesday post.
Before I go any further, I want to thank Mala Mahadevan (l/x) who hosts this months T-SQL Tuesday. Mala invites us all to discuss how we manage our database code. Plus, discuss CI/CD pipelines.
You can read the original invitation by clicking on this link for the T-SQL Tuesday 177 invitation. Alternatively, you can click on the image below.
Mala initially wanted to call her invitation post “How do you manage your database repo?”. Which I would have been fine with. Since I know that a repo is short for repository.
However, I appreciate that not everybody does. Which is why to help with any jargon I recommend that you read one my other posts. Which is a Microsoft Fabric Git integration jargon guide for Fabricators.
By the end of this post I answer some of the questions in the invite. Along the way I share plenty of links.
Managing Database/SQL Pool/ Data Warehouse code
Anyway, I mostly store code for SQL Server, SQL Pools and Microsoft Fabric Data Warehouses in Git repositories in either Azure DevOps or GitHub. Due to my experience with both of these offerings.
I previously worked for one of the largest consumers of Azure DevOps in the world. Plus, I once worked for a GitHub partner.
You can see examples of my preference to work with Azure DevOps and GitHub yourself. In the various GitHub repositories that I have made available for others to clone (copy) or download. Which can be used as templates to perform CI/CD for various services.
Like the GitHub-FabricDWDBProject repository that I recently shared to accompany my post about CI/CD for Microsoft Fabric Data Warehouses using GitHub Actions.
How the repository is structured depends on the requirements at the time. I often work with database projects to perform state-based migrations.
You can deploy to a large number of databases with state-based migrations. I know because I was part of a team who achieved this for an estate containing a four-figure number of SQL Server instances.
You can create database projects in many different ways. Some of which I covered recently in a post where I shared three ways to create a Microsoft Fabric Data Warehouse database project. All three methods also apply to SQL Server and Azure Synapse Analytics SQL Pools.
Sometimes I work with migration-based deployments as well. Which means that the repository structure is different. Like in the below example based on working with the DBUp .NET library to update an Azure SQL Database.
CI/CD pipelines
Since Mala also invited us to discuss CI/CD pipelines as well I will point out that I typically work with pipelines in either Azure Pipelines or GitHub Actions.
YAML pipelines are my preference when working with Azure Pipelines. If you are curious why, feel free to read my thoughts about disabling classic pipelines in Azure DevOps.
Anyway, how I perform CI/CD with pipelines can vary. It often depends on whether a state-based or migration-based deployment is required.
However, both share a common theme of starting after some form of event has taken place. Which is specified in the pipeline as a trigger. For example, when there is an update to a certain branch.
State-based deployments
State-based deployments tend to follow a typical pattern. Updates are first saved into an existing or new database project. Afterwards, a deployment pipeline runs which creates a dacpac file and deploys the updates to one or more databases/SQL Pools/Data Warehouses.
Like in the below diagram based on a Microsoft Fabric Data Warehouse.
You can replace the Microsoft Fabric Data Warehouses shown in the above diagram with either Azure Synapse SQL Pools or SQL Server Databases.
Another point I must mention is that this is a simplified diagram. I tend to work with additional steps as well. For example, starting the pipeline when a pull request into another branch has been completed.
Migration-based deployments
How I work with migration-based deployments can vary. Due to the fact that many factors are involved.
For example, in the past I have worked with the DBUp .NET library. Which is great when looking to perform migration-based deployments on a budget. Because it can be complimented with the dbops PowerShell module when working with deployment pipelines.
Other factors such as service limitations can be a factor as well. For example, Azure Synapse serverless SQL Pools cannot automatically create logging tables to track which scripts have run.
Therefore, when using the DBUp and dbops PowerShell module together I must add additional ‘-SchemaVersionTable $null’ syntax to my code. To make sure it does not try and create a table.
I demonstrate this in the “Using CI/CD for Serverless SQL Pools in Azure Synapse Analytics” video that I did as part of the Azure Synapse Analytics and MVP series. Please be aware though that you can now deploy a dacpac to a serverless SQL pool.
Of course, there are other tools available to perform migration-based deployments. Which come with premium features and tend to come with a cost.
At the end of the day, which tooling you work with depends on many factors. Including requirements, budget and even allowed extensions in Azure DevOps. Because some large enterprises have policies relating to which extensions can be installed in Azure DevOps.
Whichever tooling is used, the concept is always the same. Something triggers the pipeline to deploy scripts in an incremental order.
Final words about managing Database/SQL Pool/Data Warehouse code
I hope my T-SQL Tuesday contribution this month about managing Database/SQL Pool/Data Warehouse code makes for an interesting read. Plus, I hope that some of you find my links helpful.
Personally, I am glad I get to do a T-SQL Tuesday integration that that applies to SQL Server, Azure Synapse Analytics and Microsoft Fabric. Especially when the topic of CI/CD is so important.
Of course, if you have any comments or queries relating to this post feel free to reach out to me.
Be First to Comment