hp storage april 2002 technical white paper User's Guide Storing Microsoft SQL Server® Databases on the HP NAS 8000 1
executive summary The explosive growth in the amount of data being created, collected and analyzed has created an unprecedented need for storage that is quickly integrated into existing environments, highly reliable, easy to manage and maintain, and easily scalable. Contributing to the rapid growth of storage needs are highpowered database engines that can make the collection and analyzing of data a more efficient task.
executive summary ....................................................................................................................2 for more information ..................................................................................................................2 hp solution overview ..................................................................................................................4 storage management and definition guide...............................................................
hp solution overview Data integrity, reliability, scalability and high performance are top priorities in any database management system. The use of Network Attached Storage (NAS) devices to store database data files has traditionally been discouraged because it was thought that network instability and perceived vulnerabilities of non-local storage posed too great of a potential risk to data reliability and would greatly reduce system performance.
fast and efficient data access, and utilizes RAID 0/1 or HP AutoRAIDtm technology to protect the data on the storage subsystem’s hard drives. AutoRAIDtm combines RAID levels 0/1 and 5DP, controlled and configured in “real time” by the VA 7xxx firmware, to provide total protection/recoverability in the event of single hard disk failures and almost all two (2) simultaneous hard disk failures (per redundancy group).
usage plan calls for the use of NAS 8000 snapshots, space must be reserved in the volume group for the snapshots. After creating volume groups, the next step is to create file volumes. A file volume is somewhat equivalent to a file system. There are many possibilities at this level, and each choice should be weighed carefully before committing to an overall storage design. One or more file volumes are entirely contained in a single volume group.
following: -T1807. The -T1807 specifies the setting of trace flag 1807, which is the trace flag that enables SQL Server to utilize "non-local" storage for database data files. Click on OK. It is possible to set trace flag 1807 through the Query Analyzer or through the use of ISQL or ISQLW rather than altering the startup parameters of SQL Server.
would involve the use of the SQL Server Enterprise Manager in order to change the path to the database data/log files. Failure to correctly update all occurrences of the IP address could result in a database failure, or possible data loss. Whereas, if the network name were used, only the address in the Name Server(s) would need to be updated.
share level security In Share level security, the NAS 8000 administrator, in conjunction with the SQL Server DBA can decide whether or not to password protect the CIFS share(s) that house the database(s). It is recommended that the shares that house the SQL database(s) be password protected and reserved exclusively for the database(s). Security on the share (or mapped drive) is handled when the connection to the NAS 8000 is initially made, not on every access to the NAS 8000.
@ filename1 = '\\NAS8000\somepath\somefile.mdf' : @filenamex = '\\NAS8000\somepath\somefile.ldf' The SQL command sp_detach_db takes the specified database "offline" so that it is in a consistent state and cannot be accessed by any user. Once the database is offline, its data/log files are available to be moved or copied. It is not necessary to move all of the data files or transaction logs associated with a database.
to 105 drives can be added to the VA 7400 (with DS2400 enclosures). The VA 7100 and the VA 7400 can contain a mixture of 18 GB, 36 GB and 73 GB hard drives. See below for details on adding hard drives to the Virtual Arrays. If the drive bays are all full, but the size of the hard drives are not all 73 GB hard drives, then storage can still be added depending upon the RAID and hot spares setting. In this case, remove either an 18GB or a 36GB drive.
refer to the NAS 8000 Users Guide and the whitepaper NAS 8000 Backup Strategy (located off of the NAS 8000 home page) for further details on these solutions. There are four types of backup provided by SQL Server. In each backup scenario, the database remains available for user, although some operations are not allowed during a backup.
seconds. The “Restricted” setting is the same as “Normal”, but allows the “immediate report” feature of write cache to be disabled as well as flushing the write cache for the associated LUN before completing the write request. The “Secure” Data Resiliency setting forces write cache to be flushed to the hard drives at a maximum of every one second, as well as allowing the “immediate report” feature of write cache to be disabled (like the restricted setting).
storage diagram for VA 7100 This diagram depicts physical storage as a single redundancy group – because this is a VA 7100. Volume Group: VG1 LUN 1 File Vol: Gen_1 CIFS Shares: \SomeShare File Vol: SQL CIFS Shares: \SQL_Trans_Logs \SQL_Data \SQL_Sys \SQL_Index \SQL_Util \SQL_Backup One LUN is shown. LUNs aggregate the storage in a single redundancy group into one or more general “pools” of storage. Multiple LUNs could be created. One volume group is shown.
storage diagram for VA 7400 (single volume group) Hard Drives in Redundancy Group 1 Hard Drives in Redundancy Group 2 Volume Group: VG1 LUN 1 File Vol: Gen CIFS Shares: \ExportPaths File Vol: DB CIFS Shaes: \SQL_TLogs \SQL_Data \SQL_Sys \SQL_Index \SQL_Util \SQL_Backup LUN 2 This diagram depicts physical storage divided into two redundancy groups. Hard drives in the odd numbered drive bays are in redundancy group 1. Hard drives in the even numbered drive bays are in redundancy group 2.
storage diagram for VA 7400 (extra fault tolerance) Hard Drives in Redundancy Group 1 Volume Group: VG1 Hard Drives in Redundancy Group 2 Volume Group: VG2 LUN 1 LUN 2 File Vol: Gen_1 File Vol: Gen_2 CIFS Shares: \SomePaths CIFS Shares: \SomePaths File Vol: OData File Vol: OLogs CIFS Shares: \SQL_TLogs \SQL_Data \SQL_Sys CIFS Shares \SQL_Index \SQL_Util \SQL_Backups This diagram depicts physical storage divided into two redundancy groups.
glossary AutoRAIDtm – Combination of RAID Levels 0, 1 and 5DP implemented by the firmware of the VA 7xxx controller to provide automatic data protection on VA 7xxx hard drives. CIFS/SMB – (Common Internet File System / Server Message Block). Protocols used to access non-local storage over a network. Primarily used by Windows based systems. DSS Database – Decision Support System database. Characterized by complicated, CPU intensive queries. File Volume – Basic unit of logical storage.
