Backup and restore best practices for an Enterprise SQL Server 2008 Data Warehouse: HP XP24000 storage, ProLiant DL785 server and Symantec NetBackup

49 

Loading....

Loading....

Loading....

Loading....

Loading....

Full text

(1)

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

(2)

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)

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)

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)

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)

6

Figure 2. Configuration layout

HP ProLiant DL785 G5 running SQL Server 2008

Configuration

The 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)

7

Host bus adaptors

The 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)

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

Description

The 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)

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)

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)

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)

12

Figure 9. MSA volumes mounted on the SQL Server

SQL Server setup

SQL Server Edition

We 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)

13

Note

The same number of files is used for TEMPDB.

Figure 10 depicts the data file layout.

Figure 10. Data file layout

NetBackup installation and configuration

Installation

We 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)

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)

15

Disk Storage Unit

Figure 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)

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)

17

Windows Reliability and Performance Monitor

Windows 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)

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)

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)

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)

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)

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)

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)

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)

25

SQL Server backup MSA volumes

Table 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)

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)

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)

28

Figure 24. Summary of the assessment

NetBackup for SQL Server 2008

NetBackup SQL Agent—installation and settings

Installation

The 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)

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)

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)

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)

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

Introduction

As 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)

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)

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)

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)

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)

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)

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

Introduction

The 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)

39

SQLHOST "DL785G5-1" NBSERVER "BACKUP" STRIPES 004 BROWSECLIENT "DL785G5-1" MAXTRANSFERSIZE 6 BLOCKSIZE 7 RESTOREOPTION REPLACE RECOVEREDSTATE RECOVERED NUMBUFS 2 ENDOPER TRUE Note

When 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)

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)

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)

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)

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)

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)

45

Best practices

Database administrators

Location of SQL Server binaries

The 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 performance

Before 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)

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)

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)

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

(49)

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.

Figure

Updating...

Related subjects :