White Papers

BP1014 Enhancing SQL Server Protection using Dell EqualLogic Snapshot Smart Copies

 Careful scheduling is necessary when you have different backup and snapshot schedules

running against the same database.

 It is very important that backup destinations reside on storage devices that are separate from

where the databases are stored. I/O bandwidth can be very critical to the performance of the

database. You want to separate the sequential I/O load that is typically generated by backup

and restore processing from the random I/O load that is typically generated by transaction

processing databases.

 Use the CHECKSUM option of the Backup command. With this option enabled, the backup will

verify the page checksums (if they are present), and generate a separate backup checksum for

the backup stream that is stored on the backup media. This option will also cause both the

backup processing workload and the amount of time to create the backup to increase.

Example:

BACKUP DATABASE sqldb2

TO DISK = N'G:\Backup\sqldb2_bak

WITH CHECKSUM

 You should always schedule backup operations when database activity is low.

 Back up first to disk whenever possible. Backing up to disk will greatly increase the

performance of the backup process and free the resources of SQL Server®. Using file backups

also simplifies the restoration process.

 To back up a database that has the database files damaged, use the NO_TRUNCATE or the

COPY_ONLY and CONTINUE_AFTER_ERROR options of the BACKUP command.

 Test Backup files periodically using the RESTORE VERIFYONLY command. This command will

verify the backup, but not restore it. It checks that the backup set is complete and readable.

This command does not verify the structure of the data contained in the backup volume.

RESTORE VERIFYONLY

FROM DISK = N'CG:\Backup\SQLDB2-032211.bak'

WITH CHECKSUM

4.4 Best practices when using VMware ESX Server

In our lab test environment we used VMware ESX server to host SQL Server® database virtual

machines as well as the Quest Benchmark factory work load simulation virtual machines. We share the

following best practice recommendations below for running VMware ESX based virtual machines in

conjunction with EqualLogic Storage and/or Microsoft® SQL Server® environments.

ESX host configuration

 We recommend in any configuration where you are using the ESX host based iSCSI initiator

that you evaluate and take advantage of EqualLogic aware connection and path management

by installing and using the EqualLogic Multipathing Extension Module

(MEM) for vSphere 4.1.

We used MEM to optimize I/O performance for the connection path between the “iSCSIESX”

See the

EqualLogic Multipathing Extension Module Installation and User Guide

for vSphere version 4.1,

available here: https://www.equallogic.com/support/download_file.aspx?id=947