How to develop against Azure Synapse Dedicated DB Pool and implement a working CI/CD

Question

Hi all,

before anyone sends me links to Microsoft Learn about this subject, please read a bit more below.

Scenario:

I want to have a change management process with proper CI/CD on top of Azure Synapse Dedicated Database Pool.

What was tried:

• Install Visual Studio 2019 - Azure Synapse Analytics | Microsoft Learn
• Source Control Integration - Azure Synapse Analytics | Microsoft Learn

Or

• Liquibase | Quickstart | Get up and running in minutes (Not Microsoft supported)

Let's focus to Microsoft proposal:

• I've Visual Studio enterprise with latest updates
  • Including Data Workload features
• Connected to Synapse with success and imported existing data to it
• Immediately tried to do a Build
  • Failed due to missing references on system objects
    • Adding master as database reference solved (this is a bug for me, but ok)
• Tried a simple schema compare
  • Failed because of different source and target types
    • Database project assumed Microsoft SQL Server 2019 or Azure SQL Managed instance, Target of course is Synapse/Datawarehouse
    • Changed project to Target Datawarehouse, build immediately fails with unsupported data types and so many errors that I can't remember
      • Data types likes images, ntext, that I don't even use, but system views use,(Microsoft WTH ???)
      • Of course schema compare was not possible
• Ok, let's try then to generate a publish preview... with no luck because of, previous points.

So, I was not even able to get to the CI/CD piece...

My perception, development is clearly not finished, and having a proper development environment that includes change management, that should be part of any MVP in a project, not happening here.

Synapse was released more than 1 year ago and we still don't have stable and fully development platform for it.

I hope someone here knows someone in PG, that can can actually answer to a simple question:

• How can one develop a change, build it with proper Target type and finally package it.

The CI/CD fortunately I can handle well.

Another thing I checked, this article...

Answer 1

Hello @paulocorreia ,

We just recieved the below response from the PG team .

For your question
How can one develop a change, build it with proper Target type and finally package it.
There are some projects settings which need to be taken care of Azure SQL Data Warehouse provides frictionless development using SQL Server Data Tools | Azure Blog and Updates | Microsoft Azure
• After you connected the with git, you can use git to do the source control of the schema definition. This link Source Control Integration - Azure Synapse Analytics | Microsoft Learn
• To deploy changes, you can refer to this article

In case if you think if you further question , if you have a support plan you may file a support ticket, else could you please send an email to azcommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.
Subscription ID:
Subject : Attn Himanshu
Please let me know once you have done the same.

Thanks
Himanshu

Answer 2

Btw,

I got confirmation this is a known issue. But I don't see it documented anywhere.

IIt's has been like 3 years the instructions were published. I hope proper priority is given, we're talking about change management of a DWH, it can't be left unsupported for so long.

I won't add anything else, because there's nothing else to argue with facts. The ball is with PG, I trust they will be reasonable enough to allocate proper priority.

Answer 3

Any updates on a proper development process with CI/CD pipelines? (I think the CI/CD pipeline part is actually solved with DACPAC deployments?)

I'm struggling to come up with a good development workflow, certainly no branching is possible as you can't run local instances, so any change needs to be deployed to test. That kinda makes you question why you'd bother with the hassle of SSDT projects, other than for the source control integration. You could use SSMS or another tool (?) to build you DB objects then just bring them into your SSDT project with schema compare.

But also facing similar issue with that, where SSDT doesn't seem to support Azure Synapse Dedicated Pools very well, too many bugs and things that don't work properly.

Really need some proper guidance from MS as to what the proper process should be here. There's clearly been some consideration for development workflows in the Synapse Workspace environment, but seems like they forgot everything around the database management (dedicate pools, serverless pools and data lake storage!)