Post reply

What's Your Backup Speed?

  • November 3, 2016 at 9:52 pm

    #400987

    Comments posted to this topic are about the item What's Your Backup Speed?

  • November 4, 2016 at 2:18 am

    #1910527

    Steve Jones - SSC Editor (11/3/2016)

    I think it's one of the most solid subsystems in SQL Server

    I should jolly well think so!!!

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • November 4, 2016 at 2:57 am

    #1910531

    1.4 TB database, 52 mins backup time.

    So, 1 TB would be like 37 mins. Approximately 27 GB/min, 450 MB/s.

    This is with Compression = On.

    Database is on RAID 6, 10 SSDs. Backup is on 8 HDDs, don't remember the RAID level.

  • November 4, 2016 at 3:10 am

    #1910533

    165GB in 10 min 24 sec, which would mean just over an hour for 1TB (63 minutes).

    DB files on SAN disk, SQL 2008 standard, Red Gate SQL Backup with level 1 compression (produces 21.5GB file at 87% compression).

    441GB in anything from 18 to 25 minutes. 18 minutes would mean 1TB in under 41 minutes.

    Again DB files on SAN, Red Gate SQL Backup with level 1 compression (produces 13GB file at 97% compression) but this time we're running SQL 2012 Enterprise.

  • November 4, 2016 at 5:40 am

    #1910558

    20-mins (190GB in 221s @ 880MB/s - my Client's most important database)

    Windows Server 2008 R2 Enterprise x64 (VM 4vCPU 14GB)

    SQL Server 2008 R2 Enterprise x64

    Native backups via Ola Hallengren

    Compression

    Checksum

    8-stripes

    BufferCount 64

    Blocksize 65536

    Files on HPE 3PAR StoreServ 8200 (16x 3.84TB cMLC SSD SFF RAID5 (3+1))

    Backup across network to HPE StoreEasy 1650 NAS server (8x 4TB 7.2K SAS RAID6)

    10 Gbit/s Ethernet (no jumbo frames or dedicated backup network)

  • November 4, 2016 at 7:28 am

    #1910584

    2.4TB in just over 2 1/2 hours, or 1TB in just over an hour. This is for ~40DBs in total, compression ON. SQL 2012 using native backups.

    Ken

  • November 4, 2016 at 7:28 am

    #1910585

    DB Size = 1.1TB

    Backup Duration = 2Hrs 26Min

    We use EMC's Avamar backup client

  • November 4, 2016 at 7:52 am

    #1910594

    2.3 TB in an hour and a couple of minutes. On a good night, it takes about 56 minutes. And that's to NAS storage, which is a huge "safety of the data" bonus.

    We have a 10GB pipe between the server and the NAS storage. I don't know the configuration of that storage since I'm very fortunate to have an infrastructure team that does an incredible job. Of course, we're also using compression so the throughput isn't actually 2.3GB per hour but the net result is a 2.3TB box does its full backups in about an hour. That also says a bit about the machines they built for the company.

    we're also doing offsite DR to another box using "Nimble". Again, I don't personally know much about the configuration but it, too, does and awesome job and, even with the huge overnight imports and updates we do, it keeps up very nicely.

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

  • November 4, 2016 at 7:57 am

    #1910596

    This is not a good example for people to use to estimate their performance, but is a good thing to read to understand how badly things can go.

    We have a database from Intersystems, they provide a product named Cache. According to them it is not an RDBMS. It does store data in multiple files, and our system uses multiple mount points on an RHEL 7 system. We recently restored production to a test environment. The servers are almost identical, the differences do not affect performance in any measurable way.

    Restoring 700GB took 27 hours!

    Yes, we know there are issues. We are working on that.

    This restore was done from a Commvault tape backup system that utilizes disks to increase backup and restore speed. The disks are later written to tape. It is a well designed system. I don't want to get into what caused the delay as it would sound too much like pointing fingers, but what is important is that even though we have a fine network and backup system and the servers are outstanding, we are not able to restore in a timely manner.

    How did we find out? We tested.

    Dave

  • November 4, 2016 at 8:42 am

    #1910612

    826 GB

    Average backup time 14 Min 7 Seconds

    Compression is turned on

  • November 4, 2016 at 8:55 am

    #1910619

    this is averages over a few months:

    average database size was 186 GB, on Compellent SAN

    average backup time (using TSQL commands, with compression) to external Drobo disk array was 20 minutes

    average backup time (using CommVault) to tape was 79 minutes

    so extrapolating for 1 TB would be:

    115 minutes to Drobo disk

    433 minutes to CommVault tape (ouch)

    glad we're discontinuing CommVault backup of databases directly to tape!

  • November 4, 2016 at 9:46 am

    #1910642

    How about the restore speed?

    LinkedIn Profile

  • November 4, 2016 at 12:05 pm

    #1910691

    Nightly restore of 2 main production databases:

    DB1: 515GB, 13.35% free space, 10.0GB Log file

    DB2: 76GB, 36.69% free space, 33.5GB Log file (yeah... I know)

    Total time to restore including time to build the log file... 17:34 to 18:56 (mm:ss)

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

  • November 7, 2016 at 2:04 am

    #1910853

    Backup times were before vs after optimisation for one of my databases.

    Native sql default options

    BACKUP DATABASE successfully processed 4098542 pages in 3049.584 seconds (10.499 MB/sec)

    Native sql Buffercount=500, maxtransfer = 1MB

    BACKUP DATABASE successfully processed 4098705 pages in 110.046 seconds (290.979 MB/sec)

    Redgate - SQLBackup

    BACKUP DATABASE successfully processed 4098225 pages in 513.281 seconds (62.377 MB/sec).

    SQLBackup did not change times when I passed in different buffercount and maxtransfersize.

    Native sql

    Buffercount = 8

    RESTORE DATABASE successfully processed 4780071 pages in 140.001 seconds (266.743 MB/sec).

    Buffercount = 16

    RESTORE DATABASE successfully processed 4780071 pages in 137.478 seconds (271.638 MB/sec).

    Buffercount = 32

    RESTORE DATABASE successfully processed 4780071 pages in 137.080 seconds (272.427 MB/sec).

    Buffercount = 64

    RESTORE DATABASE successfully processed 4780071 pages in 145.945 seconds (255.879 MB/sec).

    Buffercount = 512

    RESTORE DATABASE successfully processed 4780071 pages in 147.236 seconds (253.635 MB/sec).

    Page count is different as I did the backups optimisation first then did the restore optimisation weeks later, which had very little to do witht he buffercount and a lot to do with rightsizing an unnecessarily bloated log file.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 7, 2016 at 7:05 am

    #1910913

    We recently had to perform some backup and restore tests in order to estimate the impact of compressed vs non compressed backups and the effect they would have on our dedupe backup tape infrastructure. Times for the backups and restore are shown below. The backups are dumped to a san (XtremeIo) mounted local volume. The restores are performed across a wan link via unc path to a non-prod server. These are from a SQL2012 Enterprise Server running 16 cores.

    Type BackupSizeGBOnDiskSizeGBStripesDurationMB/minSystemStateBackupNumber

    Compressed1,404 302 1 78 18,432BatchRunning1

    Compressed1,390 294 1 103 13,818BatchRunning2

    Compressed1,479 313 1 90 16,830BatchRunning3

    Compressed1,434 303 1 88 16,687BatchRunning4

    UnCompressed1,479 1,479 1 240 6,309LowLoad 5

    Compressed1,451 307 1 94 15,808BatchRunning6

    UnCompressed1,489 1,489 1 84 18,151LowLoad 7

    UnCompressed1,532 1,532 1 87 18,032MediumLoad8

    UnCompressed1,580 1,580 1 146 11,082LowLoad 9

    UnCompressed1,575 1,575 1 84 19,200LowLoad 10

    UnCompressed1,579 1,579 1 91 17,768LowLoad 11

    UnCompressed1,584 1,584 1 87 18,644LowLoad 12

    UnCompressed1,594 1,594 16 41 39,811LowLoad 13

    UnCompressed1,463 1,463 16 35 42,803LowLoad 14

    UnCompressed1,467 1,467 16 37 40,600LowLoad 15

    UnCompressed1,508 1,508 16 37 41,735LowLoad 16

    UnCompressed1,496 1,496 16 37 41,403LowLoad 17

    UnCompressed1,502 1,502 16 41 37,513LowLoad 18

    Type BackupSizeGBOnDiskSizeGBStripesDurationMB/minSystemStateUsingBackupNumber

    Compressed1,479 313 1 1609,467LowLoad 3

    Compressed1,479 313 1 12412,215LowLoad 3

    Compressed1,434 303 1 10813,597LowLoad 4

    Uncompressed1,479 1,479 1 6882,201LowLoad 5

    Uncompressed1,532 1,532 1 1,4821,059HighLoad 8

    Uncompressed1,580 1,580 1 4653,479LowLoad 9

    Uncompressed1,575 1,575 1 5672,844HighLoad 10

    Uncompressed1,584 1,584 1 6182,625LowLoad 12

    Uncompressed1,594 1,594 16 5193,145LowLoad 13

    Uncompressed1,463 1,463 16 4893,064LowLoad 14

    Uncompressed1,467 1,467 16 3953,803LowLoad 15

    UnCompressed1,508 1,508 16 4683,300ModerateLoad 16

    UnCompressed1,496 1,496 16 5922,588LowLoad 17

    Uncompressed1,502 1,502 16 4863,165LowLoad 18

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

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