question

pcportugal avatar image
1 Vote"
pcportugal asked Cameron-0825 answered

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

Hi all,

before anyone sends me links to Microsoft Docs 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:

Or

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...



azure-synapse-analytics
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @pcportugal ,
Thanks for the ask and using the Microsoft Q&A platform .
I have reached out to the internal PG team and will update you once I hear back from them .
Thanks
Himanshu

0 Votes 0 ·
HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT converted comment to answer

Hello @pcportugal ,

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 Docs
• 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




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @HimanshuSinha-MSFT

I appreciate your reply.

And all the links PG shared with you I already know, and I've proofs as the presented solution is currently not working.

Using target type as Microsoft DataWarehouse leads to incompatible data types error because of system views (This needs review ASAP by the way). No build possible

If I choose datawarehouse, schema compare will work partially but then… all projects fails with errors an no dacpac, sql script can be generated.
The list belows tells me there's something wrong with Synapse support, even adding master database as reference, won't solve, in fact will ad more issues.

![122620-image.png][1]


The deployment part from CI/CD is OK for me, as long a dacpac can be generated, but for that, the main issue needs to be solved.

In my opinion customer should not need to request a support plan when there are issues with the product to solve.


0 Votes 0 ·
image.png (258.3 KiB)
pcportugal avatar image
0 Votes"
pcportugal answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cameron-0825 avatar image
0 Votes"
Cameron-0825 answered

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!)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.