Storage System-storage technology (2)

SQLServer and Windows-based systems Microsoft SQL Server is an application that uses Windows-based systems. It does not cover how SQL Server reads and writes data to NTFS file systems. We will discuss the specific WindowsIO system, which reports errors to Windows event logs. This should help you solve a lot of problems

SQLServer and Windows I/O subsystems Microsoft's SQL Server is an application that uses Windows I/O subsystems and does not cover how SQL Server reads and writes data to NTFS file systems. We will discuss specific Windows I/O systems, which report errors to Windows event logs. This should help you solve a lot of problems

SQLServer and Windows I/O subsystems

Microsoft's SQL Server is an application that uses the Windows I/O subsystem and does not cover how SQL Server reads and writes data to the NTFS file system. We will discuss specific Windows I/O systems, which report errors to Windows event logs. This should help you solve many storage errors. As shown in Figure 4-8, the storage system component reports errors to Windows System Event Logs, and SQL Server Reports errors to Windows application logs. You can view Event Logs directly or use System Center to mine Event Logs to locate operational errors.

The volume manager Driver (FTDISK. SYS) creates a new I/O Request package and passes it to the partition manager Driver (partmgr. sys ). Once a request packet is created, the volume manager sends the packet to the DISK drive (DISK. SYS ). FTDISK. SYS continues to monitor the successful transfer of request packets. If the problem is detected, FTDISK. SYS reports the error to the System Event Log. These FTDISK errors usually represent very serious storage system problems. In this case, the disk driver sends the storage request to the multi-path system driver (mpio. sys) or the port driver (Storport. sys ). Multi-path I/O is a Microsoft technology used in the storage area network (SAN. The supplier can create a driver for a specific module (DSM) of the MPIO device, which details how the multi-path driver should load balance the I/O across different storage paths. Microsoft provides a common DSM that provides limited failover capabilities. Non-SAN technology, no MPIO. HBA is a hardware physical block used to connect disk drives and other storage devices. The HBA manufacturer creates a micro-port driver connected to STORPORT. SYS. Most HBA drivers report communication errors to Application Event Logs. Ideally, the entire chain of this event takes no more than 20 milliseconds. Performance is affected by multiple factors, the most important of which is latency ). FTDISK. SYS and SQL Server time each I/O, if the round-trip time exceeds 15 seconds (for SQL Server or FTDISK. SYS 1 minute), the error is reported to the SQL log and Windows Application Event Log. When you want to see that a normal operation is measured in milliseconds, then 1 second is an eternal one.

Select the correct Storage Network

This chapter introduces the SQL Server instance in a single disk drive. A more complex storage network connects multiple hosts or starters to many storage devices. These advanced storage area networks facilitate low-latency and high-throughput communication. The storage network facilitates the sharing of storage resources. Direct Connection storage (DAS) provides relatively low-cost performance, but DAS storage can isolate performance and capacity. Imagine multiple applications that grow at different rates or at different times. Unified Storage connected to the SAN network enables users to share both storage capacity and availability performance. Complex storage networks are usually built using fiber channel (FC) technology.

FC is different from most server network protocols because it is not a route. Routing allows you to create a large and elastic network, but the routing network requires a large amount of overhead. If you are familiar with fiber channel, you may already know the routing solution. There are several products that meet this role, but their use is extremely complex. Because it is not a route, FC defines standards for direct and exchange storage network connections. Modern FC networks use high-speed network switches for communication. The storage network is not limited to optical fiber channels. Several protocols define methods for transmitting and storing data over an existing Server IP network. The Channel Internet Protocol (FCIP) allows the data frame of the fiber Channel to be enclosed in an IP packet. Internet Small Computer Systems

Interface -- iSCSI) allows SCSI data to be transmitted over an IP network. Different layers of FCIP and iSCSI transmission and storage networks. Fiber Channel frames are similar to Ethernet data frames. SCSI is a storage control system, comparable to Internet protocols. The transfer control protocol is a Network Interconnection protocol, so there is no such thing in the storage network (analog ). Emerging technologies, such as fiber Channel Over Ethernet (FCoE), combine the attributes of the existing fiber Channel network and the Ethernet route network. Regardless of the network technology used to transmit storage information, remember that the bandwidth is not unlimited. Excessive storage traffic not only negatively affects the performance of a single system, but also hinders all connected components. Many applications must meet the minimum performance requirements listed in the SLA. The performance of the storage network is the key to the overall application performance.

Comparison between block-based storage and file-based storage

Windows uses NTFS to create a structure so that one or more blocks can be used to store files. When a server directly accesses a physical disk, it is called block-based access. When data is accessed through the server network, such as the TCP/IP protocol, it is called file data. The device that provides file access is called network attached storage (NAS ). The disk drive stores data in blocks. Each block contains 512 bytes of data (some storage arrays use 520 bytes of data, and an additional 8-bit definition is used to ensure data integrity checksum ). The data block of the disk drive is numbered by the logical block number (LBNs) used by the disk firmware.

For SQL Server, we recommend that you use a 64 K distribution cluster to format NTFS partitions of data and log files. This setting maximizes efficiency and minimizes wasted space. If a partition is used to store many small files, it should be formatted using the standard 4 kb cluster size. Using the allocation unit will cause a fragmented MFT $, which will significantly damage file access performance.

From SQL Server 2008 R2, the storage administrator can choose to use the Server Message Block (SMB) network to access data files. TechNet provides a detailed introduction to SMB advantages: http://technet.microsoft.com/en-us/library/ff625695 (WS.10). aspx.

SQL Server 2012 supports SMB 3.0 and provides higher performance than earlier versions. For more information about configuring SQL Server 2012 SMB 3.0, visit: http://msdn.microsoft.com/en-us/library/hh759341.aspx.

Setting the SMB network allows you to connect to your file through the UNC path (\ server_name \ share. This access can greatly simplify network-based storage settings. However, you should exercise caution and perform special checks to ensure that your specific system is supported, because NAS devices generally do not support this configuration. Compared with SMB using iSCSI, iSCSI is a protocol used to access block data through the server network. It needs to use the boot program on the host server and a compatible iSCSI storage target. SMB and iSCSI communicate with each other over the server network. You must ensure that the server network is low in latency and have available bandwidth to handle the requirements on it. Most fibre channel networks handle storage traffic ). If you use the Server network to transmit the SQL Server Information Flow of blocks or files, it may be used to transmit the storage information flow. To replace a private network, we need to consider implementing QoS. It will put a higher priority on the storage information flow, exceeding the normal network data packets. Remember, there is no technology to provide a panacea. Even a robust network can be filled with information flows, and storage transmission is extremely sensitive to latency.

Shared storage array

The shared array controller is mainly responsible for logical grouping of disk drives. The shared storage controller allows you to create large volumes that are free from faults. In addition to directly attaching the normal functions of the storage controller, a shared array Controller provides both storage performance and capacity. A shared array controller, usually called a SAN controller, provides more advanced functions than a directly attached system. Its function set is divided into three types:

? Efficient capacity utilization

? Storage hierarchy

? Data Replication

However, before studying the features of SAN arrays, this will help focus on the language that storage administrators use to describe their systems.

Capacity Optimization

To solve the waste of space, some storage array manufacturers now sell a technology called thin provisioning. The concept of automatic streamlining configuration uses the concept of real-time storage allocation in a storage pool. With it, multiple physical disk drives are combined into a large pool. Proper RAID protection applies to disk drives in the pool. Many volumes can be created from each pool. Merged or virtualized volumes are displayed on the host server. When a volume is created as a thin device, it only allocates a minimum amount of storage space.

For a streamlined pool, SQL Server allocates data and log files by writing zero to the entire data file. If Windows Server is set to use instant file initialization, files are created in a streamlined pool in a friendly way. The new storage is allocated in the pool (http://msdn.microsoft.com/zh-cn/library/ms175935 (v = SQL .105). aspx) only when the data actually increases ). Using scratch to create database files is for security purposes. Zero physical write to new space helps ensure that the previous data is not accidentally exposed to the new host. Check security requirements before activating instant file initialization.

If the data file has been created as a large file with zero padding, a zero-page recycle function can be used to recycle unused space on the array. Running the zero-page recycle function allows the array's zero space to be returned to an available storage pool so that it can be allocated to other applications. Deleting data from a database or even deleting files in a volume does not return the available space to the streamlined storage pool. As for recycling deleted File space, most storage providers provide a host tool that checks the NTFS Master File Table and reallocates the space from the deleted space. If you decide to delete a space from an SQL Server data or log file, you need to run the DBCC SHRINKFILE command to make the file smaller, and then run the host-side storage recycle tool, in order to return the space to the specified thin pool. Unfortunately, there is a dirty little secret in the streamlined storage pool. When the storage prediction is inaccurate, it is necessary to overconfigure the streamlined pool to optimize storage usage. This means that the storage team must closely monitor the growth rate of its new storage usage.

If a write request is sent to a thin volume that no longer has available space on Microsoft Windows, it will be a blue-screen server. To prevent this problem, the storage administrator has several options: run a storage array tool, such as zero-page collection, collecting unused space, and adding more physical storage to a streamlined pool, or migrate the volume to different arrays with more space. It is critical to capture growth issues before they become serious.