Difference between Azure Synapse (formerly SQL DW) and Azure Synapse Analytics workspaces
Originally posted as a techcommunity blog at: https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/what-s-the-difference-between-azure-synapse-formerly-sql-dw-and/ba-p/3597772
There has been confusion for a while when it comes to Azure Docs and the two distinct sets of documentation for dedicated SQL pools. When you do an internet search for an Azure Synapse related doc and land on Azure Docs site, the Table of Contacts has a toggle switch between two sets of documentation.
This article clarifies which documentation applies to your Synapse Analytics environment.
| Azure Synapse Analytics | Dedicated SQL pools (formerly SQL DW) |
|---|---|
|
|
You'll also see notes in many docs trying to highlight which Synapse implementation of dedicated SQL pools the document is referencing.
Dedicated SQL pools exist in two different modalities
Standalone or existing SQL Data Warehouses were renamed to "dedicated SQL pools (formerly SQL DW)" in November 2020. Ever since, dedicated SQL pools created within Synapse Analytics are "dedicated SQL pools in Synapse workspaces."
Circa 2016, Microsoft adapted its massively parallel processing (MPP) on-premises appliance to the cloud as "Azure SQL Data Warehouse" or "SQL DW" for short.
Historians remember the appliance was named parallel data warehouse (PDW) and then Analytics Platform System (APS) which still powers many on-premises data warehousing solutions today.
Azure SQL Data Warehouse adopted the constructs of Azure SQL DB such as a logical server where administration and networking are controlled. SQL DW could exist on the same server as other SQL DBs. This implementation made it easy for current Azure SQL DB administrators and practitioners to apply the same concepts to data warehouse.
However, the analytics and insights space has gone through massive changes since 2016. We made a paradigm shift in how data warehousing would be delivered. As SQL DW handled the warehousing, the Synapse workspace expanded upon that and rounded out the analytics portfolio. The new Synapse Workspace experience became generally available in 2020.
The original SQL DW component is just one part of this. It became known as a dedicated SQL pool.
This was a big change and with more capabilities. The whole platform received a fitting new name: Synapse Analytics.
PowerShell differences
One of the biggest areas of confusion in documentation between "dedicated SQL pool (formerly SQL DW)" and "Synapse Analytics" dedicated SQL pools is PowerShell.
The original SQL DW implementation uses a logical server that is the same as Azure SQL Database. There's a shared PowerShell module named Az.Sql. In this module, to create a new dedicated SQL pool (formerly SQL DW), the cmdlet New-AzSqlDatabase has a parameter for Edition that is used to distinguish that you want a DataWarehouse.
When Synapse Analytics was released, it came with a different PowerShell module of Az.Synapse. To create a dedicated SQL pool in a Synapse Analytics Workspace, you would use New-AzSynapseSqlPool. In this PowerShell module, there's no need to include an "Edition" parameter, as it's exclusively used for Synapse.
These two modules ARE NOT equal in all cases. There are some actions that can be done in Az.Sql that can't be done in Az.Synapse. For instance, performing a restore for a dedicated SQL pool (formerly SQL DW) uses Restore-AzSqlDatabase cmdlet while Synapse Analytics uses Restore-AzSynapseSqlPool. However, the action to restore across a subscription boundary is only available in Az.Sql module with Restore-AzSqlDatabase.