On high availability in SQL Server (2) files and filegroups

Before we talk about the high availability of SQL Server, let's start with the high availability of single-instance. In single instance high availability, the high availability of files and filegroups is not negligible. SQL Server allows for high availability by allowing the database to remain partially online while some files are corrupted or offline.

Files and Filegroups

A number of articles have been elaborated on the basic concepts of files and filegroups. Here I just mention that the filegroup exists as SQL Server to access an abstraction layer of the file. As a result, the actions on SQL Server are not directly targeted at files, but to filegroups.

The use of multiple filegroups and files is not only for decentralized IO and performance improvements, but also for high availability reasons. Paul Randal has done a great job of explaining how a database should contain several files or filegroups, see: http://www.sqlskills.com/blogs/paul/files-and-filegroups-survey-results/.

The advantages of using multiple files or filegroups in a database for high availability include:

The IO of a file is corrupted and the database is guaranteed to be partially online.

The index and table are stored separately, if the index file is not on the line, the data can still be accessed.

Historical data and thermal data are separated, historical archive data is corrupted, and thermal data is not affected.

Separating data files makes it possible to recover only some of the data during disaster recovery, thereby reducing downtime

The database is divided into multiple files so that you can solve the problem of insufficient space by adding files or moving files

File

In SQL Server, files are grouped into three categories, namely:

Master data File

Secondary data files

Log files

Where the primary data file defaults to the end of the extension MDF, the secondary database file defaults to the NDF end, and the log file ends with LDF. Although the extension can be modified, it is strongly recommended that you do not change the name extension.

The file name mentioned above is worth the physical file name, but it actually operates in SQL Server with the logical file name.

At any time, the files will be in a certain state, which includes:

Online

Offline

In recovery

Restore pending

Question

You can sys.database_files this DMV to view information about the state contained in the database file, as shown in Figure 1.

Figure 1. View information about the status of files in the database

You can even attach a database when the database is missing the NDF file, for details, see an article by the MCM Huang Master: http://www.sqlnotes.info/2013/05/07/attach-database-with-missing-ndf-file/.