How database files are stored in SQL Server, files and filegroups (reproduced)

Introduction

In SQL Server, there is no difference between how a database is stored on a hard disk and how ordinary files are stored in Windows, just a few files. SQL Server manages files by managing logical filegroups. Understanding the concepts of files and filegroups is the most basic knowledge of a better configuration database.

Understanding files and filegroups

In SQL Server, the file that holds the data is managed through the logical object of the filegroup.

Let's take a look at a picture:

The logical database We see is composed of one or more filegroups

The filegroup manages the files on the disk. And the file holds the actual data for SQL Server.

Why manage files through filegroups

For the user's perspective, there are only three data objects specified for the stored filegroup for the Created object: Table, index, and large object (LOB)

Using filegroups allows you to isolate users and files so that users can set up tables and indexes on filegroups rather than files on actual disks. When files are moved or modified, the user-created tables and indexes are built on filegroups and do not rely on specific files, which greatly enhances manageability.

Also, using filegroups to manage files can allow different files within the same filegroup to be distributed across different hard disks, greatly improving IO performance.

SQL Server automatically allocates newly added space based on the initial size and growth of each file set, assuming that file a in the same filegroup is set to twice times the size of file B, a new data occupies three pages (page), 2 pages are allocated proportionally to file a, and 1 pages are assigned to file B. It can be seen from this that if multiple files are assigned to a single filegroup, SQL Server is automatically writing data to those files according to the configuration of each file, and the user cannot specify which database file the new inserted table's data should be placed in, and if you want to control the data inserted into the table to be stored in a particular database file, You must use partitioned tables and multi-filegroup mechanisms to divide a table into multiple partitions by specific data rules, each using a specific filegroup, and each filegroup using a different database file.

Classification of files

• Top documents: This file is a must, and there can only be one. This file stores additional information such as the location of other files. The extension is. mdf
• Secondary files: Can be built as many as possible, for different purposes to store.. ndf extension
• Log file: Storage log with. ldf extension

After SQL SERVER 2008, a file stream data file and a full-text index file are also added.

The above file name extensions can be modified arbitrarily, but I recommend using the default extension.

We can look at the file in the database using the following statement:

It is also important to note that if a table is present in multiple files physically, the table's data page is organized as n (n for a specific number of files) for a B-tree. Instead of an object for a B-tree.

Creating and working with filegroups

Creating files or filegroups can be done either in SSMs or by using T-SQL statements. For a database, you can add files and filegroups to an existing database, as well as files and filegroups at the time of creation. Here are some of the same things. Here's how to add files and filegroups to an existing database through SSMS.

First create the filegroup:

Once the filegroup is created, you can add files to the existing filegroups:

Here we can add the created table or index to the new filegroup by using a statement:

advantages and disadvantages of using multiple files

Typically, a small database does not need to create multiple files to distribute data. But as data grows, the drawbacks of using a single file begin to emerge.

First: Using multiple files to distribute data to multiple hard drives can greatly improve IO performance.

Second: Multiple files for a slightly larger database, backup and recovery can be a lot easier. I ran into a 150G database, but I don't have a large storage device at hand ...

However, in the world of databases, each benefit is often accompanied by a disadvantage:

It's obvious that using multiple files requires more disk space. This is because each file has its own set of B-tree organization, and its own growth space. Of course, there's a set of pieces of your own-.-but in most cases, the disadvantage of more disk space is far less than the benefits of multiple files.

Summary

This article makes a brief description of the concepts of files and filegroups in SQL Server, and describes how files and filegroups are configured. According to the business organization of different file groups to distribute different files, so that the performance of the increase, for you in the middle of the night to pick up a few calls the help is gray often big drop:-)

Original link

How database files are stored in SQL Server, files and filegroups (reproduced)