What's the difference between Azure Synapse (formerly SQL DW) and Azure Synapse Analytics Workspace

[Guest stevehow]

There has been confusion for a while when it comes to Microsoft Docs and the two distinct sets of documentation for dedicated SQL pools. When you do an internet search for a Synapse related doc and land on Microsoft Docs site, the left-hand navigation has a toggle switch between two sets of documentation.

 

 

 

 

 

 

 

 

 

You will 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”.

 

 

 

This blog post is intended to help explain these modalities. It is not intended to discourage you from letting us know when ambiguity in our docs should be corrected.

 

 

 

In the beginning, there was…

 

 

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.

 

 

 

Side Note: Historians will 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 DW adopted the constructs of Azure SQL DB such as a logical server where administration and networking is controlled. SQL DW could exist on the same server as other SQL DBs.

 

 

logical diagram, for illustration purposes only

 

 

 

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 and therefore to meet customers where they are at in the journey, we made a paradigm shift in how data warehousing would be delivered.

 

 

 

…and then came Synapse Analytics

 

 

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. Note the endpoint DNS change.

 

 

 

 

 

 

This was a big change and with a lot of additional capabilities. The whole platform received a fitting new name: Synapse Analytics.

 

 

 

But what about all the existing SQL DWs? Would they just automatically become Synapse Workspaces?

 

 

 

Rebranding and Migration (and the two names you still see in docs)

 

 

SQL DW instances were not just automatically upgraded to Synapse Analytics workspaces. Many factors play into big platform upgrades, and it was best to allow customers to opt-in for this. Azure SQL DW was rebranded as “Dedicated SQL pool (formerly SQL DW)” with intention to create clear indication that the former SQL DW is in fact the same artifact that lives within Synapse Analytics.

 

 

 

From our docs:

 

Note: In product documentation and in blogs, you will also see “Dedicated SQL pool (formerly SQL DW)” sometimes referred to as “standalone dedicated SQL pool” as makes sense when looking at the above diagram.

 

 

 

Migration of a dedicated SQL pool (formerly SQL DW) in relative terms is easy. Just a few clicks from the portal. However, it isn’t quite a full migration from what is on the left of the above diagram to what is on the right. There is a subtle difference which is noticed from the toast that pops up in the portal.

 

 

 

 

The migration doc is Enabling Synapse workspace features - Azure Synapse Analytics | Microsoft Docs

 

 

 

In a migration, the dedicated SQL pool (formerly SQL DW) never really is migrated. It stays on the logical server it was originally on. server-123.database.windows.net never becomes server-123.sql.azuresynapse.net. Customers that “upgraded” or “migrated” a SQL DW to Synapse Analytics still have a full logical server that could be shared with Azure SQL DBs.

 

 

 

The Migrated SQL DW and Synapse Workspace

 

 

The upgrade or migration path described above is connected to a Synapse workspace. Migrated customers should use documentation in “dedicated SQL pool (formerly SQL DW)” for dedicated SQL pool scenarios. All of the other components of Synapse Analytics shown above would be accessed from the Synapse Analytics documentation.

 

 

 

A quick way to visualize this as a “blend” of all the additional Synapse Analytics workspace capabilities and the original SQL DW is below.

 

 

 

 

New Synapse Analytics Workspaces

 

 

 

If you never migrated a SQL DW as shown above and you started your journey with creating a Synapse Analytics Workspace, then you simply use the Synapse Analytics documentation.

 

 

 

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 leverages a logical server that is the same as Azure SQL DB uses. There is a shared PowerShell module called 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 is no need to include an “Edition” parameter as it’s exclusively used for Synapse artifacts.

 

 

 

These two modules ARE NOT equal in all cases. There are some actions that can be done in Az.Sql that cannot 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 (Restore-AzSqlDatabase).

 

 

 

Conclusion

 

 

There are two sets of documentation for dedicated SQL pools on Microsoft Docs. One for dedicated SQL pool (formerly SQL DW) and one for dedicated SQL pools in Synapse workspaces. You can use the left-hand navigation to determine which set of documentation you are currently in as well as any warning/note prompts in the document itself. Many other reference docs will apply to both, one or the other. Within each doc, the "Applies To" line or helpful notes throughout should make it clear what platforms a doc covers.

 

 

 

 

 

Hopefully, with the information above you will be able to sort through which documentation applies to your Synapse Analytics environment.

 

 

 

Do you have suggestions on how we can improve the ambiguity in our documents between dedicated SQL pool implementations? If so, please post them in the comments.

 

Continue reading...