Baking up and restores VLDBs

Question

Baking up and restores VLDBs

ericwenger1

Hall of Fame

Points: 3436
More actions
October 3, 2023 at 1:18 pm

#4300528

Some of our databases are getting be close to 2 terabytes. We are backing up to azure storage and also using multiple files. I was wanting to get opinions on how we can perform backups quicker. Same thing with restores.

Thanks in advance

Viewing 15 posts - 1 through 15 (of 27 total)

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

ScottPletcher SSC Guru Points: 101093 More actions · Answer 1

(1) Use differentials rather than full backups for most backups. For example, daily diffs and only, say, weekly full backups.

(2) If you're not already using backup compression, and particularly if the indexes aren't page compressed, start using backup compression. This could be huge time saving under the right conditions.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Michael L John One Orange Chip Points: 27054 More actions · Answer 2

How many files?

Have you attempted to adjust the MAXTRANSFERSIZE and BLOCKSIZE parameters? For Azure blob storage, these would be indicated. MAXTRANSFERSIZE=4194304 ,BLOCKSIZE = 65536

Haw large are the backup files compared to your database size?

Have you considered backing up to local disk first, and then copying the files to Azure? That can be executed in the background.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

tzimie Old Hand Points: 321 More actions · Answer 3

Hm. Some of our databases are 20Tb. Total 80Tb on one server, takes more than a day for full backup.

Michael L John One Orange Chip Points: 27054 More actions · Answer 4

tzimie wrote:

Hm. Some of our databases are 20Tb. Total 80Tb on one server, takes more than a day for full backup.

How many backup files are you creating with these backups?

What are the values you are using for MAXTRANSFERSIZE and BLOCKSIZE?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 5

At that size I think you need to be looking at snapshot backups. To me, completing that large a backup in a little over a day is actually pretty impressive even if your SQL servers have a 10 Gbps connection to the core

Michael L John One Orange Chip Points: 27054 More actions · Answer 6

CreateIndexNonclustered wrote:

At that size I think you need to be looking at snapshot backups. To me, completing that large a backup in a little over a day is actually pretty impressive even if your SQL servers have a 10 Gbps connection to the core

I am performing full database backups of a 26 TB database in ~10 hours to Azure blob, and a 13 TB in ~4 hours to Azure blob.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 7

CreateIndexNonclustered

SSChasing Mays

Points: 648

October 4, 2023 at 5:57 pm

#4301459

he has 80 tb...

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

Michael L John wrote:

tzimie wrote:
Hm. Some of our databases are 20Tb. Total 80Tb on one server, takes more than a day for full backup.
How many backup files are you creating with these backups?
What are the values you are using for MAXTRANSFERSIZE and BLOCKSIZE?

I'm not sure that I'd mess with BLOCKSIZE but I definitely agree with experimenting to find the best MAXTRANSFERSIZE and the BUFFERCOUNT.

--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)

Michael L John One Orange Chip Points: 27054 More actions · Answer 9

I'm not sure that I'd mess with BLOCKSIZE but I definitely agree with experimenting to find the best MAXTRANSFERSIZE and the BUFFERCOUNT.

BLOCKSIZE makes a big difference when backing up to Azure blobs. Local, not so much.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27054 More actions · Answer 10

CreateIndexNonclustered wrote:

he has 80 tb...

80 TB total. The server that contains the databases I referenced above total 48 TB. The entire server completes in approximately 12 hours.

Backups execute in parallel, the various settings have been adjusted, and so forth.

I do not know what kind of snapshot you may be recommending, and we do not know what kind of system the OP is backing up. A snapshot may not work for an OLTP system of this size. There may be too much data loss. The snapshot mechanism may lock the files. And so forth, There are many different things that need to be considered.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

Michael L John wrote:

I'm not sure that I'd mess with BLOCKSIZE but I definitely agree with experimenting to find the best MAXTRANSFERSIZE and the BUFFERCOUNT.
BLOCKSIZE makes a big difference when backing up to Azure blobs. Local, not so much.

Got it. Thanks for the info and for confirming the take on Local.

--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)

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 12

VSS snapshots are SQL consistent and the feature no longer requires SQL Enterprise. They are supposed to be very fast - they may not be fast without adequate maintenance, but thorough maintenance is just a non-optional cost of an instance such as that. I have only had them either function normally, and quiesce for a few hundred milliseconds at most, or not function at all and quiesce for dozens of seconds or minutes. (cough veeam, cough snap manager)

I cannot even begin to imagine having a recurring 12-hour maintenance window to run backups. I suppose if I did I would prefer traditional backups but I have never had that, even in my "8-5" shops.

tzimie Old Hand Points: 321 More actions · Answer 13

We are using 4 backup files on NetApp in the same datacenter

network is two 10gbs adapters bridged

all other settings are default

Michael L John One Orange Chip Points: 27054 More actions · Answer 14

CreateIndexNonclustered wrote:

VSS snapshots are SQL consistent and the feature no longer requires SQL Enterprise. They are supposed to be very fast - they may not be fast without adequate maintenance, but thorough maintenance is just a non-optional cost of an instance such as that. I have only had them either function normally, and quiesce for a few hundred milliseconds at most, or not function at all and quiesce for dozens of seconds or minutes. (cough veeam, cough snap manager)
I cannot even begin to imagine having a recurring 12-hour maintenance window to run backups. I suppose if I did I would prefer traditional backups but I have never had that, even in my "8-5" shops.

What kind of maintenance are you referring to?

Have you ever had the IO being frozen cause an issue with a system from a VSS snapshot? I have.

Have you every thoroughly tested the data consistency (loss) from a snapshot of any type? I have.

Have you ever had to recover (in the middle of a disaster) from a snapshot and have it fail? I have. Thankfully I had a tried and true backup and recovery process in place.

Have you every had a snapshot bring a system to it's knees, or take it completely offline? I have.

Sorry, I have had more issues with snapshots of various kinds over the years when you needed them most. To RECOVER. It's not a backup strategy, it's a recovery strategy.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/