SQL Server Database FAQ

A database is a logical container for data, structures and code with a logical name.  A single database can support many applications and users.  Permissions can be granted at the database level. However, permissions are generally better suited to be granted to an application that a user needs at the object level such as a stored procedure, table or view.

In SQL Server there are both system and user defined databases.  At the most basic level, a database consists of two physical files.  First is for the database objects and data.  The second file is for the transaction log which maintains transactional integrity as numerous users and applications are adding, removing and modifying data within the database.

The SQL Server system databases are responsible for particular functions of the underlying relational database management system. This includes:
Master – All system objects to run the active relational database management system.
Model – Template database for new user defined databases and Tempdb when SQL Server starts.
TempDB – Stores all temporary objects such as #temp tables, ##temp tables, hash and sort records, etc.
MSDB – Stores all SQL Server Agent related tables and stored procedures.
ResourceDB – Hidden and read-only database that includes all system objects.

A SQL Server user defined database supports a specific application such as CRM, eCommerce, Inventory, Reporting, etc.  Each user defined database on a SQL Server instance has a unique name and has a database owner. The initial database owner is generally the login who created the database.  However, this should be subsequently changed. The User Defined database has one or more schemas such as dbo. Each schema has tables, indexes, stored procedures, views, functions and more.

The SQL Server MDF file is one of two core files to support a physical SQL Server database.  The MDF file is responsible for storing the system objects, user defined object definitions (i.e. tables, views, stored procedures, etc.), data and indexes.  Each SQL Server database must have an MDF file and only one MDF file can exist per database.  The MDF file generally is the largest file supporting the database since the size of the data should be much larger than the space needed to support the transaction log.

A SQL Server NDF file is a secondary data file to support a physical SQL Server database.  Although it is not mandatory for a SQL Server database, an NDF file can support tables and indexes.  NDF files store a database objects across numerous disk drives to improve performance or support the database growth.

The SQL Server LDF file is the second of two core files to support a physical SQL Server database.  The LDF file supports the database transaction log which stores before and after versions of records to enable a transaction to rollback.  The LDF file is generally a fraction of the database file size, perhaps five to twenty five percent of the database.  There are circumstances with large transactions where the transaction log file may be larger. The Recovery Model determines if the transaction data is retained and requires regularly scheduled transaction log backups or if data is flushed when checkpointed.

The SQL Server Transaction Log File corresponds to the physical LDF file supporting the database.  The SQL Server Transaction Log File is responsible for storing before and after records of data involved in UPDATE or DELETE transactions in order to rollback the transactions to maintain transactional integrity with numerous users in the database.