SQL Saturday Austin 2023 – Recap

For those who attended my session, “Introducing Automated Deployments with Azure DevOps,” thank you. I think we had a good discussion on multiple pieces covered in the slides.

Speaking of the slides, here is a link to them – https://erindempster.com/wp-content/uploads/2023/03/sqlsaturdayaustin2023-devops.pptx

In the main demo, a deployment pipeline was created to build AND release code to an Azure SQL database. Below is the pipeline as we ended it. There was a question about deploying database schema changes to on-premises SQL Servers. There are tasks available in the Marketplace that will do this. It can also be accomplished through the use of a PowerShell script.

Using the pipeline above to deploy locally, there are a few changes required. First, instead of use Azure SQL Database deployment for the final task, Add a PowerShell script, like was done in the build section of this pipeline. The script will be inline and should look similar to this snippet (the options should all be on a single line with the command – this is for readability). The command sqlpackage.exe will be executed with an Action of publish. More information regarding the syntax for sqlpackage.exe can be found here.

sqlpackage.exe /Action:publish 
    /SourceFile:$(Pipeline.Workspace)\WorldWideImporters.dacpac 
    /TargetServerName:myserver.mydomain.com
    /TargetDatabaseName:WideWorldImporters
    /TargetEncryptConnection:false

What was not mentioned in the session is the PowerShell script needs to be able to connect to your on-premises SQL Server. To do that, an agent will be needed on your network and registered with Azure DevOps. Defining an agent pool is beyond the scope of this post, but this is documentation I used to create my pool . Below is what my pool, WorkshopPool, has for agents (Yes, I have 4 agents for workshops; you only need 1 agent in a pool).

In the pipeline’s deployment job, below, the properties need to be to adjusted. Instead of inheriting the agent from the pipeline, the Agent pool needs to be the one created for your on-premises agent. When the pipeline runs, this job will run on your agent and log into the SQL Server instance and deploy the DACPAC.

With a little luck the first time and knowledge on subsequent setups, on-premises SQL Server database deployments should become reasonably easy, though probably never as easy as deploying to Azure SQL DB or Azure SQL Managed Instance.