Microsoft SQL Server
Part Number 647096-001
Configuration Guide for HP IO Accelerators
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. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212,
Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S.
Government under vendor’s standard commercial license.
Microsoft, Windows, and Windows Server are U.S. registered trademarks of Microsoft Corporation.
Intended audience
This document is for the person who installs, administers, and troubleshoots servers and storage systems.
HP assumes you are qualified in the servicing of computer equipment and trained in recognizing hazards
in products with hazardous energy levels.
Contents
Installing and preparing the IO Accelerator for Microsoft Windows SQL Server ................................... 4
Initial setup ............................................................................................................................................... 4
Installing Microsoft SQL Server .................................................................................................................... 4
Additional performance tips ........................................................................................................................ 4
RAID options ................................................................................................................................ 5
RAID 0 ..................................................................................................................................................... 5
RAID 1 ..................................................................................................................................................... 5
RAID 5 ..................................................................................................................................................... 5
RAID 10 ................................................................................................................................................... 5
High availability considerations ...................................................................................................... 6
Database mirroring ................................................................................................................................... 6
Multi-site clustering .................................................................................................................................... 6
Backup considerations ................................................................................................................... 8
General recommendations .......................................................................................................................... 8
Testing IO Accelerator-powered SQL performance ............................................................................ 9
Methodology ............................................................................................................................................ 9
Workload examples .................................................................................................................................. 9
Architectural scenarios ................................................................................................................ 11
Scenario 1: The entire database fits on the IO Accelerators .......................................................................... 11
Scenario 2: The database is too big to fit on the IO Accelerators ................................................................... 11
tempdb considerations ................................................................................................................... 11
Index considerations ...................................................................................................................... 12
Frequently accessed tables .............................................................................................................. 12
Large tables .................................................................................................................................. 12
Log files ....................................................................................................................................... 12
Partition tables .............................................................................................................................. 12
Acronyms and abbreviations ........................................................................................................ 14
Contents 3
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. If you are using a RAID configuration on multiple IO Accelerators, convert the drives to Dynamic.
7. Format the drive for a filesystem, setting the File Allocation Unit Size to 64K.
Installing Microsoft SQL Server
NOTE: When you install Microsoft® SQL Server, install the executables, usually found in the
Install the following system databases on the IO Accelerator:
Program Files folder, to your local drive rather than to the IO Accelerator drive.
• Master
• Tempdb
• MSDB
• Model
If heavy paging occurs, install the page files on the IO Accelerator, which provides a significant increase
in performance (when DRAM is available).
For more information, see the latest Windows® user guide.
Additional performance tips
• Have as many user database data files as there are processor cores in the server. This ratio enables
multiple threads to operate at the same time and significantly improves the IO Accelerator
performance.
• Keep all the data files the same size.
• Allow the database to use only one log file.
Installing and preparing the IO Accelerator for Microsoft Windows SQL Server 4
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,
RAID 1
physical RAID controllers are discouraged since they might become a performance bottleneck.
RAID 1 offers half the raw capacity with disk-level redundancy.
RAID 5
HP does not recommend configuring IO Accelerators in RAID 5 because of the parity calculations
required to manage the RAID 5 array.
RAID 10
You can use Symantec Storage Foundation to configure RAID 10 with IO Accelerators.
• You must have a minimum of four IO Accelerators.
• For more details, request the Symantec Storage Foundation Configuration Guide from your HP
representative.
RAID options 5
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. In a synchronous mirroring, every transaction must be committed first on the
mirror server before it is committed on the primary server. This requirement can add high
Database mirroring occurs at the database level so you can choose which databases to mirror. System
databases cannot be mirrored, so you must create scripts using SSIS to automate the syncing of these
databases.
With SQL Server 2008, the transaction log stream is automatically compressed before being sent to the
mirror server. Database mirroring also automatically fixes any corrupted pages without user intervention.
You can use a witness server to keep a heartbeat and create an automatic failover scenario. You can
even use SQL Server Express (free version) to act as the witness server.
If an environment consists entirely of a .net codebase, then add the following command to the connection
string to make the failover completely automatic:
latency if the primary and the mirror servers are separated by a non-dedicated public Internet.
Failover Partner=myMirrorServerAddress;
When the failover occurs, the clients (Web servers, application servers, and so on) automatically re-point
to the mirror server.
If an environment consists of different Web servers, application servers, and so on, that cannot take
advantage of the connection string feature, a script can be used to push alternate versions of host files
and re-map the IP address to name mapping. This script makes the mirror server the primary server in the
event of a failover.
The enterprise edition of SQL Server 2005/2008 enables you to create a read-only snapshot on the
mirror server to be used as a standalone reporting server in addition to being a failover server. This
creates an efficient SQL Server infrastructure.
Multi-site clustering
With Windows® 2008, Microsoft® offers multi-site clustering that does not require shared storage. As
long as the operating system is Windows® 2008, the SQL Server version can be 2005 or 2008. This
feature provides an automatic and seamless failover framework. Clients talk to a virtual IP address. When
one node goes down, another one starts.
High availability considerations 6
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.com/2009/09/15/step-by-step-
configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-–-part-1/)
• Double Take Availability (http://www.doubletake.com/english/products/double-take-
availability/Pages/default.aspx)
• Neverfail (http://www.neverfailgroup.com/windows-apps/sql-server-high-availability.html)
The different cluster nodes can be in the same or separate locations.
High availability considerations 7
Loading...