Restore dedicated SQL pools in same Azure Synapse Analytics

-> Open the required Dedicated SQL pool,

-> On the overview Tab. Click on Restore,

-> On the Restore page. Select appropriate Restore point type. More details can be viewed in this article.

Automatic Restore Points

Snapshots are a built-in feature that creates restore points. You do not have to enable this capability. However, the dedicated SQL pool should be in an active state for restore point creation. If it is paused frequently, automatic restore points may not be created so make sure to create user-defined restore point before pausing the dedicated SQL pool. Automatic restore points currently cannot be deleted by users as the service uses these restore points to maintain SLAs for recovery.

Snapshots of your data warehouse are taken throughout the day creating restore points that are available for seven days. This retention period cannot be changed. Dedicated SQL pool supports an eight-hour recovery point objective (RPO). You can restore your data warehouse in the primary region from any one of the snapshots taken in the past seven days.

User-defined restore points

This feature enables you to manually trigger snapshots to create restore points of your data warehouse before and after large modifications. This capability ensures that restore points are logically consistent, which provides additional data protection in case of any workload interruptions or user errors for quick recovery time. User-defined restore points are available for seven days and are automatically deleted on your behalf. You cannot change the retention period of user-defined restore points.

-> In my case the newest restore point is on 20 March 2021 as I pause it frequently. Hence I will be using “User-defined restore points”,

-> User-defined restore point gets created first.

-> Time taken to complete the restoration is based on the size of the DW database.

-> Database will be visible as below after the restoration.

Restore dedicated SQL pools in different Azure Synapse Analytics

-> Requirement is to restore the dedicated SQL pool jbdw that is on East US to DR Location East US2.

-> From Azure Portal, create a new Dedicated SQL pool,

-> Choose appropriate settings in Networking Tab.

-> Select appropriate backup from “Additional settings” tab,

-> Dedicated SQL pool deployment failed with below error,

{

    “status”: “Failed”,
    “error”: {
        “code”: “InternalServerError”,
        “message”: “An unexpected error occured while processing the request. Tracking ID: ‘73821580-61b1-490b-a1d8-7c51f467a81b'”    
}
}

-> Couldn’t understand much from above error,

-> Opened monitor from Azure portal,

-> Opened “Activity Log” blade. Changed filter “Event Severity” to include all except “Informational,

 “statusMessage”: “{\”status\”:\”Failed\”,\”error\”:{\”code\”:\”ResourceOperationFailure\”,\”message\”:\”The resource operation completed with terminal provisioning state ‘Failed’.\”,\”details\”:[{\”code\”:\”InternalServerError\”,\”message\”:\”An unexpected error occured while processing the request. Tracking ID: ‘827d9a38-1ac4-444b-a766-d2722443c259’\”}]}}”,

-> It seems like Transparent Data Encryption is enabled with a “Customer-Managed key” on jbdw. Lets try configuring jbdw-dr also with the same key and then try a restore.

-> Create an Azure key vault on DR location East US2.

-> Complete “Access policy”, “Networking” as appropriate and then click on “Create”.

-> Open key vault associated with jbdw that is on location East US. Click on “Keys” under settings and then locate the key used by jbdw. In our case it is jbdwkey,

-> Click on the key and click “Download Backup”. Save the file in appropriate location,

-> Open key vault in East US 2 that is created for jbdw-dr. Click on keys and then select “Restore Backup”. Locate the key that was saved in previous step and select it,

-> Create a “Dedicated SQL pool (formerly SQL DW)” in East US 2 location,

-> Select appropriate value for “Networking”.

-> Click “Review + Create”,

-> Once created. Open Dedicated SQL pool jbdw-dr and then got to underlying server jbdw-dr as show below,

-> Select the key vault created for jbdw-dr and select the restored key,

-> Now we are ready to restore the backup from jbdw to jbdw-dr. Create a new dedicated SQL pool (formerly SQL DW) and follow below instructions,

-> Click “Create” after checking other tabs.

-> It completed fine with time,

-> Checking SQL Server Management Studio to see if the database is created,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.