Backup directly to cloud storage for 1.5 tb database

Question

Post reply

Backup directly to cloud storage for 1.5 tb database

mohan.bndri

Old Hand

Points: 355
More actions
September 7, 2022 at 7:34 am

#4084768

Today we looking to take backup from on prem SQL to cloud storage in blob storage directly and DB size is 1.5 tb, network speed 100 mbps.
Yes, its possible to do backup directly to cloud storage blob. But want to know which method this copy / backup can work like AZ copy or any other method ? How it will work ? and how much the time it takes to complete the backup to blob storage / cloud storage ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 1

Is that 1,5TB compressed backup size?

Will you be using backup to url= blobstorage(v2?)

or backup to azure file storage?

network speed 100mbps: is that your internet connection speed?

Are you the only user of that internet channel?

Maybe backup using multiple files may help speedup the whole operation, given you have enough bandwidth to handle it all.

( first test I performed with backup to url a couple of years ago, I consumed all bandwidth, causing people not being able to read their online papers during lunch break - sheer panic at the helpdesk )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

I don't work with Azure and did some brief work with AWS. I've found it to be easier on the server to backup to on-site storage and then copy the backup to the cloud. That way, the server doesn't have to wait.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mohan.bndri Old Hand Points: 355 More actions · Answer 3

Yes. I can do direct backup to Azure with url only ( using backup to url= blobstorage(v2)), want to know what is inside technology / architecure that the operation performed.

network speed 100mbps: is that your internet connection speed? Yes

Are you the only user of that internet channel? Yes

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

Your internet speed is why I'm suggesting a local backup to a different local machine and then have that do the upload to Azure. It keeps SQL Server from having to lollygag around while trying to get the backups done.

Either way, make sure that you have "Compressed Backups" enabled by default and that you've taken a bit of time to figure out the BUFFERCOUNT and MAXTRANSFERSIZE option in the BACKUP DATABASE and BACKUP LOG commands. It has allowed me to backup to NAS at a rate of ~3TB per hour with settings of 17 and 1048576 respectively.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mohan.bndri Old Hand Points: 355 More actions · Answer 5

Jeff Moden wrote:

Your internet speed is why I'm suggesting a local backup to a different local machine and then have that do the upload to Azure. It keeps SQL Server from having to lollygag around while trying to get the backups done.
Either way, make sure that you have "Compressed Backups" enabled by default and that you've taken a bit of time to figure out the BUFFERCOUNT and MAXTRANSFERSIZE option in the BACKUP DATABASE and BACKUP LOG commands. It has allowed me to backup to NAS at a rate of ~3TB per hour with settings of 17 and 1048576 respectively.

that means, My DB having 1.5 TB in size would take around 1 hour to copy to other machine in network. is that correct ?

And Please tell the estimated time duration for the 1.5 tb backup directly to Azure storage in blob.

This reply was modified 1 year, 7 months ago by mohan.bndri.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

mohan.bndri wrote:

Jeff Moden wrote:
Your internet speed is why I'm suggesting a local backup to a different local machine and then have that do the upload to Azure. It keeps SQL Server from having to lollygag around while trying to get the backups done.
Either way, make sure that you have "Compressed Backups" enabled by default and that you've taken a bit of time to figure out the BUFFERCOUNT and MAXTRANSFERSIZE option in the BACKUP DATABASE and BACKUP LOG commands. It has allowed me to backup to NAS at a rate of ~3TB per hour with settings of 17 and 1048576 respectively.
that means, My DB having 1.5 TB in size would take around 1 hour to copy to other machine in network. is that correct ?
And Please tell the estimated time duration for the 1.5 tb backup directly to Azure storage in blob.

No... If I'm backing up ~3TB per hour, 1.5TB is half that or a half hour.

That speed on a whole lot of things like having a fast local network and a good box with SSD drives on it to back it up to.

I seriously doubt that you'll get those kinds of speeds going to Azure but can't say for sure because, like I said before, I've never used Azure and I don't remember the performance to AWS but it certainly was NOT 3TB per hour, blob or not.

I'm saying that you need to play with the two settings no matter where you back up to.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27781 More actions · Answer 7

One item you may consider is partitioning especially if one or a few of your tables make up a large majority of the size of your database.

Here is a nice read on this:

----------------------------------------------------