04 SQL Server Database Architecture Part2

Main ideas / lessons

SQL Server instances & database limits

• On a single computer, you can configure multiple SQL Server instances:
  • 1 default instance + up to 49 named instances
• Each instance can host up to 32,767 databases
• Databases are created inside an instance using SSMS (SQL Server Management Studio)

What a database physically contains (internal architecture)

When you create a database, SQL Server automatically creates:

• A default file group called primary (conceptually like a “folder”)
• At least one data file inside that file group
  • Default data file type: MDF (master data file)
• At least one transaction log file
  • Extension: LDF

Key concepts:

• File group = a container (folder) for data files
• Data file = where table data is physically stored
• Transaction log file = where database change history is recorded for recovery

Size units inside data files

• Page
  • Fixed size: 8 KB
  • Pages are numbered sequentially
• Extent
  • Fixed size: 8 pages
  • Therefore extent size = 8 × 8 KB = 64 KB

Emphasis: page size is fixed and cannot be changed.

How data is laid out

• Table “data” is stored physically in data files—tables are logical structures
• Data is stored across:
  • pages → extents → data files → file groups → database → SQL Server/OS + hard disk
• Storage model:
  • RDBMS stores data on disk via the operating system, not in RAM
  • SQL Server manages data, while the OS writes it to the hard disk

Transaction log file purpose

• The transaction log records database operations (described as read/write-related actions and “who/when/what” metadata)
• Minimum requirement:
  • A database cannot function without at least one LDF transaction log file
• Recovery:
  • Used to recover after failures/emergencies (compared to a “black box” in an aircraft)

Data distribution strategies

• Round-robin behavior (when multiple data files exist):
  • Rows are placed sequentially across files (next row goes to the next file)
• Fill-and-go behavior (when multiple log/data files exist):
  • Fill one file, then move to the next when full

Managing file groups (administrative reasons)

File groups help with segregation for:

• placement of data
• administrative operations
• backups/restores/copy-like operations at a file-group level

Analogy: file groups act like folders (e.g., birthdays vs marriage photos).

Default behavior:

• By default, new table data goes to the default file group (primary)

---

Methodology / instructions (as presented)

1) Create a database in SQL Server (via SSMS)

1. Open SSMS
   • Start menu → SQL Server Management Studio
2. In Object Explorer
   • Expand the server and Databases node
3. Create database
   • Right-click Databases → New Database
   • Enter Database Name (example: “Baser/Basar”)
   • Click OK
   • Refresh to see the new database

2) Understand what gets created automatically when a database is made

SQL Server automatically creates:

• A file group (default name: primary)
• At least one data file in that file group (default described as MDF)
• At least one transaction log file (extension LDF)

3) View physical file locations

For an existing database:

1. Right-click database → Properties
2. Check the Files tab/section:
   • data file path (MDF)
   • log file path (LDF)
3. Browse/copy the path on disk (examples mentioned: C drive and SQL Server installation directories)

4) Create an additional file group (file-group management)

High-level steps:

1. Right-click the database → Properties
2. Find Filegroups
3. Choose Add Filegroup
4. Provide a file group name (example: “aa/ A A”)
5. Add a file into that file group
   • Go to the Files section
   • Click Add
   • Provide a file name
   • Select whether it’s a data file or log file (speaker indicates it can be treated accordingly)
6. Click Apply/OK
7. Optionally set the file group as default (so new table data goes there)

---

Speaker / sources featured

• Speaker (primary): The instructor/host speaking in the video (name not stated in the subtitles)
• Primary referenced software/systems:
  • SQL Server
  • SQL Server Management Studio (SSMS)
  • Operating System (OS) (examples: Windows, Linux, Ubuntu)
  • Hard disk storage
• Referenced concepts/entities:
  • Microsoft (for the fixed page size claim)
  • Bill Gates (mentioned only as part of the emphasis that fixed size cannot be changed)
  • CC camera / black box in an airplane (used as an analogy for transaction log recovery)