Database structure

Database structure

Data is stored by SQL Server databases in one of three sets of files:

1. Primary Data File – starting point of the database which points to other files within the database. Each database has one primary data file, defaulting to the file extension .mdf
   
2. Secondary Data Files – Optional, user defined data files that can be used to spread the data through more files for performance and/or maintenance reasons. They can be used to spread data across multiple disks by putting each file on a different disk. Also, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended extension for secondary data files is .ndf
   
   Pages live in files, and each file in a database is given a unique file ID number. To identify a page in a database, both the page number and the file ID are required. Each page is 8KB in size. After allowing header information that is needed on each page, there is a region of around 8060 bytes for data. Data pages hold data from a single database object.
   
   Groups of 8 contiguous pages are referred to as an extent. SQL Server uses extents to simplify page management. Uniform extents have pages containing data from only one object while Mixed extents have pages containing data from different objects.
   
   The first 8 pages from database objects are always stored on mixed extents. After that, data pages for the objects are stored on uniform extents. In both primary and secondary data files, a small number of pages is allocated to the tracking of extent usage within the file.
   
3. Log files – hold information used to recover the database, when necessary. Each database must have at least one log file (extension .ldf)
   
   All transactions are written to the log file using the write-ahead logging (WAL) mechanism to ensure the integrity of the database, and to support rollbacks of transactions.
   
   When data pages need to be changed, they are called and changed in memory. The dirty pages are then written to the transaction log. Later, a background process known as checksum writes the dirty pages to the database files. For this reason, the pages in the log are critical to the ability of SQL Server to recover the database.
   
   SQL Server only writes to a single log file at any point in time. Additional log files are only used when space is not available in the active log file.