Database copy for report work load

Question

Database copy for report work load

ashrukpm

SSC Journeyman

Points: 76
More actions
November 14, 2024 at 5:45 pm

#4481818

Hi Team,
I am looking for a an help, Please see my problem below.
I have a Azure MI. I would like to create a replica of two production databases of size 200GB, 40GB respectively to another MI in the same region, and it is to be used for reporting workload. I would like to sync the data only once every night, real time syncing not needed.
Which of following options would best work for me ?
snapshot geo-replication or Automating backup and restore? Do you have to suggest any option, other than these two?
I know we can ADF for syncing, but which needs development efforts. I am looking for a built-in option
Thanks,
Ashru
December 17, 2024 at 4:51 am

This was removed by the editor as SPAM
December 17, 2024 at 4:51 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

Since you don't need realtime, the cheapest and easiest way will be backup and restore. Nothing to it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ashrukpm SSC Journeyman Points: 76 More actions · Answer 2

Thank you Grant for your suggestion. Sorry for my late reply.

Can we use the T-SQL from agent jobs to restore database from the Azure automated backups to achieve the same? so that I don't need to run the backup explicitly. If you can share any article that explain it will be very nice.

Is the Azure CLI or PowerShell only supports this activity? can I use these options from Jobs?

Thanks,

Ashru

This reply was modified 3 weeks, 4 days ago by ashrukpm.

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 3

With Azure MI, yeah, you can restore using T-SQL. Microsoft documentation on it is pretty thorough. You will have to figure out how you want to secure your Azure blob storage, but that's in the docs too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ashrukpm SSC Journeyman Points: 76 More actions · Answer 4

Thank you Grant for your help.

I went through the article, it creates the backup first and use the same for restoring.

I was thinking if can we do the restore database from Azure built-in automatic backup for restoring the database. So that we don't need to run backup command own and no need to use storage either. Can we do that using T-SQL?

Thanks,

Asharaf

membrane Grasshopper Points: 14 More actions · Answer 5

ashrukpm wrote:

Thank you Grant for your help.
I went through the article, it creates the backup first and use the same for restoring.
I was thinking if can we do the restore database from Azure built-in automatic backup for restoring the database. So that we don't need to run backup command own and no need to use storage either. Can we do that using T-SQL?
Thanks,
Asharaf

If you're looking for a purely T-SQL-based approach, that isn't supported for automated backups because Azure doesn't expose these backups directly for T-SQL operations.

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 6

Pretty sure the answer to using the Microsoft backups is that your choices are:

Portal
Powershell
Azure CLI
REST API

That's per the documentation.

The issue is simple, that stuff is stored somewhere you don't have access to. So you have to go through the programmatic mechanisms of running a restore.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning