Microsoft SQL Server Configuration Guide for HP IO Accelerators Part Number 647096-001 December 2010 (First Edition)
© 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. Confidential computer software.
Contents Installing and preparing the IO Accelerator for Microsoft Windows SQL Server ................................... 4 Initial setup ............................................................................................................................................... 4 Installing Microsoft SQL Server .................................................................................................................... 4 Additional performance tips..................................................
Installing and preparing the IO Accelerator for Microsoft Windows SQL Server Initial setup After the IO Accelerator is installed, follow these steps to prepare it for use with Microsoft® SQL Server: 1. Start the HP IO Accelerator Management Tool. 2. In the Device Tree, select the IO Accelerator. 3. Click Format Low-level. 4. In the dialog box, select Advertised Capacity, and select a 4K block size. 5. Select Disk Management in Windows. 6.
RAID options RAID 0 RAID 0 offers the full capacity of the IO Accelerator without any disk-level redundancy. NOTE: Windows® Software RAID configurations with IO Accelerators have not demonstrated performance deterioration. Due to the high performance potential of the IO Accelerators, physical RAID controllers are discouraged since they might become a performance bottleneck. RAID 1 RAID 1 offers half the raw capacity with disk-level redundancy.
High availability considerations Database mirroring NOTE: Database mirroring requires Windows® SQL Server 2005 or 2008. To mirror the servers on the same local network, HP recommends synchronous mirroring for best data loss protection. If the servers are on different networks, HP recommends asynchronous mirroring to reduce the performance impact on the primary server. NOTE: The performance impact on the primary server is not due to any IO Accelerator inadequacy.
Third-party software is required to keep the data in sync between the different cluster nodes. HP recommends the following: • DataKeeper by Steel Eye (http://www.steeleye.com/DataKeeper_Cluster_Edition_144.htm) • Clustering For Mere Mortals, Step-by-Step: Configuring a 2-node multi-site cluster on Windows Server 2008 R2 – Part 1 (http://clusteringformeremortals.
Backup considerations General recommendations Backing up large SQL Server databases often takes a long time and ends up affecting the performance of the primary systems. HP recommends the following for performance improvement: • If you are using SQL Server 2008, use the built-in feature to compress backups. • If you are using SQL Server 2005, various third-party tools are available to compress backups: • • o LiteSpeed (http://www.quest.com/litespeed-for-sql-server/) o SQL Backup (http://www.
Testing IO Accelerator-powered SQL performance Methodology Generally, benchmarks such as SQLIO and ioMeter are used to measure the I/O profile of the storage sub-systems. However, with IO Accelerators, these tests often give false results since the tests were designed for measuring spinning disk media and do not completely provide the performance of the IO Accelerators. Workload examples HP recommends that you use a realistic a load when testing the IO Accelerator-powered SQL Server. For example: 1.
o Physical Disk—Average Disk queue Length o Logical Disk—Avg. Disk sec/Read [Read Latency] o Logical Disk—Avg.
Architectural scenarios Scenario 1: The entire database fits on the IO Accelerators 1. Place the data files on one IO Accelerator. NOTE: You can use one IO Accelerator or a RAID 0 set of multiple IO Accelerators. 2. Place the tempdb file for both data files and log files, the log files, and backup files on another IO Accelerator. 3. If available, give the tempdb file its own dedicated IO Accelerator. Scenario 2: The database is too big to fit on the IO Accelerators 1.
• Determine the maximum size of the tempdb file you need and then create each tempdb data file to be the same size. • Turn off auto-grow on the tempdb file. For more detailed information, see the Microsoft® website (http://support.microsoft.com/default.aspx?scid=kb;en-us;328551). Index considerations Indexes help retrieve data from the tables, so consider moving all indexes to a separate file group and then placing the file group on an IO Accelerator.
data for each year. In this scenario, you can keep the most recent data on IO Accelerators and keep older data on more traditional media.
Acronyms and abbreviations CPU central processing unit DBCC database console commands IOPS input/output operations per second IP Internet Protocol SQL structured query language SSIS SQL Server Integration Services Acronyms and abbreviations 14
