MS SQL Backup taking too long - Veritas Net Backup

This is a discussion on MS SQL Backup taking too long - Veritas Net Backup ; Hi, Another db related question here. We have a full database backup for two SQL server client scheduled every week, both of the clients on the same policy. We noticed that the backup took more than 3 hours to finish ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: MS SQL Backup taking too long

  1. MS SQL Backup taking too long


    Hi,

    Another db related question here. We have a full database backup for two
    SQL server client scheduled every week, both of the clients on the same policy.
    We noticed that the backup took more than 3 hours to finish condidering that
    we have a separate LAN setup for the backup. 12 databases for each server
    and size is not more than 20GB for all. Why is this so? This seems not to
    be normal and acceptable.

    Need advise.
    Sharon

  2. Re: MS SQL Backup taking too long

    Hi Sharon,

    I think you might need to read the manual entitled "Veritas NetBackup vN.n
    For Microsoft SQL Server, System Administrator's Guide, for Windows", and
    certainly look at using a "batch" file to tune up some of the options.
    These aren't really "batch" files in the sense of code execution, but more a
    way of "batching together" different options for different databases.

    I don't know SQL, so most of the manual didn't mean too much to me. I just
    wanted to improve my backup times. The performance of MS SQL backups at our
    site was horrible, until we tuned it a bit. When using some of the shared
    memory based options you will need to pay careful attention to how much
    memory will be used. I include some Excel (below) to help you calculate
    this. It won't appear that straightforward the first time you do this, but
    it will pay dividends, and it gets easier each time you do it. I'd
    concentrate on just one database/client to start with, until you get the
    hang of it. Don't try and do all 20 databases at once, or you'll just end
    up in a muddle.

    Do one client properly, and write yourself a little Excel spreadhseet to
    include all the parameters that you'll use and estimate the memory
    consumption. Also start recording/saving current backup times now (I save
    the activity view to Excel every day), so that you can compare backups from
    day to day and week to week, and see what impact your settings will have
    had.


    Here's an example batch file from my site ;
    --------------------------------------CUT BETWEEN THESE
    LINES----------------------------
    BATCHSIZE 2


    OPERATION BACKUP
    DATABASE "master"
    SQLHOST "CLIENT-HOSTNAME"
    NBSERVER "MASTER-MEDIA-SERVER-NAME"
    BUFFERS 8
    MAXTRANSFERSIZE 3
    BLOCKSIZE 7
    ENDOPER TRUE

    OPERATION BACKUP
    DATABASE "model"
    SQLHOST "CLIENT-HOSTNAME"
    NBSERVER "MASTER-MEDIA-SERVER-NAME"
    BUFFERS 8
    MAXTRANSFERSIZE 3
    BLOCKSIZE 7
    ENDOPER TRUE


    OPERATION BACKUP
    DATABASE "msdb"
    SQLHOST "CLIENT-HOSTNAME"
    NBSERVER "MASTER-MEDIA-SERVER-NAME"
    BUFFERS 8
    MAXTRANSFERSIZE 3
    BLOCKSIZE 7
    ENDOPER TRUE

    OPERATION BACKUP
    DATABASE "Northwind"
    SQLHOST "CLIENT-HOSTNAME"
    NBSERVER "MASTER-MEDIA-SERVER-NAME"
    BUFFERS 8
    MAXTRANSFERSIZE 3
    BLOCKSIZE 7
    ENDOPER TRUE


    OPERATION BACKUP
    DATABASE "pubs"
    SQLHOST "CLIENT-HOSTNAME"
    NBSERVER "MASTER-MEDIA-SERVER-NAME"
    BUFFERS 8
    MAXTRANSFERSIZE 3
    BLOCKSIZE 7
    ENDOPER TRUE

    OPERATION BACKUP
    DATABASE "MY-BIG-DATABASE-NAME"
    SQLHOST "CLIENT-HOSTNAME"
    NBSERVER "MASTER-MEDIA-SERVER-NAME"
    BUFFERS 16
    MAXTRANSFERSIZE 6
    BLOCKSIZE 7
    ENDOPER TRUE
    -----------------------------------CUT BETWEEN THESE
    LINES-------------------------------



    Drop these following lines into Excel, to give yourself some idea of the
    memory usage that could be used per "database". So, if you use a BATCHSIZE
    of 2 or more, then you will need to add the memoray usage for each database
    to know how much will be used in total.

    ----------------------------CUT HERE------------------------------
    BLOCKSIZE,bytes, ,MAXTRANSFERSIZE,kb, ,BUFFERS,mb
    ,per block,,,per transfer,,,per database
    6,=(512*2^A3),,5,=(B3*2^D3)/1024,,8,=(((B3*2^D3)*G3)/1024)/1024
    6,=(512*2^A4),,6,=(B4*2^D4)/1024,,8,=(((B4*2^D4)*G4)/1024)/1024
    7,=(512*2^A5),,5,=(B5*2^D5)/1024,,8,=(((B5*2^D5)*G5)/1024)/1024
    7,=(512*2^A6),,6,=(B6*2^D6)/1024,,16,=(((B6*2^D6)*G6)/1024)/1024
    ---------------------------CUT HERE--------------------------------


    As for BATCHSIZE, this indicates the number of concurrent databases (i.e.
    backup jobs) that can be secured alongside each other. So, if you had a
    batchsize of 3, but six databases to do, and policy that allowed
    multi-streaming then you could have 3 backups run from the same client,
    which could overload the system with reads, so much so that the client can't
    keep up, and you could end up having even slower backups and a thrashing
    client.

    Personally, I never use a batchsize greater than two. I put the smaller
    databases first in the "batch" file. NetBackup takes SQL databases in
    "batchsize" units, i.e. if you have a batchsize of two, then NetBackup
    client will run two backups, but won't move on to the next pair of databases
    until both databases in the pair are done. Hence I put small databases next
    to big ones. This gets the small databases out of the way without causing
    thrashing by taking two big databases together. You'll notice that I use an
    extra blank line between each pair (in the example batch file above), to
    remind myself of this.

    Remember, if you run multiple backups (i.e. batchsize > 1) then you need to
    think about how much memory each pair (or triplet, or quartet, etc) of
    databases will try to consume. There's no point causing lots of
    swapping/paging, as this will just make things worse.

    In the end I settled for two general "settings" - i.e. one set of parameters
    I use for small databases, and another that I use for large databases. I
    settled on;
    - for small databases
    BUFFERS 8
    MAXTRANSFERSIZE 3
    BLOCKSIZE 7
    - and for large databases
    BUFFERS 16
    MAXTRANSFERSIZE 6
    BLOCKSIZE 7


    If you read the documentation, you'll also see mention of another parameter
    called STRIPES. I decided not to use this. Read the manual for more
    information on this parameter. Hopefully this is enough to get you going.
    If you do implement any ideas/changes, I'm sure we'd all like to know what
    happened. I halved the duration of MS SQL backups at our site. Some of our
    databases are over 100gb, and now they max out the tape drive - basically,
    NetBackup can't write the data to tape quickly enoughwhich is always a good
    sign.

    Good luck,
    Dave.







    "Sharon" wrote in message
    news:43e9c115@ROSASTDMZ05....
    >
    > Hi,
    >
    > Another db related question here. We have a full database backup for two
    > SQL server client scheduled every week, both of the clients on the same
    > policy.
    > We noticed that the backup took more than 3 hours to finish condidering
    > that
    > we have a separate LAN setup for the backup. 12 databases for each server
    > and size is not more than 20GB for all. Why is this so? This seems not to
    > be normal and acceptable.
    >
    > Need advise.
    > Sharon




+ Reply to Thread