DATABASE CONFIGURATION AND MAINTENANCE

Data is store within database on disk in one or two files.

TYPES OF FILE

SQL Server uses two different types of files.

1.Data files 

2.Transaction log files.

DATA FILES

 Data files are responsible for the long-term storage of all the data within a database.

      Data files contain data and objects such as tables, indexes, stored procedures, and views. Data files can be grouped together in filegroups for allocation and administration purposes.

TRANSACTION LOG FILES

Transaction log files are responsible for storing all the transactions that are executed against a database. Log files contain the information that is required to recover all transactions in the database.

FILEGROUPS

Filegroups are a logical structure,  defined within a database, that map a database and the objects contained within a database, to the data files on disk. Filegroups can contain more than one data file.

DATA MANIPULATION

All data manipulation within SQL Server occurs in memory within a set of buffers. If you are adding new data to a database, the new data is first written to a memory buffer, then written to the transaction log, and Finally persisted to a data file via a background process called check pointing.

FILE EXTENTION

Designing the data layer of a database begins with the database creation. When you create a database, it should have three Files and two filegroups. You should have a file with an mdf extension within a filegroup named PRIMARY, a file with an ndf extension in a filegroup with any name that you choose, and the transaction log with an ldf extension.

     The file extensions SQL Server uses conventionally are .MDF, .NDF and .LDF respectively.

MDF

MDF is more commonly known as the primary data file. It is believed that it originally stood for the master database file before the master database concept arrived. It contains a database header and system tables and points to other files in the database . User objects and relational data can be stored in the primary data file and/or in many secondary files.

NDF

NDF is an instance of a secondary data file, N denoting the canonical term for ‘1..N’. Some databases do not need or have any secondary files .  Separation of a primary and N secondary data files is primarily useful to allow systematic growth of the database across multiple disks. It is also very useful to increase IO performance, allowing less contention for file locks and allowing multi-threaded access to the data.

LDF

LDF is the Log Data File (the transaction log). One or more transaction log files can exist for a database and are used to help disaster recovery scenarios as well as ensure that operations on the data are ACID.

Default Filegroup

When objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup.  The files in the default filegroup must be large enough to hold any new objects not allocated to other filegroups.

        The PRIMARY filegroup is the default filegroup unless it is changed by using the ALTER DATABASE statement. Allocation for the system objects and tables remains within the PRIMARY filegroup, not the new default filegroup.

ALTER DATABASE <database name> MODIFY FILEGROUP <filegroup name> DEFAULT

TRANSACTION FILES...

A transaction log is a sequential record of all changes made to the database while the actual data is contained in a separate file. The transaction log contains enough information to undo all changes made to the data file as part of any individual transaction. The log records the start of a transaction, all the changes considered to be a part of it, and then the final commit or rollback of the transaction. Each database has at least one physical transaction log and one data file that is exclusive to the database for which it was created.

     SQL Server keeps a buffer of all of the changes to data for performance reasons. It writes items to the transaction log immediately, but does not write changes to the data file immediately. A checkpoint is written to the transaction log to indicate that a particular transaction has been completely written from the buffer to the data file. When SQL Server is restarted, it looks for the most recent checkpoint in the transaction log and rolls forward all transactions that have occurred from that point forward since it is not guaranteed to have been written to the data file until a checkpoint is entered in the transaction log. This prevents transactions from being lost that were in the buffer but not yet written to the data file.

FILESTREAM DATA..

To tackle the problem of storing, managing, and combining the large volumes of unstructured databases with the structured data in your databases, SQL Server 2008 introduced FILESTREAM.

     The FILESTREAM feature allows you to associate files with a database. The files are stored in a folder on the operating system, but are linked directly into a database where the fi les canbe backed up, restored, full-text-indexed and combined with other structured data.