SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.



No comments.

Leave a Comment

Please register or log in to leave a comment.