White Papers

BP1014 Enhancing SQL Server Protection using Dell EqualLogic Snapshot Smart Copies

4 Best practice recommendations

In the following sections we summarize best practices to follow for using Auto-Snapshot Manager and

for SQL backup strategies in general.

4.1 Including EqualLogic Smart Copy snapshots as part of your SQL Server

database protection strategy

In order to include EqualLogic smart copies or snapshots in your overall SQL protection strategy and

measure the impact on achievable RPO and RTO, a few considerations are necessary: You should pay

attention to the following considerations when including EqualLogic smart copies or snapshots in your

overall SQL protection strategy.

Know your workload

It is important that you understand the performance characteristics of your database workload. If

you plan to use snapshots to backup SQL data by attaching the snapshots to a backup server (off-

host backup), be aware that the backup process will place an additional IOPS and throughput load

on your SAN. You should make sure that the SAN can handle the additional load (largely sequential

reads) that is typically created by backup processing. Test your backup process and measure the

additional load on SAN by using the EqualLogic SAN Headquarters (SANHQ) monitoring tool.

Know your recovery times from Smart Copies

You should measure recovery times when restoring from a backup set vs. restoring from an Auto-

Snapshot Manager Smart Copy. This information will allow you to plan for optimal Recovery Time

Objectives (RTOs). We measured and compared recovery times for each method. The results from

that test are provided in Section 3.5, Testing time to complete point-in-time recovery, on

page 18.

Monitor your snapshot reserve utilization

ASM Smart Copies create array based snapshots. Therefore, Smart Copies require additional space

allocation (snapshot reserve) in the EqualLogic volume to store any data changed after a snapshot

was created. The default value of volume snapshot reserve space is 100% of volume size. The

actual reserve setting may be higher or smaller than that, depending on your workload profile and

the number of snapshots you want to keep. It is important to carefully monitor reserve utilization

and tune the reserve setting for optimal storage utilization. The number and size of Smart Copies

you can retain is limited by the volume snapshot reserve setting. If you reach the reserve limit you

will need to increase the reserve setting or delete an older Smart Copy (or array based snapshot)

before you can create another Smart Copy. A good way to measure reserve utilization is to start

with the default (%100) reserve allocation and create a series of array based snapshots every hour

during a period of normal production workload. While creating the snapshots use SANHQ to

monitor how reserve usage increases. Note that when you perform a point-in-time recovery from

a Smart Copy, an additional snapshot of the volume hosting the database is first created and then

the database is restored to the point when the Smart Copy was created. Therefore, it is important

to include one additional snapshot in your snapshot reserve sizing calculation.