Hello, I am completely new to gitlab ci/cd. I needed to know how I can add a step to take an existing DACPAC and restore it to Azure SQL. Can anyone provide some guidance here?
Is a DACPAC something like a binary blob or database backup that gets uploaded to Azure SQL somehow? Please share more details how this is performed manually on the CLI for example, and link documentation resources.
It’s basically a fancy zip that has data and schema. To deploy via Azure Pipelines, the yaml looks like:
- task: SqlAzureDacpacDeployment@1
displayName: Execute Azure SQL : DacpacTask
DacpacFile: ‘<Location of Dacpac file in $(Build.SourcesDirectory) after compilation>’
To do this using SqlPackage, you can do this:
SqlPackage.exe /Action:Publish /SourceFile:“” /TargetDatabaseName: /TargetServerName:“”
Assuming that the SqlPackage blob is stored somewhere accessible (Generic package registry, Git LFS object, etc.), and putting the YAML into the Git repository, you should be able call the command in a Windows environment (GitLab Runner). Or the yaml is not needed and you can add all parameters to the SqlPackage call.
provision-job: tags: [windows] # needs a GitLab Runner that runs on Windows, use the tag during registering to assign the job script: # TODO: Download or prepare the SqlPackage first # TODO: Ensure that the yaml task file is in the same repository that will be cloned to the runner job (if needed) # Run SqlPackage with all parameters - SqlPackage.exe /Action:Publish /SourceFile:"myfile.zip" /TargetDatabaseName:"mydb" /TargetServerName:“myserver” /SqlUsername:"myuser" /SqlPassword:"mypass"
SqlPassword I’d suggest looking into environment variables that store the secret values in the project settings > CI/CD > CI/CD Variables, and not persist them plain text in .gitlab-ci.yml GitLab CI/CD variables | GitLab access should work with
%variablename% following the Windows syntax, but I haven’t done this before.
I also found a blog post that describes how to create a container image on Linux that runs sqlpackage, which could also be an option with GitLab. Automatically Deploy your Database with Dacpac Packages using Linux and Azure DevOps | Programming With Wolfgang and this one shedding some light into the CLI parameters: A simple database deploy pipeline using sqlpackage | sqlsunday.com
Hi, thanks for your help. Unfortunately I’m stuck using a Linux runner :(. I envisioned three steps to the process: 1: Download/install sqlpackage, 2: Retrieve the dacpac file from Artifactory (it’s something like mydatabase.dacpac), then 3: deploy using sqlpackage. Would it be possible for you to write out the gist of what that pipeline might look like? Here’s what I’ve done via a skeleton:
- dotnet install sqlpackage??? I think there’s a better option using curl, incase dotnet isn’t available
script: however this is done, assume all required creds, URLs, etc, are going to be variables.
- deploy here, assume that the connection string will be a variable, so that needs to be represented here, dacpac name from above will be a variable here, etc. SQLPackage deploys should be pretty easy.