Maximizing backup performance with SQL Server 2000

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

Requires Free Membership to View

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 http://www.microsoft.com

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.

This was first published in February 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.