Problem solve Get help with specific problems with your technologies, process and projects.

Maximizing backup performance with SQL Server 2000

In this tip, author Rick Cook shares with you some Microsoft tricks of the trade designed to help you maximize performance and minimize your backup times.

Although backing up or restoring a database from a DBMS like SQL Server 2000 is always a major operation, according to Microsoft there are a number of things you can do to improve the performance considerably.

Maximizing performance starts with architecture. In general spreading the SQL Server database over more disks gives better performance, both in ordinary operations and in backup. Ideally, Microsoft recommends striping the database files evenly across multiple disks to improve I/O. Similarly, multiple backup devices, whether tape drives or disks, improve parallelism and hence performance.

Next, look at your backup software. A number of third party vendors have backup products that use Microsoft's Virtual Device Interface (VDI) for Backup to transfer data for backup using shared memory architecture to increase the speed of the operation.

If you are backing up to a local RAID array, use RAID level 1 or RAID level 10 rather than RAID 5. RAID 1 or 10 is about half as fast as RAID 0 (which provides no data protection), but about twice as fast as RAID 5.

Another trick is copying the database to disk and then copying to tape from the copy. This cuts the time the primary database is slowed down by backing up.

Also consider the use of log shipping. Log shipping doesn't speed up backups and technically it isn't even a restoration method, but it can have you up an running in the event of a server crash very quickly. Log shipping lets the you maintain a standby server which is synchronized almost real time to the production server. When transaction logs are backed up on the primary server, a copy of the log is automatically sent to the standby server and restored there. In the event of a server failure, the standby server is brought on line manually to handle application requests. To reduce the number of lost transactions, and if there is still connectivity to the primary server, try to backup, copy and apply the orphaned log (NO_TRUNCATE)

Microsoft discusses performance tuning of SQL Server 2000 in Chapter 4 of the SQL Server 2000 Operation Guide, available on Microsoft's website

For more information:

Tip: Snapshots in SQL Server 2000

Tip: The problems backing up big databases

Tip: Tune for RAID performance

About the author: Rick Cook has been writing about mass storage since the days when the term meant an 80K floppy disk. The computers he learned on used ferrite cores and magnetic drums. For the last twenty years he has been a freelance writer specializing in storage and other computer issues.

Dig Deeper on Backup and recovery software

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.