exploring SQL and file systems Flashcards
What is SQL Physical Architecture?
Tells about how the data is actually stored in file system of an operating system.
Page, extent, database files, transaction log etc. are core components of physical architecture
What is logical architecture?
Tells how data is logically grouped and presented to the user.
eg: Tables, constraints, views, stored procedures, triggers etc. are core components of logical architecture
What are examples of Physical data structure components?
Page extent table index database files database file group transaction log file
What are File groups?
collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.
SQL Server maps a _______over a set of operating-system files.
database
Data and log information are _____ mixed in the same file, and individual files are used only by _____ database(s).
never, one
What do read only files help to reduce?
reduce the number of backups
by not backing up read only files due to not modifying
what is the typical log file extension?
.ldf
these are not apart of file groups
What are the three types of SQL file types?
Primary database files
Secondary database files
Transaction log files
Databases have two different file names. They are:
Logical file names
OS file names
What are logical file names?
Used in T-SQL to refer to a physical file
What are OS file names?
full name; folder path and physical file
What are primary database files?
-contains startup info for the database
- every database has one primary data file
file name extension : .mdf
-user data and objects stored here or in secondary
what are secondary database files?
files that are optional, user-defined, and store user data
- can be used to spread data across multiple disks, by putting each file on a different drive
- file name extension- .ndf
What are transaction log files?
holds log info that is used to recover the database
- must be at least one log file for each database
- file name extension .ldf
4 steps for modifying a file in a database
- runs a query to modify row in table
- change is recorded in transaction log
- query returns
- change is recorded in data file
What are pages in a database
fundamental unit of storage
8kb per page
96 byte header
contains header and body (body=data rows)
What is included in a page header?
Stores system info
- page number, page type, amount of free space, allocation unit ID pf the object that owns the page
What are slot arrays?
Slot arrays indicate the logical order of the data row on the page
What are different page types?
Data index text/image page free space index allocation map globally allocated map etc.
What are extents?
Basic unit in which space is managed
- 8 physically contiguous pages
- 16 extents per megabyte
- New tables are usually mixed extents until allocated space is full.
Uniform extents are?
owned by a single objects
-all 8 pages in the extent can only be owned by one object
mixed extents
- share by up to 8 objects
each page can be owned by a different object
What is the difference between log and database files
-database system is comprised of both
- all transactions write to log file immediately
pages are read from database memory
modified when needed.
