Storages structures with sql server
The challenge of RDBMS such as SQL Server is to ensure the best performance possible when writing and reading of large amounts of data.To ensure the optimal storage, SQL Server uses two types of files: data files and transaction log files.In this article, we will talk about these two types of files
The challenge of RDBMS such as SQL Server is to ensure the best performance possible when writing and reading of large amounts of data. For this each publisher heal its storage engine with even more serious than hard disk access, regardless of their specifications is the slowest part of a computer system. To ensure the optimal storage, SQL Server uses two types of files: data files and transaction log files.Data files
The data files provide a structured organized storage. A group of files can contain one or more files, which themselves contain data pages or index, grouped into extensions (extents).The filegroup is a logical content, that functions as explicit or implicit destination objects (partitions, tables, index). When you create a table or index, you can choose the file group in which it will be stored. A database has at least one group of files, created automatically at CREATE DATABASE. This group is called PRIMARY, and collect catalog tables (system metadata tables). So it can not be deleted. Each database has a default file group in which every object will be created if no other group of files is not explicitly specified in the order of creation. This group of file by default can be changed with the command:ALTER DATABASE name_of_the_base
MODIFY FILEGROUP [nom_du_groupe_de_fichiers] DEFAULT;
The commands to know the group of files by default are:SELECT *
FROM sys.filegroups
WHERE is_default = 1;
-- or
SELECT FILEGROUPPROPERTY ('PRIMARY', 'IsDefault')
A group of files can contain one or more files. It is simply Disk files, which indicate the location of the physical storage. If the filegroup is multifile, SQL Server will handle entries in the manner of a software RAID 1, in single mirror. The content is divided more or less equally between the different files.Transaction log files
The transaction log is a common mechanism in the DBMS world, which ensures transactional consistency of the data modification operations, and disaster recovery. In a word, all write data is enlisted in a transaction, either of the length of the instruction, or encompassing several instructions in the case of a declared user transaction using the BEGIN TRANSACTION command. Each transaction is firstly recorded in the transaction log to be canceled or validated in one block (property of atomicity of the transaction) to completion, either when the statement completes or in the case of a user transaction, at a ROLLBACK TRANSACTION or COMMIT tRANSACTION. All transaction is completely recorded in the log before being reflected in the data files. The log contains all the information needed to cancel the transaction in case of rollback (which is equivalent to undo step by step all changes), or replay it in case of server recovery (which can meet the requirement of sustainability the transaction).