Today we are going to discuss on how to clone any lower environment dedicated SQL pool with the upper environment dedicated SQL pool or vice versa within a subscription.
There could be the situation if you have lower environments which does not get refreshed every day, but you must clone them with the PROD version of data in some time interval.
Dedicated SQL Pool is formerly known as SQL DW
What does clone means? Actually, you create a new dedicated SQL pool or you restore with the same data or object from the source dedicated SQL Pool. In other term, copying a dedicated SQL pool and creating a new dedicated SQL pool with the same configuration.
What does restore means? You are taking the backup of the original DB of dedicated SQL pool and restoring in the same subscription.
Cloning and restoring DB is provides the same process, hence we can refer it as either cloning or restoring DB.
Let’s say you have a DEV environment which needs to be cloned using PROD environment database. You will need a snapshot of the DB so that it can be restored.
When you Create/Restore dedicated SQL pool in synapse workspace / Synapse Studio, then you can see three options.
<<Navigate yourself to the synapse studio > click on Manage tab > click on SQL Pool>>Click on ADD
In the basics tab you need to provide the Database name and its compute capacity while on the other tabs you can see the option shown in the pic 1.
- You can just create an empty DB.
- You can select the Backup option and mention the latest backup taken by the Azure and create the DB.
- You can select Restore Point option.
We will discuss second and third point because cloning of Restoration is done using these two options. The first option if to create a brand new Dedicated SQL pool new.
Restore Using BACKUP Option
If you want to have the database with the same name then either delete the old DB in target workspace or you need to rename the old DB so that you no need to delete the existing DB.
By default, the dedicated SQL pool takes an automatic backup recursively after every 8 hours. If you select this option, then you can see all the workspace in the subscription which has the dedicated SQL pool. It always shows the most recent taken backup for each resource group, you choose anyone from the list and you will have the new dedicated SQL pool with the same property inherited from that dedicated SQL pool.
It will not ask any other details, you can mention you tag <<Tag is just identifier of your resource, like you can mention for which team it belong, what is the purpose of having this>> and finally click on Review + create.
Restore using Restore Point option.
The second option is to create Restore Point. There are two methods through which you can see the Restore points. Dedicated SQL pool creates a restore point every 7 days. You can see two sub options underneath Restore Point.
You will need to specify the workspace name and the database name from which you want to clone. There are two types of Restore Point.
- Automatic Restore points – These Restore points gets created every 7 days and it always holds two automatic created restore point, so that we can either choose the latest or the week back restore point. You can select anyone of the restore points and click on Review + create.
- User Defined Restore Point – << navigate to the target dedicated SQL Pool > In the overview tab you can see the option to create New restore point>> This Restore point needs to be created manually by going to the source Dedicated SQL Pool and click on the option (highlighted in the below screenshot in red) and give the name of the Restore point.
You need to provide the restore point name.
Once you have created a Restore Point, you can select them from the drop down.
You can assign some tags as per your requirement and click on Review + create.
The Restore Point/Backup is like the snapshot of the DW at that particular point, these restore point/Backup holds the backup of the DW along with its object. It is like you have BACPAC file. These Restore points gets deleted after 7 days from the date of its creation.
Note: Once you click on “Create” it takes some time depending on the volume of the Data in the Pool to show on the pane. You can run your environment configuration scripts after it comes back online.
