How to create a copy of an existing Dedicated SQL Pool (formerly SQL DW)

Consider you have a Dedicated SQL Pool in an Azure Subscription and you need to create a copy of this resource to a newly created Azure Subscription under the same tenant. To be more precise, please refer to the below image.

If you ever be in that situation, you may follow the below steps to achieve your goal. These steps must be executed in Windows PowerShell.

1. $ErrorActionPreference = “Stop”
   • The $ErrorActionPreference variable specifies the action to take in response to an error occurring. The following values are supported:
     • SilentlyContinue — Don’t display an error message continue to execute subsequent commands.
     • Continue — Display any error message and attempt to continue execution of subsequence commands.
     • Inquire — Prompts the user whether to continue or terminate the action
     • Stop — Terminate the action with error.
   • In our case we set the variable to “Stop” by using the above command.
2. $SourceSubscriptionName=”Source_Subscription_Name”
   • Set your Source Subscription Name
3. $TargetSubscriptionName=”Target_Subscription_Name”
   • Set your Target Subscription Name
4. $TargetSubscriptionID=(Get-AzSubscription -SubscriptionName $TargetSubscriptionName).id
   • Get the Id of the Target Subscription and store in the variable $TargetSubscriptionID
5. $SourceResourceGroupName=”Source_Resource_Group”
   • Set the Source Resource Group Name
6. $TargetResourceGroupName=”Target_Resource_Group”
   • Set the Target Resource Group Name
7. $SourceServerName=”SourceSQLServerName”  # Without database.windows.net
   • Set the Source SQL Server Name without database.windows.net
   • For example if the server name is “sourceserver.database.windows.net”, just specify “sourceserver”.
8. $TargetServerName=”TargetSQLServerName” # Without database.windows.net
   • Set the Target SQL Server Name without database.windows.net
   • For example if the server name is “targetserver.database.windows.net”, just specify “targetserver”.
9. $SourceDatabaseName=”SourceDatabaseName”
   • Set the Source Database Name
10. $TargetDatabaseName=”TargetDatabaseName”
    • Set the Target Database Name
11. $location = “Region”
    • Set the Region
12. $Environment=”Target_Environment_Name”
    • Set the Target Environment Name
    • You can set any name as you see fit
13. $TargetAdminUser = “Target_Admin_User”
    • Set the Target Admin User
14. $TargetAdminPassword = -join ((65..90) + (97..122) | Get-Random -Count 14 | % {[char]$_}) + ‘.’ + $(Get-Random 100)
    • Generate the Target Admin User Password
15. $TargetAdminPassword = ConvertTo-SecureString “$TargetAdminPassword” -AsPlainText -Force
    • Convert the Target Admin User Password to a Plain Text
16. $SQLCreds = New-Object System.Management.Automation.PSCredential (“$TargetAdminUser”, $TargetAdminPassword)
    • Store the Target Admin User and Target Admin User Password
17. Set-AzContext -SubscriptionName “$SourceSubscriptionName”
    • This command is going to set the Subscription to the Source Subscription
18. New-AzSqlServer -ResourceGroupName “$SourceResourceGroupName” -ServerName “$TargetServerName” -Location “$location” -SqlAdministratorCredentials $SQLCreds
    • This command will create a new SQL Server in the Source Resource Group with the name as mentioned in the variable $TargetServerName
19. Set-AzContext -SubscriptionName “$TargetSubscriptionName”
    • This command is going to set the Subscription to the Target Subscription
20. Set-AzKeyVaultSecret -VaultName “TargetKeyVaultName” -Name “DWH-Admin-Password” -SecretValue $TargetAdminPassword
    • The above command is to save the Target Admin User Password to the Target Key Vault under the name DWH-Admin-Password
21. Set-AzContext -SubscriptionName “$SourceSubscriptionName”
    • This command is going to set the Subscription to the Source Subscription
22. $SourceDatabase = Get-AzSqlDatabase -ResourceGroupName $SourceResourceGroupName -ServerName $SourceServerName -DatabaseName $SourceDatabaseName
    • This command is to get the specific database to restore
23. $PointInTime = ((Get-AzSqlDatabaseRestorePoint -ResourceGroupName “$SourceResourceGroupName” -ServerName “$SourceServerName” -DatabaseName “$SourceDatabaseName”).RestorePointCreationDate)[-1]
    • The above command is to pick the most recent restore point using RestorePointCreationDate “xx/xx/xxxx xx:xx:xx xx
24. Restore-AzSqlDatabase -FromPointInTimeBackup -PointInTime “$PointInTime” -ResourceGroupName “$SourceResourceGroupName” -ServerName “$TargetServerName” -TargetDatabaseName “$TargetDatabaseName” -ResourceId $SourceDatabase.ResourceID
    • The above command is to perform Restore to the specified target server in the SOURCE subscription
25. Move-AzResource -DestinationResourceGroupName $TargetResourceGroupName -ResourceId (Get-AzResource -ResourceGroupName “$SourceResourceGroupName” -ResourceName “$TargetServerName”).ResourceId -DestinationSubscriptionId $TargetSubscriptionID -Force
    • Move to Target Subscription and RG

Few Points to Note:

1. You do not have to create either the SQL Server or the SQL Data Warehouse in the Target Subscription. The above code snippet will automatically create those resources.
2. The logic above creates the SQL server and SQL Database in the Source Subscription and then moves them to the Target Subscription

---

ITECHSTORECA

FOR ALL YOUR TECH SOLUTIONS