High Availability in SQLServer (2) ---- file and file group

Before talking about the high availability of SQLServer, we should first talk about the high availability of a single instance. In the high availability of a single instance, the high availability of files and file groups cannot be ignored. SQLServer allows the database to remain partially online when some files are corrupted or offline, thus ensuring high availability. Files and file groups

Before talking about the high availability of SQL Server, we should first talk about the high availability of a single instance. In the high availability of a single instance, the high availability of files and file groups cannot be ignored. SQL Server allows the database to remain partially online when some files are corrupted or offline, thus ensuring high availability. Files and file groups

Before talking about the high availability of SQL Server, we should first talk about the high availability of a single instance. In the high availability of a single instance, the high availability of files and file groups cannot be ignored. SQL Server allows the database to remain partially online when some files are corrupted or offline, thus ensuring high availability.

 

Files and file groups

Many articles have already elaborated on the basic concepts of files and file groups. Here I just mention that the website space and file group exist as an abstraction layer for SQL Server to access files. Therefore, SQL Server does not directly target files, but file groups.

Using multiple file groups and files is not only for the purpose of dispersing IO and improving performance, but also for the high availability. For details about how a database should contain several files or file groups, Paul Randal has already elaborated on it. See :.

Advantages of using multiple files or file groups in a database in terms of high availability include:

 

File

In SQL Server, files are divided into three types:

By default, the primary data file ends with the mdf extension, the Hong Kong virtual host, and the U.S. server. The secondary database file ends with ndf by default, and the log file ends with ldf. Although the extension can be modified, we strongly recommend that you do not change the extension.

The file name mentioned above is a physical file name, but the operation is actually performed on SQL Server. The operation is performed on the logical file name.

Files are in a certain State at any time, including:

You can use the sys. database_files DMV to view information about the database file including the status, as shown in 1.

. View the status of files in the database and other related information

 

You can even attach a database when the database lacks an NDF file. For details, see an article by Master MCM Huang :.

 

File Group

In SQL Server, the status of a file in the file group determines the status of the entire file group. However, the file status is independent from the database status. For example, the file status is offline, but the database can still be online. This is the so-called online database. For example, A file contains a table named selldata. If the file is offline but the database is online, all operations on the table for this selldata will fail.

If you want a file group in the database to be online, all files in the file group should be online.

 

Table Partitioning

Table Partitioning is a concept that emerged after SQL Server 2005. I have written an article about Table Partitioning before. Although the concept of Table Partitioning is very old, the use of table partitions is still very elementary in many places.

I have seen most examples of using Table partitions as a result of performance problems, so that I/O of large tables can be dispersed. But in fact, table partitions also improve availability. Benefits of using Table partitions include:

 

It is worth noting that the non-clustered index on the table should be partitioned after the table is partitioned. Otherwise, there may be performance examples.

 

DEMO

DEMO1: only rebuilding a partition

The following example shows a simple Partition Table. After the index is partitioned, only a partition is rebuilt, instead of the entire index. For example, if a table is partitioned by hot and cold data, you can only rebuild the hot data, which greatly reduces the time required to rebuild the index, as shown in code list 1.

(100,500) -- PARTITION architecture create partition scheme [x] as partition [t] TO ([PRIMARY], [FileGroup1], [FileGroup1]) [Sales]. [SalesOrderDetailPartition] ([SalesOrderID] [int] not null, [SalesOrderDetailID] [int] IDENTITY (1, 1) not null, [CarrierTrackingNumber] [nvarchar] (25) NULL, [OrderQty] [smallint] not null, [ProductID] [int] not null, [SpecialOfferID] [int] not null, [UnitPrice] [money] not null, [UnitPriceDiscount] [money] not null, [LineTotal] AS (isnull ([UnitPrice] * (1.0)-[UnitPriceDiscount]) * [OrderQty], (0.0), [rowguid] [, [ModifiedDate] [datetime] not null,) ON x (SalesOrderID) insert into [Sales]. [SalesOrderDetailPartition] ([SalesOrderID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], ModifiedDate, rowguid) SELECT [SalesOrderID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], ModifiedDate, rowguid FROM [Sales]. [SalesOrderDetail] test_partition_idx ON [Sales]. [SalesOrderDetailPartition] (ProductID) on x (SalesOrderID) test_partition_idxON [Sales]. [SalesOrderDetailPartition] REBUILD Partition = 1