Synapse Dedicated: DW_COMPATIBILITY_LEVEL & MCD hash distribution

[dzsquared]

Discussed in #220

^{Originally posted by brain246 February 10, 2023}
Hi there!

Not sure if this is the right place to ask/discuss this, but i am currently developing a Synapse dedicated pool database with VS2022/Data-Tools/SQl-Database-Project.

Since we had performance issues with some tables, i set database scoped configuration setting "DW_COMPATIBILITY_LEVEL" to 9000. That enables (since August 2022) the multi column hash distribution feature, meaning since half a year this feature is already in public preview. In our project i cannot live without it anymore 😄

Official announcement

Now that setting is neither available in the VS project (as database setting), nor can i set it in a (build) script myself as this leads to build & intellisense errors.

When i do a schema compare & update from dev-database to the project, the multi column distribution code is written correctly to the local project files. However this results, again, in intellisense and build errors.

Is my assumption correct, that you will deal with this, as soon as it is out of public preview and becomes a regular feature? Or is there anything i am missing and it should work already? Maybe anyone knows a workaround? 🤣

Have a nice weekend,
Thomas

[jitheshksn]

Waiting for this. When will it be ready? We are facing issue with DACPAC 9 (sqlproj)

[fabiocarello]

Sorry, has this been implemented? I’m experiencing the same issue as the original users who posted. I’m developing a SQL Project using SSDT on Visual Studio 2022. Do I need to manually update any tool or dependency?

Thanks!

[microsoftprince]

Hi Can you please install the SQLPackage (Version number: 162.1.172) ? https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16 regards, Ela From: fabiocarello ***@***.***> Sent: Thursday, January 18, 2024 3:58 AM To: microsoft/DacFx ***@***.***> Cc: Elavarasan Dhayalan (Quadrant Resource LLC) ***@***.***>; Manual ***@***.***> Subject: Re: [microsoft/DacFx] Synapse Dedicated: DW_COMPATIBILITY_LEVEL & MCD hash distribution (Issue #224) Sorry, has this been implemented? I'm experimenting the very same issue as the original users posting. I'm developing a SQL Project using SSDT on Visual Studio 2022. Does I need to manually update any tool or dependence? Thanks! - Reply to this email directly, view it on GitHub<#224 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/A7X3P5MXI3WI2S6RF23USMLYPEE6LAVCNFSM6AAAAAAU4A2A22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOJYGM2DGNBWG4>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.******@***.***>>

[OleksiiKostyniuk]

Hi, guys!

I'm struggling with the same issue in Azure DWH (Synapse Analytics).
When I try to create a multi-column distributed table, I receive the following error in the MS Visual Studio 2022:

CREATE TABLE [test].[clients]
(
	[client_id] [int] NOT NULL,
	[client_external_id] [int] NOT NULL,
	[client_name] [nvarchar](100) NOT NULL
)
WITH
(
	DISTRIBUTION = HASH([client_id], [client_external_id]),
	HEAP
)

SQL71657: Cannot create a multi-column distributed table.

I'm using Visual Studio 2022 Community Edition, and I've installed SQLPackage (Version number: 162.1.172) as suggested earlier, but without success.

In the project properties, I've tried to set the following: Project Settings (Target platform -> MS Azure Data Warehouse) -> Database settings -> Miscellaneous -> Compatibility level = SQL Server 2017 (140).
However, this doesn't help either.

Should I manually update any tools or dependencies in the project to fix this issue?

On the DWH side, this script creates a table without any issues.

I would appreciate any help or suggestions.

Best regards,
Alex

[microsoftprince]

Hi , We have fixed the issue and will be releases on Feb 13 (VS 17.10.0 Preview 1) Please find the workaround for MCD as follows: Option 1: MCD Publish using SQLPackage.exe 1. Download Latest SQLPackage.exe (Version 162.1.167.1) dotnet tool install -g microsoft.sqlpackage 2. Extract Dacpac from Dev Server (Source) SQLPackage /Action:Extract /TargetFile:{filename}.dacpac /DiagnosticsFile:{logFile}.log /SourceConnectionString:"Server={Server},1433;Initial Catalog={DatabaseName};Persist Security Info=False;User ID={UserName};Password={Password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" 3. Publish Dacpac to Prod Server (Destination) SQLPackage /Action:Publish /TargetFile:{filename}.dacpac /DiagnosticsFile:{logFile}.log /SourceConnectionString:"Server={SourcerServer},1433;Initial Catalog={DatabaseName};Persist Security Info=False;User ID={UserName};Password={Password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" SqlPackage /Action:Publish /SourceFile:{filename}.dacpac /TargetConnectionString:"Server={DestinationServer},1433;Initial Catalog={DatabaseName};Persist Security Info=False;User ID={UserName};Password={Password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" Option 2: – SSDT Visual studio 2022 Preview-3 1. Create a new Database project. 2. Import Database (from source server) 3. If need modify script 4. Publish to Destination Server Note: In source DW need to set COMPATIBILITY to 9000. Please find the script for source as follows: Find the current DWCompatibilityLevel value

…

------------------------------------------------------ SELECT [dbscdw].value AS [DWCompatibilityLevel] FROM [sys].[databases] [db] WITH (NOLOCK) LEFT JOIN [sys].[database_scoped_configurations] AS [dbscdw] WITH (NOLOCK) ON [dbscdw].[name] = N'DW_COMPATIBILITY_LEVEL' WHERE [db].[name] = DB_NAME() Set the the DWCompatibilityLevel value to 9000 - MCD

------------------------------------------------------ ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 9000 Sample MCD Table

------------------------------------------------------ CREATE TABLE TableMCD_HASH ( [id] int NOT NULL, [lastName] varchar(20) NULL, [zipCode] varchar(6) NULL ) WITH(DISTRIBUTION = HASH ([id],[zipCode])); Regards Ela

________________________________ From: OleksiiKostyniuk ***@***.***> Sent: Friday, January 19, 2024 5:56 AM To: microsoft/DacFx ***@***.***> Cc: Elavarasan Dhayalan (Quadrant Resource LLC) ***@***.***>; Manual ***@***.***> Subject: Re: [microsoft/DacFx] Synapse Dedicated: DW_COMPATIBILITY_LEVEL & MCD hash distribution (Issue #224) Hi, guys! I'm struggling with the same issue in Azure DWH (Synapse Analytics). When I try to create a multi-column distributed table, I receive the following error in the MS Visual Studio 2022: CREATE TABLE [test].[clients] ( [client_id] [int] NOT NULL, [client_external_id] [int] NOT NULL, [client_name] [nvarchar](100) NOT NULL ) WITH ( DISTRIBUTION = HASH([client_id], [client_external_id]), HEAP ) SQL71657: Cannot create a multi-column distributed table. I'm using Visual Studio 2022 Community Edition, and I've installed SQLPackage (Version number: 162.1.172) as suggested earlier, but without success. In the project properties, I've tried to set the following: Project Settings (Target platform -> MS Azure Data Warehouse) -> Database settings -> Miscellaneous -> Compatibility level = SQL Server 2017 (140). However, this doesn't help either. Should I manually update any tools or dependencies in the project to fix this issue? On the DWH side, this script creates a table without any issues. I would appreciate any help or suggestions. Best regards, Alex — Reply to this email directly, view it on GitHub<#224 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/A7X3P5JVYMWPCD36FJV26ADYPJ3PZAVCNFSM6AAAAAAU4A2A22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMBQGQ3DQOJUHA>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[pscorca]

Hi,
I've updated my Visual Studio 2022 with SSDT to 17.9.2 version and I've created a new database project: I cannot change the DW compatibility level yet for Azure SQL Data Warehouse as a target platform.
The Visual Studio 2022 version 17.9 Release Notes documentation says that the 17.9.0 version has the "support for MCD (Multi-Column Distribution) in SSDT".
Now, how could I change the DW compatibility level inside a Visual Studio 2022 database project for a Synapse dedicated SQL pool? Thanks

[dzsquared]

@pscorca - the DW compatibility level is surfaced in Visual Studio 2022 as a database setting, in the screen where you set the target platform there should be a button for "Database Settings". In the operational tab in that dialog is the DW compatibility level.