Using Microsoft SQL Server with Dell EqualLogic PS Series Arrays Best practices and configuration guidelines for deploying SQL Server with EqualLogic storage arrays.
Revisions Report Date Description 1.0 October 2005 Initial release 2.0 June 2007 Added SQL 2005 coverage and support for PS Series 3.2 firmware release 2.1 March 2008 Added new information covering Auto-Snapshot Manager v3.0+ and Smart Copy features. 2.2 August 2010 Updated doc to SQL Server 2008 R2 and latest PS Series software 3.
Table of contents Revisions ............................................................................................................................................................................................. 2 Table of contents .............................................................................................................................................................................. 3 Preface .........................................................................................
Preface PS Series arrays optimize resources by automating performance and network load balancing. Additionally, PS Series arrays offer all-inclusive array management software, host software, and free firmware updates. Visit WWW.DELL.COM/PSseries for more information. Audience The information in this guide is intended for administrators that have or are planning to deploy SQL Server with EqualLogic storage arrays. Dell online services You can learn about Dell products and services using this procedure: 1.
Software and firmware versions The following table shows the software and firmware used for the preparation of this document. Vendor Model Software Revision Microsoft® SQL Server 2008, 2008 R2, 2012 Microsoft® Windows Server 2008, 2008 R2, 2012 Dell™ EqualLogic PS Series Array Firmware V7.x* Dell™ EqualLogic Host Integration Tools for Windows V4.7* *For complete version support see the Host Integration Tools Release Notes.
1 Introduction SQL Server provides the enterprise data management platform your organization needs to adapt quickly to a fast-changing environment. Benchmarked for scalability, speed, and performance, SQL Server delivers rapid return on your data management investment, with low implementation and maintenance costs as well as rapid development of enterprise-class business applications.
Reliability, availability, and serviceability – The environment must be robust, resilient, and easily repaired with hot swappable components while the system remains available. In a SAN configuration, the server, network, and storage arrays should all have a high level of fault tolerance and redundancy to avoid costly downtime and allow scheduled maintenance to take place without service disruption.
With PS Series replication capabilities, SQL Server data can be automatically transferred to remote data centers or replicated synchronously within the same datacenter or campus protecting the data from disasters. Comprehensive host integration – The EqualLogic Host Integration Tools for Microsoft (HIT/Microsoft ) software includes a suite of tools and utilities to assist server and database administrators with setting up Windows servers to run with PS Series SANs.
9 Member-by-member RAID level control Collections (consistency groups) tying volumes together across arrays and pools Command line and PowerShell scripting TR1013 Using Microsoft SQL Server with Dell EqualLogic PS Series Arrays
2 Planning and design considerations When designing a SQL Server environment, you should understand the challenges of optimizing the entire system from server to storage and target a design that adresses the following aspects: Server resources Storage volume distribution RAID choices SQL database volume sizing Database files and layout It is critical to focus on these challenges before you begin the initial SQL installation.
engine as well as lower the IO load on the storage system. When planning to utilize the buffer pool extension, it’s recommended to use local SSD disks for the buffer pool file system. 2.2 Distributing data The load on a database file consists of sequential and random reads and writes. The load on a log file consists of sequential writes during normal operation and sequential reads during the restoration of a database.
Automatic tiering can be achieved within a single pool by utilizing the PS Series group’s automatic load balancing algorithms. The load balancers continually work to balance the workload across all the arrays in the pool. Over time, volumes may be automatically moved to arrays offering the best RAID level for that volume. This activity happens automatically based on the performance needs of the volume and the array on which it is hosted.
3 Deploying SQL Server with a PS Series SAN The following sections describe SQL Server requirements and recommendations and the basic tasks for deploying SQL with a PS Series SAN. The following section will reference related Windows server and application deployment best practices and reference architecture documents. For copies and downloads of these documents, please visit the EqualLogic Technical Content page on Dell TechCenter at the following link: http://en.community.dell.
HIT/Microsoft optimizes the server to run with PS Series iSCSI storage. It also installs the EqualLogic MPIO DSM and Auto-Snapshot Manager. The EqualLogic MPIO DSM is proven to improve performance on hosts connected to PS Series storage volumes. Auto-Snapshot Manager can act as a centralize management portal for managing setting to PS Series groups as well as a VSS requestor for application consistent pointin-time protection of SQL databases.
PS Series arrays allow volumes to be configured with thin provisioning. Thin provisioning has tremendous benefits for managing storage space on the SAN and volume capacity-on-demand. Thin provisioning has always been recommended for files that grow slowly over time and/or the data is not reconstructed from time to time.
4 Migrating SQL Server databases to PS Series storage If you are currently using a local system disk, DAS, or other storage media for your current SQL configuration, you can easily migrate databases and logs to volumes in a PS Series group and gain the performance and scalability benefits of an iSCSI SAN. Several methods for migrating databases to your PS Series SAN are described below. Method 1: Use Detach/Attach to move existing databases to a PS Series SAN.
The Copy Database Wizard can perform all the tasks above, but there are restrictions on each. Note that the destination server must be running SQL Server 2005 SP2 or later. For more information on the Copy Database Wizard and options in SQL Server 2005, see the following Microsoft article. http://msdn2.microsoft.com/en-us/library/ms188664.
5 SQL Server data management Data integrity is vital in a solid SQL Server environment. Backing up your data as well as monitoring for disk space usage are two important disk management tasks. You should monitor your SQL volumes so they do not fill up unexpectedly. When a volume starts to get full, you may want to consider increasing the volume size, as described below. Tested and documented backup and restore procedures are important in maintaining uptime of SQL Server environments.
5.2 Expanding SAN storage online As storage requirements grow for each application, PS Series storage can be easily expanded, online and with no disruption to users. For example, you can increase the size of a PS Series volume using the Group Manager GUI or the command line interface (CLI). You must then enable the operating system to recognize the size increase. The additional space will be immediately available for use.
6 SQL Server database protection Data protection is crucial for database applications. Microsoft has embedded data protection and high availability capabilities into SQL Server. With past versions of SQL Server database administrators could backup and restore databases, cluster SQL Server instances and databases, replicate transactions across distributed servers, and perform protection scenarios such as Log Shipping.
Allow system or database administrators to restore SQL Server databases in the following ways: • In-place SQL Server database and volume recovery • “Side-by-side” SQL Server database and volume recovery to new locations using transportable Smart Copies • On a new system for testing, development, or reporting • At a remote location for disaster recovery using Smart Copy replicas For detailed information on SQL Server data protection with Auto-Snapshot Manager see the following document. http://en.community.
7 Summary An iSCSI SAN comprised of PS Series storage arrays provides an ideal storage infrastructure for SQL Server installations. A PS Series SAN brings all the reliability, performance and protection needed for a successful deployment. As your SQL Server installation grows and the workload increases, a PS Series SAN can scale easily and dynamically while maintaining availability.
Technical support and customer service Dell support service is available to answer your questions about PS Series SAN arrays. Contacting Dell 1. 2. 3. 4. 5. 23 If you have an Express Service Code, have it ready. The code helps the Dell automated support telephone system direct your call more efficiently. If you are a customer in the United States or Canada in need of technical support, call 1-800-945-3355. If not, go to Step 3. Visit https://eqlsupport.dell.com/secure/login.
