Dell Microsoft SQL Server 2008 Backup and Restore Guide using Dell EqualLogic
Summary
Optimize your data protection strategy with this comprehensive whitepaper detailing SQL Server backup and restore workflows utilizing Dell EqualLogic storage. Designed for DBAs and Storage Engineers, this guide analyzes the performance impact on the SQL host during various backup operations (full, differential, T-LOG). It provides best practice recommendations, test studies, and reference architectures to ensure efficient, reliable, and optimized data recovery services in your enterprise environment.
Page 1 Text Content
Dell Equal Logic Best Practices Series
Microsoft SQL Server 2008 Backup and Restore using Dell Equal Logic A Dell Technical Whitepaper
Storage Infrastructure and Solutions Engineering Dell Product Group March, 2012
Page Summary Contents For Dell Microsoft SQL Server 2008 Backup and Restore Guide using Dell EqualLogic
Page 1
Dell Equal Logic Best Practices Series Microsoft SQL Server 2008 Backup and Restore using Dell Equal Logic A Dell Technical Whitepaper Storage Infrastructure and Solutions Engineering Dell Product Gro...
Page 2
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. ...
Page 3
Table of Contents 1 Introduction ....................................................................................................................................................... 3 1.1 Audience ...
Page 4
Acknowledgements This whitepaper was produced by the PG Storage Infrastructure and Solutions of Dell Inc. The team that created this whitepaper: Lakshmi Devi Subramanian, Puneet Dhawan, and Camille Da...
Page 5
Executive Summary Creating an optimized Microsoft® SQL Server® database backup is the foundation for every DBA’s data protection plan. This paper studies the impact on SQL Server host during backup an...
Page 6
1 Introduction SQL Server databases form the backbone for many businesses, housing data for critical applications. Because access to this data is critical for day to day business operations, Service L...
Page 7
2 Considerations for selecting a data backup solution Business environments differ widely, from small businesses running just a few servers to large data centers managing complex server and storage ar...
Page 8
3 Test System Configuration The SQL Server test system used to conduct testing for this paper is shown in Figure 1 and Figure 2. Figure 1 Native SQL Server LAN and i SCSI SAN connectivity BP1021 Micro...
Page 9
Figure 2 High level view of test system components Key design details in the test system configuration include: • Installed and configured SQL Server 2008 R2 Enterprise Edition in a Windows Server 200...
Page 10
Figure 3 v Switch0 configuration o A separate v Switch (v Switch1) for i SCSI SAN connectivity. o Created virtual network adapters (type VMXNET 3) within the VM and assigned them to the v Switch1 (ref...
Page 11
• The two local disks installed in the R710 server were configured as a RAID 1 set. ESXi 4.1 was installed on these disks, and the guest virtual machine OS disk partitions were also hosted within the ...
Page 12
4 Test studies To support development of this paper, a full-scale virtualized Microsoft SQL server configuration running within a VMware v Sphere v4.1 virtual machine environment was deployed and test...
Page 13
4.1.1 Baseline Test #1: Impact of multiple backup target files on backup throughput Baseline test #1, studies the behavior of increasing the backup process speed by creating multiple writer threads vi...
Page 14
The read/write I/O throughput is the main performance measure which impacts database backup duration. The database backup is mostly a sequential operation with a block size of approximately 256KB or l...
Page 15
4.1.2 Baseline Test #2: Impact of multiple target volumes/files on backup throughput Baseline test #2, studies the impact of increasing storage volumes for hosting the backup target files. As describe...
Page 16
Figure 8 illustrates the write I/O throughput achieved in each of the configurations shown in baseline test#2 (refer to Figure 7). Note the highlighted data with maximum throughput of 307MB/sec for th...
Page 17
Figure 9 Backup/restore times in both the configurations Figure 10 Backup read/write Throughput comparisons Writing to multiple files across different disk volumes provides better data backup rates an...
Page 18
4.2 Impact of backup and restore processes on SQL host using native SQL server tool The goal of this test scenario was to study the performance impact on the production server when running the SQL ser...
Page 19
Test details: • Total database size at the full backup point: ~1TB • Database workload: During the test timeline Quest Benchmark Factory was used to simulate an OLTP style workload. A constant 500 use...
Page 20
Impact of full backup & verify Figure 13 shows an increase in CPU utilization at the SQL production server during full backup and verify. The average CPU utilization percentage increased from 1.7%...
Page 21
Figure 15 shows the IO operations at the storage arrays, recorded using SANHQ. It shows the IOPS before full backup and during full backup. The user transactions have been running throughout before, d...
Page 22
Impact of transaction logs backup Figure 17 illustrates the increase in CPU utilization during every transaction log backup. This increase appears as a spike in utilization for each of the transaction...
Page 23
Restore time and throughput The total time required to complete full database and transactional log restore were measured along with throughput during each operation. Table 3 shows the restore operati...
Page 24
Figure 20 Restore throughput comparisons BP1021 Microsoft SQL Server 2008 Backup and Restore using Dell Equal Logic 21
Page 25
4.2.2 Test #2: Performance impact studies during full, tlog, and differential backups This test simulates an environment with weekly full backups, daily differential backups, and transaction log backu...
Page 26
CPU Utilization Figure 22 shows increase in CPU utilization during full, tlog, and differential backups. The average CPU utilization percentage increased from 1.7% to 13% during full backup, 1.7% to 1...
Page 27
Figure 24 shows the IO operations at the storage arrays, recorded using SANHQ. It shows the IOPS before full backup and during full and differential backups. The user transactions have been running th...
Page 28
Disk Latency Figure 25 shows the increase in disk read latency (measured using Perfmon on the SQL Server host) from the Equal Logic array that is part of the data pool during full backup and verify pe...
Page 29
The “Baseline-Full Restore” in Figure 26 is from the baseline test configuration 2(c) from Figure 7. Figure 26 compares the restore times of baseline and Test Case#2 (Full, Tlog, and Differential Back...
Page 30
4.2.3 Test #3: SQL Server performance impact studies during Compression SQL compression, if enabled could improve backup storage requirements, however could also impact the resource utilization on the...
Page 31
Figure 29 Backup duration comparison Figure 30 compares the reduction in the backup file size after enabling compression. It also shows the increase in the CPU utilization on the SQL server both durin...
Page 32
4.2.4 Analysis and Conclusion As expected, we saw a significant increase in CPU utilization, application response times and disk response times during full backup as compared to transaction log and di...
Page 33
5 Best Practice Recommendations 5.1 Network Infrastructure The following are network infrastructure design best practices: • Design redundant SAN component architectures. This includes the NICs on the...
Page 34
5.3 VMware v Sphere ESXi Server/VM The lab test environment for this paper was comprised of a VMware ESXi server to host SQL Server database virtual machines as well as the QBMF work load simulation, ...
Page 35
5.4 Best practices: Data backup using native SQL server tools Follow these best practices for data backup using native SQL server tools and Equal Logic arrays. Database Layout Ensure that SQL Server d...
Page 36
General backup strategy best practices It is very important that backup destinations reside on storage devices (Equal Logic storage pools) that are separate from where the databases are stored. I/O ba...
Page 37
Appendix A 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 5 Test Configu...
Page 38
Table 6 Test configuration software components Test Configuration Software Components Operating systems Host: VMware v Sphere ESXi Server v4.1 Guest: Microsoft® Windows Server 2008 R2 Enterprise Editi...
Page 39
Additional resources Support.dell.com is focused on meeting your needs with proven services and support. Dell Tech Center.com is an IT Community where you can connect with Dell Customers and Dell empl...
Page 40
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.
Manual Details
| Brand | Microsoft |
|---|---|
| Pages | 40 |
| File Size | 1.71 MB |
| Published | May 30, 2026 |
75 views
Enter the captcha to get the download link:
Frequently Asked Questions
What is the primary goal of this whitepaper?
This paper studies the impact on the SQL Server host during backup and provides best practices and reference architecture for data protection plans.
Which types of backups were tested for performance impact on the SQL server?
Performance was studied across full, transaction log (tlog), differential, and compressed backups to determine system load.
What storage solutions are recommended for enhancing SQL Server protection?
Dell EqualLogic Smart Copy Snapshots or integrating with PS Series Arrays via iSCSI SANs are referenced for optimal protection.