c. Use the Schema Compare tool to download the latest changes from the source Synapse Dedicated SQL pool to your local SQL project in Visual Studio. You can individually select each change you want to include.
d. IMPORTANT: Make sure to do a local build to see if the project builds successfully before committing and merging with the main branch.
e. Commit and push code to a git branch.
Release Engineer Workflow Continuous integration:
Build a. Run build pipeline on your new code that is in the repository. This will use the project solution file to build a dacpac artifact. Then it will publish this as a release artifact.
Deployment to target environment
a. Run the release pipeline for the specified target environment. - This will download the previously generated build artifact and dacpac file. It will also download secure connection strings from Azure KeyVault. Then it will deploy to your Synapse Dedicated SQL Pool using dacpac deployment.
CI/CD Pipeline Setup Assumptions: - Have SQL Server Code in your repository
- Have deployed a dedicated SQL pool to Azure with its accompanying resources
- Folder Structure for example Contoso DB(Below screenshot)
- Make sure the pipeline runs on a Windows Agent - Reason for windows agent : This is because the Visual Studio Build task will only work on windows
Steps: - Build Task: - Use the Visual Studio build solution task - Direct the task to the database solution file ('ContosoDb/ContosoDb.sln' in our POC) - Set the configuration to release, if not set this will configure as debug - Example:
-Azure Key Vault Task - Use the an Azure Key Vault to take store the connection string to the Dedicated SQL Pool - Grab this connection string from the key vault and input it as an Azure Pipelines Variable - Example:
- SQL Dacpac Deployment Task -Specify the location of the .dacpac file in the build artifact from the build pipeline -Specify the connection string that was taken from the Azure Key Vault task -Example: