sqlserver-underlying infrastructure-architecture-file and filegroup structure

SQL Server maps a database to a set of operating system files.

Data and log information is never mixed in the same file, and a file can only be used by one database. Filegroups are named collections of files for master data layout and administrative tasks, such as backup and restore operations.

Database files

SQL Server The database has three types of files:. MDF,.LDF,NDF (master data file, log file, secondary data file), note: The suffix name of the file is self-named.

Master data files: Each database has a primary database file, which is the starting point of the database, pointing to other files in the database.

Secondary data files: Databases can have no secondary data files, or can have multiple data files

Log files: Log files are log information used to store the database, including all the log information used to recover the database;

In SQL Server, the location of all files in the database is recorded in the primary file and the master database of the database. In most cases, the SQL Server database engine uses the file location information from the master database.

The database engine initializes the file location entries in the master database by using the file location information of the primary file in the following cases:

· When you attach a database by using the CREATE database statement with the for ATTACH or for attach_rebuild_log option.

· When you upgrade from SQL Server version 2000 or 7.0.

· When you restore the master database.

Logical and physical file names (SQL Server file has two names: Logical_file_name, Os_file_name)

logical_file_name is the name that is used when referencing a physical file in all T-SQL statements. The logical file name must conform to the SQL Server identifier rule and must be unique.

os_file_name is the physical file name that includes the directory path. It must conform to the operating system file naming rules .

Data file page

SQL Server the pages in the data file are numbered sequentially and the first page of the file starts with 0. Each file in the database has a unique file ID number. To uniquely identify a page in a database, you need to use both the file ID and the page number. The following example shows the page numbers in the database that contains the 4-MB master data file and the 1-MB secondary data file.

650) this.width=650; "Width=" 309 "height=" 233 "src="/e/u261/themes/default/images/spacer.gif "alt=" sequential page number in two data files " Style= "Background:url ("/e/u261/lang/zh-cn/images/localimage.png ") no-repeat center;border:1px solid #ddd;"/>

The first page of each file is a page with a file containing information about the properties of the file. The other pages at the beginning of the file also contain system information, such as allocation mappings. A system page that is stored in the main data file and the first log file is a database boot page that contains information about the database properties.

File Size

SQL Server files can automatically grow from the size that they originally specified. When you define a file, you can specify a specific increment. Each time the file is populated, its size is increased by this increment. If there are multiple files in the filegroup, they do not grow automatically until all the files are filled. When they are filled, these files will grow in circulation.

Each file can also specify a maximum size. If you do not specify a maximum size, the file can grow until you run out of all available space on the disk. This feature is especially useful if SQL Server embeds an application as a database, and the user of the application cannot quickly contact the system administrator. Users can automatically grow files as needed to reduce the administrative burden of monitoring the available space in the database and manually allocating additional space.

Database Snapshot files

The file format used by the database snapshot to store its "copy on write" Data depends on whether the snapshot was created by the user or used internally:

· A user-created database snapshot stores its data in one or more sparse files. Sparse file technology is a feature of the NTFS file system. First, the sparse file does not contain any user data, and the sparse file is not allocated disk space for user data. For general information about using sparse files in database snapshots and how database snapshots grow, see how database snapshots work and understand the sparse file sizes in database snapshots.

· database snapshots are used internally by specific DBCC commands. These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. The internal database snapshot uses the sparse alternate data stream of the original database file. As with sparse files, the standby database stream is also a feature of the NTFS file system. With sparse alternate data streams, you can make multiple data allocations that are associated with a single file or folder, but do not affect file size or volume statistics.

Database file Group

· for ease of allocation and management, database objects and files can be grouped together into filegroups. There are two types of filegroups:

· Primary file group

· The primary filegroup contains the master data file and any other files that are not explicitly assigned to other filegroups. All pages of the system table are assigned to the primary filegroup.

· User-defined file groups

· A user-defined filegroup is any filegroup that is specified by using the FILEGROUP keyword in the CREATE database or ALTER database statement.

· log files are not included in the filegroup. Log space is managed separately from the data space.

· A file cannot be a member of multiple filegroups. tables, indexes, and large object data can be associated with a specified filegroup. In this case, all of their pages are assigned to the filegroup, or the tables and indexes are partitioned. Partitioned tables and indexes are split into cells, and each unit can be placed in a separate filegroup in the database. For more information about partitioned tables and indexes, see partitioned Tables and partitioned indexes.

· a filegroup in each database is designated as the default filegroup. If a filegroup is not specified when the table or index is created, all pages are assumed to be assigned from the default filegroup. You can have only one filegroup at a time as the default filegroup. Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another. If you do not specify a default filegroup, the primary filegroup is the default file group.

• File and filegroup examples

• The following example creates a database on an instance of SQL Server. The database consists of a master data file, a user-defined filegroup, and a log file. The master data file is in the primary filegroup, and the user-defined filegroup contains two secondary data files. The ALTER DATABASE statement designates the user-defined filegroup as the default filegroup. The table is then created by specifying a user-defined filegroup.

  Example:

Use master;

GO

CREATE DATABASE MyDB

On PRIMARY

(Name= ' mydb_primary ', filename= ' c:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\mydb_ Prm.mdf ',

SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB),

FILEGROUP MYDB_FG1

(NAME = ' mydb_fg1_dat1 ' FILENAME = ' c:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\mydb_fg1_ 1.ndf ',

SIZE = 1MB, MAXSIZE=10MB,FILEGROWTH=1MB),

(NAME = ' mydb_fg1_dat2 ', FILENAME = ' c:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\mydb_fg1_ 2.ndf ',

SIZE = 1MB,MAXSIZE=10MB,FILEGROWTH=1MB)

LOG on

(Name= ' Mydb_log ', FILENAME = ' c:\Program files\microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\mydb.ldf ',

SIZE=1MB,MAXSIZE=10MB, FILEGROWTH=1MB);

GO

ALTER DATABASE MyDB

MODIFY FILEGROUP mydb_fg1 DEFAULT;

GO

sqlserver-underlying infrastructure-architecture-file and filegroup structure