How to upgrade the SQL Server version on Dedicated SQL Pool?

Question

Hi,

I have a Dedicated SQL Pool as part of my Azure Synapse.

I was just checking the version and saw it was version [Microsoft SQL Azure (RTM) - 12.0.2000.8 May 22 2023 22:22:02 Copyright (C) 2022 Microsoft Corporation] for the system master database and version [Microsoft Azure SQL Data Warehouse - 10.0.20443.0 Mar 14 2023 04:45:46 Copyright (c) Microsoft Corporation] for the DataWarehouse which is a Dedicated SQL Pool. From what I've read this is SQL Server 2014 and SQL Server 2008 installed.

How can I upgraded these to SQL Server 2019? I've looked for documentation but all seem to be for MS SQL Server DBs and not reference to Dedicated SQL Pool.

Do I even need to do this? Or is it automatically done?

Thanks

Answer 1

Hello,

The only thing you can do in terms of upgrade is go to Gen2 as described here :

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/gen2-migration-schedule

Be aware that migration from Gen1 to Gen2 through the Azure portal is permanent. There is not a process for returning to Gen1.

If you have the c present in DW100c then it means you are already Gen2.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/upgrade-to-latest-generation

SQL Compatibilty level and versionning in Azure Synapse Analytics is not something that users typically need to worry about or manage .

More informations on thoses :

To programmatically determine the engine edition, use SELECT SERVERPROPERTY('EngineEdition'). This query will return '5' for Azure SQL Database, '8' for Azure SQL Managed Instance, and '6' or '11' for Azure Synapse :
https://learn.microsoft.com/en-us/sql/t-sql/functions/version-transact-sql-configuration-functions

The database engine version numbers for SQL Server and Azure SQL Database are not comparable with each other, and rather are internal build numbers for these separate products. The database engine for Azure SQL Database is based on the same code base as the SQL Server database engine. Most importantly, the database engine in Azure SQL Database always has the newest SQL database engine bits. Version 12 of Azure SQL Database is newer than version 15 of SQL Server.
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

This article helps understand a bit more about the history of the product : https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/what-s-the-difference-between-azure-synapse-formerly-sql-dw-and/ba-p/3597772

If this answer was helpful, please mark it as accepted so other users that have the same question can find this topic.

Reagrds

Answer 2

Hi Casey,

For synapse dedicated SQL pool, you can set DW_COMPATIBILITY_LEVEL using the below command.

Please see the supported DW_COMPATIBILITY_LEVEL

ALTER DATABASE SCOPED CONFIGURATION

SET DW_COMPATIBILITY_LEVEL = 9000

select* from sys.database_scoped_configurations

SELECT name, compatibility_level FROM sys.databases -- (My understanding is this compatibility level does not apply to synapse dedicated SQL pool)