If you have done any amount of DACPAC-based deployment, you have almost certainly hit this wall: you need to make a change that the automatic schema comparison cannot safely do on its own - adding a new non-nullable column to a table that already has data, or migrating data out of a column/table that is about to be dropped. The natural instinct is "I'll put that in a pre-deployment script." And then it fails, and you lose an afternoon figuring out why.

The reason is subtle but important, and it was the subject of a long-standing DacFx feature request (microsoft/DacFx#482): pre-deployment scripts run after the schema comparison, not before it. The good news is that preplan script support has now shipped. This post explains the problem it solves, how to use it, and how I use preplan scripts for deliberate destructive actions too - including sample pipeline steps for GitHub Actions and Azure DevOps.

This applies to both of the modern SDK-style SQL project options:

• MSBuild.Sdk.SqlProj 4.4.0
• Microsoft.Build.Sql 2.3.0

Both build a .dacpac and both deploy with sqlpackage, so everything below applies regardless of which one you use.

The deployment pipeline inside sqlpackage

When sqlpackage publishes a dacpac, it runs a fixed sequence of steps:

1. Compare the source dacpac against the target database and generate the deployment script.
2. Run the pre-deployment script.
3. Run the generated deployment script.
4. Run the post-deployment script.

Notice the ordering: the comparison happens first, and only then does the pre-deployment script run. That single detail is the root of a confusion that has existed for at least 15 years.

Why "just use a pre-deployment script" doesn't work

Say you want to add a new NOT NULL column to an existing table that already has rows. You want to:

1. Add the column as nullable (or with a temporary default).
2. Backfill the data.
3. Make it NOT NULL.

So you write a pre-deployment script to backfill the data... but it runs after the comparison has already decided to add the column as NOT NULL, and after the generated deployment script tries (and fails) to apply it. The script that was supposed to prepare the data never gets the chance, because the deployment blew up first.

The same applies to data migrations before a destructive change - moving data out of a column or table that is being dropped. By the time your pre-deployment script runs, the comparison has already planned the drop.

Every existing workaround involves manually writing a change script that runs outside the normal publish process - managing it separately in Visual Studio, running it as a separate pipeline step, or splitting the change across multiple check-ins and deploying in stages. They all technically work, but they are convoluted and easy to get wrong.

The fix: a preplan script

The feature requested in DacFx #482 has now shipped (DacFx/SqlPackage 170.5.96): a preplan script that runs before the schema comparison. The publish workflow is now:

1. Run preplan script ← the new step
2. Compare and generate deployment script
3. Run pre-deployment script
4. Run deployment script
5. Run post-deployment script

With a preplan step, you prepare the target database before the diff is calculated - back-fill data, stage a migration, or otherwise shape the schema/data so the subsequent comparison produces a safe, correct deployment script. Best of all, it lives inside the project and is handled automatically as part of a normal publish, instead of being a bolt-on script you have to remember to run.

Adding a preplan script to your project

Add a preplan script to the project so sqlpackage runs it automatically before the comparison. Include the file in your project with the PrePlan item:

<ItemGroup>
  <PrePlan Include="pre-plan.sql" />
</ItemGroup>

Keep the preplan script idempotent (guard every change with existence checks) so re-runs and retries are safe.

-- pre-plan.sql : backfill before the comparison adds a NOT NULL column
IF COL_LENGTH('dbo.Customer', 'Region') IS NULL
BEGIN
    ALTER TABLE dbo.Customer ADD Region nvarchar(50) NULL;
END
GO

UPDATE dbo.Customer
SET Region = 'Unknown'
WHERE Region IS NULL;
GO

Preplan for deliberate destructive actions

The preplan step is also where I like to handle intentional destructive changes - dropping a column or a table. By default BlockOnPossibleDataLoss=true will (correctly) stop a publish that would drop a populated column. Rather than blanket-disabling that guard, I use a preplan script to deliberately and visibly perform the drop (or migrate the data out first), so that:

• The destructive action is an explicit, reviewed line of SQL in source control - not a silent side effect of a schema diff.
• By the time the comparison runs, the object is already gone, so the generated deployment script has nothing dangerous left to do and the data-loss guard stays on for everything else.

-- pre-plan.sql : deliberately drop a column that is being retired
-- (optionally archive the data first)
IF COL_LENGTH('dbo.Customer', 'LegacyNotes') IS NOT NULL
BEGIN
    INSERT INTO archive.CustomerLegacyNotes (CustomerId, LegacyNotes)
    SELECT Id, LegacyNotes FROM dbo.Customer WHERE LegacyNotes IS NOT NULL;

    ALTER TABLE dbo.Customer DROP COLUMN LegacyNotes;
END
GO

This gives destructive changes the same explicit visibility that the old refactorlog never really provided - the drop is right there in a reviewed script instead of hidden in a diff.

⚠️ Beware: you need the latest sqlpackage

One gotcha that bites people regardless of this feature: the sqlpackage version matters a lot.

• Newer SqlServerVersion targets (e.g. Sql160, Sql170) and newer DacFx behaviours require a recent sqlpackage.
• An old globally-installed sqlpackage on a build agent will throw confusing errors or silently produce wrong results.
• The native preplan support only exists in recent sqlpackage (170.5.96 or later), so using the latest is essential if you want to use it.

So always install/update the latest sqlpackage in your pipeline and locally rather than relying on whatever is pre-installed on the agent:

dotnet tool install -g microsoft.sqlpackage

Sample pipeline: GitHub Actions

With the preplan script included in the project, publishing is a single sqlpackage step - the preplan runs automatically before the comparison.

name: database

on:
  push:
    branches: [ main ]

jobs:
  deploy:
    runs-on: ubuntu-latest
    environment: production   # add a manual approval gate here
    steps:
      - uses: actions/checkout@v4

      - name: Setup .NET
        uses: actions/setup-dotnet@v4
        with:
          dotnet-version: 8.0.x

      # Always get the latest sqlpackage!
      - name: Install sqlpackage
        run: dotnet tool install -g microsoft.sqlpackage

      - name: Build dacpac
        run: dotnet build ./src/MyDatabase/MyDatabase.sqlproj -c Release

      # Publish - the preplan script runs automatically before the comparison
      - name: Publish
        run: |
          sqlpackage /Action:Publish \
            /SourceFile:"./src/MyDatabase/bin/Release/MyDatabase.dacpac" \
            /TargetConnectionString:"$" \
            /p:BlockOnPossibleDataLoss=true

A couple of notes:

• The preplan script travels inside the dacpac/project, so there is no separate step to forget.
• BlockOnPossibleDataLoss=true (the default for publish) still guards you against unexpected destructive operations - your deliberate drops already happened in the preplan.
• The GitHub Environment gives you an approval gate before the deployment runs.

Sample pipeline: Azure DevOps

The same flow translates cleanly to Azure DevOps YAML.

trigger:
  branches:
    include:
      - main

stages:
  - stage: Deploy
    jobs:
      - deployment: DeployDatabase
        environment: production   # add approvals/checks on this environment
        pool:
          vmImage: ubuntu-latest
        strategy:
          runOnce:
            deploy:
              steps:
                - task: UseDotNet@2
                  inputs:
                    packageType: sdk
                    version: 8.0.x

                # Always get the latest sqlpackage!
                - script: dotnet tool install -g microsoft.sqlpackage
                  displayName: Install sqlpackage

                - script: dotnet build ./src/MyDatabase/MyDatabase.sqlproj -c Release
                  displayName: Build dacpac

                # Publish - the preplan script runs automatically before the comparison
                - script: |
                    sqlpackage /Action:Publish \
                      /SourceFile:"$(Pipeline.Workspace)/MyDatabase.dacpac" \
                      /TargetConnectionString:"$(SqlConnectionString)" \
                      /p:BlockOnPossibleDataLoss=true
                  displayName: Publish

Azure DevOps Environments support approvals and checks, which is the natural place to require a sign-off before the deployment runs.

Tip: There is also the built-in SqlAzureDacpacDeployment@1 task for the publish itself, but calling sqlpackage directly gives you full control over the version, which matters when you are depending on newer DacFx behaviour. Work is in progress to modernize/replace this task with a more flexible and modern approach.

One script, in the right place

It took at least 15 years and a long-standing feature request, but the gap is closed: there is now one place in the publish pipeline where you can prepare the target database before the comparison locks in its plan. No more splitting changes across check-ins, no more separate scripts to remember to run, no more fighting a diff that already decided what it's going to do.

Drop pre-plan.sql into the project, keep it idempotent, and let it carry both the data backfills and the deliberate drops - as ordinary, reviewed SQL sitting right next to the rest of the schema. Just make sure the build agent is running sqlpackage 170.5.96 or later, or none of this is available yet.

Happy (safer) deploying!

Free Database Performance Monitoring – The Web Viewer and Custom Views

Chapters

• 00:00:00 – Introduction
• 00:01:31 – Web Viewer Overview
• 00:04:20 – Security and Access Control
• 00:06:37 – Web Viewer Features
• 00:09:00 – Custom Views and Dashboards

Full Transcript

Erik Darling here with Darling Data, and in today’s video, we, by we I mean me and Batsmaru here, are going to talk about my free database. And I have to say database now, it’s not just SQL Server, since it gained the capability to monitor Postgres and Aurora Postgres as well. The thing is, titling this stuff is like, Free SQL Server and Postgres is it Aurora Postgres, it doesn’t roll off the tongue. So, free database performance monitoring. I can’t see a world where I start bridging into, like, Postgres or, I mean, it’s a bridge into Postgres, MySQL or Oracle, or, you know, some other obtuse, obscure database. But, you know, I think SQL Server and Postgres are probably good enough for me for now. Don’t ask about MongoDB monitoring. I, I, even I have morals and standards. Don’t, don’t give me that. But, uh, I’m going to do this a little bit backwards. Uh, and because, uh, doing a video of an install, uh, turns out not very interesting. Doing a video, doing videos about the stuff you get after you do the sort of, like, boring run of PowerShell command install, much more interesting. So, today we’re going to look at the web viewer for this. Um, If you, if you recall, uh, maybe the earlier versions of the, the, the light and the old full dashboard thing, they only had the WPF viewer, which was very Windows-centric. Uh, moving beyond a Windows-centric view of the world, uh, it turns out web viewers, wonderful for a lot of people. And you can do a lot of stuff in a web viewer that you can’t do, uh, well in, like, a WPF app. So, anyway, down in the video description, you will find all sorts of helpful links to interface and interact with me.

Uh, where you can, uh, hire me for consulting, purchase my training, become a supporting member of this fine YouTube channel. Uh, ask me office hours questions. I do those every Tuesday. If this is your first time here, I do those every Tuesday. Answer five community-submitted questions. Uh, and of course, please do like, subscribe, and tell a friend. Uh, all of the, the helpful links are down in the, your video description. If you would like to take a look at this free SQL, free SQL Server and Postgres monitoring tool, uh, I, I half fixed this slide. The, the headline is fixed, the, the byline is still not fixed, or whatever that’s called. Uh, uh, again, totally free, totally open source, no email signup, no phone home. I don’t try to keep in touch with you for any reason. Uh, unless you want to pay me, that’s different. Uh, but it gets all the stuff that you would care about from, uh, for, from, your servers in order to, uh, monitor, uh, monitor and even troubleshoot, uh, performance issues. Uh, if you are a particularly robot-y type person, as many people are these days, uh, there are built-in MCP servers that, uh, allow you to interface and interact with your monitoring data, much in the same way that you could interface and interact with me, uh, but they can’t hug you. Uh, and you can just, you know, have the robots sort of look at that collected data and, uh, tell you the wrong things.

It’s fun. Anyway, uh, I am speaking at Pass Summit West. I should make this more colorful. I need to get some stuff in here. Uh, I started to mess with this slide and then I got bored. Uh, November 9th through 11th, I have a pre-con there and I have a regular session there. It’s going to be a good time. Uh, this slide is going to be much better in the next video, uh, now that I’ve realized that I forgot to finish working on it. Anyway, let’s go talk about, apparently reaping time has come. We got our beer, we got our size and pitchforks. We got, we got our crows flying. We got, yeah, we’re having a nice time. Anyway, uh, over to the web viewer, right? This is, uh, what you see when you get into it. Now, uh, when, when you say the words web viewer, a lot of people will be like, ah, man, and this, that it does refresh. It blinks when it refreshes. It’s not, it’s not you. You’re not having a brain episode. It refreshes and blinks itself. Uh, when you say the words web viewer, uh, security people tend to get a little worked up, right? Cause they’re like, well, who, who can view it?

So this, the, the way that this works, um, um, the, the, I’m implementing OIDC for this thing, but, uh, you, you also get a bearer token with this and the bearer token, uh, controls, uh, not only, uh, what, like, you know, who gets to see it, but the level of access that they have. Uh, there is like an admin, uh, uh, set, uh, admin token where you can get in and do whatever you want. And then there’s a read only token. Uh, read only is a little bit loose because you can still create custom views with the, the read only token. Uh, but you can’t change like monitoring settings and stuff. You can’t like mess with other stuff, but this is kind of what you get, uh, out of the box. Uh, you get a fleet overview. So this shows you, all right, boinky. This shows you kind of what’s going on. Uh, you, you, you might notice here that I have not only, um, uh, my, my SQL boxes, but I also have, uh, these Postgres boxes being monitored. Uh, these, uh, things are not very active at the moment. Uh, cause I don’t, I don’t generate a lot of Postgres workload here.

All of the Postgres monitoring was done by sort of, um, dog fooding at client sites. So they were just like, yeah, go ahead. Uh, want to monitor Postgres? Yeah. And build, use ours. Take a look, see what you can get. So that’s what I did. Uh, so, uh, get that. Uh, this is the, the, the thing is, is, is, is you see it when you first walk in. Uh, you’ll see SQL Server 2025 needs some attention. I’ve got HammerDB running on SQL Server 2025 at the moment. So there’s some stuff going on. Uh, but the rest of the things are kind of quiet. Uh, there is also, uh, reasonably good availability group monitoring.

Um, if you have one of those crazy, uh, you know, uh, you know, distributed AGs, uh, life gets a little trickier. So, uh, not quite there yet, but here, uh, you, if you have a normal-ish AG, uh, I can tell you all sorts of things about it. Uh, this fleet sweeps thing, uh, this sort of goes through and gives you, um, it, that, uh, whatever cadence you desire, uh, a sort of overview of what’s going on with your entire, uh, fleet at once. So, like, how this changes throughout the day. It’s like, you know, reports and it says, hey, things got weird since the last time we looked.

Uh, there are all sorts of, there’s all sorts of alerting that you can do with the monitoring tool. Generally, it can go to Slack, it can go to Teams, it can go to PagerDuty, uh, all those, the normal things that DBAs sort of rely on to get alerted for stuff. Um, and then, uh, let’s see, let’s just go down to the server that is currently in the red. And we can, we take a look at this and we will see that we have some spikes. Uh, we have our overview here with all this stuff going on.

We can sort of get a, get a sense of what the server is under the covers. Uh, maybe I need to install a CU or something. Um, and then, you know, sort of like, you know, what’s going on in the server? We’ve got some IO latency, we’ve got some blocking, we’ve got some other stuff happening. Uh, you know, from the web view, I can’t do as many interesting things with the web view as I can, uh, with the WPF viewer as far as, like, interactivity goes, but the web viewer does give you a great way to view what’s happening.

So we get wait stats, we get CPU, we get memory, uh, we get blocking file IO, you know, the queries that we’re running, how the server is configured, if anyone’s changing stuff, um, you know, perfmon sort of activity on the server. Um, uh, system events from the, uh, from various, uh, views within the server to look at CPU and other sort of overall server health stuff, system health event type stuff. Uh, and then, you know, something that sort of tells you about the collector health in general, because what good is a monitoring tool if it doesn’t monitor itself a little bit and tell you if things are spitting up, right?

Because, you know, like, ah, everything looks fine. Oh, wait, it’s not collecting anything. That’s not right. That’s not a good time. So, uh, that’s what you get sort of from the web viewer experience. Um, down here are the, a couple of custom views that I have. Uh, this one is, uh, that I put together. This one is showing me how much resources that HammerDB, uh, queries are using and what they are specifically responsible for. And down here, uh, here’s another fun one, uh, the HammerDB TPCC workload stuff going on. Oh, look at all these deadlocks. You can see all the things going on in here. Then these, these are custom views that I created, uh, just within the web viewer itself, putting those things together. Uh, there’s a sort of server health one in general that just sort of, um, you know, puts together a, um, you know, it’s like sort of whatever, whatever metrics you want to mish mash together. Like I put together like the, the sort of bigger dashboard, the grand overview of everything up there. If there’s a bunch of stuff that you make sense to you, for you to look at together at once for a server, go ahead and mish mash it in together here. When you want to create a new view, just go into the new view, choose whatever stuff you want to put in there. Uh, you know, you can add panels, you know, you can do all this fun stuff, right? And you can measure all the cool, all the stuff you want in your SQL Server, highly configurable. Um, I tried to, tried to make this as sort of open-ended as possible, uh, to allow you to, uh, create whatever views make sense for you to keep track of what’s happening on your servers. But, uh, you know, if, again, this is, this is an open source tool. If there’s code you want to contribute, you’re welcome to do so. If you’re, if you’re one of those people who’s like, but I don’t know how to code, you, you can have your robot friends help you with stuff. Um, it’s, it’s fine with me. Uh, you know, if you have, if you find a problem, GitHub, port a bug, I’ll fix it, right? It’s a, it’s a beautiful thing, right? And again, totally free, totally open source, costs you nothing. I don’t want to bother you with stuff. Uh, but yeah, it’s, it’s, it’s been fun working on this, uh, highly gratifying experience. And, uh, uh, everyone who uses it loves it. That’s why it’s called Darling. All right.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I’ll see you in tomorrow’s video where we will, uh, talk about some boring time zone stuff. But then, uh, next week, I’m going to do some more walkthrough videos of, uh, what’s been going on and what’s been, uh, happening with, uh, the, the, uh, development of the monitoring tool stuff lately. All right. Thank you.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

The post Free Database Performance Monitoring – The Web Viewer and Custom Views appeared first on Darling Data.

The post GitHub Copilot brings on-device AI coding to new Windows PCs appeared first on Source.