Table of contents
Introduction ... 3
Why performing fast backup is important ... 3
Online versus offline backup ... 4
Configuration and settings ... 5
Configuration layout ... 5
HP ProLiant DL785 G5 running SQL Server 2008 ... 6
Configuration ... 6
Host bus adaptors ... 7
Windows Server setup ... 7
Operating system ... 7
Instant file initialization ... 7
HP StorageWorks XP24000 Disk Array ... 7
HP StorageWorks Enterprise Systems Library ... 8
Description ... 8 Firmware ... 9 Cabling ... 9 Host Access ... 9 HP StorageWorks MSA2000 ... 10 SQL Server setup... 12 SQL Server Edition... 12 SQL Server installation ... 12 Database layout ... 12
NetBackup installation and configuration ... 13
Installation ... 13
Configuration ... 13
Monitoring infrastructure ... 16
HP StorageWorks XP Performance Advisor ... 16
Windows Reliability and Performance Monitor... 17
XP SVP (service processor) ... 17
SQL Server backup and restore ... 18
Database backup ... 18 Database restore ... 18 Parameters ... 18 Buffer space ... 19 Reporting ... 19 Backup ... 19 Restore ... 20 Baseline testing ... 20
Backup and restore best practices for an Enterprise
SQL Server 2008 Data Warehouse: HP XP24000
storage, ProLiant DL785 server and Symantec
NetBackup
Disk drives ... 20
Read performance on the source ... 20
Write performance on the target (backup) ... 20
Tape drives ... 21
SQL Server backup command ... 22
SQL Server backup to NULL ... 22
Influence of the buffercounts ... 22
Influence of the backup compression to NULL ... 23
SQL Server backup/restore to tape ... 23
SQL Server backup MSA volumes ... 25
SQL Server restore MSA volumes ... 25
Backup and restore— behind the scene ... 26
Summary... 27
NetBackup for SQL Server 2008 ... 28
NetBackup SQL Agent—installation and settings ... 28
Installation ... 28
NetBackup SQL policy ... 28
Parallelism ... 29
Initiate SQL Server backup and restore by using NetBackup Agent ... 30
NetBackup MS SQL Client GUI ... 30
SQL backup by using NetBackup ... 32
Introduction ... 32
Backup command generated by NetBackup ... 32
Back up to disks—setting disk drives ... 33
Backup to tapes—settings LTO3 tape drives ... 33
Impact of the compression ... 33
Back up to tape with NetBackup—Performance summary ... 35
Back up to disks with NetBackup—performance summary ... 36
Other consideration ... 37
Restore by using NetBackup ... 38
Introduction ... 38
Restore from tapes ... 40
Restore from disks ... 41
Best practices ... 45
Database administrators ... 45
Location of SQL Server binaries ... 45
Planning for file groups ... 45
Set up transaction log ... 45
TEMPDB ... 45
Server administrators ... 45
Monitor server workload ... 45
Windows Instant File Initialization ... 45
Storage administrators ... 45
Monitor XP/MSA performance ... 45
Backup administrators ... 45
Symantec NetBackup ... 45
Select the correct target backup device ... 46
Perform incremental backups to save time and space ... 46
Summary ... 46
Appendix A ... 47
Bill of materials ... 47
For more information ... 49
3
Introduction
Microsoft® SQL Server 2008 backup and recovery operations are critical activities. With mission-critical data warehouse workloads, the ability to back up and recover your data within the specified backup window is vital. This paper discusses the backup-recovery of an enterprise class data warehouse by using various backup and recovery methodologies.
To help you choose from the available configuration options and backup and recovery procedures, HP conducted extensive laboratory tests to determine best practices. This paper discusses those test results so that users can understand the options and the limitations of implementing backup and recovery by using Linear Tape-Open (LTO) tape and disk-to-disk devices. The audience for this paper is HP users in an enterprise environment currently running or planning to run SQL Server 2008. The paper includes:
Best practice recommendations for configuration, design, and deployment
Backup and recovery recommendations to integrate Symantec NetBackup and Microsoft SQL Server 2008
Impact on database performance and throughput for LTO tape and disk-to-disk
General recommendations for selecting backup and recovery methods
Supporting configuration recommendations for HP servers and for HP StorageWorks XP24000 disk array
By leveraging the recommendations and best practices, administrators can shorten backup windows and efficiently load the server, which reduces costs and maximizes hardware use and personnel resources.
Why performing fast backup is important
Figure 1 shows the impact of the backup on a query response time. The response time is multiplied by four when the backup is in progress. Even if the database stays online during the backup, it is going to be difficult to maintain the quality of service expected.
4
Figure 1. Impact of the backup on a query response time
The faster the backup can be processed, the easier it is for the administrator to provide the required performance.
Online versus offline backup
There are several obvious advantages for performing online backups of SQL databases with the backup and restore API). First, when full backups are performed, it guarantees that all data files and log files required are available in case of restore. It guarantees consistency from the transaction standpoint. It also provides more granularity for the restore (database, file group, and data page). Transaction log backup and file-group backup are supported.
Using offline backup (for example, operating system level backup), might cause the following issues:
Incomplete backup due to missing files
Inconsistent backup , backup occurs when SQL is running
Point-in-time restore is not doable
Full backup is required
0:00:00 0:07:12 0:14:24 0:21:36 0:28:48 0:36:00 0:43:12 0:50:24 0:57:36 1:04:48
Ref (Query) Ref (Backup)
Both
Query Response time Backup time
5
Configuration and settings
The data provided in this document is gathered by using HP server, storage, and software products along with SQL Server 2008. This section describes the infrastructure deployed for this project, and the recommended configuration guidelines for a data warehouse on the XP24000. Each component is presented in detail and includes the following:
The SQL server and the management servers
The storage subsystem
Configuration layout
Figure 2 shows the configuration implemented for this project and includes the following components.
Database server running SQL Server 2008 on an HP ProLiant DL785 G5 server (1)
HP StorageWorks XP24000 Disk Array (1)
HP StorageWorks 2000fc Modular Smart Array (MSA2000fc) Disk Array (2)
HP ESL 712e Tape Library (1), with Fibre Channel LTO3 tape drives (4)
Backup server running on an HP ProLiant DL380 G5 server (1)
6
Figure 2. Configuration layout
HP ProLiant DL785 G5 running SQL Server 2008
ConfigurationThe HP ProLiant DL785 G5 is an eight socket server based on the AMD Opteron™ processor. The server used for this testing is configured with 8 quad core processors (2.5 GHz) and 256 GB of RAM.
7
Host bus adaptorsThe server is configured with six 8-Gb dual-channel HP StorageWorks 82E PCI-e Fibre Channel host
bus adapter (HBA). Drivers
For this HBA, we use the Storport driver 2-10a7-1e driver. Our test configuration also includes Windows Multipath IO (MPIO) and Device Specific Module (DSM) for XP.
Link speed
Since the XP24000 and the MSA2000 host ports are 4-Gb ports, the link speed on the HBA is forced to 4 Gb. Topology is set to fabric.
Management
The Emulex management tool HBAnyware is used to set up and configure the HBAs on both servers.
Windows Server setup
Operating system
Microsoft Windows® Server 2008 Enterprise Edition for Extended System (x64) is deployed on this server.
Instant file initialization
Operations such as creating a database, adding files to an existing database, or restoring a database require some file initialization. This process causes these operations to take longer. In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.
To enable instant file initialization, the SQL Server service account must be granted
SE_MANAGE_VOLUME_NAME.
Note
As reported in the SQL Server ERRORLOG, the log file is always initialized even if Instant File Initialization is turned on:
2009-07-16 15:09:28.06 spid54 Zeroing completed on X:\1\Option3\tpch3000gcp.ldf
HP StorageWorks XP24000 Disk Array
The HP StorageWorks XP24000/XP20000 Disk Array is a large, enterprise-class storage system designed for organizations that cannot afford any downtime. The XP mitigates the risk of business downtime by providing complete hardware redundancies, hot-swappable components, and non-disruptive online upgrades.
Figure 3 shows how the sixteen array groups (eight disks each) were configured on the array. Each array group had three LDEVs defined.
8
Figure 3. XP24000 Disk Array group layout
For more details about the XP layout, see the ―SQL 2008 Data Warehouse best practices for HP XP24000 Storage, HP ProLiant DL785 G5 Server and HP Integrity rx8640 Server‖ white paper at http://h20195.www2.hp.com/V2/getdocument.aspx?docname=4AA2-5263ENW.pdf.
HP StorageWorks Enterprise Systems Library
DescriptionThe family of HP StorageWorks Enterprise Systems Library (ESL) E-series Tape Libraries offers best-in-class drive and cartridge densities. In a single cabinet, ESL can scale up to 1,139 TB of compressed (2:1) storage capacity with a transfer rate of up to 20.7 TB/hr compressed (2:1). The ESL achieves drive densities up to 109 TB of compressed (2:1) storage capacity per square foot. The ESL can be managed as a single library with up to five frames. The HP StorageWorks ESL that we evaluate includes the following components:
LTO3 Ultrium tape drives (4)
Slots (712)
9
Figure 4. ESL 712e settings
Firmware
The ESL is configured with the following firmware revisions:
Robotic: 5.10.15
Tape Drives: L67W Cabling
Figure 5 shows the cabling of the HP ESL connected to the Storage Area Network. As presented, the HP ESL has two fabric ports that are used to connect to SAN. For more information about setting up and configuring the ESL 712e, see the HP StorageWorks Enterprise Backup Solutions (EBS) overview and features at http://www.hp.com/go/ebs
Figure 5. HP ESL cabling
Host Access
When using HP StorageWorks Command View for TL, the tape drive and the robotic are presented to the host HBAs, as shown in Figure 6. As a result, all of the paths are used and the available
10
Figure 6. ESL Host Access
Figure 7 shows the ESL devices in the server device manager comprised of four tape drives and the tape library (robotic).
Figure 7. ESL devices in the server device manager
HP StorageWorks MSA2000
The MSA2000c features a high-performance 4 Gb Fibre Channel connected array. It allows you to grow the storage capacity up to 27 TB SAS or 60 TB SATA and supports up to 64 hosts for Fibre Channel attachment.
11
The configuration includes the following components: Dual controllers MSA2212fc (2)
Additional drive enclosures (3 per MSA)
300 GB, 15 K Dual Port SAS disks (48 disks per MSA)
Disks for the two MSA (96 total), which means 28.8 TB of raw storage The MSAs are configured as follows:
RAID 5 Virtual Disk (vdisk) (4) per MSA exposed through the two controllers (2 vdisks per controller)
Disks (12 per vdisk)
Volume (1 per vdisk)
Figure 8 shows the vdisk configuration.
Figure 8. Vdisk configuration
The eight volumes are presented to the database server and formatted by using the following parameters:
NTFS format
64 K cluster size
Default partition alignment
12
Figure 9. MSA volumes mounted on the SQL Server
SQL Server setup
SQL Server EditionWe use SQL Server 2008 Enterprise Edition Build 10.0.1779 x64 (ProLiant DL785) for this project. SQL Server installation
The installation of SQL Server is straightforward. The default instance is used, because only one instance is installed on the server. The authentication mode is set to Windows and SQL Server. The binaries of SQL Server are stored on the C: drive.
Note
You can specify the TEMPDB location during the installation. Backup and restore do not generate loads in TEMPDB. TEMPDB should be configured based on the application workload.
Database layout
We use a typical warehouse star schema database for this project that includes the following:
One-fact table
Multiple dimension tables
The data warehouse database size is approximately 4.5 TB and is based on two file groups:
LINEITEM, which is the container for the LINEITEM table (fact table)
GENERAL, which is the container for the dimension tables
We use 32 files per file group. This corresponds to one data file per CPU core, which is a SQL Server best practice. This eases the storage re-layout, allowing more flexibility without having to regenerate the 4.5 TB database.
Microsoft recommends multiple files in the file groups to avoid PFS (Page Free Space) contentions and because they are needed to stripe multiple storage volumes. However, the recovery time may be impacted as the file recovery is processed sequentially, (for example, one file after the other). To limit the scope of the project, we do not consider tests with a different number of files per file group in this project.
13
NoteThe same number of files is used for TEMPDB.
Figure 10 depicts the data file layout.
Figure 10. Data file layout
NetBackup installation and configuration
InstallationWe use NetBackup 6.5.4 for this project.
For more details regarding the installation of NetBackup, see the NetBackup Installation Guide for Windows.
The NetBackup topology for this project includes two nodes: the Master Server and the Media Server. In this project, the Master Server runs on the HP ProLiant DL380 G5, and it does not dispose to any backup devices.
The Media Server is installed on the HP ProLiant DL785 G5 (SQL Server). The Media Server has two backup target devices types: the disks (MSA) and the tape drives (ESL).
The NetBackup Client and the SQL Agent are automatically installed on the Master and the Media Servers.
Configuration
The configuration includes the following steps. 1. Configure the target devices on the Media Server.
oTapes library oDisks drives 2. Configure the Client
14
Tape library storage unit
The tape library is defined in NetBackup by using a Wizard. It allows you define the Robot Type and the technology of the tape drives. Figure 11 shows the tape library configuration.
15
Disk Storage UnitFigure 12 shows the MSA volumes configured as disk storage units in NetBackup.
Figure 12. Storage Unit configuration
Client-Media server
To target the local devices (tape drives or disks), the NetBackup client (SQL Server) properties must be changed. The default Media Server must be set with the host name of the SQL Server (see Figure 13). This way, the backup streams do not go through the network but instead, use the local devices to perform the backup and the restore. Figure 13 shows the NetBackup Client properties for the SQL Server.
16
Figure 13. NetBackup Client Properties for the SQL Server
Figure 14 shows what the NetBackup topology looks like when configured. The tape drives are physically attached to the dl785g5-1. The robotic is also physically attached to the SQL Server but controlled by the master server (backup)
Figure 14. NetBackup topology
Monitoring infrastructure
HP StorageWorks XP Performance Advisor
The HP StorageWorks XP Performance Advisor Software is a web-based application that allows you to collect and monitor real-time performance data from the HP StorageWorks family of XP disk array products. The simple browser-based interface of XP Performance Advisor Software allows you to customize performance data collection and set performance alarms.
17
Windows Reliability and Performance MonitorWindows Reliability and Performance Monitor is a Microsoft Management Console (MMC) snap-in that combines the functionality of previous stand-alone tools, including Performance Logs and Alerts, Server Performance Advisor, and System Monitor. It provides a graphical interface for customizing performance data collection and Event Trace Sessions.
Both XP Performance Advisor and Windows Reliability and Performance Monitor are run on the same server.
XP SVP (service processor)
The XP SVP changes the XP configuration from RAID 5 to RAID 1. It is also used for advanced performance monitoring. The XP SVP runs on a dedicated server within the XP disk array.
Note
18
SQL Server backup and restore
The SQL Server database engine includes backup and restore capabilities that can be initiated either by using the T-SQL backup/restore commands (with command line or with SQL Server Management Studio) or through the backup and restore API exposed by SQL Server. The NetBackup SQL Agent uses this API to perform the backup and the restore of SQL databases.
Figure 15 presents the SQL Server VDI API. For more information, see the SQL Server Books online.
Figure 15. SQL Server VDI API
Database backup
The scope of the backup with SQL Server can include one of the following items.
The entire database
A set of files/file groups
SQL Server supports full or differential backups. A full database backup represents the entire
database at the time of the backup finished. A differential database backup contains the data extents that were modified since the most recent database backup. Partial backup is a full backup of the targeted file groups or files. Differential backup are available as well for partial backups.
Database restore
SQL server support different restore levels:
The database
The data file
The page
In this project, only full backup and full restore of the entire database are considered.
Parameters
The backup and the restore can be initiated either by using SQL Server Management Studio or the command line. The command line gives more options to tune and optimize the backup/restore.
19
BLOCKSIZE is the size, in bytes, that is used as the device BLOCKSIZE. All data transfers are in integral multiples of this value. Values must be a power of two between 512 bytes and 64 KB inclusive. If this option is not specified with the command, then a default number of 512 bytes is used.
BUFFERCOUNT is the total number of buffers (of size MAXTRANSFERSIZE) that is used by the BACKUP or RESTORE operation. When more than one virtual device is used, the buffers are used as needed and are not associated with any given device.
Note
Smaller buffers may be used by SQL Server to handle the small metadata transfers. These small buffers are not included in the BUFFERCOUNT.
MAXTRANSFERSIZE is the size, in bytes, of the maximum input or output request which is issued by SQL Server to the device. The MAXTRANSFERSIZE must be a multiple of 64 KB. The range is from 64 KB through 4 MB. If this option is not specified with the command, a default of 64 KB is used
BUFFERCOUNT and MAXTRANSFERSIZE determine the total buffer space the backup process that is available. By using some trace flags, additional information is displayed in the SQL ERRORLOG. The following command shows how to enable the trace flags:
dbcc traceon(3605,3004,3014,3213,-1)
Buffer space
The following scenarios present the buffer space assigned by SQL Server:
Scenario 1: no parameters oBufferCount: 69 oMaxTransferSize: 1,024 KB oTotal buffer space: 69 MB
Scenario 2:
oBufferCount: 250 oMaxTransferSize: 4,096 KB oTotal buffer space: 1,000 MB
Total Buffer Space = BufferCount * MaxTransfersize
Reporting
SQL Server backups are recorded in ―MSDB‖, a system database. Note that both SQL native backups and VDI backups are reported.
Backup
The following T-SQL command generates a report showing the backup history. SELECT * FROM msdb.dbo.backupset
Backup_set_id Backup_start_date Backup_finished_date Compatibility level Database name Server name Compressed_backup_size
20
Restore
The following T-SQL command generates a report showing the restore history. select * from msdb.dbo.restorehistory
Baseline testing
To optimize the backup configuration, it is important to determine the capabilities of the different components involved in a backup or a restore operation. This includes the source and the target devices.
During a backup, the source is the XP disks that contain the SQL database. The target is either the disks (MSA2000) or the four LTO 3 tape drives.
For a restore, the source is either the MSA2000 or the tape drives. The XP disks are the target.
Disk drives
Read performance on the source
When using synthetic workload, the source LUNs are evaluated to measure the read performance. Figure 16 show the MB/s measured with different I/O size.
Figure 16. Influence of the I/O size on the read performance
Write performance on the target (backup)
When using synthetic workload, the target LUNs are evaluated to measure the write performance. Table 1 lists the measured MB/s.
0 500 1000 1500 2000 2500 3000 512K 1024k 2048K 4096k M e gab yte s p e r se co n d
21
Table 1. MSA2000 write performance
MSA2000
Target
Eight volumes (12 disk drives per volume) Profile Sequential R/W ratio 100% write I/O size 1,024 k MB/s 1,384
Tape drives
The performance of the tape drives is evaluated by using HP StorageWorks Library and Tape Tools (LTT). Figure 17 shows the performance of one of the LTO3 drives. To download LTT, see the HP StorageWorks Library and Tape Tools overview and features website located at
http://www.hp.com/support/tapetools.
Figure 17. HP StorageWorks library and tape tools
Each tape drive is evaluated individually. Table 2 lists the performance per drive and the profile of the test.
22
Table 2. LTO3 tape drive performance
Drive 1 Drive 2 Drive 3 Drive 4
Write 8 GB 8 GB 8 GB 8 GB
Block size 64 KB 64 KB 64 KB 64 KB
I/O size 1 M 1 M 1 M 1 M
Compression ratio 2:1 2:1 2:1 2:1
151 MB/s 152 MB/s 152 MB/s 152 MB/s
SQL Server backup command
As previously mentioned, a backup command is available with SQL Server to perform backups without any third-party software. With this command, a specific database can be backed up to the specified target devices. A NULL device can be used as a target to measure the performance of the source devices. Using NULL is a good way to size the target devices.
SQL Server backup to NULL
The following command shows how to use NULL device with the BACKUP statement. BACKUP DATABASE [tpch3000gcp] TO DISK='NUL'
WITH COMPRESSION, NOFORMAT, NOINIT, NAME = N'tpch3000gcp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Figure 18 shows the Transfer Rate measure during a backup operation targeting NULL devices. It reports an average of 1,100 MB/s.
Figure 18. Transfer rate during a backup to NULL
Influence of the buffercounts
Buffercount is a parameter that can be used in the SQL Backup command to increase the performance of the backup. Figure 19 shows how the value of this parameter impacts the performance of the backup. For this evaluation, the target device is set to NULL.
23
Figure 19 Influence of the buffercounts on backup performance
Influence of the backup compression to NULL
Backup compression is introduced in SQL Server 2008. The purpose of the test is to measure the impact of the compression when the backup is targeting NULL devices Figure 20.
Figure 20. Influence of the compression on backup performance
Compression increases the backup duration and the CPU use when targeting NULL devices.
Note
The NetBackup SQL Agent is not able to take advantage of the built in compression capability provided with SQL server 2008. The NetBackup agent has its own compression mechanism.
SQL Server backup/restore to tape
SQL Server cannot manage the robotic of the tape library. For this evaluation, we preloaded tapes in the four LTO3 tape drives. SQL used them as if there were standalone drives. As the average capacity of a LTO3 tapes is 800GB (that is if the data can be compressed at 2:1), four tapes were not
0:00:00 0:07:12 0:14:24 0:21:36 0:28:48 0:36:00 0:43:12 0:50:24 0:57:36 1:04:48 Default 50 100 150 200 250 300 350 B ac ku p d u ration (H H :M M :SS ) Buffercounts 0:00:00 0:07:12 0:14:24 0:21:36 0:28:48 0:36:00 0:43:12 No compression Compression B ac ku p d u ration (H H :M M :SS )
24
sufficient to store the entire database (4.5 TB). Manual tape unloads and loads were required during this test. Command View for TL was used for this operation.
BACKUP DATABASE [tpch3000gcp] TO
tape='\\.\Tape0',tape='\\.\Tape1',tape='\\.\Tape2',tape='\\.\Tape3' WITH COMPRESSION,FORMAT, NAME = N'tpch3000gcp-Full Database
Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Note
NetBackup services might lock the tape drives. This test can be done only if NetBackup is not installed or if the services are stopped.
Table 3 lists the measured results. The best performance is obtained with compression ON, the buffercount set to 250, and with Maxtransfersize set to 4 MB.
Table 3. Backup duration to tape
Figure 21 shows backup and restore duration with SQL Server native command.
Figure 21. SQL Server native backup and restore duration
0:00:00 0:28:48 0:57:36 1:26:24 1:55:12 2:24:00 2:52:48 3:21:36 3:50:24 4:19:12 Backup Restore D u ration (H H :M M :SS ) No compression Compression
# of tapes Compression Buffercount Max transfers size (reads) Duration (HH:MM:SS)
4 on default default (256k) 1:53:25
4 on 250 4MB 1:38:48
25
SQL Server backup MSA volumesTable 4 lists the backup duration for the different backup tests targeting the MSA2000 volumes. In this scenario, eight target backup files are used.
Table 4. Backup duration to MSA2000 volumes
Compression Buffercount Max transfer size
Duration (HH:MM:SS) MB/s ON Default Default 0:53:13 1,076.59 ON Default 4 MB 0:50:17 1,139.40 ON 250 Default 0:49:35 1,155.48 ON 250 4 MB 0:46:53 1,222.03 ON 350 4 MB 0:48:33 1,180.07
OFF Default Default 0:53:27 1,071.89
OFF 250 4 MB 0:49:39 1,153.93
SQL Server restore MSA volumes
Figure 22 shows the performance of the backup and restore when targeting the MSA volumes. Note
To evaluate the impact of the performance, a test is performed with 16 targets (two targets per MSA volume).
As reported, having more targets decreases the backup duration. Restore duration is not impacted by the number of targets nor by the compression.
26
Figure 22. Backup and restore performance with SQL native backup commands
Backup and restore— behind the scene
Behind the scenes, interesting events take place during the backup and the restore operations. When monitoring the data file access, all 64 files (32 files per file group) are concurrently read or written at the beginning of the backup or restore process. Also, some files are processed faster than the others and are completely backed up or restored much earlier. As a consequence, the load on the volumes is not homogeneous during the backup and the restore.
The Total Write Bytes/s during a restore is relatively linear as opposed to the load on each volume (see Figure 23). 0:00:00 0:07:12 0:14:24 0:21:36 0:28:48 0:36:00 0:43:12 0:50:24 0:57:36 Compression (8 targets) Compression (16 targets) No Compression (8 targets) B ac ku p /R e st o re d u rat io n (HH :M M :SS) Backup Restore
27
Figure 23. Volume-write activities during a restore
Summary
Figure 24 shows the performance measured during the configuration assessment. In our configuration, the source (XP Array) can deliver a higher data rate than the write rates on the targets (MSA2000 and LTO3 tape drives).
Note
While backing up to NULL device, the best performance measured with SQL Server is much lower than the raw read throughput we get from the array with synthetic load.
28
Figure 24. Summary of the assessment
NetBackup for SQL Server 2008
NetBackup SQL Agent—installation and settings
InstallationThe SQL Agent is automatically installed with the NetBackup Client Agent. NetBackup SQL policy
A SQL database can be backed up by using a NetBackup policy. A new policy must be defined with the following parameters:
Policy type: MS-SQL-Server
Policy storage unit
Policy volume pool
A Schedule
o Type of Backup: Application or Automatic o Media multiplexing
Client name: the SQL Server name
Backup Selection: the name of a SQL Script stored on the SQL Server o This script defines:
o The target database(s) o The number of stripes o The buffers per stripe o The block size (read)
29
The script can be automatically generated by using the NetBackup MS SQL Client GUI. When using this tool, you can either start the backup/restore or store the script file that is then specified in the SQL policy.Figure 25 shows a NetBackup policy for a SQL server.
Figure 25. SQL policy configuration
Parallelism
The media multiplexing is defined on the policy. The settings of the target backup devices must be adjusted accordingly.
Backup devices (Storage Unit) parallelisms
Tape drives: backup targeting simultaneously multiple tape drives o Enable or disable Multiplexing
o Streams per drive
Multiplexing: multiple streams per drive o Maximum concurrent job
The properties of the Master Server might need to be changed to increase the number of jobs per client.
30
Figure 26. Master server properties
Initiate SQL Server backup and restore by using NetBackup Agent
SQL backup can be initiated either by using the NetBackup MS SQL Client GUI or by using a NetBackup SQL Policies.NetBackup MS SQL Client GUI SQL Server connection
To connect to the SQL Server backup API, the NetBackup agent requires the SQL credentials. These credentials are defined in the SQL Server connection properties (see Figure 27).
31
Figure 27. SQL Server connection properties
SQL Server backup
Figure 28 shows the backup objects (for example, the databases and file groups) that the NetBackup client can back up once it is connected to the SQL Server instance.
Figure 28. Backup objects
Set NetBackup client properties
The client properties must be configured with the NetBackup server name and with additional parameters that are passed to SQL Server Backup API.
32
Figure 29. NetBackup client agent parameters
Sample of script file
The settings must be stored to automate the backup process. The following example displays a script file describing a job of a full backup of the tpch3000gcp database. This script is stored locally on the SQL Server. OPERATION BACKUP OBJECTTYPE FILEGROUP DATABASE “tpch3000gcp” FILEGROUP $ALL SQLHOST “DL785G5-1” NBSERVER “BACKUP” STRIPES 20 MAXTRANSFERSIZE 6 BLOCKSIZE 7 POLICY SQL NUMBUFS 2 ENDOPER TRUE MAXTRANSFERSIZE 6 corresponds to 4 MB BLOCKSIZE 7 corresponds to 64 KB
SQL backup by using NetBackup
IntroductionAs NetBackup SQL Agent uses the backup and restore API (VDI) provided by SQL Server, all of the SQL NetBackup backups are reported in the SQL server system database.
Backup command generated by NetBackup
The backup command generated by the NetBackup agent can be trapped by using Microsoft SQL Server Profiler.
The following command is equivalent to four stripes with 4 MB (MAXTRANSFERSIZE) and 32 as Client
33
backup database "tpch3000gcp" to VIRTUAL_DEVICE='VNBU0-6580-6600-1246428807', VIRTUAL_DEVICE='VNBU1-6580-6600-VIRTUAL_DEVICE='VNBU0-6580-6600-1246428807',
VIRTUAL_DEVICE='VNBU2-6580-6600-1246428807', VIRTUAL_DEVICE='VNBU3-6580-6600-1246428807' with stats = 10, blocksize = 65536, maxtransfersize = 4194304, buffercount = 128
The buffercount value is set based on the following formula:
Buffercount = # of stripes * Client Buffers per DBMS stripe
The stripe is equivalent to a VDI stream. Back up to disks—setting disk drives
To improve the performance of the backup when targeting disks devices, advanced parameters must be defined. You must create the file SIZE_DATA_BUFFERS_DISK.
C:\Program Files\Veritas\NetBackup\db\config\SIZE_DATA_BUFFERS_DISK: 1048576
Backup to tapes—settings LTO3 tape drives
To improve the performance of the backup when targeting tape devices, some advanced parameters must be defined. The following files must be created.
C:\Program Files\Veritas\NetBackup\db\config\NUMBER_DATA_BUFFERS C:\Program Files\Veritas\NetBackup\db\config\SIZE_DATA_BUFFERS C:\Program Files\Veritas\NetBackup \NET_BUFFER_SZ
For more information on these advanced NetBackup settings, see the Symantec support documents located at http://support.veritas.com/docs/245920 and http://support.veritas.com/docs/183702.
Table 5. Backup performance with different Config file settings
NetBackup setting Default settings Enhanced settings
NET_BUFFER_SZ Default 512 NUMBER_DATA_BUFFERS Default 256 SIZE_DATA_BUFFERS Default 262,144 Backup duration
(HH:MM:SS) 5:07:11 2:42:06
As presented, the role of these configuration files is clear. The performance is significantly improved with the enhanced settings enabled.
Impact of the compression
The first observation is that the compression is performed by the NetBackup agent and not by the SQL backup/restore API. This is confirmed by the fact that the SQL backup reports a non compressed size (compression ratio is 1).
SELECT database_name "Database Name", CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, backup_size) / CONVERT (FLOAT, compressed_backup_size)))
"Compression Ratio" FROM msdb.dbo.backupset Table 6. Compression ratio
Compression source Database name Compression ratio
Compression provided by the NetBackup Agent tpch3000gcp 1 Compression provided by SQL Server 2008 tpch3000gcp 1.94
As shown in Figure 30, the compression ratio obtained with the NetBackup agent is not as efficient that the SQL backup compression feature added with SQL Server 2008.
34
Figure 30. Backup compression ratio
Figure 31 shows the performance impact on backup when NetBackup compression is disabled or enabled. When the backup is performed to tapes, NetBackup compression should be turned off as the compression provided by the tape drives is more efficient. When the backup is performed to disk, NetBackup compression reduces the backup duration.
Figure 31. Impact of the compression on backup (tapes and disks)
As expected the CPU use is much higher when NetBackup compression is turned on. Figure 32 shows the average CPU use when performing a backup with compression and without compression to disks.
1 1.59 1.94 0 0.5 1 1.5 2 2.5 3 3.5 0 0.5 1 1.5 2 2.5
backup size (no compression SQL)
backup size (compression NetBackup)
backup size (compression SQL) Ter ab yte s Co m p re ssi o n r atio 0:00:00 1:12:00 2:24:00 3:36:00 4:48:00 6:00:00 To Tapes To Disks B ac ku p d u ration (H H :M M :SS ) Compression ON Compression OFF
35
Figure 32. Impact of the compression on CPU use
Back up to tape with NetBackup—Performance summary
Figure 33 shows in repeated testing that the backup duration improves. The data rate (MB/s) is calculated based on a 3.60-TB database.
The best performance (2:00:36) is obtained with the following parameters:
Target type: 4 LTO3 tape drives
Four stripes
NetBackup compression OFF
Buffer count 128
Max transfer size: 4 M
NetBackup tapes config files
Adding more stripes (eight) does not improve the performance.
0 10 20 30 40 50 60 70 80 90 Compression No Compression Pr o ce ssor tim e ( % )
36
Figure 33. Backup duration improvement during the project (tapes)
Back up to disks with NetBackup—performance summary
Figure 34 shows how the backup duration improves with repeated testing. The data rate (Megabytes per second) is calculated based on a 3.60 TB database.
The best performance (0:40:33) is obtained with the following parameters:
Target type: disk volumes (8)
32 stripes
NetBackup Compression OFF
Buffer count 512
Max transfer size: 4 M
NetBackup disk config files
6:02:03 4:45:51 3:46:05 5:07:11 2:13:16 2:42:06 2:00:36 1:38:48 0:30:21 0 200 400 600 800 1000 1200 1400 1600 1800 2000 0:00:00 1:12:00 2:24:00 3:36:00 4:48:00 6:00:00 7:12:00
Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Run 7 Ref SQL to Tape Ref SQL To NULL M e gab yte s p e r se co n d B ac ku p d u ration (H H :M M :SS ) Backup Duration Megabyte per second
37
Figure 34. Backup duration improvement during the project (disks)
Other consideration Back up to local device
During the backup, HP recommends that you verify that there is no traffic over the network. Make sure that you are not backing up over the Ethernet to another media server, which is indicated by very high network use. Assign the NetBackup client to the NetBackup media manager to perform the backup on the local devices (tape drives or disks drives).
This can be determined by using Windows Server Resource Monitor.
Figure 35. Windows Server Resource Monitor
Mounting duration
Figure 36 shows the time needed to demount and mount the needed tapes to the tape drives. It takes 200 seconds to complete this task. The target database size is roughly 3.5 TB and one LTO3 tape
1:33:44 1:05:12 1:17:58 1:11:18 0:40:33 1:12:45 0:56:30 0:46:480:46:53 0:30:21 0 200 400 600 800 1000 1200 1400 1600 1800 2000 0:00:00 0:14:24 0:28:48 0:43:12 0:57:36 1:12:00 1:26:24 1:40:48 Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Run 7 Run 8 Ref SQL To disks Ref SQL to NULL M e gab yte s p e r se co n d B ac ku p d u ration (H H :M M :SS ) Backup duration Megabytes per second
38
contains 800 GB, which means that eight tapes are required for each backup when using the four drives.
Figure 36. Transfer rate while changing tapes
Storage unit load balancing policy
When the backup is performed to disks storage units, load balancing policies are available to control the way that the NetBackup processes access the different targets. These policies have been
evaluated (for the one applicable). As Table 7 lists, the policies do not significantly impact the backup duration.
Table 7. Compression ratio
Load balancing policy
Backup duration (HH:MM:SS) Prioritized 1:17:34 Failover N/A RoundRobin 1:17:20
Media server load balancing N/A
Restore by using NetBackup
IntroductionThe restore is initiated from the NetBackup MS SQL Client on the SQL Server. Browse the backup history from the GUI. The target backup version must then be selected. Restore options are available to check the consistency or to define the required recovery state. You can then directly initiate the restore. All the settings can also be stored in a text file .bch for
OPERATION RESTORE OBJECTTYPE DATABASE DATABASE "tpch3000gcp"
# The following image is type: Full NBIMAGE
39
SQLHOST "DL785G5-1" NBSERVER "BACKUP" STRIPES 004 BROWSECLIENT "DL785G5-1" MAXTRANSFERSIZE 6 BLOCKSIZE 7 RESTOREOPTION REPLACE RECOVEREDSTATE RECOVERED NUMBUFS 2 ENDOPER TRUE NoteWhen using the GUI to initiate the restore, set the recovery state to Not recovered. To get the restored database online after the restore, set the recovery state to Recovered.
Figure 37 shows the GUI when performing a restore. Select the backup image needed for the restore from the backup history.
Figure 37. Browsing the backup history
When using multiplexing, it might take a while for NetBackup to initiate all the streams needed for a database restore. The default timeout setting 300 seconds was not enough and the restore operations were aborted. It is required to increase the value of the timeout; with 1000 seconds, NetBackup was able to initiate all the streams and to start the restore of the target database. With 32 streams, we measured roughly 11 minutes between the initialization of the first stream and the last stream. As Figure 38 shows, the timeout is defined on the NetBackup client properties.
40
Figure 38. Timeout settings
Restore from tapes
The best performance (2:55:01) for the restore (entire database) is obtained with the following parameters:
4 stripes
Block size 64 K
Max transfer size 4 M
Buffercount 128
Compression OFF
The following command is captured with the SQL Profiler when launching the restore with the NetBackup SQL client:
Restore database "tpch3000gcp" from VIRTUAL_DEVICE='VNBU0-6072-5400-1247056009', VIRTUAL_DEVICE='VNBU1-6072-5400-VIRTUAL_DEVICE='VNBU0-6072-5400-1247056009',
VIRTUAL_DEVICE='VNBU2-6072-5400-1247056009', VIRTUAL_DEVICE='VNBU3-6072-5400-1247056009' with stats = 10, blocksize = 65536, maxtransfersize = 4194304, buffercount = 128, replace
Note
Increasing the number of stripes (multiplexing) might increase the performance of the backup, but it significantly impacts the time to restore.
41
Figure 39. NetBackup backup and restore duration with tape drives
Restore from disks
The best performance (1:28:33) for the restore (entire database) is obtained with the following parameters:
8 targets (MSA Volumes)
32 stripes
Block size 64 K
Max transfer size 4 M
Buffercount 512
Compression OFF
Figure 40 shows how the restore duration improves with repeated testing. The data rate (Megabytes per second) is calculated based on a database size of 3,604,556,203,008 bytes (3.6TB).
0:00:00 0:28:48 0:57:36 1:26:24 1:55:12 2:24:00 2:52:48 3:21:36 Backup Restore Duration
42
Figure 40. Restore duration improvement during the project (disks)
Figure 41 shows the disk activities while performing a restore from a non compressed backup. The MSA disk readings are written to the XP disks.
Note
The average CPU use is 26%.
1:54:21 2:23:572:14:57 1:28:331:32:32 4:19:03 1:32:321:33:32 0 100 200 300 400 500 600 700 0:00:00 0:28:48 0:57:36 1:26:24 1:55:12 2:24:00 2:52:48 3:21:36 3:50:24 4:19:12 4:48:00
Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Run 7 Run 8
M e gab yte s p e r se co n d R e sto re d u ration (H H :M M :SS ) Restore duration Megabytes per second
43
Figure 41. Restore—read and write activities with no compression
Figure 42 shows the disk activities while performing a restore from a compressed backup. There are more write activities than read activities.
Note
44
Figure 42. Restore—read and write activities with compression
Figure 43 shows the backup and restore duration with the MSA.
Figure 43. NetBackup backup and restore duration with MSAs drives
0:00:00 0:14:24 0:28:48 0:43:12 0:57:36 1:12:00 1:26:24 1:40:48 No compression Compression D u ration (H H :M M :SS ) Backup Restore
45
Best practices
Database administrators
Location of SQL Server binariesThe SQL Server 2008 binaries should be placed on a drive separate from all the data drives. Planning for file groups
When planning for file groups, recoverability is as important as performance. Consider the need to back up and restore your database when designing the database schema. Using multiple files on multiple LUNs helps to maintain a balanced storage design to achieve high-performance backups. The SQL Server I/O demand can be evenly balanced across XP ports/controllers. Using multiple data files and multiple backup devices allows for higher backup concurrency and higher performance backups. Understanding server and storage array use during backups helps to identify opportunities for
increasing concurrency and backup performance. Set up transaction log
With a Data Warehouse workload, the transaction log is not as critical because the accesses are mainly reads. However, HP recommends placing the transaction log files and data files on separate LUNs for higher resiliency.
TEMPDB
TEMPDB does not have a key role for backup and restore operations. However, the TEMPDB storage
layout must be adjusted based on the application workload
Server administrators
Monitor server workload
Server administrators should note how SQL Server performs during backups. Servers may have room to reconfigure backup jobs to allow for more concurrency, which, in turn, should provide for better performance. Backing up a larger database with four concurrent streams can significantly increase the CPU use, especially if compression is turn on.
Windows Instant File Initialization
Instant File Initialization should be turned on. Without, SQL data files are initialized by dumping zeros before the file is used. This can affect performance when accomplishing tasks such as database restores or creation.
Storage administrators
Monitor XP/MSA performanceBefore storage administrators set up a disk-to-disk backup scenario on the same array, they need to characterize both the array I/O and the application workload.
Backup administrators
Symantec NetBackup Tune the device files to receive the best performance from the target backup devices.
Adjust the timeout for complex restore operations (multiples streams).
Increase the maximum concurrent jobs.
Be sure that the backup and restore is not operated through the network. Windows Resource Monitor is a good way to check the operation flow.
46
Know that increasing the number of concurrent stripes for the backup might significantly affect the restore performance.
Select the correct target backup device
Understanding the performance of the source devices helps to size the performance and the number of target devices. In our scenario, the bottleneck was located on the target devices. With more powerful target devices, backup and restore duration could be improved. Reads and Writes capabilities must be assessed. Keep in mind that a restore generates writes on the backup source. Perform incremental backups to save time and space
When using larger databases with SQL Server, you can use differential and incremental (transaction log) backups instead of daily full backups. They are not in the scope of this project but should be considered, especially on a Data Warehouse.
Summary
These test results demonstrate how to properly plan for, successfully deploy, and productively use backup and recovery technologies for SQL Server 2008 with SQL Native Backup, Symantec NetBackup, and HP servers and storage. Both tested configurations—LTO tape and disk-to-disk backup—have a valid place in SQL Server 2008 backup design.
47
Appendix A
Bill of materials
Table A-0. Tape Storage -HP StorageWorks ESL 712e Tape Library Qty Part number Description
1 AA934C Zero drive, 712 LTO cartridge base library 4 AA938A ESL E-Series Drive Cluster
4 AD595A ESL E-Series Ultrium 960 FC Drive Upgrade Kit 1 AD576A ESL E-Series e2400-FC 4 G Interface Controller 1 Interface Manager (comes with Enterprise Library)
Table A-1. HP StorageWorks XP24000 Disk Array
Qty Part number Description
1 AE131A HP XP24000 DKC Disk Control Frame 3 AE136A HP XP24000 16 Port 4 Gb FC CHA 1 AE150A HP XP24000 12V DKC Power Supply 1 AE151A HP XP24000 Cache Memory Adapter
6 AE152A HP XP24000 8 GB Cache Memory
1 AE155A HP XP24000 Shared Memory Adapter
4 AE157A HP XP24000 4 GB Shared Memory
1 AE160A HP XP24000 DKC 12V Battery
1 AE162A HP XP24000 Cache Switch
2 AE164A HP XP24000 DKA Disk Adapter Set 1 AE173A HP XP24000 DKU Disk Unit Frame
43 AE179A HP XP24000 300GB 15 k rpm Array Group 4 AE179AS HP XP24000 300GB 15 k rpm Spare Disk
Table A-2. HP StorageWorks MSA2212FC
Qty Part number Description
2 AJ745A HP 2212fc DC Enh Modular Smart Array 6 AJ750A HP MSA2000FC 3.5in Dual I/O 12 Drive Encl 72 AJ736A HP MSA2 300GB 15K rpm 3.5 inch SAS HDD
48
Table A- 3. SQL Server HP ProLiant DL785 G5 256 GB RAM
Qty Part number Description
1 AH233A HP ProLiant DL785 G5 Rack CTO Chassis 1 507528-L21 AMD Opteron 8384 SE 2.5 GHz-2 MB Quad-Core
Processor (75W ACP) 4P Option Kit
1 507528-B21 AMD Opteron 8384 2.5 GHz Quad-Core Processor (75W ACP) 4P Option Kit
1 405139-B21 HP Smart Array P400 256 MB FIO Cache
1 264007-B21 Slimline DVD-ROM Drive (8X/24X) Option Kit (Servers) 2 418367-B21 HP 146 GB 3 G SAS 10 K SFF DP ENT HDD
5 AJ763A HP StorageWorks 82E Dual Channel 8 Gb PCI-e HBA 32 408854-B21 8 GB REG PC2-5300 (2 x 4 GB) Kit, dual rank
Table A-4. Management servers
Qty Part number Description
2 399524-B21 HP ProLiant DL360 G5 Rack CTO Chassis
2 416579-L21
Dual-Core Intel® Xeon® Processor 5160 – 3.00 GHz, 80 Watts, 1333 FSB
2 397411-B21 2 GB FBD PC2-5300 2 x 1 GB Kit 2 413741-B21 Smart Array P400i controller for DL360 G5 4 384842-B21 HP 72 GB 3 G SAS 10 K SFF DP HDD
Table A-5. Backup server
Qty Part number Description
1 391835-B21 HP ProLiant DL380 G5 Rack CTO Chassis
2
461463-L21 Dual-Core Intel Xeon Processor L5240 (3.00 GHZ, 40 Watts, 1333 FSB), FIO Kit
2 397413-B21 4 GB FBD PC2-5300 2 x 2 GB Dual Rank Kit
1 405160-B21
HP Smart Array P400/256 for DL380 G5/ DL385 G2 FIO Controller
For more information
HP StorageWorks Enterprise Backup Solutions (EBS) http://www.hp.com/go/ebs
HP Customer Focus Testing Solutions http://www.hp.com/go/hpcft
Veritas NetBackup Home Page
http://www.symantec.com/business/netbackup
Veritas NetBackup Manuals and links
http://seer.entsupport.symantec.com/docs/290282.htm
Microsoft SQL Server 2008
http://www.microsoft.com/sqlserver/2008/en/us/default.aspx
SQL Server Backup compression
http://technet.microsoft.com/en-us/library/bb964719.aspx
Feedback
To help us improve our documents, please provide feedback at
http://h20219.www2.hp.com/ActiveAnswers/us/en/solutions/technical_tools_feedback.html.
Technology for better business outcomes
© Copyright 2010 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements
accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein.
Microsoft and Windows are U.S. registered trademarks of Microsoft Corporation. AMD Opteron is a trademark of Advanced Micro Devices, Inc. Intel and Xeon are trademarks of Intel Corporation in the U.S. and other countries.
