SQL Server Deploy Example

Introduction

This repository will demonstrate how to deploy an Azure SQL Server instance using pipelines with Bicep files, and then deploy and/or update a database schema using a SQL Server Database Project, which creates a DACPAC file that is then published to Azure with a YML pipeline.

Usage

Follow these steps to publish and update a database schema to an existing Azure SQL Server using Azure DevOps pipelines:

1. Create a SQL database on your local server or desktop.

2. Create a new SQL Server Database Project in Visual Studio with the SQL Server Data Tools installed.

   If the intended deploy target is Azure SQL, go into the project properties and set the Target Platform to "Microsoft Azure SQL Database".

3. Import the database schema into the project by right-clicking on the project and select Import... and then Database... and select the database you wish to import, which will populate the project with the schema objects.

4. If an initial set of data is desired in the database, add a post deployment script to the project by right clicking on the dbo folder and selecting Add and then Script... and then selecting Post-Deployment script. Name the script something like Post-Deployment.sql and add the desired SQL commands to populate the database with data.

   Note: The script will be run EVERY TIME the database is created or updated, so be sure the script is idempotent and will not create multiple versions of the initial data.

5. Check the updated project code into the repository and then run one of the pipelines to build the DACPAC file and deploy the database to the target server.

   Note: the folder, solution, and project name are hard-coded into the pipelines, so if those are changed, the pipelines will need to be updated.

6. When changes are made to the database, use the Schema Compare tool to compare the source database to the database project and then update the project with the changes, check in the code, and run one of the pipelines again to publish your changes.

   Note: If publishing to Azure SQL, the Schema Compare options should be set to ignore Database Roles and Users, as those do not transfer well to Azure SQL. Check the changes into the repository and run the pipeline again to deploy the changes to the target server.

Pipeline Setup

See the .azdo/pipeline/readme.md file for details on what each pipeline does and how to set up the Azure DevOps environment to make the pipelines function correctly.

Additional Notes

• This project is focused on database SCHEMA changes, not on changing to the actual DATA in a database (except for the initial data populate), although there is an example of running scripts as part of the update and the option to run a pipeline that just runs a script .

References

• Blog: Continuous Delivery for Azure SQL DB

  • Example Pipeline from the article

• Blog: DevOps for Azure SQL

• MS Learn: Azure SQL database deployment

• Visual Studio: SQL Server Data Tools

• YML Task: SqlAzureDacpacDeployment@1