Advanced database management
System
Chapter 1: Data File and Database options
12/22/2025 1
Overview
Database systems are designed to manage large data set in an organization.
The data management involves both definition and the manipulation of the
data which ranges from simple representation of the data to considerations
of structures for the storage of information.
The data management also consider the provision of mechanisms for the
manipulation of information.
Today, Databases are essential to every business.
They are used to maintain internal records, to present data to customers and
clients on the World-Wide-Web, and to support many other commercial
processes.
Databases are likewise found at the core of many modern organizations.
12/22/2025 2
Overview…
The power of databases comes from a body of knowledge and technology
that has developed over several decades and is embodied in specialized
software called a database management system, or DBMS.
A DBMS is a powerful tool for creating and managing large amounts of
data efficiently and allowing it to persist over long periods of time, safely.
These systems are among the most complex types of software available.
What is a database?
In essence a database is nothing more than a collection of shared
information that exists over a long period of time, often many years.
Database is a repository for collection of computerized data files
In common dialect, the term database refers to a collection of data that is
managed by a DBMS.
12/22/2025 3
Data File and Database options
Data within a database is stored on disk in one or more data files.
Prior to being written to the data file(s), every transaction is written to a
transaction log file.
Storing all your data in memory would provide extremely fast access, you
would lose everything after the machine was shut down.
To protect your data, it has to be persisted to disk.
Underneath each database is one or more files for persisting your data.
SQL Server uses two different types of files data and transaction log files.
Data files are responsible for the long-term storage of all the data
within a database.
Transaction log files, are responsible for storing all the
transactions that are executed against a database.
12/22/2025 4
Data File and Database options…
Instead of defining the storage of objects directly to a data file, SQL Server
provides an abstraction layer for more flexibility called a filegroup.
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.
All objects that contain data, tables, indexes, and indexed views have an
ON clause that you can use to specify when you create an object that allows
you to specify the filegroup where SQL Server stores the object.
As data is written to the objects, SQL Server uses the filegroup definition to
determine on which file(s) it should store the data.
12/22/2025 5
Data File and Database options…
At the time that a file is added to a database, you specify the initial size of
the file.
You can also specify a maximum size for the file, as well as whether SQL
Server automatically increases the size of the file when it is full of data.
File Extensions
SQL Server uses three file extensions: .mdf, .ndf, and .ldf.
A file with an .mdf extension is usually the first data file that is created
within a database,
generally is associated with the primary filegroup, and
usually is considered the primary data file which contains all the system
objects necessary to a database.
12/22/2025 6
Data File and Database options…
The .ndf extension is generally used for all other data files underneath a
database, regardless of the filegroup to which the file is associated.
The .ldf extension generally is used for transaction logs.
The file extensions that you see for SQL Server are nothing more than
naming conventions.
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.
12/22/2025 7
Data File and Database options…
When you modify or delete an existing row, if the row does not already
exist in memory, SQL Server first reads the data off disk before making the
modification.
Similarly, if you are reading data that has not yet been loaded into a
memory buffer, SQL Server must read it out of the data files on disk.
When the database is created, the primary filegroup is marked as the default
filegroup.
After you create the database, you should mark the second filegroup as the
default filegroup.
By changing the default filegroup, you ensure that any objects you create
are not accidentally placed on the primary filegroup and that only the
system objects for the database reside on the primary filegroup.
12/22/2025 8
Data File and Database options…
Transaction Logs
When SQL Server acknowledges that a transaction has been committed,
SQL Server must ensure that the change is hardened to persistent storage.
Although all writes occur through memory buffers, persistence is
guaranteed by requiring that all changes are written to the transaction log
prior to a commit being issued.
In addition, the writes to the transaction log must occur directly to disk.
Because every change made to a database must be written directly to disk,
12/22/2025 9
Data File and Database options…
FILESTREAM data
The volume of data within organizations has been exploding, leading the
way in this data explosion is unstructured 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 introduced FILESTREAM.
The FILESTREAM feature allows you to associate files with a database.
12/22/2025 10
Data File and Database options…
FILESTREAM data
The files are stored in a folder on the operating system,
but are linked directly into a database where the files can be backed up,
restored, and combined with other structured data.
To store FILESTREAM data within a database, you need to specify where
the data will be stored.
You define the location for FILESTREAM data in a database by
designating a filegroup within the database to be used for storage.
The FILENAME property defined for a FILESTREAM filegroup specifies
the path to a folder.
12/22/2025 11
Data File and Database options…
tempdb Database
SQL Server uses tempdb for worktables used in grouping/sorting
operations, worktables to support cursors, the version store supporting
snapshot isolation level, and overflow for table variables.
You can also cause index build operations to use space in tempdb.
Due to the potential for heavy write activity, you should move tempdb to a
set of disks separated from your databases and any backup files.
12/22/2025 12
Data File and Database options…
Database Options
A database has numerous options that control a variety of behaviors.
These options are broken down into several categories, including the
following:
Recovery
Auto options
Change tracking
Access
12/22/2025 13
Data File and Database options…
Recovery Options
The recovery options determine the behavior of the transaction log and how
damaged pages are handled.
Recovery Models: Every database within a SQL Server instance has a
property setting called the recovery model.
The recovery model determines the types of backups you can perform
against a database.
The recovery models available in SQL Server are:
Full
Bulk-logged
Simple
12/22/2025 14
Data File and Database options…
Recovery Options
Full recovery model: when a database is in the Full recovery model, all
changes made, using both data manipulation language (DML) and data
definition language (DDL), are logged to the transaction log.
Because all changes are recorded in the transaction log,
it is possible to recover a database in the Full recovery model to a given
point in time
so that data loss can be minimized or eliminated if you should need to
recover from a disaster.
12/22/2025 15
Data File and Database options…
Recovery Options
Bulk-logged recovery model: allows certain operations to be executed
with minimal logging.
When a minimally logged operation is performed, SQL Server does not log
every row changed but instead logs only the extents,
thereby reducing the overhead and improving performance.
Bulk-logged recovery model does not log every change to the transaction
log, you cannot recover a database to a point in time, within the interval that
a minimally logged transaction executed, when the Bulk-logged recovery
model was enabled.
12/22/2025 16
Data File and Database options…
Recovery Options
Simple recovery model: A database in the Simple recovery model logs
operations to the transaction log exactly as the Full recovery model does.
Each time the database checkpoint process executes, the committed portion
of the transaction log is discarded.
A database in the Simple recovery model cannot be recovered to a point in
time,
because it is not possible to issue a transaction log backup for a database in
the simple recovery model.
12/22/2025 17
Data File and Database options…
Auto Options
There are five options for a database that enable certain actions to occur
automatically:
AUTO_CLOSE
AUTO_SHRINK
AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS
AUTO_UPDATE_STATISTICS_ASYNCH
12/22/2025 18
Data File and Database options…
Access
Access to a database can be controlled through several options.
The status of a database can be explicitly set to ONLINE, OFFLINE, or
EMERGENCY.
When a database is in an ONLINE state, you can perform all operations
that would otherwise be possible.
A database that is in an OFFLINE state is inaccessible.
A database in an EMERGENCY state can be accessed only by a member of
the db_owner role, and the only command allowed to be executed is
SELECT.
12/22/2025 19
Data File and Database options…
Access
You can control the ability to modify data for an online database by setting
the database to either READ_ONLY or READ_WRITE.
A database in READ_ONLY mode cannot be written to.
when a database is placed in READ_ONLY mode, SQL Server removes
any transaction log file that is specified for the database.
Changing a database from READ_ONLY to READ_WRITE causes SQL
Server to re-create the transaction log file.
12/22/2025 20
Data File and Database options…
Access
User access to a database can be controlled through the SINGLE_USER,
RESTRICTED_USER, and MULTI_USER options.
When a database is in SINGLE_USER mode, only a single user is allowed
to access the database.
A database set to RESTRICTED_USER only allows access to members of
the db_owner, dbcreator, and sysadmin roles.
If multiple users are using the database when you change the mode to
SINGLE_USER or users that conflict with the allowed set for
RESTRICTED_USER, the ALTER DATABASE command is blocked until
all the non-allowed users disconnect.
12/22/2025 21
Data File and Database options…
Maintaining Database Integrity
In a perfect world, everything that you save to disk storage would always
write correctly, read correctly, and never have any problems.
Unfortunately, your SQL Server databases live in an imperfect world where
things do go wrong.
this occurs very rarely, data within your database can become corrupted if
there is a failure in the disk storage system as SQL Server is writing to a page.
Data pages are 8 kilobytes (KB) in size, but SQL Server divides a page into
16 blocks of 512 bytes apiece when performing write operations.
If SQL Server begins writing blocks on a page and the disk system fails in the
middle of the write process, only a portion of the page is written successfully,
producing a problem called a torn page.
12/22/2025 22
Data File and Database options…
Database Integrity Checks
databases have an option called PAGE_VERIFY.
The page verification can be set to either TORN_PAGE_DETECTION or
CHECKSUM.
The PAGE_VERIFY TORN_PAGE_DETECTION option exists for
backwards compatibility and should not be used.
When the PAGE_VERIFY CHECKSUM option is enabled, SQL Server
calculates a checksum for the page prior to the write.
Each time a page is read off disk, a checksum is recalculated and compared
to the checksum written to the page.
If the checksums do not match, the page has been corrupted.
12/22/2025 23
Thanks!
12/22/2025 24