CI CD in Azure Synapse Analytics Part 4 - The Release Pipeline
Published Jan 31 2021 09:58 PM 54.2K Views

Here's a quick review of the road so far:

 

CI CD in Azure Synapse Analytics Part 1

  • Creating an Azure DevOps project
  • Linking our Azure Synapse Analytics environment to that Project via Git
  • Validating that our Azure DevOps Repo was populated with our Azure Synapse Analytics environment

CI CD in Azure Synapse Analytics Part 2:

  • Create a new branch on our Repo
  • Edit our Azure Synapse Analytics environment
    • Specifically my SQL scripts have demos all over the place and Buck Woody said I have to clean up my very messy room .... Azure Synapse Analytics environment
  • Create a Pull Request in Azure Synapse Analytics to merge our new branch with the main
  • Approve the Pull Request in Azure DevOps
  • Validate our main branch is updated in our Azure Synapse Analytics Environment

CI CD in Azure Synapse Analytics Part 3

  • Create an Artifact pipeline
    • This is to create an Artifact we can use to deploy to another environment

This time we will:

  • Give our Azure DevOps Service Principal access to our Azure Synapse Workspace
  • Validate or Give our Azure DevOps Service Principal Storage Contributor & User Access Administrator (*This is only if your storage account was provisioned before you created your Synapse Workspace or if you connect your Dev, QA, and Prod to the same ADLS G2 storage account.  If you create your Synapse Workspace and Storage account from an ARM template deployed from DevOps, then your DevOps Service Principal will have Owner on the Storage Account and that gives the Service Principal User Access Administrator capabilities.)
  • Create the release pipeline
  • Validate the deployment
  • *If you have SQL Provisioned Pools as part of your deployment pause them, because they will be created on deployment

Before we create our release pipeline we need to make sure the Azure Service Principal account has the proper permissions.  If we do not, you well get cryptic errors with a GUID and something about "does not have permissions to blah blah blah".  Trust me, it is super annoying.

 

The two permission we will need are located in two different places, the first is in our Azure Synapse Workspace, specifically using Azure Synapse Studio.  The second will be in the Storage Account for our Azure Data Lake Gen 2 that is the default ADLS connection for our Azure Synapse Studio.

 

First open your Azure Synapse Studio and navigate to the Management Blade.

 

CI CD Release Pipeline 01.png

 

Now Click on Access Control.  The user that created the Azure Synapse Workspace is automatically given the role of Synapse Administrator, the second user with that role will be the Managed Identity for the Azure Synapse Workspace.  We need to add our Azure DevOps Service Principal to this role.

 

*A quick note, I prefer to manage this in an Azure Active Directory Group. In this blog I will show how to add the account directly to the Azure Synapse Workspace.  However, the best practice would be to have an AAD group that is granted Synapse Administrator and then add the role to that group.

 

Click the +Add link.

 

CI CD Release Pipeline 02.png

 

Now we will type in the Azure DevOps Project name.  If your project URL was https://bobsburgers.visualstudio.net and your project name was Azure Synapse Studio CI CD you would type in bobsburgers-Azure Synapse Studio CI CD.  This would show you the Service Principal GUID following that name. 

 

Click the name as it appears and then click the Apply Button.

 

CI CD Release Pipeline 03.png

Now open a browser and navigate to the Azure Portal.  In the search window at the type Storage Accounts.  Select the storage account that you are using as your default ADLS Storage Account for your Azure Synapse Workspace.  Click the Access Control (IAM) blade.  Click +Add, then click Add role assignment.

 

CI CD Release Pipeline 05.png

 

Select Storage Blob Data Contributor for Role.  In the Select text box, type in the Azure DevOps Service Principal the same way we did in the for the Synapse Administrator role.

 

CI CD Release Pipeline 06.png

 

Repeat the previous steps, except this time specify the User Access Administrator Role.

 

CI CD Release Pipeline 07.png

 

Next we will navigate to our Azure DevOps Project.   Select pipelines, Releases, and New Pipeline.

 

CI CD Release Pipeline 08.png

 

Click on Empty Job.  Then click on Add an artifact.

 

CI CD Release Pipeline 09.png

 

Ensure our project is selected.  Select the name of the Build Pipeline that we created in our previous blog (Or whatever YOU wanted to name your Build Pipeline because my naming conventions do not define you!).  Select the Latest Build, and click Add. 

CI CD Release Pipeline 10.png

 

Rename the Release Pipeline to reflect what we are doing.  We selected Deploy Dev Release.  Clock on the Stage1 link 1 job, 0 task

 

CI CD Release Pipeline 11.png

Click the + plus sign on Agent Job.  In the search text box type "Synapse", the Synapse workspace deployment task will appear if you have installed it from the Marketplace.  If not, FEAR NOT!  You should see a link for it below under the heading Marketplace.  Click on it to install the task to your Azure DevOps project.

 

CI CD Release Pipeline 12.png

 

Once you have added the task, click on the task.  We will fill out the Template, Template parameters, Synapse Workspace connection type, Synapse Workspace name, and we will get to OverrideArmParameters in a moment.  That will a lot more details.

 

First click on the ... ellipses by Template. 

CI CD Release Pipeline 13.png

 Navigate through the build pipeline, ASW_Drop, ARM, to the TemplateForWorkspace.json.  Select the .json file and click OK.

 

CI CD Release Pipeline 14.png

 

Now repeat the same steps for the Template Parameters text box, this time selecting the TemplateParametersForWorkspace.json file.

 

CI CD Release Pipeline 15.png

 

 

Under Synapse workspace connection type, select the Azure Subscription that contains the environment where we are deploying our release.  Specify the Resource Group and the Azure Synapse Workspace name.

 

CI CD Release Pipeline 16.png

Now we begin to focus on the override parameters.  First we will travel back to our Repo and look at the TemplateForWorkspace.json.  Any string that has a type "secureString" will need to have an override parameter. Depending on the level of development you have done, you may have many of these strings, in our example we have two.  The default workspace connection to the Provisioned SQL Pools and a Linked Service I created to an Azure SQL Database.

 

CI CD Release Pipeline 17.png

 

Dear Reader, you are wondering where to find those.  You are in luck!  Navigate to your Azure Synapse Analytics Workspace, click on the Manage blade, then Linked services.  Now click on the { } Code symbol after the name of the linked service that is a type securedString.  

CI CD Release Pipeline 18.png
This will open a view of the JSON in that contains the data we need.  Copy the text between the double quotes.  DO NOT SELECT THE DOUBLE QUOTES!! JUST THE STRING BETWEEN THE DOUBLE QOUTES!  

Sorry for yelling, but we will use this string soon and the double quotes "" will cause it to fail.

CI CD Release Pipeline 19.png

 

Now let's do the same thing for the Azure SQL Database.

CI CD Release Pipeline 20.png

 

 

Now navigate back to our Azure DevOps Release pipeline.  Click on Variables, then click the + Add button 3 times.  We will be creating two variables based on the secureStrings in our JSON file.  We will also be creating a system.debug value to give us extra information in our release pipeline, it's value is True.

After you copy in the secureStrings, click the lock button by the two connection strings, leave system.debug unencrypted.

CI CD Release Pipeline 22.png

Your pipeline should look similar to this.

CI CD Release Pipeline 22b.png

 

 

Now we will go to the OverrideArmParameters text area.  We will use the following syntax                                -variableNameFromTheJsonFile $(devOpsPipelineVarriable)

For example:

            -bballasw-WorkspaceDefaultSqlServer_connectionString $(WorkspaceDefault) -Lahman_connectionString $(Lahman)

CI CD Release Pipeline 23.png

Yours may vary based your number of secureStrings and names.  Now let's click Save on our pipeline.

CI CD Release Pipeline 24.png

Make a comment and click OK

CI CD Release Pipeline 25.png

Now click Create release.

CI CD Release Pipeline 26.png

 

Click Create

 

CI CD Release Pipeline 27.png

Click Release-1 (or whatever your release number is).

CI CD Release Pipeline 28.png

 

After your Agent begins to process click on Logs and watch it run!

 

CI CD Release Pipeline 29.png

 

 

AND NOW!!!!! ......it failed.

 

A few times.  But hey, it's not developing if there isn't a failure.  So it's almost 1 am, and I *believe* I have it running so let me take this time to walk you through what I've found.

Spark Pools and Self Hosted Integration Runtimes are not created in a pipeline.  If you have a Linked Service that uses a Self Hosted Integration Runtime you will need to manually create that in your QA or Prod environment prior to deployment. 

If you are developing Notebooks and have them connected to a Spark Pool, you will need to recreate that Spark Pool in QA or Production.  Notebooks that are linked to a Spark Pool that does not exist in an environment will fail to deploy.

 

Name them the same thing.  Do not change names.  Trust me.  

If you are doing a deployment and your Provisioned SQL Pools are Paused then the deployment will fail.  *More to come on database migrations, a database project build, and release is still needed.

 

Here's a quick image. 

CI CD Release Pipeline 30.png

I'm on release 4, attempt 2.  This appears to be running just fine for me.

VICTORY!!

 

CI CD Release Pipeline 31.png

 

Now let us go and check our QA Workspace!  First up Scripts and Notebooks.

 

CI CD Release Pipeline 32.png

Excellent!  Everything is there.  Next let us look at our Provisioned SQL Pools.

CI CD Release Pipeline 33.png

Looks great!  As a quick side note the databases will be brought over at DW100c, so you can auto scale them as needed.  Also if you are in a demo environment like me, be sure to pause them after the deployment completes.   Next up Pipelines.

 

CI CD Release Pipeline 34.png

I like this!  Now let's check out linked services.

 

CI CD Release Pipeline 35.png

 

I don't like this.  Here are my Dev links for my default workspace in my QA environment.  Right now the only way I've found to clean this up is to use the Az.Synapse PowerShell Module.  Navigate back to your release pipeline.  Edit it, add an Azure PowerShell task.  We will then use this script:

 

 

##Required for azure devops initial deployment
Install-Module Az.Synapse -RequiredVersion 0.2.0 -Scope CurrentUser -Force

#get rid of dev linked service in QA
Remove-AzSynapseLinkedService -WorkspaceName yourworkspaceName -Name linkedservicetoRemove
Remove-AzSynapseLinkedService -WorkspaceName yourworkspaceName -Name linkedservicetoRemove

 

Under Azure PowerShell version options select Specify other version, set Preferred Azure PowerShell Version 3.1.0.  

 

The next time you run your deployment, this should clean up those links.

CI CD Release Pipeline 36.png

 

All right Dear Reader, I'm off to sleep.  Happy Monday and as always, thank you for stopping by.

Thanks,

 

Brad

 

 

 

 

 

41 Comments
Brass Contributor

Hi Brad,

Thanks for this new article and all the work you've done, I can't wait to give it a try and hopefully get this working finally! I can already see places I went wrong. :D

 

 

Thank you @KeyBee !  Good Luck :)!

Microsoft

Thank you Brad for this article! YOU SAVED MY DAY! I spent already hours to get the thing running...

Copper Contributor

Hi Brad,

 

Thanks a lot for the blog. It explains my questions on why it ignores my Spark pools when deploying to another workspace.

One question though, why do we have to override those parameters with type secureString? Before reading this I had tried a few times without overriding any parameters and didn't see any obvious error (might have missed something?)

 

And also fyi, my workspace studio publishes the resources into a separate branch named 'workspace_publish' so we have to deploy from there. I'm not sure why yours uses the same branch.

Hi @MinhDNguyen By Default, unless you change it to main, the default publish branch will be Workplace_Publish.  In part 1 I left this as the default, but you can change it, even after deployment.

In the Management Blade you would click on Git Configuration, then click at the Gear icon at the top and it will open a blade on the right hand screen.  you can change workplace publish to main.

 

Other wise every time you publish your JSON in Azure DevOps, it will publish the newest JSON to that Workplace_Publish branch.  You would need to build off of that branch instead of main.  It's get's a bit confusing to me, but my Buddy Steve St. Jean (to whom I owe teaching me how to do this prior to Winter Break in December) who is one of the best Developers I've ever met, assured me this was the way to go.  And if I got any of this wrong, it is my fault and not Steve's, I'm just doing a bad job re-explaining this :). 

Copper Contributor

Thank you Brad for your explanation. Additionally, I would like to ask: why do we have to override those secureString parameters?

I had tried to deploy a few times without overriding any parameters before coming across this article, and didn't see any obvious issues. I might have missed something though....

Hi @MinhDNguyen In ARM secureStrings are a data type requiring an encrypted value.  It is the most common and popular way of handling secrets in ARM template parameters. If you  have a field in an ARM template that specifies a parameter of type secureString, you must pass it an encrypted value.  It cannot be null, it cannot be in plain text. 

 

Instead of inserting the text you can use Azure Key Vault to pass strings, you can also create variable groups that could be assigned per environment so you can have different variables for QA and Prod.  I plan to get into those things eventually but not this early in the series. 

I cannot explain why you are not getting this error, this was the first error I ran into that had to be fixed.  I'm glad you are not receiving errors :).

Copper Contributor

Thank you so much for this.  I was attempting to use documentation but could get none of the steps in it to work.  This series has been a breath of clarity.

 

 

Copper Contributor

Hi @Bradley Ball (MICROSOFT) quick question about the powershell task that removes the dev linked services.  I had a few issues with it if I'm honest, I found that with

Install-Module Az.Synapse -RequiredVersion 0.2.0 -Scope CurrentUser -Force

Remove-AzSynapseLinkedService was not in the list of cmdlets imported, changing to version 0.7.0 solved this for me.  I also had to add a -Force command to the  Remove-AzSynapseLinkedService task:

$linkedService | Remove-AzSynapseLinkedService  -Force

All of these were surmountable and as you said

But hey, it's not developing if there isn't a failure. 

What I did find however was that the Linked service that had Related objects (the default storage in my case) is not removed. 

Before

BeforeBefore

After
AfterAfter

How did you solve that issue if indeed you could? I noticed in your image there is 1 related object I assume its an Integration dataset. Have you a way to change the name of the service that Integration datasets are pointing to? 

Copper Contributor

Hello Bradley , Thanks a lot for sharing! All steps I've done and it works perfectly.

 

 

Microsoft

Hi @Bradley Ball (MICROSOFT) ,

Thank you for publishing this series.

In the official CI/CD documentation, there are two tasks before the Synapse Workspace deployment task. One is for deploying an ARM template. Though it's not explicitly stated, I take it this is to create or update the actual Synapse Workspace itself, before deploying the contents?

Can you elaborate on why that's not in your walkthrough, please?

 
 
Copper Contributor

Thank you for the post. In your response to @MinhDNguyen I understand your perspective but there are still issues with that approach. There are valuable insights that we lose when we don't maintain lineage between main and our releases, like user story/task/bug tracking. Right now, this is proving to be challenging, because when I link and track changes with my release pipeline, I lose all insight on the release, but I can track those changes for my database project for the Synapse dedicated SQL Pool, maintained in a separate repo with independent build and releases. My concern is that for larger teams where we have policy protected branches, we lose the ability to merge these changes back into main with the manual publish, to do this we would need an additional pull request. The Azure Data Factory team has a much better approach to this issue. Automated publishing for continuous integration and delivery - Azure Data Factory | Microsoft Docs I think the biggest advantage to an approach where we leverage a more traditional build pipeline is that it empowers the engineering team to leverage the most appropriate branching strategy for their teams and is more suitable for managing hotfixes without a ton of manual work to deploy changes.

Copper Contributor

Hi Brad,

 

In this example we can see that the provisioned SQL pools databases are populated in the new workspace. How about the SQL on demand databases? Are they propagated from the source workspace too? In my experience, they are not. I do hope that I am missing something on this because I need the SQL on demand databases :cry:

Copper Contributor

Hi Brad,

 

We have successfully implemented the CI/CD as described in this blog and it is working great. However, we have noticed that deleting objects in the Synapse workspace, like a trigger or SQL script, are not deleted in further environments when running the release pipeline. The TemplateForWorkspace.json file that is built does not contain the deleted objects. Is there a trick to delete objects in a Synapse workspace and have those objects deleted from the further environments? Just wondering what method can be used to handle the deletions. Thanks.


Eric

Hi @swordSDC this is fixed in the latest version of the Azure DevOps Synapse Deployment task for workspace.  Make sure you are using version 1 and not version 0 the default in this blog.  I will be posting a follow up on this soon!

Hi @eleicht you are correct, working on a blog for this as well (YOU ALL ASK GREAT QUESTIONS!!!).  The Synapse Deployment task for workspace does not delete objects from a QA or Production environment if you delete them in development.  For this you must use a PowerShell command to remove the pipelines, notebooks, or SQL Scripts you've created and deleted in Dev.  

Great article bradley  , the effort you put into this will save a lot of sleepless nights for others :)

Brass Contributor

Hi @MinhDNguyen the SQL Serverless DB does not get created as part of the pipeline (from our testing). So we have gone down the PowerShell route as part of the release pipeline that creates the databases, keys, schemas, procs, permissions etc. If anyone has found a better method please do shout! 

Brass Contributor

@eleicht I logged a support call with MSFT about deleted artefacts from DEV hanging around in QA/PROD as I had no idea how to approach this with PowerShell and they sent me a screenshot of a checkbox (that I had no idea was there, maybe it's new?!) Give it a try, it has worked for me!

KeyBee_0-1622212556278.png

 

Copper Contributor

Great series of articles Brad, I am stuck on some permission issue though when running the deployment in ADO.  I get the error:

 

Getting Artifacts from workspace: <target workspace name>.
Failed to fetch workspace info, status: 403; status message: Forbidden
Error during execution: Failed to fetch workspace info Forbidden
 
Now I figure it is because the Service Account we use in ADO doesn't have access to the target workspace, so I added them as Synapse Administrator, however I still get the error.  In your previous post you said "*a quick side note, make sure the AAD account you are using to connect to Azure DevOps is the same account that has permissions to your Azure Synapse Studio workspace.", which I am sure I have done (basically I have given everyone Synapse Administrator and I still get the issue!).  Are we supposed to also set something else up in the Synapse IAM roles?
 
Edit: Don't worry about this - there were about 20 service principals with the same name, I added them all as Synapse Administrators and I was successful at pushing through changes. Leaving this here in case it helps others.
Copper Contributor

I get below error after following these steps:

"Encountered with exception:Error: Failed to parse package: TypeError: Cannot read property 'startsWith' of undefined"

Copper Contributor

Hi Brad, thanks for this series, it has helped a lot! 

I'm having a problem with the Synapse deployment task in the release pipeline.  I'm getting a 404 for workspace not found.  I've followed all the steps, including the pre-requisites but I can't figure this one out.  The workspace does exist (running az synapse workspace show in the pipeline works).  Any thoughts?  Thanks for any help you can give.

Log snippet:

2021-07-21T16:36:06.0662405Z Workspace info url: https://management.azure.com***/providers/Microsoft.Synapse/workspaces/<workspace-name>?api-version=2019-06-01-preview
2021-07-21T16:36:06.0704131Z ##[debug][POST]https://login.windows.net/***/oauth2/token/
2021-07-21T16:36:06.5016969Z Failed to fetch workspace info, status: 404; status message: Not Found
2021-07-21T16:36:06.5072255Z Error during execution: Error: Get workspace location error: Failed to fetch workspace info Not Found
2021-07-21T16:36:06.5074281Z ##[debug]task result: Failed
2021-07-21T16:36:06.5161943Z ##[error]Encountered with exception:Error: Get workspace location error: Failed to fetch workspace info Not Found

 

Copper Contributor

Hey Brad - I have also created a process for doing CD for SQL scripts, releasing these to higher environments via an Azure Powershell script.  This enables me to do continuous deployment on SQL Scripts stored in the Develop part of Synapse Studio with releases happening automatically when the "Publish" button is hit. It's a little fragile, but can easily be adapted to specific circumstances.  When you say "*More to come on database migrations, a database project build, and release is still needed." - this isn't currently possible as Visual Studio 2019 doesn't support Synapse Serverless pools yet, but they are working on it.

 

For us, we have Cosmos databases storing event streams in Dev/QA/Prod.  We develop views over the Cosmos data and store these views as SQL Scripts executed against the Dev instance.  To release to the QA/PROD environment we:

  1. Create scripts in Dev Synapse Studio and commit/publish
  2. On publish, run an ADO build then release pipeline
  3. Run the Synapse release process as you describe
  4. Run a Powershell script which:
    1. Extracts the SQL Scripts from our Repo
    2. Makes modifications to the SQL Scripts depending on the environment
    3. Executes the SQL Scripts on the target environments serverless SQL DB

Brent_Leslie_0-1627441767452.png

 

There are a few special things I picked up with running Powershell scripts along the way, including having to escape key powershell characters (like "$") with "`" and special cases where you need to use double quotes and escape single quotes.  But as a result we can run full CD processes for Synapse.  If anyone wants the script, I am happy to share.

Copper Contributor

Hi,

 

Great blog on CI/CD for most of Synapse Workspace :)

 

And what about Synapse Dedicated DB Pool? What've tried with VS 2019 and database projects, does not yet support it :(

 

Do you have any tips or insights about it?

Microsoft

@pacorreia you can create dedicated sql pool using powershell cmdlet - New-AzSynapseSqlPool (Az.Synapse) | Microsoft Docs

Once you create the sql pool, you can use VS 2019 SSDT database project to develop objects for SQL pool. Do change the target platform of the SSDT project to "Azure SQL Data Warehouse" to support dedicated pool. There is no support to Serverless pool though

Copper Contributor

Hi Bradley,

 

Thanks for the post. I have followed the same steps, I am not facing any error. Release pipeline executes succefully but I am not able to see any scripts in destination workspace. 
I have a question here when i create a synapse deployment task- > Synapse Workspace connection type-> should i use the Subscription that is showing in azure portal or should i use the service connection of Devops?

 

Thanks and Regards,

Shashwathi

Copper Contributor

HI Bradley, 

 

We have followed your article and now we are able to migrate the code to UAT and Production. 

Is there way to do incremental deployment instead of migrating all code every time ? 

 

We have large number of pipelines and our expectation is move the code which is actually changed when compare to previous. 

 

Thanks

Prasanna

Copper Contributor

Hi @ ,

Thank you for the article. I got stuck at the begging. I am not able to get name of the service principal in RBAC.
The URL of my org is a little different. It is in the form:
https://dev.azure.com/myoranization/myproject
I tried searching for service principals in IAM using both words myorganization and myproject. I could not find them and grant them permissions on storage and synapse.
Where can I find service principal names?

Is there something that we need to to on either DevOps or AAD before that would be connected?
Is there some permission that I need before I can see service principal?

Thank you.

Dejan

Copper Contributor

Hi Brad,

 

Great article!!! this helped me a lot to setup my first release pipeline for my Azure Workspace.
I have two questions if you please answer,

 

1. Is it possible to push artifact of my dev workspace from a feature branch without merging my changes into main (collaboration) branch and deploy into test/qa workspace?

 

2. Also once we click manually on publish button in main branch which generates TemplateForWorkspace.json and TemplateParametersForWorkspace.json into workspace_publish branch in my azure devops repo and then my release pipeline deploy my dev workspace changes into Test/QA/UAT workspace using these templates, is it possible to automate this publish activity? I want to trigger my release pipeline as soon as my PRs are merged into main branch and these two template jsons are generated automatically.

 

Any help would be appreciated.

 

Thanks,

Kapil

Hi @dejans2102 , 

In the Azure Portal if you go to Azure Active Directory | Under Manage go to App registrations | then

IF you were the one to create the DevOps Project that contains the repo:

            you can click on Owned applications and it should list all of the Service Principals that you own.  All of my DevOps projects Service Principals are there.  You should be able to find the project name in part of the Service Principal name.

IF you were NOT the one to create the DevOps Project and you have the permissions required to list the App registrations screen you can search on that page for the project name, if you do not have permissions to access AAD then you may need to team with someone in your organization who does.

Quick example: I presented at the Fall DevIntersections / SQL Server & Azure SQL Conference in Las Vegas.  I taught a post con on DevOps where I created a project named FallIntersectionsPostCon2021.   When I type in FallIntersections (and keep in mind this is case sensitive so you want to match the case) I get two services that match.  One I was testing my content on that happened to have a similar name, and the other Service Prinicpal for my account. 

response part 4 blog 1.png


Hope this helps!


Hi @kapilmalik ,

Thank you for the kind words!  Let me try to take these questions one at a time.


1. Is it possible to push artifact of my dev workspace from a feature branch without merging my changes into main (collaboration) branch and deploy into test/qa workspace?

 

The problem I have witnessed with this approach is the TemplateForWorkspace.json or TemplateParametersForWorkspace.json can get disconnected and a merge issue could present itself which requires manual resolution of the template files, if you are merging some files to CustomBranch, and others to Main.

When I've run into this, it was a major headache (for me) to fix.  I have developer friends that went, "Oh yeah just go into the files and manually fix the merge.".   This is where I like to remind folks, I'm more data person than developer.  So if manually fixing merge conflicts is something you are comfortable with, just keep in mind you may need that skill set.

 

The short answer, however, is Yes.  You can create a build package off of a branch other than main and use that to deploy to QA or Prod.  Just make sure your developer friends are on stand by!

2. Also once we click manually on publish button in main branch which generates TemplateForWorkspace.json and TemplateParametersForWorkspace.json into workspace_publish branch in my azure devops repo and then my release pipeline deploy my dev workspace changes into Test/QA/UAT workspace using these templates, is it possible to automate this publish activity? I want to trigger my release pipeline as soon as my PRs are merged into main branch and these two template jsons are generated automatically.


If I understand correctly, the question is once you publish from workspace_publish to Main we can trigger the build package.  After that you want to trigger the release package?

Yes!  Go to the Pipelines and click on Releases.  Click the lightning bolt at the top of the package.  That will open a blade on the righthand side of your browser that is currently disabled.  If you enable it, it will trigger the release pipeline whenever a new build package is created.

response part 4 blog 2.png


Hope this helps! 

Copper Contributor

Hi Bradley

 

This series of articles has been extremely helpful! Thank you for sharing.

 

One thing I've noticed is that if I drop an item from my dev workspace it won't then drop the equivalent in my test workspace. Is there a setting for allowing the release to drop items?

Copper Contributor

Hi bradley,

 

The above mentioned steps helped a lot. Thanks a ton :) Also , I’ve few queries mentioned below . It would be great if you could help me with the same.

 

1) I am unable to deploy the Dedicated SQL pools into the other environment using the above process. Only SQl script, pipelines etc are automatically deployed using this process. Is there any way to deploy the dedicated Sql pool automatically as well?

 

2) Suppose I’ve a large number of synapse pipelines and i want only few to be deployed to the next environment. Basically, i want to exclude certain pipelines from getting deployed in the other environment. I’ve already tried the exclude pattern but it is not working in the above scenario

 

Copper Contributor

Hello Bradley

 

Tons and tons of thanks to you for writing this detailed blogs for us, you have made very easy for us to use Devops especially for synapse.

 

I followed your steps and was able to deploy synapse code from dev to QA like a hot knife passing through butter.

 

Like previous member, Serverless pool was not deployed for me as well. Will try to figure out but if you got some hints let us know.

 

Thanks once again !!

 

Kind Rgds,

Sanjay

Copper Contributor

I rarely read such well documented series which are as easy to follow, love it! 

Thanks so much for the series, it helped me out a lot. There are some changes (e.g. the checkbox to delete any artifacts in the target synapse workspace which are not in the template anymore) and the PowerShell Script (remove linked services) does not work, but those are only minor things anyway which we can figure out on our own :) 

Copper Contributor

Hi Bradley,

 

This is really useful.

 

I only have the one securestring (the default) as everything is in a key vault.  The deployment is failing on the keyvault with a 403 forbidden.  I have assigned the contributor role to the DevOps account I am using against both dev and prod key vaults but no joy.  Any suggestions on what I am doing wrong?

 

2022-11-14T15:40:06.1434878Z ##[debug]{"subscriptionID":"[REMOVED]","subscriptionName":"Microsoft Azure Enterprise","servicePrincipalClientID":"***","environmentAuthorityUrl":"https://login.windows.net/","tenantID":"[REMOVED]","url":"https://management.azure.com/","environment":"AzureCloud","scheme":"ServicePrincipal","activeDirectoryResourceID":"https://management.azure.com/","azureKeyVaultServiceEndpointResourceId":"https://vault.azure.net","azureKeyVaultDnsSuffix":"vault.azure.net","scopeLevel":"Subscription","authenticationType":"spnKey","servicePrincipalKey":***,"isADFSEnabled":false,"applicationTokenCredentials":{"clientId":"***","domain":"[REMOVED]","baseUrl":"https://management.azure.com/","authorityUrl":"https://login.windows.net/","activeDirectoryResourceId":"https://management.azure.com/","isAzureStackEnvironment":false,"authType":"spnKey","secret":***,"isADFSEnabled":false}}
 
2022-11-14T15:40:06.1437097Z ##[debug][POST]https://login.windows.net/[REMOVED]/oauth2/token/
 
2022-11-14T15:40:06.6396150Z For Artifact: Az_Kv_SSSC: ArtifactDeploymentTask status: 403; status message: Forbidden
 
Copper Contributor

Addendum to above:

2022-11-14T16:10:37.8894325Z For Artifact: [MY KEYVAULT LS]: Deploy artifact failed: {"error":{"code":"Unauthorized","message":"The principal '[REMOVED]' does not have the required Synapse RBAC permission to perform this action. Required permission: Action: Microsoft.Synapse/workspaces/linkedServices/write, Scope: workspaces/[REMOVED]-dev-workspace."}}
 
The principal id I have removed is the correct principle.  And the removed workspace is the correct workspace.  The principal has "owner" role on the workspace.
 
Copper Contributor

@StuartPlover - Keyvaults are a bit special, you need to give the Principal an Access Policy, which is done in the KeyVault as well (usually get/list permissions are fine):

 

Brent_Leslie_0-1668459701385.png

 

Copper Contributor

If anybody else is having trouble with KeyVaults then you need to:

1. Go to the destination synapse workspace (the target of the deployment);

2. Open the synapse workspace, go to the "Manage" section on the left, then down to "Security" and "Access control";

3. Add the role "Synapse Artifact Publisher" to the DevOps account.

 

(Sorry, I don't have the permission to upload images)

Copper Contributor

Thanks for this detailed blogs, really help to use Devops for synapse.

Have anyone tried any rollback startegy for Synapse Serverless SQL through CI CD pipelines ? 

As far as i understood , synapse serverless SQL doesn’t persist any data (only schema for EXTERNAL TABLES and VIEWS) so it relies on ADLS recovery. Schema can be stored and managed using the source control integration feature of Synapse with Azure devops repository for DR purpose.

Is there a way to create restore point for synapse serverless SQL or to backup serverless SQL pool databases, please suggest how to achieve !!

Copper Contributor

Thank you for detailed explaination! 

I have one question regarding the OverrideArmParameters the deployment task: is it possible to outsource the parameters to a different (e.g. yaml-)file in the repo? 

The info I found online, where files instead of direct value assignment are used for this, are for Github Actions. I could neither find an example here, nor in the official MS doc here: https://learn.microsoft.com/en-us/azure/synapse-analytics/cicd/continuous-integration-delivery#set-u...

theodicy_0-1693211763636.png

The best I could find is this: https://github.com/Azure/Synapse-workspace-deployment#overriding-parameters-overridearmparameters 

But it uses Github actions instead of Azure pipeline. 

I gave it a try nevertheless, where instead of specifying the param names and values, I put a file path for OverrideArmParameters like : "./deploymentParams/params.yaml" or "deploymentParams/params.yaml" which is located in my repo, and judging by the results, neither worked. The deployment pipeline went through without error, including this line:

theodicy_2-1693212015589.png

And when I check the synapse workspace, the property values for those resources are not overwritten. 

 

Do you have more insights on this, or point me somewhere where I could find info on whether / how the OverrideArmParamters could be outsourced as a file with Azure devops pipeline? 

Version history
Last update:
‎Jan 31 2021 09:56 PM