Introduction:

As a Database or Data Warehouse administrator, it is crucial to back up and restore databases on a frequent basis. Azure Synapse Analytics, the data warehousing offering on the Azure cloud platform, provides different types of pools, including serverless SQL pools, dedicated SQL pools, and Spark pools. In this article, we will focus on dedicated SQL pools and explore the process of restoring them.

Creating a Restore Point:

Before we can restore a dedicated SQL pool, we need to create a restore point. A restore point is a checkpoint that allows us to revert to an earlier state of the pool if needed. In Azure Synapse Analytics, there are two types of restore points: automatic restore points and user-defined restore points. Automatic restore points are created by Azure Synapse Analytics every four to eight hours, while user-defined restore points are created by the user.

To create a user-defined restore point, navigate to the Azure Synapse workspace and open the dedicated SQL pool. From the toolbar, click on the “New restore point” link. Provide a name for the restore point and click “Apply”. The restore point will be created shortly.

Restoring a Dedicated SQL Pool:

Once we have a restore point, we can proceed with restoring the dedicated SQL pool. To do this, navigate to the Azure Synapse Analytics workspace account, open the dedicated SQL pool, and click on the “Restore” button from the toolbar. Select the restore point to which you want to restore the pool. You can choose from automatic restore points or user-defined restore points.

If you select a user-defined restore point, provide the name for the new dedicated SQL pool, select the desired restore point, configure the account and performance level, and click “Review + Restore”. Review the details and click “Create dedicated SQL pool – Restore database” to initiate the restoration process.

Monitoring the Restoration Process:

The restoration process may take some time depending on the volume of data and database objects in the pool. To monitor the state of restoration, you can connect to the Azure Synapse Analytics workspace account and check the status of the newly created pool. While the restoration is in progress, the status of the dedicated SQL pool will be shown as “restoring”.

Verifying the Restoration:

Once the restoration is complete, you can verify whether the new dedicated SQL pool has the same database objects as the original pool before the restore point was created. If everything is successful, the new pool should have the same data and objects as the original pool.

Conclusion:

In this article, we have learned how to restore dedicated SQL pools in Azure Synapse Analytics. By creating restore points and utilizing them, we can revert to earlier states of the pool and retrieve data and database objects. This process is crucial for database administrators to ensure data integrity and recover from any unwanted changes or deletions.