SQL Server Architecture

Each instance of the Microsoft SQL Server typically represented by the tree on the left window of the Management Studio GUI has four system databases and at least one user database. The System databases are named master, model, tempdb, and msdb. The master database holds information about other databases and entities. The model database is a template holder for new databases. The tempdb database holds transient and temporary data for the DBMS while msdb is used to store scheduling and job related information. While some organizations use a single user database, others categorize their data into several appropriately structured databases. For example, a University DBMS may have separate databases for courses, enrollment, fees, and employees. Databases may be fluidly detached from one SQL Server instance and attached to another.

The SQL Server allows concurrent access to the tune of thousands of users manipulating several databases. Each user is associated with a predefined set of security permissions and roles. A user's connection to an instance of SQL Server is typically associated with a particular database - the default database assigned to the user by the DBA.

SQL Server databases and log files are separately stored in a set of files on the Windows Operating System. The primary data file is the first data file associated with a database. Each database has exactly one primary data file (typically with an extension .mdf) and this file contains references to all other data files. This file represents the starting point of data within the database and contains references to the other data files. Other files that contain data for the database are called Secondary data files. These files have an .ndf extension. Small databases may not have secondary data files while large databases may be associated with several secondary data files. Log files hold records of all transactions and commands associated with a particular database and have the extension .ldf. Each database has one or more log files. Note that the data and log file extensions are recommended rather than compulsory.

SQL Server Database Structure

The locations of all the files that constitute each database are held both in the master database and the primary file. A logical file name is used to refer to files in T-SQL commands and queries and must be unique within a database. The Operating System file name refers to the actual name and physical location of the file.