Configuration best practices for Microsoft SQL Server 2005
with HP StorageWorks Enterprise Virtual Array 4000 and
HP blade servers white paper
Executive summary... 3
Intended audience ... 3
Overview of test environment ... 3
Test environment architecture... 3
Storage configurations... 4
Performance and results... 6
Performance collection and monitoring ... 6
Disk Group comparisons... 8
Microsoft SQL Server performance results... 8
EVA4000 performance results ... 10
VRAID1 and VRAID5 comparison ... 12
Overall evaluation—Server and storage ... 16
Server evaluation ... 16
Storage evaluation ... 18
Backup and restore with SYMANTEC NetBackup ... 18
Disk-to-FATA backup and restore... 19
Disk-to-tape backup and restore ... 19
Disk-to-FATA-to-tape backup and restore ... 20
Backup and restore performance results... 20
Appendix A—Reference configuration BOM ... 23
Appendix B—HP StorageWorks Enterprise Virtual Array VRAID technology... 24
Appendix C—Performance metrics ... 25
Viewing Microsoft SQL Server 2005 performance metrics when running in WOW mode... 25
Windows Server Performance Monitor counters... 25
For more information... 28
HP SAN Design Guide ... 28
HP Active Answers (requires secure login account) ... 28
Microsoft SQL Server Performance Information ... 28
Command View EVA and EVAperf... 28
Enterprise Backup Solution (EBS)... 28
Executive summary
Configuration best practices for Microsoft SQL Server 2005 with HP StorageWorks Enterprise Virtual Array 4000 and HP blade servers is a best practices paper that leverages HP servers, storage, and
management software by providing the recommended best practices for configuration of an HP StorageWorks Enterprise Virtual Array (EVA) storage subsystem deployed into a Microsoft® SQL Server 2005 environment with HP servers. Additionally, sizing and performance data based on the tested reference configuration is presented. The paper covers guidelines for a total end-to-end deployment of Microsoft SQL Server, encompassing servers, storage, management software, and offline full database backup practices.
This solution is targeted at the entry-level and mid-range or small and medium business (SMB) market. The test configuration and equipment used for best practices in this paper is meant to represent what a typical SMB would run in its IT environment.
This paper includes:
• Detailed description of the test environment
• Overall best practice recommendations for storage layout based on real performance results • Offline database backup and restore methods and performance results using SYMANTEC
NetBackup and HP StorageWorks Library and Tape Tools Utility
Intended audience
This paper is intended for solution architects and storage administrators engaged in planning and deployment of Microsoft SQL Server 2005 environments on HP EVAs. Many of the terms and references pertain to specific storage technologies and require knowledge of storage design and architecture.
Overview of test environment
This paper focuses on examining the effects of different storage configurations on the HP
StorageWorks 4000 Enterprise Virtual Array (EVA4000). Variables such as Virtual RAID types, virtual disks and Disk Group layouts, and database sizes are discussed. (For information on EVA VRAID technology, see Appendix B.) Microsoft SQL Server 2005 data and log file placement are examined and performance numbers are compared to give the reader perspectives on what to expect when designing a Microsoft SQL Server 2005 environment on an EVA4000.
Test environment architecture
HP ProLiant BL45p blade servers are used to host the Microsoft SQL Server application and an HP ProLiant BL35p blade server is used as a backup server to host the backup application. HP
StorageWorks Insight Manager was used to monitor server events (for a list of server hardware and software components, see Appendix A.).
Figure 1. Test environment
Storage configurations
The storage consisted of an EVA4000 with two HSV210 controllers and four drive enclosures (2C4D configuration). The disks were placed in an inline layout and striped vertically. The database and log files resided on 28 146-GB high-speed Fibre Channel drives and the backups resided on 28 250-GB FATA drives. HP StorageWorks Command View EVA software was installed on a separate server to manage the EVA4000 and to configure the storage.
The database storage will be designed using three different virtual disk and Disk Group layouts (see Figure 2). Each storage layout will be tested using EVA VRAID1 and VRAID5 and the results
contrasted. The three different storage examples being tested include:
• Multiple Virtual Disks/Multiple Disk Groups—Separate Disk Groups will be used for data and log files.
– 4 VDisks will be used to host the data files. – 1 VDisk will be used to host the log files.
– 20 physical disks make up the data files Disk Group. – 8 physical disks make up the log files Disk Group.
• Two Virtual Disks/Multiple Disk Groups—Separate Disk Groups will be used for data and log files. – 1 VDisk will be used to host the data files.
– 1 VDisk will be used to host the log files.
– 20 physical disks make up the data files Disk Group. – 8 physical disks make up the log files Disk Group.
The Microsoft SQL Server database sizes will be 100 GB, 250 GB, 500 GB, and 1 TB. The user load on the Microsoft SQL Server 2005 databases will be simulated using an OLTP-type workload and a no-wait or constant 3:2 read to write ratio of transactions. The workload will be modified for each database size to maintain a <=15ms response time across database volumes. This load scenario will be used in every test case and modified accordingly to accommodate various database sizes.
Figure 2. Storage layouts
Table 1.
Test # Disk Groups Virtual Disks Physical Disks Data Group Physical Disks Log Group Total Physical Disks 1 2 5 20 8 28 2 2 2 20 8 28 3 1 2 28 28
Performance and results
The performance comparison and results will be broken up between Disk Group configurations and virtual RAID level differences. The Disk Group configurations will show the performance variances of different database sizes configured with the three storage layouts as described in the previous section. The focus will be on VRAID1 for the highest possible performance for the given test configurations. Performance comparisons between VRAID1 and VRAID5 will follow the Disk Group configuration testing. This section shows the user how performance will differ between VRAID1 and VRAID5.
Performance collection and monitoring
Performance metrics were collected using Microsoft Windows® Performance Monitor or Windows Perfmon. Performance metrics from Microsoft SQL Server 2005 and the EVA4000 integrate directly into the Microsoft Windows Perfmon utility. To view a detailed description of Microsoft SQL Server 2005 and EVA4000 performance metrics, see Appendix C—Performance metrics.
Note:
In some instances specifically running Microsoft SQL Server 2005 32 bit on Microsoft Windows Server 2003 x64 Edition (WOW or Windows on Windows mode), the Microsoft SQL Server 2005 counter metrics will not show up. For more information, see Appendix C—Performance Metrics.
To set up Microsoft Windows Performance Monitor to view and log Microsoft SQL Server 2005 performance, launch Windows Perfmon by way of command line perfmon.exe or the Windows MMC Performance snap-in. Perfmon will start up with the System Monitor running with some basic counters selected. To view Microsoft SQL Server 2005 counters with no logging, right-click in the plotting area and click Add Counters. The Performance Object dropdown menu displays. Use this dropdown list to scroll to the Microsoft SQL Server counters. Figure 3 shows an example of this list. The performance counters monitored as part of this test are listed in Appendix C.
Figure 3. Windows Performance Monitor—SQL Server 2005 counters
To set up logging to view performance over time, click the Counter Logs option in the Performance MMC. A default log is in the right-hand pane. Right-click in the right-hand pane and select New Log
Setting. Choose a name for the log settings. In the General tab, select Add Counters. Next, click the Log Files tab and choose the log file type from the dropdown menu. Binary is the most common type
but there a few from which to choose. If the default location for log files is not satisfactory, choose an alternate location by clicking the Configure button. Set up a schedule to start logging performance or select Manual to run the log at any time. When these steps are completed, click OK to save the log file. If the manual option was chosen in the Schedule tab, right-click the log settings and choose Start to start logging the performance counters.
To view the results when finished, click the icon that looks like a disk in the System Monitor toolbar. Highlight the Log Files button and click Add. Choose the log file to view and use the Data tab to add counters to view. The system monitor will only show the log of those counters chosen in the Data tab. To view a particular time of a run, use the Source tab and move the Time Range to the desired time.
Viewing performance metrics for the EVA4000 is very similar to the preceding process. The only difference is that Microsoft Windows Performance Monitor must be run from the server running the HP StorageWorks Command View software and HP Command View EVAperf must be installed on the server. EVAperf can be run by way of a command line or integrated with Microsoft Windows
Performance Monitor. For more information on HP Command View EVAperf, see Appendix C and the For more information section.
Note:
When using Microsoft Windows Performance Monitor for background logging of HP Command View EVAperf statistics, HP recommends setting the EVA Data Collection Service to start automatically. This can be done by modifying the start up parameters through the server’s MMC Computer Management—Services snap-in.
Disk Group comparisons
As previously stated, the EVA4000 configuration consisted of two HSV200 controllers with 28 high-performance Fibre Channel drives and 28 FATA drives. The database and log files were placed on the 28 high-performance drives for best performance and the database backups were placed on the 28 FATA drives for optimum capacity utilization.
Note:
In production OLTP environments, the number of physical disks in a Disk Group will directly affect the performance of the databases.
The performance information provided in this document is based on the entry-level EVA4000, using a limited quantity of disk drives. The EVA4000 used in these performance tests can scale to a maximum of 56 disk drives, with performance scaling near linearly with random I/O workloads as drives are added. The HP StorageWorks 6000 Enterprise Virtual Array (EVA6000) can scale to 112 drives and the HP StorageWorks 8000 Enterprise Virtual Array (EVA8000) to 240 drives. When planning a Microsoft SQL Server storage environment, always consult the HP Microsoft SQL Server Sizer.
Microsoft SQL Server performance results
Figures 4 and 5 show performance results of the Microsoft SQL Server databases for each of the three storage configurations previously mentioned. The SQL Server workload was generated by an OLTP load generator producing a constant (no wait) load to the database for maximum transactional performance. The workload was varied to maintain a <=15ms response time across the database volumes.
Figure 4 shows the total IO per second for each configuration and database size. Notice for this hardware environment the total IO/sec seems to max out at about 5100–5200 IO/sec for the single Disk Group result. This result is expected and is due to the number of physical disks (28) in the EVA4000 configuration and the amount of IO they can handle. At this rate, each disk is running at about 180 IOPS. By adding more physical disks to the EVA and the Disk Groups, the amount of IOPS across the Disk Groups would increase.
Figure 4. Microsoft SQL Server total IO/sec
VRAID1 Total Server IO Throughput
0 1000 2000 3000 4000 5000 6000 1TB 500GB 250GB 100GB Database Size IO /S ec 5 VDisks-2DG 2 VDisks-2DG 2 VDisks-1DG
Figure 5 shows the transactions per second and buffer cache hit ratio of the database volumes. The database size had significant impact on transactions per second and buffer cache hit ratio. This graph would indicate as the database size got smaller the latency across the disks decreases and allowed more transactions/second and a higher buffer cache hit ratio. Here it looks like the number of physical disks could be playing into the low performance of the larger databases. This could be corrected by adding more spindles and increasing the amount of possible I/O across the storage array. This is not an indication of poor server performance.
Figure 5. Microsoft SQL Server Transactions/sec and Buffer Cache Hit Ratio
Transacitons/sec and Cache Hit Ratio
0 100 200 300 400 500 600 700 1TB 500GB 250GB 100GB Database Size T ra n sact io ns /s ec 86 88 90 92 94 96 98 100 Buffe r Cac h e Hit Ra tio
5 VDisks-2DG 2 VDisks-2DG 2 VDisks-1DG Buffer Cache Hit Ratio
There is little performance difference with the split Disk Groups. At most there is about a 15 percent increase in performance on the 100-GB database by distributing the data files across four virtual disks. There is a much greater performance increase with the single Disk Group configuration compared to the split Disk Groups. At the highest point there is about a 39 percent increase in performance by placing the virtual disks in one single Disk Group and distributing IO across a higher number of physical disks.
EVA4000 performance results
The EVA4000 performance is collected and read using EVAperf. There is a list of all the EVAperf counters in Appendix C. The data in Figure 6 shows the performance impact of the storage configurations on the EVA4000 system. The data for the graph is taken from EVAperf—HP EVA storage array object. There is no additional activity on the EVA4000 other then the load to exercise the Microsoft SQL Server databases.
Figure 6 shows the total host requests/sec (combined read and write) on the EVA storage array for each storage configuration. There is little difference between the two split Disk Group configurations and a significant difference with the single Disk Group configuration. This data follows the information presented in the preceding Microsoft SQL Server performance results.
Figure 6. EVA4000 total host requests/sec
EVA Host Port - Total Requests/second
0 500 1000 1500 2000 2500 3000 3500 4000 4500 5000 1TB 500GB 250GB 100GB Database Size Req/s 5 VDisks-2DG 2 VDisks-2DG 2 VDisks-1DG
It is evident that with this setup there is no performance advantage in separating the data and the log files into separate Disk Groups. Both instances where separate Disk Groups were used showed similar results and did not perform as well as the instance with the single Disk Group. The graphs show at most a 17 percent performance difference between the two split Disk Group configurations. However, more importantly, they show about a 38 percent difference between the split Disk Groups compared to the better performance single Disk Group on the EVA4000.
Best practice
For optimum performance in storage configurations with limited physical disks, it is best to combine all disks into one Disk Group and spread IO across as many spindles as possible.
The advantage with placing all 28 disks in one Disk Group in this type of configuration is that the database files can be spread across all 28 disks for better performance. In the multiple Disk Group configurations the data files could be spread only across 20 disks due to the EVA’s physical disk requirement for a Disk Group (minimum of eight physical disks per Disk Group.) In a larger EVA configuration (2C6D or 2C12D) the performance impact would be much less due to the increased number of physical disks available to use in Disk Groups. In that case it may be preferable to separate database and log files into separate Disk Groups to improve availability.
VRAID1 and VRAID5 comparison
The virtual RAID comparison examines the performance results of the EVA4000 when configured for VRAID1 and VRAID5. The values shown in this section are taken from the highest performing configuration from the pervious section—the single Disk Group storage configuration.
With each VRAID level there are performance penalties that offset the benefits of additional fault tolerance and redundancy. With both instances these performance penalties occur as a result of the additional write requests and data transfers. VRAID1 requires two writes, one to the data block and one to the corresponding mirror block. VRAID5 requires four data transfers, two to read the original data and parity block and two to write the new data and parity block. The expected result is that VRAID1 will outperform VRAID5 in most database environments. The following data supports that expectation. The choice to use VRAID1 or VRAID5 usually occurs as a result for the need for capacity. Though VRAID1 yields higher performance, the performance has a cost and the cost is capacity. VRAID5 has less performance then VRAID1 but has less cost on capacity. For example, 1 TB of raw disk space will allow 500 GB of VRAID1 space and about 750 GB of VRAID5. The difference of 250 GB of possible VRAID5 disk space is the decision to build a higher performing storage environment or to settle for the higher capacity, lower performing storage environment.
Figure 7 shows the file size versus usable capacity that VRAID1 offers compared to VRAID5. For this graph the 1-TB file size was compared and VRAID5 offered 409 GB of usable capacity over VRAID1.
Figure 7. File size vs. usable capacity
File Size vs. Usable Capacity
1045 594 1045 1003 0 500 1000 1500 2000 2500 VRAID1 VRAID5 1TB Database Tota l GB
Figure 8 shows the relationship of VRAID1 to VRAID5 throughput seen by the server. The graph shows the total IO, reads, and writes to the system LUNs by the server. There is a substantial performance increase by using VRAID1. This result is expected when figuring in the performance overhead that VRAID5 uses by extensive writing and calculations to the parity bit.
Figure 8. VRAID1 vs. VRAID5—Server IO throughput
VRAID1 vs VRAID5 Server IO Throughupt
6000
5000
4000
VRaid1 Server IO/s 30
IO/s VRaid5 Server IO/s
00
VRaid1 Log IO/s VRaid5 Log IO/s 2000
1000
0
1TB 500GB 250GB 100GB
Database Size
The server throughput for both IO/sec and MB/sec showed greater performance with VRAID1 over VRAID5. At the 1-TB database there is about an 18 percent increase in performance. At the 100-GB database there is about a 31 percent increase in performance.
Figure 9 depicts the EVA storage array—Total host requests/sec.
Figure 9. VRAID1 vs. VRAID5—EVA storage array total host requests/sec
EVA Storage Array - Total Host Requests/second
0 500 1000 1500 2000 2500 3000 3500 4000 4500 5000 1TB 500GB 250GB 100GB Database Size R e q
/sec VRaid1 Req/s
VRaid5 Req/s
Similar to that of the server, the performance on the EVA4000 showed much better throughput with VRAID1 in comparison to VRAID5. At the 1-TB database there is about 20 percent increase in performance by using VRAID1 compared to the 100-GB database that showed about a 30 percent performance increase with VRAID1 over VRAID5.
The new controller technology in the EVA4000 provides increased throughput by utilizing twice the cache mirroring ports for improved I/O performance. This technology remarkably improves on VRAID5 performance penalties compared to other array technologies. However it is clear that VRAID1 out performs VRAID5 for Microsoft SQL Server OLTP database environments.
Best practice
Choose VRAID1 for best performance of Microsoft SQL Server 2005 database and log disks on an EVA4000. Choose VRAID5 when disk capacity utilization is critical.
Overall evaluation—Server and storage
Server evaluationThere was a clear server/storage resource mismatch. By examining the processor utilization on the server during peak transactions/second runs, it was evident that the HP ProLiant BL45p blade servers used in this configuration may have been overpowered. Although the BL45p blade server is the right choice when running enterprise-sized databases such as a 1-TB database, CPU and memory were underutilized during this testing (see Table 2). CPU percentage was very low and server memory percentage was at 73 percent due to allocating 73 percent directly to SQL Server.
Table 2. BL45p resource utilization—VRAID1 single Disk Group test
DB size CPU percent Memory allocation percent
100 GB 12.4 73
250 GB 8.6 73
500 GB 7.6 73
1 TB 6.2 73
One way of determining the right server for a predetermined storage environment would be to consult the HP ProLiant Transaction Processing Sizer for Microsoft SQL Server 2000 on the HP Active Answers Tools site (see the For more information section). Although at the time of this document the
development was still underway for the ProLiant Sizer for Microsoft SQL Server 2005, the current sizer for Microsoft SQL Server 2000 will give an accurate representation of server resources. When using the sizer you will be able to determine the correct ProLiant server for your Microsoft SQL Server environment based on database size and transactional workloads.
Figure 10 shows an output of the ProLiant Sizer for Microsoft SQL Server 2000.
Figure 10. ProLiant Transaction Processing Sizer for Microsoft SQL Server 2000
Depending on your inputs for database size and transaction workload you might see server options similar to Table 3 for a given database size.
Table 3.
DB size Server Option 1 Server Option 2
100 GB ProLiant DL385 1P 6144MB RAM ProLiant BL20p G3 2P 6144MB RAM
250 GB ProLiant DL385 1P 14336MB RAM ProLiant BL20p G3 2P 14336MB RAM
500 GB ProLiant DL580 G3—Intel® Xeon™ 1P 28672MB RAM ProLiant BL25p 1P 28672MB RAM
Storage evaluation
It is clear that for this hardware configuration (2C4D, 28 database disks and 28 backup disks) placing the Microsoft SQL Server data and log files on virtual disks located in the same Disk Group has a significant performance advantage. Typically in large EVA environments where physical disk quantities are not an issue it is recommended to separate data and log files into different Disk Groups to isolate random and sequential data transfers to the EVA virtual disks. However in environments where physical disks are limited and budget constraints prohibit extra spending to increase storage capacity, this approach is a best practice.
It is also clear that VRAID1 virtual disks perform better than VRAID5 even with the added caching and virtualization enhancements that the EVA4000 provides. When possible, placing data and log files on VRAID1 volumes is recommended and considered a best practice with the EVA4000.
From a performance perspective the only bottleneck found on the EVA4000 was the number of physical disk drives allocated to the database. It was evident from the transactions per second and buffer cache hit ratio graph (Figure 5) that as the database size increased the transactions/sec and buffer cache hit ratio decreased. The workload was driven to maintain a <=15ms response time across database volumes. The data from the graph indicates that as the database size increased, the response time also increased, so to maintain a <=15ms response time, the workload had to be throttled down and transactions per second decreased.
Future testing will include scaling out this environment with additional physical disks or scaling up by using an EVA6000 or EVA8000 array with a larger disk configuration. To size your storage
environment, consult the HP StorageWorks Sizer (see the For more information section). The HP StorageWorks Sizer is a downloadable tool that you can use to size a complete storage environment.
Backup and restore with SYMANTEC NetBackup
The backup and restore piece of this testing is done with SYMANTEC NetBackup 5.1 Maintenance Pack 3A. At the time of this testing NetBackup did not support Microsoft SQL Server 2005, so the backup and restore operations were all performed on offline Microsoft SQL Server databases. Procedures for setting up the SYMANTEC environment were done according to the HP Enterprise Backup Solution for SYMANTEC NetBackup on Windows.
There are three parts to the backup of the Microsoft SQL Server database files and three parts to the restore of these files. The first piece examines the performance throughput of backup and restore operations when running a disk-to-disk backup using the HP EVA4000 FATA drives as the destination location of the backup and initial location for the restore. The second examines the performance throughput of backup and restore operations of the Microsoft SQL Server files directly to and from the HP StorageWorks MSL6030 tape library. The third examines the disk–to-FATA-to-tape (D2D2T) or staging scenario and how performance is affected by this type of backup operation.
1. Disk-to-FATA—backup and restore using FATA drives
2. Disk-to-tape—backup of the Microsoft SQL Server database files directly to tape
Disk-to-FATA backup and restore
The disk-to-FATA backup and restore operations are done using 250-GB FATA drives as the backup target residing on the same EVA4000 array system as the database drives. In this configuration there is no other storage area network (SAN) activity or array activity other than the moving of data from the database drives to the FATA drives. In production environments, the daily load of production databases on the array controller may be too heavy at times to handle the backup or data movement from production drives to backup drives. In that case it may be beneficial not only for disaster tolerance but for performance as well to have a second disk array system for disk-to-disk backup operations.
Best practice
It is always a best practice to characterize array I/O before setting up a disk-to-disk backup scenario on the same array to determine: (1) The time of day or week of the lightest I/O load on the array and (2) if the array can handle both the production database load and the load of the data movement from the disk-to-disk backup operation. Table 4 shows the performance throughputs of the disk-to-FATA backup and restore tests.
Table 4. Disk-to-FATA backup and restore throughput SYMANTEC
MB/s
SYMANTEC GB/hr
EVA storage array Total MB/s
Backup 84 302 163
Restore 44 160 92
Disk-to-tape backup and restore
The disk-to-tape backup and restore operations are done using an MSL6030 tape library with LTO2 460 tape drives. In this example the Microsoft SQL Server database files are backed up directly to the tape library using a single data stream to the library. The FATA drives are not used in this example, so there is a significant reduction in the array workload during backup. However, there is still disk read activity.
Table 5. Disk-to-tape backup and restore throughput SYMANTEC
MB/s
SYMANTEC GB/hr
EVA storage array Total MB/s
Backup 28.6 103 29MB/s
Disk-to-FATA-to-tape backup and restore
The disk-to-FATA-to-tape backup operation is combined to create a D2D2T (disk-to-disk-to-tape) or
staging method of backup. In a staging scenario the database files are first backed up to disk, FATA
drives in this case, and then the data is moved from the FATA drives to the tape library using a manual relocate operation within SYMANTEC NetBackup. With the D2D2T staging method, the SQL Server 2005 database can be backed up to disk quickly during times of low activity workload and then streamed to tape in the background. The advantage of this type of scenario is that while the data is being backed up to tape, there is little to no overhead to the database performance. The stream to the tape library is configured by a preferred path other than that of the database disks.
To set up a staging scenario, create a Disk Storage Unit (DSU). To create the staging method of backup:
1. Create a new Disk Group and virtual disk on the EVA4000 using the FATA disks.
2. Present the virtual disk to the host and format the volume.
3. Configure preferred paths of the backup volumes.
4. Open SYMANTEC NetBackup Administration Console and create a new storage unit with the newly added LUN by opening the Storage Unit list and using the Add New Storage Unit wizard.
a. Select Disk Staging as the storage unit type and point to the drive letter of the volume. b. Click the schedule button to add the backup schedule.
c. Add the destination unit (tape library) in the destination dropdown box.
5. Create a policy that will use the DSU as the backup device.
SYMANTEC is ready to back up the database files to the DSU. Because this is a disk-to-disk backup, the backup window will be shortened and there will be less impact on the Microsoft SQL Server databases. When the disk-to-disk backup is complete, right-click the DSU and select Manual
Relocation. SYMANTEC will relocate the files from DSU to the destination point you chose when
creating the DSU with little impact to the EVA4000 and the production Microsoft SQL Server databases.
Backup and restore performance results
The backup and restore performance results examines the SYMANTEC NetBackup performance results and then looks at the results from running tests using HP StorageWorks Library and Tape Tools (L&TT). HP L&TT can be downloaded and installed from the HP Storage Enterprise Backup Solution website (see the For more information section). L&TT allows the user to perform system and device performance tests to understand how the backup system and backup devices will perform in their environment. L&TT is an excellent tool when initially setting up the backup and restore environment and planning the backup window around performance.
Figure 11. SYMANTEC NetBackup backup performance
NetBackup Backup Performance
0 50 100 150 200 250 300 350
Disk 2 Fata Disk 2 Tape Fata 2 Tape
Backup Type
Figure 12. SYMANTEC NetBackup restore performance
NetBackup Restore Performance
0 20 40 60 80 100 120 140 160 180
Fata 2 Disk Tape 2 Disk
Restore Type
GB/hr
Table 6. SYMANTEC backup and restore times
D2T backup times D2F backup times F2T backup times D2D2T backup times T2D restore times F2D restore times 1 TB 10:08 3:27 12:05 15:32 8:17 6:31 500 GB 4:51 1:39 5:46 7:25 3:58 3:08 250 GB 2:24 :49 2:53 3:42 1:59 1:33 100 GB :58 :20 1:09 1:29 :47 :37
Appendix A—Reference configuration BOM
Table A-1. HP ProLiant BL45p Blade Server
Description Quantity
HP BL45p 2.6-GHz 1-GB 1P Svr 2 servers HP ProLiant 4-GB PC3200 2x2-GB Mem Upgrade 2 per server AMD Opteron 852 2.6/1-GHz-1M SC BL45p Proc 3 per server HP ProLiant BL25/35/45p Fibre Channel Adapter 1 per server 146.8-GB 15,000 rpm U320 Universal Hard Drive 1 inch 2 per server Microsoft Windows Server 2003, 32-bit Enterprise Edition 1 per server HP MPIO Full Feature Failover for EVA4000/6000/8000
Arrays
1 per server
Microsoft SQL Server 2005—June CPT Edition 1 per server
Table A-2. HP ProLiant BL35p Blade Server
Description Quantity
HP BL35p 2.2 Ghz-1 MB, DC, 2 GB, SAS (2P) 1 server 36-GB SAS 10k rpm, Universal Hard Drive (2.5 inches) 1 per server HP ProLiant BL25/35/45p Fibre Channel Adapter 1 per server Microsoft Windows Server 2003, 32-bit Enterprise Edition 1 per server HP MPIO Full Feature Failover for EVA4000/6000/8000
Arrays
1 per server
SYMANTEC NetBackup 5.1 Maintenance Pack 3A 1 per server
Table A-3. HP StorageWorks 4000 Enterprise Virtual Array
Description Quantity
42U EVA Cabinet 60Hz 1 cabinet
HP EVA4000 2C1D Array 1 array
HP M5314 FC Drive Enclosure 3
146.8-GB 15K FC-AL Drive 1 inch 28
250BG FATA Drive 28
HP EVA XCS v5.0 Media Kit 1
Appendix B—HP StorageWorks Enterprise Virtual Array
VRAID technology
The HP StorageWorks Enterprise Virtual Array VRAID technology distributes data across all disks in a Disk Group by slicing up the regions of the physical disks. The data is then spread across each disk in a striping pattern similar to RAID0. When creating a virtual disk, the user has the capability of leaving the VDisk at VRAID0 or choosing one of the other VRAID options of either VRAID5 or VRAID1 (Figure 13).
Figure 13. EVA VRAID
In Figure 13 the physical disk is broken up in slices. When a virtual disk is created, it takes a slice of each physical disk in the Disk Group. Disk Groups must have a minimum of eight physical disks. Virtual disks in the default state will be created as VRAID0 unless another VRAID is chosen, such as VRAID5 or VRAID1. The choice between VRAID0, VRAID5, and VRAID1 depends on the type of storage environment needed per application. Though VRAID1 allows for higher performance than VRAID5, there are capacity considerations to take into account. VRAID1 will use more disk space than VRAID5 and ultimately be more costly in the long run. On the other hand if the applications using the virtual disks need a high-performance environment, then VRAID1 is a better choice then VRAID5.
Appendix C—Performance metrics
It is important to capture the necessary metrics for performance characterization. Performance metrics for this project were taken from the Windows Performance Monitor or Perfmon utility for both the Microsoft SQL Server 2005 application performance and the EVA4000 performance.
Viewing Microsoft SQL Server 2005 performance metrics when running
in WOW mode
Windows on Windows (WOW) mode is when Microsoft SQL Server 2005 32-bit edition is run on Microsoft Windows Server 2003 x64 edition. At the time of this document the Microsoft SQL Server 2005 June CPT Edition performance counters would not show up when starting the 64-bit Windows Performance Monitor from the command line, perfmon.exe, the Performance MMC snap-in, or Administrator Tools start up menu. To view and set up performance monitoring in this case you must launch the 32-bit version of Microsoft Windows Performance Monitor by typing percentwindir percent\Syswow64\perfmonexe from the command line. This will launch the 32-bit version of Perfmon and you will be able to view and set up performance monitoring for Microsoft SQL Server 2005.
Windows Server Performance Monitor counters
Physical disk counters in Windows 2003Physical disk counters keep track of performance information of each disk instance presented to the server. Getting familiar with each physical counter and what they say is an excellent start to knowing what is going on from a disk performance perspective.
• Current Disk Queue Length < 2
– This number must be divided by the number of physical disks that make up the storage LUN. – Example: Current Disk Queue Length on G: is 45 but G: is a storage LUN made up of 28
physical disks, so actual disk queue length is 45/28=1.6 • Avg. Disk Sec/Transfer < 0.3 seconds
• Disk Reads/sec, Writes/sec • Disk Bytes/sec; Total, Read, Write • Avg. Disk sec/Read < 15ms • Avg. Disk sec/Write < 10ms • R/W ratio: <none> (must calc)
IOPS: Physical disk: reads, writes and transfers/sec
The following throughput calculations can be used to determine physical disk IO per second of a RAID volume.
• RAID 0: (Disk Transfers/sec) / #spindles in the array
• RAID 1: (Disk Reads/sec + 2*Disk Writes/sec) / #spindles in the array • RAID 5: (Disk Reads/sec + 4*Disk Writes/sec) / #spindles in the array
Latency: exposes mis-configured SANS
• PhysicalDisk(drive:) Avg. Disk sec/Read – Low latency: < 15 ms 95th percentile • PhysicalDisk(drive:) Avg. Disk sec/Write
– Low latency: < 10 ms 95th percentile • Logs: Writes < 8 ms
• Disk space capacity versus I/O capacity – Want to be < 80 percent max I/O capacity
Microsoft SQL Server Performance Monitor counters
The Microsoft SQL Server 2005 performance metrics are directly integrated into Windows Perfmon when Microsoft SQL Server is installed on the physical server. There are many object and counters for Microsoft SQL Server to choose from and it can be confusing when trying to decide what to look at and what not to for metrics. This paper discusses some of the more important counters to keep an eye on when characterizing performance of a Microsoft SQL Server environment.
The following section lists important Microsoft SQL Server performance monitor objects and counters to consider when tuning and optimization of Microsoft SQL Server environments. Each Perfmon object will be followed by the particular counter and each counter will have a corresponding threshold value (if applicable) to use as a guide when examining the actual value.
• Backup Device
– Device Throughput Bytes/sec • Buffer Manager
– Buffer Cache Hit Ratio > 90 percent – Checkpoint Writes/sec
– Page Reads/sec—want a low value
– Free Buffers—want a consistently high value – Lazy Writes/sec—want a low value or 0 – Stolen Pages—want a low value
• Cache Manager
– Cache Hit Ratio > 80 percent • Databases
– DatabaseInstance: Active Transactions
– DatabaseInstance: Backup/Restore Throughput/sec – DatabaseInstance: Percent Log Used < 70-80 percent – DatabaseInstance: Transactions/sec
• General Statistics – User Connections • Locks
– Average Wait Time (ms)—steady over time – Lock Waits/sec
– Number of Deadlocks/sec
• Microsoft SQL Server Memory Manager – Microsoft SQL Cache Memory – Target Server Memory
– Total Server Memory < 80 percent Target Server Memory • Microsoft SQL Server Statistics
HP Command View EVA Performance Monitor counters
HP Command View EVAperf is a utility that is packaged with HP Command View software that can be run by way of a command line or integrated with Windows Performance Monitor. EVAperf allows you to view and log performance metrics of one or more EVA subsystems. The following lists
examples of EVAperf performance objects viewed through Windows Performance Monitor. For more information on EVAperf, see the For more information section.
Figure 14. HP Command View EVAperf—Windows Performance Monitor counters
• HP EVA DR Tunnels
– Reports intensity and behavior of link traffic between source and destination EVAs. • HP EVA Host Connection
– Provides information on the activity from each adapter seen as a host to the array. • HP EVA Host Port Statistics
– Provides information on the performance and data flow through each of the EVA Controller host ports.
• HP EVA Physical Disk
– Reports information on each physical disk on the EVA subsystem that is or has been a member of a Disk Group.
For more information
HP SAN Design Guide
• http://h20000.www2.hp.com/bizsupport/TechSupport/DocumentIndex.jsp?contentType=SupportManual&loc ale=en_US&docIndexId=179911&taskId=101&prodTypeId=12169&prodSeriesId=406734
HP Active Answers (requires secure login account)
• HP’s Microsoft SQL Server—Solutions Component List
– http://h71019.www7.hp.com/ActiveAnswers/cache/70728-0-0-0-121.html
• Sizers and Tools
– http://h71019.www7.hp.com/activeanswers/Secure/71110-0-0-0-121.html
• ProLiant Transaction Sizer for Microsoft SQL Server 2000
– http://h71019.www7.hp.com/activeanswers/Secure/70697-0-0-225-121.html
• HP StorageWorks Sizer
– http://h30144.www3.hp.com/
Microsoft SQL Server Performance Information
• Microsoft SQL Server 2000 Optimization Guide, Prentice Hall PTR Microsoft Technologies Series,
2001
Command View EVA and EVAperf
• Command View EVA
– http://h20000.www2.hp.com/bc/docs/support/SupportManual/c00448142/c00448142.pdf
• Performance Analysis
– ftp://ftp.compaq.com/pub/products/storageworks/whitepapers/5983-1674EN.pdf
Enterprise Backup Solution (EBS)
• Technical Documents Link
– http://www.hp.com/go/ebs
Call to action
• Web: http://h18006.www1.hp.com/storage/solutions/storagesolutions.html
© 2005 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