Glenn's profileGlenn Berry's SQL Server...BlogListsNetworkMore Tools Help

Blog


    11/14/2009

    Using Dedicated NICs to Segregate SQL Server 2008 Backup Traffic

    If you need to initialize a mirrored database for SQL Server 2008 Database Mirroring, one of the first things you have to do is take a full backup of the database that you want to mirror. You can either back it up locally and copy the backup file(s) over the network to the server where you are going to mirror it, or you can just backup over the network, directly to the destination server.

    In order to run a SQL Server backup to a file share on a remote server, you have to grant read/write access to the SQL Server Service Account for that file share.  I like to do striped backups (which means that you have multiple backup files that compose the full backup set), going to multiple file shares that map to multiple disk arrays in order to maximize backup and restore performance. I also use SQL Server 2008 Native Backup Compression to reduce the size of the backup files. You need SQL Server 2008 Enterprise Edition to use backup compression, or you can use a third party solution.

    Backing up directly to the destination server can be faster in total elapsed time (if your network infrastructure can handle it), since you are getting the backup file(s) where they need to be in one operation. One thing to be careful about if you do this is the effect on your network card. If you have enough I/O capacity and throughput on both sides, it is possible to completely saturate a single gigabit Ethernet NIC, which could affect the performance of the destination server.

    Most recent vintage commodity servers have two or more gigabit Ethernet NICs embedded on the motherboard. You can take advantage of this by using the IP address of a specific NIC in a UNC path for your BACKUP command (like you see below). This allows you to target that NIC for the backup traffic.

    -- Full compressed, striped backup to a specific NIC on a remote server
    BACKUP DATABASE [ngcontent01] 
    TO  DISK = N'\\192.168.xxx.xxx\SQLBackups\ngcontent01FullCompressedA.bak',  
        DISK = N'\\192.168.xxx.xxx\SQLBackups2\ngcontent01FullCompressedB.bak' WITH NOFORMAT, INIT,  
    NAME = N'ngcontent01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 1
    
    -- Compressed log backup to a specific NIC on a remote server
    BACKUP LOG [ngcontent01] 
    TO  DISK = N'\\192.168.xxx.xxx\SQLBackups\ngcontent01Trans.trn' WITH NOFORMAT, INIT,  
    NAME = N'ngcontent01-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 1

    Below, you can see the Networking tab of Task Manager showing Local Area Connection 2 of the destination server running at 85% utilization, while the Local Area Connection is not affected during the backup. Ideally, you could talk to your network staff about having a separate VLAN connection to completely segregate the backup traffic.

    image

    This shows the destination server, having a very easy time receiving the backup files.

    image

    Comments (1)

    Please wait...
    Sorry, the comment you entered is too long. Please shorten it.
    You didn't enter anything. Please try again.
    Sorry, we can't add your comment right now. Please try again later.
    To add a comment, you need permission from your parent. Ask for permission
    Your parent has turned off comments.
    Sorry, we can't delete your comment right now. Please try again later.
    You've exceeded the maximum number of comments that can be left in one day. Please try again in 24 hours.
    Your account has had the ability to leave comments disabled because our systems indicate that you may be spamming other users. If you believe that your account has been disabled in error please contact Windows Live support.
    Complete the security check below to finish leaving your comment.
    The characters you type in the security check must match the characters in the picture or audio.

    To add a comment, sign in with your Windows Live ID (if you use Hotmail, Messenger, or Xbox LIVE, you have a Windows Live ID). Sign in


    Don't have a Windows Live ID? Sign up

    i personally prefer to team NIC's, as HP calls it. by default when you team the NIC's you get 1 NIC's worth receive speed and 2 NIC's worth of transmit. you get the benefits of a dedicated NIC for backups and ease of management as well as fault tolerance and load balancing. we try to plug NIC's into more than one switch in case of hardware failure
    4 days ago

    Trackbacks

    The trackback URL for this entry is:
    http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!2107.trak
    Weblogs that reference this entry
    • None