-
Notifications
You must be signed in to change notification settings - Fork 1
/
serverlessSQLPool-sqlpackage.yml
91 lines (77 loc) · 3.87 KB
/
serverlessSQLPool-sqlpackage.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
# This is a pipeline to extract the contents of a database in a serverless SQL Pool using SQL Package into a dacpac file
# From there it deploys the dacpac file to another database in a serverless SQL Pool
# Please note that you need the below variables created for this to work
# I recommend doing this by creating at least one variable group
# agentpool - The name of the agent pool you want to use (ideally a self-hosted one with latest sqlpackage installed)
# Otherwise you must put additional logic in this pipeline to deploy latest version of sqlpackage onto the agent
# TargetFile - The name of the dacpac that you want to be created
# SQLPoolEndPoint - The name of your serverless SQL Pool endpoint (which you can get in the Azure Synapse overview)
# SourceDB - The name of database in the serverless SQL Pool you want to extract the schema from
# SQLPooluser - A SQL login that can access the serverless SQL Pool
# SQLPoolpw - The p/w for the above SQL login
# SQLPoolartifactname - A name for the created artifact
# AzureSubscription - The Azure subscription that contains the serverless SQL Pool where you want to deploy the dacpac
# DestinationDB - The name of the target/destination database where you to deploy the dacpac
# This pipeline uses the variable group: SQLPackageTest
# Feel free to create youw own and use the below
variables:
- group: SQLPackageTest
# Is triggered to run if the main branch is updated
trigger:
- main
# This is the name of the pool you have setup your local Azure DevOps Agent in
pool:
name: $(agentpool)
stages:
- stage: Pooldacpac
displayName: 'Build dacpac'
jobs:
- job: 'Builddacpac'
displayName: 'Build SQL Pool dacpac'
steps:
- task: PowerShell@2
inputs:
targetType: 'inline'
script: |
# PowerShell to extract contents of a database in a serverless SQLPool into a dacpac file
SqlPackage /Action:Extract /TargetFile:$(Build.ArtifactStagingDirectory)\$(TargetFile) /p:VerifyExtraction=true /SourceServerName:$(SQLPoolEndPoint) /SourceDatabaseName:$(SourceDB) /SourceUser:$(SQLPooluser) /SourcePassword:$(SQLPoolpw)
- task: PublishBuildArtifacts@1
displayName: 'Publishes dacpac as an artifact'
# Publishes the dacpac as part of an artifact within Azure DevOps
inputs:
PathtoPublish: '$(Build.ArtifactStagingDirectory)'
ArtifactName: $(SQLPoolartifactname)
publishLocation: 'Container'
# Now lets deploy the dacpac to a serverless SQL Pool
# Note that doing this using a deployment job so can state environment
- stage: serverlessSQLPool
displayName: 'Serverless SQL Pool'
jobs:
- deployment: 'SQLPool'
displayName: 'Serverless SQL Pool'
environment: Production
pool:
name: $(agentpool)
strategy:
runOnce:
deploy:
steps:
- task: DownloadBuildArtifacts@0
displayName: 'Dowload Artifacts'
inputs:
buildType: 'current'
downloadType: 'specific'
artifactName: '$(SQLPoolartifactname)'
downloadPath: '$(System.ArtifactsDirectory)'
- task: SqlAzureDacpacDeployment@1
displayName: 'Install DACPAC on serverless SQL Pool'
inputs:
azureSubscription: $(AzureSubscription)
AuthenticationType: 'server'
ServerName: $(SQLPoolEndPoint)
DatabaseName: '$(DestinationDB)'
SqlUsername: '$(SQLPooluser)'
SqlPassword: '$(SQLPoolpw)'
deployType: 'DacpacTask'
DeploymentAction: 'Publish'
DacpacFile: '$(System.ArtifactsDirectory)\$(SQLPoolartifactname)\$(Targetfile)'