Learn what the Terraform MCP server is, how to install it locally, common use cases, and best practices for secure setup.

Every DBA has a box like this. Sitting untouched for months. Nobody’s proud of it, nobody’s fixed it, it’s just there — a handful of small compliance gaps that never made it to the top of anyone’s list. I pointed … Continue reading →

Learn T-SQL With Erik: Aligning Queries and Indexes Part 2

---

Chapters

• 00:00:00 – Introduction
• 00:02:45 – Problematic Query Analysis
• 00:06:38 – Index and Predicate Issues
• 00:09:21 – Optimizing the Query with Outer Apply
• 00:11:24 – Improved Performance Explanation
• 00:12:55 – Conclusion

Full Transcript

Ahem, Erik Darling here, Darling Data. In today’s video, we’re going to continue talking about query and index alignment. This one’s kind of in the same vein as yesterday’s video, but with a little bit of a twist to it. You know how I love keeping you emotionally hostage? Just kidding. So, again, more sort of aligning queries and indexes. It’ll be fun. Trust me. This is, of course, all material from the Learn T-SQL with Erik course. This is just little dribs and drabs of it to get you excited and entice you and force you to buy things from me, because in this consumerist society, that’s the way the world spins, I guess.

But down in the video description, you’ll find all sorts of helpful information. You’ll find all sorts of helpful links, including a link to purchase the full course material. It’s a great course. You should check out the full thing sometime with money. That’d be nice.

You can also find other ways to spend money on me. You can hire me for consulting. Maybe you think, oh, wow, he sure seems to know what he’s talking about. I wonder if he could come know what he’s talking about on our SQL Server. The answer is yes. Yes, I can.

You can become a supporting member of the channel, too. If you think that the things that I do and say and talk about here are helpful to you, and talk about here are just so outstanding and wonderful that you want to give me four bucks a month, and you want to take part in the glory and the greatness of this YouTube channel, maybe finally outdo that Amiga repair channel, you can do that.

Other stuff that there are links for, asking me office hours questions to do every Tuesday, answering five of them. And of course, if you feel strongly about the content, the content here, but perhaps you’re irresponsible with money in many other ways, and you can’t afford any of the paid stuff, you could always like, subscribe, and tell a friend, which has value of its own.

If part of your financial and your fiscal irresponsibility is spending too much on SQL Server monitoring tools, well, golly, I can help you out there. Boy, can I save you a pretty penny.

I’ve got a free monitoring tool. It’s up on GitHub. Again, the link for all this stuff is down in the video description. Totally free, totally open source performance monitoring. It does all the stuff you would expect a monitoring tool to do, except it’s written by someone who actually looks at SQL Server performance for a living, not someone who has never done that in their life, which is a problem a lot of other monitoring tool companies have.

So, you ought to check that out, shouldn’t you? Getting real close to 10,000 downloads, so I’m feeling like credibility is in the… I’m out in the world a little bit.

June 12th and 13th, I will be at Data Saturday Croatia. And November 9th through 11th, I will be at PaaS Data Summit in Seattle, Washington. At Data Saturday Croatia, I have a pre-con on Advanced T-SQL.

You might even find some of this material that we’re learning about today is in that course. And you might even find that if you show up to Data Saturday Croatia, you will get free access to the full course material if you come into my pre-con.

PaaS Data Summit, a lot of unknowns there so far. Who knows what’s going to happen? It’s going to be crazy. But anyway, we will continue making our way through May somehow, some way.

So, I’ve got these indexes, right? I’ve got one on the badges table on user ID and date. And these will all make a little bit more sense when you see the query.

And I’ve got this one down here on the comments table on user ID and post ID. And then I’ve got this index in here. And if you remember yesterday’s video, we almost had the same index except post ID and owner user ID were kind of swapped around there.

Or rather, owner user ID was at the beginning of the index, post type ID was second. We’re going to deal with a very similar query, but now we’re going to have to figure out a way to take better, to rewrite our query to take better advantage of this index.

I think Joe Sack had a great blog post some years ago. It was called like the gatekeeper problem. And we have a gatekeeper in here because when you create a roadmap, you have to set up a post store indexes.

We had like the ordering of the columns in those indexes is of course, like in like the way that queries can access data in those indexes. This is of course defined by the order of the key.

So like if we want to like do a search on post type ID something, we have like the immediate access to that data ordered in this index here. And if we wanted to search on post type ID and score, well we would have post type ID in order.

in order, and then we would have score in order for any duplicates in post type ID. So this would line up those two things pretty well. But as soon as you get to like wanting to do things like just search on score or search on score and owner user ID, the ordering of the index no longer benefits those searches as well, because we’re not first accessing queries by post type ID in order to sort of maintain the B-tree traversal that you get when you use those types of indexes.

So this query used to be a lot worse when I first wrote it. It was like 2000, well, it was on SQL Server 2017, and it was on a much worse laptop. So I need to play a few tricks here to maintain the nostalgic feelings that I have about this demo, because I truly love this demo.

So we’re going to hint things back in time a little bit. We’re going to tell SQL Server to use compat level 140. That was the 2017 compat level. And we are going to tell SQL Server, you can only run at max.4, because that was what my old laptop sort of permitted, right? So all that out of the way, let’s look at the query plan for this.

And it runs for about 4.2 seconds total. And the majority of that time is spent in one branch over here, right? So 4.2 seconds total, and 4.1 of those 4.2 seconds is spent in this branch, right? We can see 4.1 seconds ending up there.

The branch that this is hitting is, of course, the one where we are trying to find, let me go back up to the query and make a little bit more sense to do that. This thing right here. Now, there are going to be lots of times in your query tuning life where using a temp table is going to be beneficial, right? And so when I was writing this demo, one of the things I experimented with was using a temp table. So what I did was I took all the query that were already fast, and I was like, well, I’m going to put all you into a temp table, right? And that happens pretty quickly, right? That’s 47 milliseconds, right? So no complaints there. But then I was like, now, of course, with those 740 rows, right, in a temp table, right, 740 rows materialized, stabilized into a temp table, SQL Server will have to, this has to be faster. SQL Server will have to do something better or smarter here, make better choices, do something.

Do something helpful, but no, it actually slows down a little bit, 4.7 seconds. I guess if I ran this a few times, it might alleviate, but we’re not going to mess with all that. But again, that whole, like the whole problematic branch is over here, right? So all this stuff going on in this chunk. And again, it’s the same thing over here. So the problem that we really have is that our query, or rather the index that we have is on post type ID, score, and then owner user ID.

Right? So we can’t sort direct, rather, we can’t seek directly to post type ID and owner user ID because that score column is in the way. So in yesterday’s, yesterday’s video, I showed you a rewrite using top one and max and stuff. None of those rewrites here are terribly effective. In the full video, I go into all the ins and outs of why, but rather than the full course material, which is available for purchase, I go into all the ins and outs of why, but here, if we get this estimated plan, the, like the, the, the reason why kind of becomes a little bit more obvious, right? And if let’s move this over here, so my giant head isn’t in the way.

And if we look at the tool tip for this, you’ll see that like, and one of these branches where we’re seeking to post type ID one, but, but then we have this residual predicate over here on owner user ID, right? So because that score column is in the way, we can’t get directly to owner user ID.

We can get to the post type ID. We don’t care about, but then the score column is like, well, no, I don’t think so. I was like a sassy little thing and saying, no, you can’t seek directly to post type ID and owner user ID here.

You are forced to go through me, right? And pry that owner user owner user ID out of my cold, dead data. So one thing that you can do in order to take better advantage of this index is just considering what we want from this query, right?

So it’s a little bit. Yeah. Yeah. More clear. If we go up here a little bit, we want the top score, uh, from the population of post type IDs one and two, right?

So it doesn’t matter if it’s post type ID one or two, we want you to, we just want their highest score, right? Question or answer. It can be the highest score.

What we can do is we can, instead of writing the query with a single outer apply, where we union all both of these things together, what we can do is we can change this query. A little bit so that we use two outer applies and we find the, the, the top, the top, uh, score, uh, first for post type ID one, and then we use that score to act as an additional filter for post type ID two, right? So, uh, what we’re going to do is say, Hey, you know what?

We just found this top question score. Let’s go find the top, uh, sorry, the top. Yeah. The top question score, let’s go find that top answer score, but we’re going to use. The score that we found for questions to filter out and say, you know what?

Maybe we don’t need all of the, we can use this, like to sort of seek to some scores that we care about because we know what the top, the top question score is for this user. We can pass score down a little bit and allow it to act as an additional filter. So this is how we’re going to rewrite this query.

We have the first outer apply right here. Uh, that’s going to find the top one, uh, score for questions, right? Ordered by score descending. Right.

Correlated to owner user ID, just like before, but then down here, we’re going to add in this new thing and we’re going to say only go find me, uh, answer scores for that person for when the answer score is higher than the question score, right? So we’re using this, we’re giving it, we’re adding this additional predicate down here so that SQL Server can better traverse that B tree index from post type ID to score to owner user ID. And if we do this.

Uh, this finishes just about instantly. Now in this first branch, and I, again, I go into this far more and far more detail in the full material, but we have this first branch up here, right? This takes 217 milliseconds.

This one still has the same problem, but this one isn’t really the, the, where we get the big performance at the big performance hit that we get, uh, is on the, the one for post type ID two, right? Cause post type ID one, there’s about 6 million rows for that post type ID two. There’s like almost 12.

million rows for that, but down here in the second branch, right? So like this, this index seek here, you can see this is where we’re finding, uh, post type ID one, right? And then for this second branch, now this looks a lot different.

We have two seek predicates here, right? And this is not the same as having multiple seek keys. I know in another video I talked about like multi seeks and dynamic seeks.

This is not the same thing. Uh, we only have seek keys one here, but now you can see that we are correlating. On, uh, uh, post type ID two here, and we have this additional filter to say where score is greater than expression 1 0 0 3 expression 1 0 0 3 is of course the score column that we found from this first top one query out here.

So finding the top one question score, and then using that as an additional filter in this outer apply to say only give me answer scores that are higher than the top question score. We give SQL Server a better way. Use the index that we already had.

All right, cool. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you next Tuesday for office hours. Bye.

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 Learn T-SQL With Erik: Aligning Queries and Indexes Part 2 appeared first on Darling Data.

Building automated pipelines with your SQL database projects enables you to build a rich CI/CD ecosystem to ensure that your application is being deployed with good quality code and at high confidence of success. SQL Database Projects are compatible with just about every automation environment because fundamentally they’re built on top of the .NET SDK, a free and cross-platform development platform. You can develop the Microsoft.Build.Sql projects in VS Code and SQL Server Management Studio (SSMS). In this post, we’ll take a look at the things you need to know to get started with building and deploying SQL projects in Azure DevOps pipelines, a crucial part of integrating database development with the rest of your application development lifecycle. The concepts from this post will apply to any automation environment you choose.

Prerequisites

• An Azure DevOps project with a repository containing your SQL project
• Permissions to create a service connection in Azure DevOps project settings
• The .NET SDK available in your pipeline environment (pre-installed on Microsoft-hosted agents)
• An Azure SQL Database on a logical server with:
  • Microsoft Entra authentication enabled (Entra-only recommended)
  • Public network access set to “Selected networks”
  • An Entra admin configured on the server
• An Azure subscription where you can assign RBAC roles on the SQL Server resource

To start our example, we setup a repository that contains our SQL project in the folder labeled AdventureWorks and an empty folder for our pipeline definitions. The AdventureWorks project is located inside the root of the repository, so our file tree looks similar to:

.
├──  AdventureWorks/
│   ├── AdventureWorks.sqlproj
│   ├──  dbo/
│   │   ├──  Functions/
│   │   │   ├── ufnGetAllCategories.sql
│   │   │   ├── ufnGetCustomerInformation.sql
│   │   │   └── ufnGetSalesOrderStatusText.sql
│   │   ├──  StoredProcedures/
│   │   │   ├── uspLogError.sql
│   │   │   └── uspPrintError.sql
│   │   ├──  Tables/
│   │   │   ├── BuildVersion.sql
│   │   │   └── ErrorLog.sql
│   │   └──  UserDefinedTypes/
│   │       ├── AccountNumber.sql
│   │       ├── Flag.sql
│   │       ├── Name.sql
...
│   └──  Security/
│       └── SalesLT.sql
└──  Pipelines/
└── .gitignore

If you’re unsure of how to set up a SQL project from your current database, here’s a tutorial article on getting started from an existing database: https://learn.microsoft.com/sql/tools/sql-database-projects/tutorials/start-from-existing-database

Build before deploy

When we work with a SQL project in an IDE like VS Code, Visual Studio, or SSMS, we often leverage the build and publish actions as the primary development checkpoints. SQL project build validates the syntax is correct and matches the target platform that we have selected. This is one way to ensure that the database code we’re working with is compatible with the anticipated target (like Azure SQL Database), especially if we have an application that we’ve been developing while uncertain which platform it might be deployed to. While running build from a GUI interface involves using the menu option for build, in automation environments we need a command-line interaction to run project build.

In this case, it’s as straightforward as running “dotnet build” in an environment that has the .NET SDK installed. Microsoft-provided automation environments have a variety of software pre-installed, including the .NET SDK. If you leverage a self-hosted runner in the future, you would be responsible for installing the .NET SDK in that environment. Validating the project syntax represents the minimal continuous integration (CI) pipeline for our SQL project example.

We will use the starter template for pipelines in Azure DevOps to establish the continuous integration (CI) pipeline that validates the project builds successfully. From that starter pipeline template, we:

1. Modify the trigger to focus only on the main branch and only on changes within the Adventure Works folder.
2. Remove the provided script steps and use the task panel to add the “.NET (Core)” task template.
3. Set up the .NET task with the path to our SQL project file (AdventureWorks/AdventureWorks.sqlproj). Optionally, add the RunSqlCodeAnalysis property for additional insights on our database’s code quality.

When we save our pipeline definition, it looks similar to the below and can now be run ad-hoc as well as automatically when changes are made to our database project on the main branch.

trigger:
  branches:
    include:
    - main
  paths:
    include:
    - AdventureWorks

pool:
  vmImage: ubuntu-latest

steps:
- task: DotNetCoreCLI@2
  inputs:
    command: 'build'
    projects: 'AdventureWorks/AdventureWorks.sqlproj'
    arguments: '/p:RunSqlCodeAnalysis=true'

Modifying the steps of an Azure DevOps pipeline can be easily done through the graphical task selection and settings pane. However, the YAML code itself is also directly editable. Continued customization, source control of the pipeline definition itself, and repeated use of specific components leverages the code-first nature of the automation definition. The comprehensive documentation for Azure DevOps Pipeline YAML schema is available at https://aka.ms/yaml.

When the build pipeline runs, it prepares a summary of the errors and warnings on the pipeline run page. SQL project build produces a .dacpac build artifact, but if we do not explicitly preserve that file, it will not be captured by the build pipeline for future use. The .dacpac is temporarily available for use in the pipeline at its output location, which we can spot from the detailed build output. For the default configuration, the .dacpac is located in the bin/Debug folder of the SQL project.

Now that we have our SQL project successfully building in an automated environment, we’re ready to explore the steps needed to apply the deployment to different SQL environments. Deployment pipelines can be used for a variety of situations, including ephemeral (temporary) validation environments, shared staging instances, as well as production databases through gated pipelines.

Publish the SQL project

To build our understanding of a SQL project deployment, we’re going to create a separate Azure DevOps pipeline and use it for deploying the SQL project to a development Azure SQL Database. Even though this is a development instance where we are free to apply database changes without impacting a production workload, we are not going to compromise on security standards that we would expect protecting our data. Create an Azure SQL Database on a server with only Microsoft Entra authentication enabled and public network access only enabled for selected networks. In Azure DevOps, we’re able to adhere to these standards more easily through the use of service connections, which bind an Azure DevOps pipeline to an Azure Entra app registration. Setup of a service connection is done through the project settings in the Azure DevOps project.

The name of the Azure DevOps service connection is an important value to know, as it will be used throughout the pipeline. The default value can be quite lengthy and difficult to distinguish, but can be modified from the Azure DevOps interface. In this example, our service connection name is ContosoAzure.

We’ll start a new pipeline definition from the Starter template and apply what we learned previously about building a SQL project to make that the first step in our new pipeline. Instead of this pipeline running automatically on code changes, we’re going to remove all pipeline triggers such that it only runs ad-hoc.

# AdHoc deployment pipeline
trigger:
- none

pool:
  vmImage: ubuntu-latest

steps:

- task: DotNetCoreCLI@2
  inputs:
    command: 'build'
    projects: 'AdventureWorks/AdventureWorks.sqlproj'
    arguments: '--configuration Release'
  displayName: 'Build SQL Project to ./AdventureWorks/bin/Release/AdventureWorks.dacpac'

The SqlPackage CLI provides a flexible way to extract and apply the SQL project database definition. The SqlPackage CLI is an automatable tool that fits well into CI/CD environments like Azure DevOps pipelines. While the SqlPackage CLI is not likely installed in our automation environments, it can be easily installed through a quick script command in the pipeline by adding a step to install it. This is the same command we would use to install SqlPackage across Windows, Linux, and macOS environments.

- script: dotnet tool install -g microsoft.sqlpackage
  displayName: 'Install SqlPackage'

Setup passwordless authentication in Azure

The service connection from Azure DevOps to Microsoft Azure is listed as an “Enterprise Application” (app registration) in Entra. By granting the proper permissions to this identity, we’re able to interact with the database from the Azure DevOps pipeline without storing passwords or access tokens. Use the Entra blades in the Azure Portal to locate the app registration and note its display name, which may look something like “yourorg-yourproject-12345678-1234-5678-9012-123456789012”. We need to provide the service connection with two layers of access:

1. Database-level permissions as a contained user to deploy schema changes
2. Azure RBAC permissions on the SQL Server resource to manage firewall rules

Connect to the database with an Entra admin user such that you can add the service connection user in the database and provide it with elevated permissions. We start with “db_ddladmin”, “db_datareader”, and “db_datawriter”. These three roles together allow SqlPackage to create and modify schema, read existing data for comparison, and write static/reference data without granting full database ownership associated with “db_owner”. However, it is possible that in the future your database project will include database settings and changes that require elevated permissions.

CREATE USER [yourorg-yourproject-12345678-1234-5678-9012-123456789012] FROM EXTERNAL PROVIDER;

ALTER ROLE db_ddladmin ADD MEMBER [yourorg-yourproject-12345678-1234-5678-9012-123456789012];
ALTER ROLE db_datareader ADD MEMBER [yourorg-yourproject-12345678-1234-5678-9012-123456789012];
ALTER ROLE db_datawriter ADD MEMBER [yourorg-yourproject-12345678-1234-5678-9012-123456789012];

In addition to access to the SQL permissions, we also want to grant the service connection access to manage the firewall rules on the SQL Server. You may create a custom role with access to “Microsoft.Sql/servers/firewallRules/read”, “Microsoft.Sql/servers/firewallRules/write”, and “Microsoft.Sql/servers/firewallRules/delete” if your Entra license permits it. Alternatively, assigning the “SQL Server Contributor” role grants the identity access to manage the firewall and other aspects of the SQL Server in Azure.

Learn more about the “SQL Server Contributor” role to see if it’s right for your organization: https://learn.microsoft.com/azure/role-based-access-control/built-in-roles/databases#sql-server-contributor

Run SqlPackage in the pipeline

The database change deployment (migration) is applied through the SqlPackage CLI, which dynamically calculates the difference between the SQL project build artifact (.dacpac) and the database we connect to. We provide a connection string to the database in a pipeline variable – “SQLDBCONNECTIONSTRING” – which we may also set as a secret. The connection string is retrieve for the specific database we’re deploying to and we want the “Active Directory Default” format for ADO.NET:

Server=tcp:yourserver.database.windows.net,1433;Initial Catalog=yourdatabase;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";

In addition to the connection string variable, we’ll also need a variable for the server name (prefix to “.database.windows.net”) and the resource group for a total of 3 preset pipeline variables:

1. SqlDbConnectionString
2. SqlServerName
3. ResourceGroup

The “Azure PowerShell” task in Azure DevOps ensures that the script we provide is run with the pipeline authenticated to Azure with the specified service connection, simplifying the pipeline’s definition a bit. We’ll use this task several times to complete the pipeline. Adding the Azure PowerShell task and providing an inline script to execute sqlpackage publish enables the pipeline to run SqlPackage as the passwordless service connection to Azure.

- task: AzurePowerShell@5
  displayName: 'Run SqlPackage'
  inputs:
    azureSubscription: 'ContosoAzure'
    ScriptType: 'InlineScript'
    Inline: |
      sqlpackage /Action:Publish /SourceFile:"./AdventureWorks/bin/Release/AdventureWorks.dacpac" /TargetConnectionString:"${env:SQLDBCONNECTIONSTRING}"
    azurePowerShellVersion: 'LatestVersion'
  env:
    SQLDBCONNECTIONSTRING: $(SqlDbConnectionString)

Navigate the Azure SQL Database firewall

If we were to run the pipeline now, it would fail to connect to the database and the error message would provide an IP Address that needs to be added to the server firewall. However, we want to ensure that the firewall is only open to the address that the pipeline environment has for the duration of the run and be able to automatically use the correct IP address for the pipeline (since it will change over time in a shared environment). To meet these requirements, we’ll add 2 steps before SqlPackage runs and a final step at the end of the pipeline.

Before the SqlPackage publish step, add a PowerShell step and an Azure PowerShell step. These 2 steps combine to:

1. determine the IP address assigned to our pipeline environment
2. add a firewall rule for the pipeline to access the server

- task: PowerShell@2
  displayName: 'Get Public IP and put in variable runnerIP'
  inputs:
    targetType: 'inline'
    script: |
      $runnerIP = (New-Object net.webclient).downloadstring("https://api.ipify.org")
      Write-Host "##vso[task.setvariable variable=runnerIP]$runnerIP"

- task: AzurePowerShell@5
  displayName: 'Create SQL Server Firewall Rule'
  inputs:
    azureSubscription: 'ContosoAzure'
    ScriptType: 'InlineScript'
    Inline: |
      New-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME} -StartIpAddress ${env:RUNNERIP} -EndIpAddress ${env:RUNNERIP}
    azurePowerShellVersion: 'LatestVersion'
  env:
    SQLSERVERNAME: $(SqlServerName)
    RESOURCEGROUP: $(ResourceGroup)
    FIREWALLRULENAME: $(FirewallRuleName)
    RUNNERIP: $(runnerIP)

After the SqlPackage step in the pipeline, add an Azure PowerShell step that will be used to remove the firewall rule. This step includes a “condition” parameter that ensures it will run even if a prior step fails, like the SqlPackage publish, such that a firewall rule doesn’t remain in place.

- task: AzurePowerShell@5
  displayName: 'Remove SQL Server Firewall Rule'
  condition: always()
  inputs:
    azureSubscription: 'ContosoAzure'
    ScriptType: 'InlineScript'
    Inline: |
      Remove-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME}
    azurePowerShellVersion: 'LatestVersion'
  env:
    SQLSERVERNAME: $(SqlServerName)
    RESOURCEGROUP: $(ResourceGroup)
    FIREWALLRULENAME: $(FirewallRuleName)

The deployment pipeline we created is setup to only run on demand, although some development environments may be automatically updated from a shared branch in source control. When we invoke the pipeline from Azure DevOps, we can follow the steps in the logs, including the modification of the firewall rules and the steps taken by SqlPackage to update the database.

The entire pipeline definition for ad-hoc deployments would be:

# AdHoc deployment pipeline
# Variables: SqlServerName, ResourceGroup, SqlDbConnectionString

trigger:
- none

pool:
  vmImage: ubuntu-latest

steps:

- task: DotNetCoreCLI@2
  inputs:
    command: 'build'
    projects: 'AdventureWorks/AdventureWorks.sqlproj'
    arguments: '--configuration Release'
  displayName: 'Build SQL Project to ./AdventureWorks/bin/Release/AdventureWorks.dacpac'

- script: dotnet tool install -g microsoft.sqlpackage
  displayName: 'Install SqlPackage'

- task: PowerShell@2
  displayName: 'Generate Firewall Rule Name with BuildId suffix'
  inputs:
    targetType: 'inline'
    script: |
      $firewallRuleName = "FirewallRule-$(Build.BuildId)"
      Write-Host "Generated Firewall Rule Name: $firewallRuleName"
      Write-Host "##vso[task.setvariable variable=FirewallRuleName]$firewallRuleName"

- task: PowerShell@2
  displayName: 'Get Public IP and put in variable runnerIP'
  inputs:
    targetType: 'inline'
    script: |
      $runnerIP = (New-Object net.webclient).downloadstring("https://api.ipify.org")
      Write-Host "##vso[task.setvariable variable=runnerIP]$runnerIP"

- task: AzurePowerShell@5
  displayName: 'Create SQL Server Firewall Rule'
  inputs:
    azureSubscription: 'ContosoAzure'
    ScriptType: 'InlineScript'
    Inline: |
      New-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME} -StartIpAddress ${env:RUNNERIP} -EndIpAddress ${env:RUNNERIP}
    azurePowerShellVersion: 'LatestVersion'
  env:
    SQLSERVERNAME: $(SqlServerName)
    RESOURCEGROUP: $(ResourceGroup)
    FIREWALLRULENAME: $(FirewallRuleName)
    RUNNERIP: $(runnerIP)

- task: AzurePowerShell@5
  displayName: 'Run SqlPackage'
  inputs:
    azureSubscription: 'ContosoAzure'
    ScriptType: 'InlineScript'
    Inline: |
      sqlpackage /Action:Publish /SourceFile:"./AdventureWorks/bin/Release/AdventureWorks.dacpac" /TargetConnectionString:"${env:SQLDBCONNECTIONSTRING}"
    azurePowerShellVersion: 'LatestVersion'
  env:
    SQLDBCONNECTIONSTRING: $(SqlDbConnectionString)

- task: AzurePowerShell@5
  displayName: 'Remove SQL Server Firewall Rule'
  condition: always()
  inputs:
    azureSubscription: 'ContosoAzure'
    ScriptType: 'InlineScript'
    Inline: |
      Remove-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME}
    azurePowerShellVersion: 'LatestVersion'
  env:
    SQLSERVERNAME: $(SqlServerName)
    RESOURCEGROUP: $(ResourceGroup)
    FIREWALLRULENAME: $(FirewallRuleName)

Wrap up

In this article we setup two Azure DevOps pipelines that enable minimal continuous integration (CI) and continuous delivery (CD) of our database, all based on a database source code format that applies to the entire Microsoft SQL family and is available in SSMS and VS Code. The CI pipeline we created focused on the SQL project build only and your future exploration could include setting it as a required pull request check for changes to the application code that impacts the database. Depending on the different testing and staging environments used by your database, you may establish multiple deployment pipelines or emit deployment scripts from SqlPackage for further review before updating an environment. Most importantly, you learned the basic components of an Azure DevOps pipeline required to keep your database secure and let the pipeline navigate both the network and authorization security steps through the Azure DevOps service connection.

Links to learn more:

• https://aka.ms/sqlpackage-ref
• https://marketplace.visualstudio.com/items?itemName=ms-mssql.sql-database-projects-vscode
• https://learn.microsoft.com/azure/devops/pipelines/targets/azure-sqldb
• https://learn.microsoft.com/azure/devops/repos/git/branch-policies

The post Fundamentals of Azure DevOps with SQL projects appeared first on Azure SQL Dev Corner.

I had a great day off. The kids had a summer camp for the morning, so I took a joyful, meandering drive through the mountains and towards the beach. One beach-adjacent taco stand later, I wandered back. This is why I’ll never want a self-driving car.

[article] 7 reasons experienced EMs get stuck. This may be for engineering managers, but these reasons apply to nearly any manager position.

[blog] A return to two-pizza culture. Amazon’s Werner Vogels looks at the shift in when you write down your plan, and how teams work with AI.

[blog] Learning faster with Antigravity. How do you build a frontend for a backend you’re unfamiliar with? Andrew did a learning loop, and created a skill to make this process repeatable.

[article] Z.ai launches ZCode to challenge Cursor, Claude Code and GitHub Copilot in AI coding. The hottest AI coding model just got a coding UX. Smart fast-following offering.

[blog] Bringing speed and strong cost performance to the market with Gemini Omni Flash and Nano Banana 2 Lite. Speaking of cool models, these two are remarkable for those doing media creation.

[blog] Guide to the OWASP MCP Top 10. Here are the security issues you need to watch out for with MCP. Some of these you can directly mitigate, and others might require some proxies or gateways to protect your users.

[blog] AlloyDB AI Functions – now with revolutionary performance boosts and cost savings. We’ve made it cheaper and faster to make LLM calls directly from your SQL statements.

[blog] Ten do’s and don’ts for LinkedIn posts, according to actual research, based on pre-slop posts. Solid AI-summary from Coté here. Do these things to perform better on LinkedIn and stop hating yourself.

[blog] Why we built ADK 2.0. A big part of this was bringing deterministic execution to agents with “workflows.” Good post on when to use this new abstraction.

[article] When Developing an AI Strategy, Beware the Urgency Trap. Purpose matters more than anything else. Get that right! Rushing headlong in the wrong direction rarely pays off.

Want to get this update sent to you every day? Subscribe to my RSS feed or subscribe via email below:

DevSecOps was due for a fresh look.

DevOps — and later DevSecOps — came into focus more than a decade ago because organizations were trying to solve a very real problem: how do we ship software faster without giving up reliability, quality, or trust?

That problem still matters; in fact, it has become a critical factor in the adoption of AI. And the environment around it has changed in significant ways.

• AI is reshaping how developers work
• AI has amplified automation efforts
• Security threats are more sophisticated and constant
• Platform engineering is redefining ownership boundaries
• Toolchains are expanding — and fragmenting

As we stepped back in the Developer Division at Microsoft, and asked, “What does DevSecOps mean now?” one thing became clear: the industry has evolved, but many of our shared models have not kept up.

The Modern DevSecOps Customer Capability Model (CCM) came out of that gap. We wanted a practical way to help organizations understand where they are today, what might be getting in their way, and what it could take to move forward. This work was developed in parallel with the Platform Engineering Customer Capability Model. I’ll share more about how we built it later, but first I want to start with what the model is trying to do.

At a basic level, the model helps teams talk through three questions:

1. Where are we today?
2. Where do we want to go?
3. What capabilities do we need to get there?

Customer Capability Models are useful because they map a journey, not just a checklist of practices. They can help teams:

• Highlight current conditions and pain points
• Show progression toward higher levels of proficiency
• Align teams across product, strategy, and execution

What makes this DevSecOps model “modern”?

DevSecOps models have been around for 12–15 years. We intentionally redesigned this one for the environment teams are working in now.

Two key shifts informed the design:

1. The convergence of knowledge and ability

In modern environments, success is not just about whether a team has the right tools or policies. It also depends on:

· Whether teams understand what to do

· And whether they can operationalize it at scale

2. The expansion of the DevSecOps surface area

When we looked at where leaders are investing, it was clear that DevSecOps now reaches across:

· Developer experience

· Security posture

· Automation and orchestration

· AI-assisted workflows

A static, linear model does not really capture how organizations change. We needed a more complete view of the breadth and depth of DevSecOps as a capability area for a modern digital enterprise.

How the model works

The structure is simple on purpose:

• Rows = Core capabilities required for DevSecOps success
• Columns = Stages of maturity
• Cells = What’s happening at each stage, including:
• Realized capabilities
• Gaps and challenges

What this model enables

The model is not just a visualization. It can be used in a few practical ways:

• Helps organizations identify inflection points in their DevSecOps journey
• Supports product and offering alignment across Microsoft
• Surfaces common pain points and gaps across customers
• Acts as a tool for onboarding and education

At Microsoft, it has already been used to:

• Support strategic planning
• Map Microsoft’s portfolio to real customer needs
• Align internal teams on where to invest

How we built it: research, synthesis, and a lot of collaboration

This model was not created in isolation. While I’m authoring this piece, the work reflects the input, expertise, and thinking of many people. I especially want to recognize Nate Listrom, Adrian Diglio, James Duncan, Mark Simos, and Nick Courad for the knowledge and partnership they brought to the process.

The process included:

Secondary research

• Reviewing and synthesizing dozens of existing DevOps and DevSecOps models
• Analyzing internal and external best practices

Primary research

• Interviews with enterprise customers
• Conversations with Microsoft MVPs
• Surveys across multiple audiences

Internal collaboration

• Workshops with subject matter experts
• Alignment with Microsoft security frameworks and messaging
• Iterative design and validation cycles

That mix of inputs helped us make sure the model was:

• Grounded in real-world practice
• Aligned with Microsoft strategy
• Validated across multiple perspectives

After several months of research, workshops, synthesis, and iteration, we landed on a model with:

• Six core capability swim lanes
• Five stages of progression

The swim lanes are meant to capture the breadth of modern DevSecOps. The stages are meant to reflect how organizations tend to mature over time — often unevenly, and rarely in a straight line.

What we learned along the way

One of the clearest lessons was that organizations do not move through DevSecOps maturity in a neat, linear way.

We observed that:

• Capabilities within a single area can vary widely
• Progress is uneven across teams and functions
• Many organizations struggle to move past early-mid maturity stages

One pattern stood out: a lot of the hardest work seems to happen in the middle stages, where complexity increases and alignment gets harder.

Introducing the Modern DevSecOps Model

To make the model easier to approach, we also created a simplified view. This version is meant to help teams quickly orient themselves, identify where they may be today, and start a more concrete conversation about what might come next.

In the simplified view, you can see a progression from limited activity and tooling, to more localized efforts, then to more orchestrated and streamlined approaches, and finally to pioneering practices in organizations that are ready to push further. The top two rows point to the importance of organizational buy-in — both culturally and through policies and priorities. The remaining rows follow the software development lifecycle, which is still the foundation many teams use today. AI may change parts of that lifecycle over time, but these fundamentals still matter because they create the conditions for shipping digital products and services responsibly.

In the full detailed model (download full image below), the modernization shows up in a few ways: security is carried through the model rather than treated as a late-stage activity, and AI appears across phases instead of being isolated as a separate topic. Those choices reflect what we heard and observed: teams are trying to make sense of DevSecOps in a world where security, AI, automation, and developer experience are increasingly connected.

Final thought

DevSecOps is no longer just a set of practices. It is a complex, evolving capability system.

To make meaningful progress, organizations need more than tools or isolated guidance. They need:

• A shared understanding of where they are
• A clear vision of where they’re going
• And a roadmap grounded in real-world complexity

My hope is that this model makes that journey easier to see, easier to discuss, and easier to act on. Next, I will write about the ProductOps model that I developed.

April Reagan

Senior Design Researcher at Microsoft

---

Reimagining DevSecOps: A Modern Capability Model for How Teams Actually Work Today was originally published in UXR @ Microsoft on Medium, where people are continuing the conversation by highlighting and responding to this story.