Microsoft SQL Server Database Basics
108 Agent for Microsoft SQL Server Guide
Microsoft SQL Server Database Basics
To achieve optimal results using CA ARCserve Backup to back up your Microsoft
SQL Server databases, the following database fundamentals are provided.
A database is a collection of tables, which are comprised of rows or records. Each
row is made up of columns that contain some sort of structured information
about a record. Database data is stored across two operating system files -- data
files and log files:
■
Data Files
--Contain user data and metadata.
–
Primary Data File
--Contains information about the database itself
including references to the other files comprising the database. In a
simple database, user-defined data and objects can be stored in the
Primary Data File, though Secondary Data Files should be used for such
data. Each database has one primary data file (.mdf).
–
Secondary Data Files
--Contain user-defined data and objects, for
example, sales figures, employee information, or product details. Use
secondary data files (.ndf) to store database information across multiple
disks and to manage database growth.
Data Files can include full-text search catalogs, which allow you to search on
the full-text catalogs stored with a database. Though not actually data files,
FILESTREAM data allows you to store unstructured data including video,
photographs and other documents usually stored outside the database and
are treated as files in the database.
■
Log Files
--Contain information at the transaction level required to restore
your database to a specific point in time in the event of a disaster. There is at
least one log file (.ldf) per database though you may add more as needed.
Though SQL Server supports single-disk systems, you should store your Data
and Log Files on separate disks.
About Files and FileGroups
All databases have a primary FileGroup. By default, the Primary Data File and
any Secondary Data Files you create are stored in the Primary FileGroup.
However, you may create user-defined FileGroups to store your Secondary Data
Files. Use FileGroups to manage database performance, growth, and data
allocation.
Note:
A file can be part of only one FileGroup. A file or FileGroup can be used by
only one database. Transaction log files are not contained in FileGroups at all.
