2.3 Planning the I/O subsystem

2.3 Planning the I/O subsystem

2.3.1 Partitions and volumes

The disk needs to be partitioned and formatted before it can save a variety of information. Early Disk Management was primarily the partitioning of a disk into multiple logical parts, i.e. partitions (Partition).

The concept of partitioning is replaced by a "volume" (volumn), as disk space demands expand and the space of multiple hard disks needs to be centralized for use. Windows has the following 2 types of volumes.

Simple volume. It occupies part or all of a disk, and is actually a partition in the traditional sense. In this mode of operation, this disk is called "Basic Disk".

Cross-region volumes. In this mode of operation, you first convert these disks to "dynamic Disks" and then concatenate a minimum of 2 disks together to create a large volume, which is primarily intended to increase the capacity of the volume. The biggest disadvantage of a spanned volume is that any one disk fails and the entire volume is compromised.

2.3.2 RAID

RAID (Redundant array of independent Disks, disk array) is the combination of a set of disks into a disk group, with the design of data distribution, improve data security and speed up the reading speed.

RAID-0 is the data-stripe-stripping technology that creates a stripe set on a disk group consisting of a minimum of 2 hard disks. When used, data is distributed to all hard drives and read and write, so it can almost multiply the data read speed, but it does not have the data redundancy function, so it is not a real RAID structure. RAID-0 is only in scenarios where data security requirements are not high and you want to improve the reading and writing capabilities of your data.

RAID-1 is disk mirroring, which mirrors the data of one disk onto another disk. When the data is written, it is written on another disk as long as there is at least one disk available on any pair of mirrored disks in the system, and the system can function properly to ensure absolute security of the data. However, this also increases the cost significantly, with disk utilization of 50%. As a result, RAID-1 is used to preserve critical, critical data.

RAID-5 is a disk group with distributed parity, which does not mirror data directly to another disk, but instead accesses data and parity information across all disks, with a speed close to RAID-0 when reading data. However, when writing, it is necessary to read the old parity information and write the new parity code, resulting in a loss of writing efficiency. If one of the disks is damaged, the repair work can be done with the parity code. RAID-5 requires a minimum of 3 disks, with a utilization of n-1 (n is the number of disks).

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/5A/12/wKiom1T1XiuQZ7w_AAN-aGy9tUo774.jpg "title=" RAID plots. png "alt=" wkiom1t1xiuqz7w_aan-agy9tuo774.jpg "/>

The raid 0+1 or RAID 1+0 is a combination of RAID-0 and RAID-1 from the name, which creates a stripe set in disk mirroring, which simultaneously boasts RAID-0 's extraordinary speed and high reliability of RAID-1 data. RAID 0+1 requires at least 4 disks with a utilization rate of 50%. Hardware manufacturers support RAID 1+0 is more common, often called RAID-10.

RAID is typically implemented through hardware, and Windows 7 and Windows Server 2008 also provide software RAID functionality. Volumes with RAID-0, RAID-1, and RAID-5 functions are called striped volumes, mirrored volumes, and RAID-5 volumes, respectively.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5A/11/wKiom1T1SibhAgs-AAO1wmJj_v0696.jpg "title=" Soft raid.png "alt=" Wkiom1t1sibhags-aao1wmjj_v0696.jpg "/>

2.3.3 an external disk array

The disk array can be placed directly inside the computer, and the computer can also connect to the external storage device via a cable.

One is to use a built-in disk array card. For example, the disk is installed inside the server, the host does not directly access the disk but the array card is accessed through the internal I/O bus, and the data distribution on the drive group is managed by the array card.

The other is an external disk array enclosure. The external raid enclosure, which is equipped with a large number of disks, has the advantages of fast data storage, high storage capacity, and is ideal for deploying massive data storage in central storage areas within the enterprise.

Commonly used external disk array cabinets have the following types of interfaces.

DAS (direct attached Storage, directly attached storage) refers to connecting an external storage device directly to a computer via a connecting cable. In this case, the data and operating system are often not separated. DAS is mainly used to solve the storage space expansion and high performance transmission requirements of a single computer. In addition, with certain software and hardware configurations, DAS can also form a dual-machine high-availability system for 2 computers.

NAS (Network attached Storage, net attached storage) is a specially crafted network file system server. Advantages include ease of use and manageability of the system, granular data sharing, and sharing of files between shared users.

A SAN (Storage area Network) is a high-speed network that provides data transfer between a computer and a storage system (usually a disk array). The SAN's support technology is Fibre Channel (Fibre channel, referred to as FC), so it has high bandwidth. The complexity of the system is greatly reduced by the connection between the host and the storage device through the Intelligent San Switch and the unified management of the San management software.

ISCSI (Internet SCSI) is a TCP/IP-based protocol used to establish and manage the connectivity between IP storage devices, hosts, and clients, and to create an IP-based SAN. Essentially, the SCSI block is mapped to a TCP/IP packet for transmission, so the server and storage devices are much less constrained in space layout and can even span regions and countries. Windows Server 2008 and subsequent editions have an "ISCSI initiator" built into the administrative tools, where the storage device is called the target, and the host is called the Initiator (initiator).

2.3.4 File System

The FAT (File Allocation table, or document allocation table) file system is the file system that was most widely used and supported by the operating system. Currently the Windows operating system supports the FAT32 format, but a single file cannot exceed 4GB. Typically, SQL Server data files may exceed 4GB, so SQL Server supports but is not recommended for installation on disks on the FAT32 file system.

NTFS (NT File system) is currently the mainstream Windows file system, its significant advantage is security and stability, in the use of the process is not easy to produce file fragmentation, the use of disk space and the speed of the software is beneficial.

Tips:

You can convert the FAT32 file system to NTFS by running the convert command in the Command Prompt window.

2.3.5 evaluating the I/O subsystem using SQLIO

SQLIO is Microsoft's official Disk Stress test tool for testing the IOPS (Input/output per second) value of the disk. Although not designed for SQL Server working mode, this tool can still be used to diagnose the performance of some of the I/O types related to SQL Server, measuring the IOPs, throughput (MB/s) and latency of the disk.

This tool needs to be downloaded to Microsoft's official website to install the SQLIO Disk Subsystem Benchmark tool.

Open the target folder of the installer, locate the Param.txt file, and modify it with the text Editing tool. For example:

F:\testfile.dat 2 0x0 300

Each row has 4 parameters, respectively:

(1) The test file and its full path. The above example indicates that you want to test the IOPS of the F drive and generate a test file named Testfile.dat on the F drive.

(2) How many threads are used at the same time when testing. The recommendations are consistent with the number of CPUs.

(3) Mask. is always 0x0.

(4) The size of a test file that is generated. Unit is MB.

Open a Command Prompt window, go to the installation folder of SQLIO, and run the test command (case sensitive). For example:

Sqlio-kr-s360-frandom-o8-b8-ls-fparam.txt

The values of ios/sec and mbs/sec are highlighted in the output results.

2.3.6 evaluating the I/O subsystem using SQLIOSIM

The SQLIOSim is used to verify the basic functionality of the I/O subsystem after pressurization. This tool attempts to simulate the actual operating mode of SQL Server (random Read and write data files, sequential read and write log files) and check the correctness of the results. This tool is typically used to isolate hardware-related issues with SQL Server.

This tool is installed by default with SQL Server and can be downloaded to the official website. After installation there will be 2 files, one command line tool and the other is the graphical interface tool.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/5A/11/wKioL1T1g52zOTs1AAKliOn7upo405.jpg "style=" float: none; "title=" SQLIOSIM 1.png "alt=" Wkiol1t1g52zots1aaklion7upo405.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/5A/15/wKiom1T1govTbeY-AAak8aysnJE161.jpg "style=" float: none; "title=" SQLIOSIM 2.png "alt=" Wkiom1t1govtbey-aaak8aysnje161.jpg "/>

This article from "SQLServer2014 series" blog, declined reprint!

2.3 Planning the I/O subsystem