How to use bare devices in SQL Server

This article is also suitable for Oracle DBA and MySQL DBA Reading

Bare devices apply to Linux and Windows

In Oracle and MySQL is also supporting the bare devices!!

Introduced

Everybody sees this title must be confused, bare equipment?? A nude device?? Too wicked. O (∩_∩) o

Raw EQUIPMENT: Some people call raw disk, also known as raw device

Baidu Click:

Http://baike.baidu.com/link?url=ran4ytgFFrsCmAywsOLa0uQczn8rxsEwWXF5WjdTNLx3Sv9Ng2Y1ScJsLT8k7CO7G1gOsiuq3EILSVhbYsgsSa

A raw device, also known as a raw partition, is a special character device that is not formatted and is not read by UNIX through a file system.

It is the responsibility of the application to read and write it. does not pass the file system buffer .

It is a device that is not managed directly by the operating system.

This device has less operating system this layer, I/O efficiency is higher. Many databases can improve I/O efficiency by using bare devices as storage media.

Because the use of bare devices to avoid the next layer of the operating system, data directly from disk to the database transmission, so the use of raw devices for read and write frequent database applications, can greatly improve the performance of the database system.

Of course, this is only true if disk I/O is very large and disk I/O is already known as a system bottleneck.

If the disk reads and writes are very frequent, so that disk reads and writes becomes the system bottleneck, the adoption of bare devices can greatly improve performance

SQL Server database corruption, detection, and simple fixes in Song's article

In this process, data-dirty pages are written by memory to the persisted IO subsystem, during which data may pass through these layers, depending on the IO subsystem:

Windows (write data must call the Windows API) the middle tier of Windows (anti-virus software, disk encryption system)

RAID controller (IO subsystem has RAID) disk or persistent storage such as SSD

In fact, if you use a raw device, the data is not required to go through the I/O subsystem, directly to write to the file

According to MSDN, the limitations of the bare devices in SQL Server

Using Raw Partitions

SQLSERVER2000 supports database creation in a partition using RAW format. The raw format partition is a file system that is not formatted, just like the FAT file system and the NTFS file system.

In some cases, database creation in RAW format partitions can produce significant performance improvements relative to NTFS or FAT file systems.

However, in most cases, it is recommended that you use NTFS or FAT partitions to store your database files

When you create a database under raw partitions, you cannot specify the physical name of the database data file; You can only specify the drive letter of the disk on which the database data file is to be stored .

If you use the Windows2000 server version, you can mount a drive that uses the raw file format. When you mount a local drive in an empty folder,

Windows2000 assigns a disk path, which is better than assigning only one drive letter.

The mounted drive is not subject to a 26-letter limit (more than 26 partitions can no longer be established), so you can use a raw partition that is not subject to a quantity limit.

Here are a few limitations that need to be considered when using raw partitions

(1) Each raw partition can have only one database data file.

(2) standard file operations, such as: Copy files, move files, delete files can not be used in raw partitions

(3) database files placed on raw partitions cannot be backed up directly using backup software, such as Acronis.True.Image.Echo.Enterprise.Server.

However, SQL Server's full database backups, differential backups, log backups, and these SQL Server backup functions are available for normal use