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.

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

This entry was posted in SQL Server 2008. Bookmark the permalink.

82 Responses to Using Dedicated NICs to Segregate SQL Server 2008 Backup Traffic

  1. Alen says:

    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

  2. Unknown says:

    http://www.salesid.com and http://www.saletoo.com Are Top Wholesale: Belts Caps Clothing Glasses Handbags Shoes Wallets Watches.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s