Dell EqualLogic Best Practices Series Enhancing SQL Server Protection using Dell EqualLogic Smart Copy Snapshots A Dell Technical Whitepaper This document has been archived and will no longer be maintained or updated. For more information go to the Storage Solutions Technical Documents page on Dell TechCenter or contact support.
THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND. © 2011 Dell Inc. All rights reserved. Reproduction of this material in any manner whatsoever without the express written permission of Dell Inc. is strictly forbidden. For more information, contact Dell.
Table of Contents 1 Introduction ....................................................................................................................................................... 4 1.1 2 SQL Server backup considerations ................................................................................................................ 5 2.1 3 Audience .....................................................................................................................................................
Acknowledgements This whitepaper was produced by the PG Storage Infrastructure and Solutions team between January 2011 and April 2011 at the Dell Labs facility in Round Rock, Texas.
1 Introduction Backup and recovery operations are the focus of business continuity and data protection plans and often the main source of anxiety for IT departments. Few businesses are fully satisfied with their backup and recovery solutions. Not only must data be protected from complete site failures, such as those resulting from natural disasters, data must also be protected from corruption or data loss, such as that resulting from a computer virus or human error.
2 SQL Server backup considerations A copy of data that can be used to restore and recover the data is called a backup. Backups let you restore data after a failure. Microsoft® SQL Server® enables you to back up and restore your databases. The SQL Server® native backup and restore utility provides an important safeguard for protecting critical data stored in SQL Server® databases.
Restoration Tools and Processes The tools and processes your enterprise has in place to restore data ultimately determine what kind RTOs you are able to support. Some tools may require multiple manual steps in order to complete the restore process. A long and complicated recovery process can increase the chances for human error. The number different teams involved in data recovery can also impact restoration times. 2.
Eliminate Backup Window using off-host backups A good snapshot implementation should provide near instantaneous and non-disruptive consistent copy of the database. If the snapshots are transportable or can be attached to a host different from the production host, backup applications running on a proxy backup server can use them as a backup source. This allows you to off-load backup processing impact from the product host to a backup proxy host.
3 Test system configuration To conduct the system testing detailed in this paper we created the SQL Server® test system as shown in Figure 1. Figure 1 – Test System Configuration Some key design details in the test system configuration: BP1014 We Installed and configured SQL Server® 2008 R2 in a Windows Server 2008 R2 virtual machine (SQLDBVM01) hosted on the VMware ESXi 4.1 server. A Dell PowerEdge R710 server was used to run VMware ESXi server software.
The MGMT server was a Dell PowerEdge R710 server running Windows 2008 R2 natively. It was used to host the following management and monitoring tools: EqualLogic SAN 1 Headquarters (SANHQ), VMware vCenter, remote desktop access to the workload VMs, and 2 SQL Server® Management Studio . The SAN switches consisted of two Dell PowerConnect 6248 switches, configured as a single stack.
3.1 Storage volume layout A single pool was created from each array. Table 1 lists the volumes and Figure 2 shows the volume layout.
3.2 Testing SQL Server backup processing impact In this test we measured the impact on server and database performance when running the SQL Server® native backup utility to create a full database backup across the SAN fabric to a volume hosted on a second storage array. Test details: Database size at the Full Backup point: 130GB Database Workload: During the test timeline Quest Benchmark Factory was used to simulate a TPC-C style workload.
In Figure 4 we show the impact on CPU utilization during the backup process. Figure 4 – CPU utilization impact when creating SQL Server backup using native backup utility 3.2.1 Analysis and conclusion As expected, we saw a significant increase in CPU load on the system during the time that the backup utility was creating the backup. We also measured a 30% average increase in application response time during the same period.
snapshot is transactionally consistent with the source database at the time the snapshot is created. A snapshot will persist until it is dropped. 3.3.1 Time to create a SQL Server snapshot as a function of user load In the first test we measured how long it took to complete snapshot creation at different simulated user loads. Test details: The same system configuration was used as in the previous section.
snapshot creation, while retaining multiple snapshots. Differences between this test and the previous one: During creation of all snapshots in the series we ran a constant TPC-C style workload on the system, simulating 4000 users. We did not drop any previous snapshots during the test sequence. The results of this test are presented in Figure 6.
Figure 7 – System and database performance impact when creating SQL Server snapshot In the next section, we now look at how snapshots at EqualLogic SAN layer perform under similar test conditions. 3.4 Testing EqualLogic Smart Copies EqualLogic Auto-Snapshot Manager/Microsoft® Edition (ASM/ME) gives a SQL Database administrator the ability to create a “Smart Copy Snapshot” of the database. A “Smart Copy Snapshot” is a transactionally consistent point in time copy of the SQL Server® database.
3.4.1 Time to create EqualLogic Smart Copy snapshots Test details: As in the previous tests, we used Quest Benchmark Factory to simulate a TPC-C style workload. A constant 4000 user workload was simulated, generating an average I/O load of 220 transactions per second against the database. The Smart Copy snapshots consumed snapshot reserve assigned to the same volume hosting the database. Previous snapshots were retained before taking new snapshots. The results of this test are shown in Figure 8.
3.4.2 Impact on system and database performance while creating Smart Copy snapshots We also measured system and database performance impact while creating a series of Smart Copy snapshots. Figure 9 shows database server CPU utilization and database transactions per second (TPS) while creating a series of ASM/ME Smart Copy snapshots. Figure 10 shows the difference in average application response time, as measured from Quest Benchmark Factory, during creation of SQL Server® snapshots vs.
Figure 10 – Comparing response times during creation of SQL Server snapshots and ASM/ME Smart Copies In section 3.4, we illustrated the non-disruptive nature of EqualLogic Smart Copy Snapshots on SQL Server® performance and time efficiency of smart copies to quickly create consistent protection points.
Test 2 o We performed a system recovery to the same point in time as in test #1. o In this test case we restored using the most recent Auto-Snapshot Manager snapshot Smart Copy (using Auto-Snapshot Manager in-place restore with the apply logs option). o After completing the in-place restore we replayed necessary incremental or transaction log backups from that point forward using the SQL Server® native backup utility. Figure 11 below illustrates the recovery timeline components involved in each test.
Recovery Task Time to complete (seconds) Restore full database (130 GB) to T0 900 Replay of 13 transaction Logs 1560 Recover database 29 Total 2489 (41:29) 3.5.2 Test 2 results: In-place restore from Smart Copy plus log replay The total time required to complete database restore, recovery and place it back on-line was 5.5 minutes. The time required to complete each component of the process is shown in Table 3.
Figure 12 – Results of recovery time comparison Conclusions from the results presented in Figure 12: The ASM Smart copy based recovery process was approximately 7.5 times faster than the recovery process using the native SQL Server® backup utility for recovering the database to the same point (which involves restore plus log replay).
Figure 13 – Restore and recovery data flow path using native SQL Server backup utility BP1014 Enhancing SQL Server Protection using Dell EqualLogic Snapshot Smart Copies 22
Figure 14– Restore and recovery data flow path using EqualLogic Smart Copy in-place restore BP1014 Enhancing SQL Server Protection using Dell EqualLogic Snapshot Smart Copies 23
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.
Carefully assess your RPO goals: Depending on your target RPO goals, you should ascertain whether you can fulfill them with just Smart Copies alone or would need a combination of backup sets in conjunction with Smart Copies.
Database Layout As a best practice, make sure that SQL Server® databases and associated transactions logs 5 should each reside in dedicated volumes. This will prevent creation of “torn” Smart Copies during selective restore scenarios. This will also enable faster restore and recovery times for individual SQL Server® databases. The Volume Shadow Copy Service (VSS) imposes a limit on the time it takes to create a Smart Copy. Smart Copies involving many volumes might exceed this time limit (30 seconds).
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.
host iSCSI connection path between the SQL database server and the PS6500E backup pool array. Per Microsoft® SQL Server® storage requirements, SQL Server® guest operating system images must be deployed on physical disk drives separate from physical drives hosting SQL Server® data. The VMDK files containing the database server OS file systems were stored on the local disks installed on the R710 ESX host.
Appendix A Auto Snapshot Manager / Microsoft Edition This appendix provides a brief introduction to EqualLogic Auto-Snapshot Manager/Microsoft® Edition. For more in depth information, please see the following references: Auto-Snapshot Manager/Microsoft Edition User Guide (v3.5.1): https://www.equallogic.com/support/download.aspx?id=10243 (registered support.equallogic.
Auto-Snapshot Manager manages the interaction with SQL Server® to prepare the database for the Smart Copy operation. When you create an ASM Smart Copy, SQL Server® first places the database in a consistent state, and then Auto-Snapshot Manager creates the Smart Copy. The result is a dataconsistent point-in-time copy (snapshot, clone, or replica) of the SQL Server® database. AutoSnapshot Manager also manages recovery of SQL Server® databases.
Appendix B Test system component details This section contains an overview of both the hardware and software configurations used throughout the testing described in this document. Table 4 – Test Configuration Hardware Components Test Configuration – Hardware Components SQL Server® (ESX01) I/O Workload Generators (INFRA) One (1) Dell PowerEdge R710 Server running VMware ESX v4.1, hosting a single SQL Server® Database virtual machine: BIOS Version: 2.1.
Table 5 – Test Configuration Software Components Test Configuration – Software Components Database Server VM (SQLDBVM01) Workload Servers (QBMF01-04) Monitoring and Management BP1014 Windows Server 2008 R2 Enterprise Edition EqualLogic Host Integration Toolkit (HIT) v3.4.2 EqualLogic Auto-Snapshot Manager/Microsoft® Edition v3.4.2 SQL Server® edition / version details2008 R2 8 x Windows Server 2008 R2 Enterprise Edition Workload generators (running within VMs): Quest Benchmark Factory version 6.
Related publications The following Dell publications are referenced in this document or are recommended sources for additional information. Microsoft SQL Server Database Protection Using EqualLogic Auto-Snapshot Manager / Microsoft Edition http://www.equallogic.com/WorkArea/DownloadAsset.aspx?id=5247 EqualLogic Configuration Guide http://www.delltechcenter.com/page/EqualLogic+Configuration+Guide Auto-Snapshot Manager/Microsoft Edition User Guide (v3.5.1): https://www.equallogic.
THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND.
