Introduction

In the world that is best practice and technical limitation, I always strive to live to best practice. It’s a pet peeve to let technical limitation drive me into poor cloud adoption practices. The first company I worked for growing up was a high-end residential painting company with a simple slogan, “do it right the first time.” That has always stuck with me through my career, and while finding the ‘right’ way to do something and working through the challenges limitations impose often don’t jive, I love to believe that there’s a solution for everything.

The Problem

Take for example the limitations of performing a lower region refresh and Azure Synapse. Specifically, the limitation that is the inability to perform a cross subscription restore of a Synapse pool. In my opinion, this is a huge issue when it comes to Azure Synapse, the Cloud Adoption Framework for enterprise scale, and a guiding principle that is segmenting production and non-production workloads, access, and security through subscription management.

Create your initial Azure subscriptions - Cloud Adoption Framework | Microsoft Docs

If you can’t perform cross subscription restore of your Synapse pool, then how do you manage the separation of production and non-production environments, security, policies, etc. and on occasion refresh your stale lower environment (dev, qa, stage) with masked production data? If you ask around the community or product group regarding this limitation, you get several responses and approaches.

Vote on the requested feature….. LOL

Yes, the product group does well to review responses to requested features and work to make improvements in their platform. However, they also get bombarded with more features requests than a team can possibly implement. VOTE AWAY, but don’t expect this one to just pop up tomorrow. It’s been a requested feature for 3 years. So, what do you do in the interim while you find ways to vote through the many different accounts you have?

Do a data migration using ADF and DDL migrations. HMM

Well, that has its challenges, plus does anyone want to manage that solution for an ever-changing database that might be 100tb+ cross subscription? This is essentially performing a full data warehouse migration project for each request of a lower region refresh that comes across your backlog. Yes, we have pipeline templates, DDL tools and scripts for this, but even we don’t want to be in this business.

Place both Synapse Workspaces in the SAME subscription!

I think we know how I feel about this. Mixing non-prod and prod isn’t ideal for a multitude of scenarios and best practices. Security, RBAC, Compliance, principal, all the above? I’m not willing to introduce risk in those areas by placing non-prod and prod workloads in the same subscription. Yes, it’s feasible but not best practice and in many circumstances, Synapse is NOT going to win the battle of exceptions within the organizational cloud adoption framework. Building an efficient and scalable cloud landing zone trumps taking the easy way out. Never let short cuts win when it comes to foundational practices that allow your data landing zone on Azure to scale at an enterprise level.

Looking for Clues

Until recently, the documentation simply said something like the following:

“If you need to directly restore across subscription, vote for this capability here. Cross subscription restore is not supported”.

That has since changed to:

“If you need to directly restore across subscription, vote for this capability here. Restore to a different server and 'Move' the server across subscriptions to perform a cross subscription restore.”

Backup and restore - snapshots, geo-redundant - Azure Synapse Analytics | Microsoft Docs

Hey GREAT!! Well, how the heck exactly do you do that with Azure Synapse Workspace?!?!?! You cannot find those details anywhere, and you can’t POINT & CLICK your way through this either. Just try it, there is no dialog for moving the entire service across subscriptions.

Could you move the whole resource group? It doesn’t appear that you could.

Where do you turn when there’s no reference and no UI to do the job? Azure PowerShell, a wild guess, and some experience! But what’s your best vehicle? Can you even take an Azure Synapse workspace and move cross subscription? Maybe. I have tried, and it’s not always a great experience because Synapse is so tied to all its supporting resources like ADLS gen2, SQL, Spark, Workspace components, RBAC, etc. Additionally, provisioning of a Synapse workspace is difficult because of all the supporting services takes a bit more time and introduces just more points of failure.

Solution

A Synapse Dedicated Pool is considered a SQL DB server and database behind the scenes. However, there are implementation differences between Synapse DEP and SQL DB OFR, which is why Synapse cross subscription restore is not supported.

Might a better vehicle for cross subscription restore be that which was the host for SQL DW? An Azure SQL Database Server? Let’s find out.

What does using an AZ SQL Database server as the lower region restore look like?

1. Create an AZ SQL Migration Vehicle Server

If you prefer you can do this part manually. Otherwise, a small bit of PowerShell does the trick. Fill in the variables as you like.

$migrationServer = New-AzSqlServer -ResourceGroupName $MigrationResourceGroupName -Location "East US2" -ServerName $MigrationServerName -ServerVersion "12.0" -SqlAdministratorCredentials $cred

2. Take a restore point of your Synapse dedicated pool

Same goes for #2 as in #1, do manually in the UI or another one liner here with some more variable inputs works fine and dandy.

New-AzSynapseSqlPoolRestorePoint -ResourceGroupName $ResourceGroupName -WorkspaceName $ServerName -Name $DatabaseName -RestorePointLabel ‘blogMigration’

3. Restore your Synapse workspace dedicated pool to Az SQL Server with some trickery

What is that trickery? This part you cannot do through the UI yet. Therefore, you are forced to perform via code. Luckily, if you live in documentation enough you’ve grabbed onto a few clues that caught my own attention. Specifically, in the restore documentation for Synapse dedicated pools, they don’t (well they didn’t, this documentation has been removed at some point and I can no longer find it) use Synapse PowerShell in their example. They use the AzSql module and do some tricks with renaming of the database id between workspaces and sqlPools to servers and databases.

So, once you manage a quick fix of the dedicated pool type to work with azure sql, you can then fall back to previous practices and use the AzSql command to restore your Synapse workspace dedicated pool to an Azure sql instance.

$pool = Get-AzSynapseSqlPool -ResourceGroupName $ResourceGroupName -WorkspaceName $ServerName -Name $DatabaseName

$databaseId = $pool.Id -replace "Microsoft.Synapse", "Microsoft.Sql" `

   -replace "workspaces", "servers" `

   -replace "sqlPools", "databases"

# Get the latest restore point that was just created in BACKUP

$restorePoint = $pool | Get-AzSynapseSqlPoolRestorePoint | Select -Last 1

$RestoredDatabase = Restore-AzSqlDatabase –FromPointInTimeBackup –PointInTime $restorePoint.RestorePointCreationDate -ResourceGroupName $MigrationResourceGroupName -ServerName $MigrationServerName -TargetDatabaseName $MigrationDatabaseName –ResourceId $databaseId

Update Note: The trickery of renaming isn’t required. It did work with or without the naming type change. However, had it not been for that documentation reading the way it did, this thought would have not been seeded deep enough to trigger an idea.

4. MOVE your AZ SQL Server across subscription using resource move operations

Now we’re back to the easy button. UI or PowerShell with some variables.

Move-AzResource -DestinationSubscriptionId $DestSubscription.Id -DestinationResourceGroupName $DestResourceGroupName -ResourceId $migrationServer.ResourceId -Force

The rest is flip and reverse it or the inverse of what you just did.

5. Take a restore point of your recently moved AZ SQL Warehouse Pool

New-AzSqlDatabaseRestorePoint -ResourceGroupName $DestResourceGroupName -ServerName $MigrationServerName -DatabaseName $MigrationDatabaseName -RestorePointLabel ‘mydestinationrestorepoint’

6. Restore your AZ SQL Warehouse Pool to your lower regions AZ Synapse Workspace

Restore-AzSynapseSqlPool -FromRestorePoint -RestorePoint $restorePoint.RestorePointCreationDate -Name $DestDatabaseName -ResourceGroupName $DestResourceGroupName -WorkspaceName $DestServerName -ResourceId $database.ResourceId -PerformanceLevel $PerformanceLevel

7. Drop your AZ SQL Migration Server

Remove-AzSqlServer -ResourceGroupName $DestResourceGroupName -ServerName $MigrationServerName -Force

8. Validate the move

Cross Subscription Restore Input:

Cross Subscription Restore Output:

Looks good from my house!

Conclusion

How does this perform in action? Fairly well. We scripted this out in Az PowerShell and moving an 8tb warehouse took around 1 – 1.5 hours on several test runs. Comparably, when this warehouse was on-prem the same process (lower region restore) took 4-8 hours. The physical migration of this warehouse had taken 24+ hours in some instances and we had approached the migration with a variety of approaches including polybase, data factory copy activities, bcp and C#. So, we experienced an overall win in this case, and in all supporting cases, where you have a Synapse dedicated pool segregated by non-prod and prod workloads by subscription segmentation which follows best practices for cloud adoption and scale.

If you have any questions surrounding this topic or want to discuss it further, please contact us today!

Disclaimer: Upon publishing of this blog and the release, Microsoft release a similar blog Synapse Cross-Subscription Restore (microsoft.com) regarding the cross subscription restore support for a dedicated pool within a SQL server. This further simplifies the process as you can now exclude the ‘move’ of resources in the process and restore directly from the SQL migration server to the destination subscription Synapse workspace.