How To Deploy Fabric SQL and Azure SQL Databases with Azure DevOps

Jeff Taylor
1 hour ago
6 min read

Fabric has CI/CD built in, but if you've tried to use it for database deployments, you've probably already run into its limits. It's still very much in its infancy. The moment you change a column's length, drop a column, or remove a table that already contains data, the deployment fails — Fabric throws up a safety precaution and refuses to proceed. That's a reasonable default, but it stops real schema changes in their tracks.

There could be ways around it — third-party tools like Redgate can automate much of this — but there is no Fabric SQL Database support yet, and there are newer CLI functions, but again, it lack configurable deployment options.

In this post, I want to show a simple, reliable way to deploy databases using SqlPackage and Azure DevOps. It may not be the best fit for every scenario, but it's about the easiest way to get a clean, repeatable deployment while covering all your bases.

The best part: this works identically for Fabric SQL Database and Azure SQL Database. They're the same flavor under the hood, so the only difference between them is the connection string.

There are two pieces to set up: a build pipeline that extracts your schema into a DACPAC, and a release pipeline that deploys that DACPAC through your environments.

The Build Pipeline

This pipeline's only job is to produce a clean DACPAC — a complete, 0-to-100 picture of the database — and publish it as an artifact.

First, pick your agent. I build on a Microsoft-hosted agent, and I usually recommend windows-latest so you don't have to come back later to change it.

Next, get your sources. There are two ways to feed the build. If your schema objects are already checked into source control from the Fabric side, you can build a single package straight from source control. In this example, however, I'm building it from the database instead.

Now, add a PowerShell step and add an inline command to install and update the tooling on the agent. Microsoft is constantly pushing changes to Fabric, so you want the latest SqlPackage on every run.

Azure DevOps Pipeline Install SQLPackage

Here is the command:

dotnet tool update -g microsoft.sqlpackage

Next, extract the schema. SqlPackage is a PowerShell add-on, so you can run this exact command locally too if you have PowerShell installed. The action is Extract, the source is your connection string, and the target file goes into the artifact staging directory.

Here is the command:

SqlPackage /Action:Extract `
  /SourceConnectionString:"$(ConnectionString)" `
  /TargetFile:"$(Build.ArtifactStagingDirectory)/AzureSqlDb.dacpac"

Notice the source is a variable, not a literal. You always want your connection string in a secure pipeline variable so usernames, passwords, and connection strings aren't sitting in plain text. You can break those out into separate variables if you like — I just use one connection-string variable to keep it simple.

Now publish the DACPAC into the staging directory so the release pipeline can pick it up. That's the build done.

The Release Pipeline

Over in Releases, I have a release with two environments: QA and Production.

First, grab the artifact we just built — always set to latest from the build pipeline.

Next, each stage runs the same two tasks.

The first makes sure SqlPackage is updated (same reason as the build — keep the tooling current).

Here is the command

dotnet tool update -g microsoft.sqlpackage

The second is the publish, and that's where this approach earns its keep.

Here is the command:

SQLPackage /Action:Publish /SourceFile:"$(System.ArtifactsDirectory)\_Azure SQL DB Pipeline\drop\AzureSQLDB.dacpac"  /TargetConnectionString:"$(ConnectionString)" 
/p:VerifyDeployment=True 
/p:ScriptDatabaseOptions=False 
/p:IgnoreComments=True 
/p:IgnoreWhitespace=True 
/p:IgnorePermissions=True 
/p:DropObjectsNotInSource=True  
/p:AllowTableRecreation=True 
/p:DisableAndReenableDdlTriggers=True 
/p:DoNotAlterReplicatedObjects=False /p:DoNotAlterChangeDataCaptureObjects=False /p:CompareUsingTargetCollation=True 
/p:BlockOnPossibleDataLoss=False 
/p:DoNotDropObjectTypes="Users"

The publish uses the Publish action with the DACPAC as its source. The target is the environment's connection string — and here's the clean trick: QA and Production are two different servers, so give the variable the same name in both stages but scope a different value to each. The pipeline picks the right one based on the environment, so you never have to think about it.

The Switches That Make It Work

This is the heart of it, and it's exactly the control Fabric's built-in CI/CD won't give you. The publish forces your destination to match the package exactly — it syncs and overwrites whatever's there, so the target ends up identical to the DACPAC.

Here's the publish command with the switches I use:

SQLPackage /Action:Publish /SourceFile:"$(System.ArtifactsDirectory)\_Azure SQL DB Pipeline\drop\AzureSQLDB.dacpac"  /TargetConnectionString:"$(ConnectionString)" 
/p:VerifyDeployment=True 
/p:ScriptDatabaseOptions=False 
/p:IgnoreComments=True 
/p:IgnoreWhitespace=True 
/p:IgnorePermissions=True 
/p:DropObjectsNotInSource=True  
/p:AllowTableRecreation=True 
/p:DisableAndReenableDdlTriggers=True 
/p:DoNotAlterReplicatedObjects=False /p:DoNotAlterChangeDataCaptureObjects=False /p:CompareUsingTargetCollation=True 
/p:BlockOnPossibleDataLoss=False 
/p:DoNotDropObjectTypes="Users"

A few of these are worth calling out:

IgnorePermissions=true — we don't store permissions in source control, and we don't want a deploy changing them. This lets you control permissions independently in each environment without them getting clobbered on every release. You never want to do that across environments unless you have the same groups in both.

DropObjectsNotInSource=true — if an object exists in the destination but is no longer in the source, it gets removed. That's how you keep a clean picture and avoid drift, and Fabric won't do that for you.

DisableAndReenableDdlTriggers=true — disables DDL triggers at the start and re-enables them at the end, so a table rebuild or other data manipulation doesn't fire triggers constantly and slow the deploy down.

CompareUsingTargetCollation=true — uses the destination environment's collation so the comparison is correct for that environment.

BlockOnPossibleDataLoss=false — if the plan calls for dropping a table, drop it. In a data-warehouse context, that's usually fine because you'll repopulate or rebuild the table anyway. This flag isn't even available in Fabric's CI/CD, and it's one of the main reasons you need this approach.

Once it runs, the database is in sync — it looks exactly like the previous environment. Production runs the identical two tasks; the only difference is the connection-string variable pointing at the Production server.

Want to see what's been deployed? Head to Releases and you'll see every run. Here you can see several releases that have been deployed successfully to QA and Production.

Fabric vs Azure SQL — Just the Connection String

I mentioned before, this process is exactly the same for a Fabric SQL Database as for an Azure SQL Database. Swap the connection-string variable and everything else — the extract, the artifact, the publish switches — works unchanged.

One thing to be clear about: this is out of band from the rest of your Fabric deployments. If you're publishing lakehouses, reports, or semantic models, they follow their own path. The database side lives here in Azure DevOps. The payoff is that you're never manually editing scripts, deploying scripts by hand, or modifying objects on the fly.

Preserving Data on Destructive Changes

A good question always comes up here: what if a change is destructive and you need to preserve the existing data — combining columns, changing a data type, or populating a new table from existing rows — while keeping production data intact?

If you need to preserve data, don't allow data loss, and handle the data movement yourself with pre- and post-deploy scripts checked into source control for the release. The pipeline runs the pre-scripts before the deploy and the post-scripts after. For anything genuinely complex, build a small solution around those: save the affected data out first (a pre-script, or even kick off a notebook beforehand to do the prep), let the deploy run, then move the data back in a post-script. OLTP database deployments do this all the time — there's almost always a pre and a post.

If you'd rather not hand-roll it, Redgate gives you this more out of the box. But fundamentally, these are just steps in a pipeline. The key is structuring your deployments so the right scripts are checked in to run at the right moment.

How Fast Is It, and Is It Safe?

Fast. A straightforward QA deployment ran in about 3 minutes start to finish, and the longest single step was updating the agent. It scales with the size of your database and the amount of data you're manipulating, but the schema sync itself is quick — one of the bigger jobs I've run finished in roughly 10 minutes.

Safe. The changes are wrapped in a transaction, so if something goes wrong, it rolls back, and you're not left in a bad state. The one caveat: if you're using OLTP in-memory objects, those can't be wrapped in a transaction, so you'll need to handle them in a separate step in your deployment.

Conclusion

Fabric's CICD will get there eventually — it's still very new, and I'm hoping it will be there soon. But until it can handle real world schema changes on its own, this is the approach I use and recommend If you want to check out all of the options available for SQLPackage go to Microsoft's full SqlPackage CLI documentation.

I hope this step-by-step guide is helpful. Let me know in the comments below if this worked for you.