Module 2: Database
Architecture
Overview
Schema and Data Structure (Objects) Storage Architecture
Data Blocks, Extents, and Segments
Storage Allocation
Managing Extents and Pages
Tablespaces and Datafiles
SQL Server Data Files
Mapping of Tablespaces and Filegroups
Logging Model Data Dictionary
Schema and Data Structures (Objects)
Schema – a collection of objects owned by a database user Schemas in SQL Server provide logical separation of objects, similar to Oracle’s schema
Oracle SQL Server Table Table Index Index View View Synonym Synonym Sequence Sequence
Procedure Stored Procedure
Function Function
Package N/A
Queue in Streams Advanced Queuing Service Broker Queue
Object Type Type
XML DB XML Schema Collection & data type
Storage Architecture
Database storage architecture includes physical and logical structures Physical structures are data files, log files, and operating system blocks Logical structures are subdivisions of data files used to manage storage space
Data File Data File Data File Data File Data File Data File
Temporary Tablespace Groups Tablespace Extent Extent Filegroup Tablespace Segment Segment Extent Extent Filegroup Heap/Index Heap/Index Extent Extent Extent Blocks Blocks
Data Blocks, Extents, and Segments
Structure Oracle SQL Server 2012
Smallest unit of
logical storage Block Page
Block size Variable 8 KB fixed
Storage allocation Performed in multiple blocks; are ‘extents’ Performed in multiple pages; are ‘extents’
Extent size Variable 64 KB fixed
Segment Any logical structure that is allocated
Storage Allocation
Fundamental difference in storage allocation between Oracle and SQL Server
2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 2K block 6 x 2K = 12K EXTENT 8 x 2K = 16K EXTENT 12K + 16K = 28K SEGMENT (Table/Index) 8K block 8K block 8K block 2K block 8K block 8K block 8K block 2K block 8K block 8K block 8K block 2K block 8K block 8K block 8K block 2K block 8K block 8K block 8K block 2K block 8K block 8K block 8K block 2K block 8K block 8K block 8K block 2K block 8K block 8K block 8K block 2K block 8 x 8K = 64K EXTENT 8 x 8K = 64K EXTENT 64K + 64K = 128K HEAP/INDEX Oracle SQL Server
Managing Extents and Pages
In Oracle, each extent is dedicated to an allocated
object. In SQL Server, the equivalent is a uniform extent. SQL Server uses mixed extents: pages are allocated to objects with less than 8 blocks of data
Similar to the Oracle bitmap functionality used to
manage free space and extent allocation, SQL Server uses the Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM)
Oracle keeps track of extents using extent allocation maps
Managing Extents and Pages (Continued)
Current Use of Extent GAM Bit Setting SGAM Bit Setting
Free, not being used 1 0
Uniform extent, or full mixed
extent 0 0
Mixed extent with free pages 0 1 File Header Extent
Extents in SQL Server
Tablespaces and Datafiles
Oracle and SQL Server store data in datafiles
The largest logical storage structure in Oracle is a tablespace
The largest logical storage structure in SQL Server is a filegroup
Tablespaces/filegroups are used to group application objects
Tablespaces/filegroups optimize administration of datafiles
SQL Server Data Files
Three file types supported by SQL Server: Primary Data Files
Secondary Data Files
Demonstration: Working with Filegroups and
Data Structures
Demonstration:
Working with Filegroups and Data Structures
Mapping of Tablespaces and Filegroups
System Tablespace SysAux Tablespace Temporary Tablespace
BigFile Tablespace User Data Tablespace User Index Tablespace Undo Tablespace Redo Log Files
Master DB Resource DB TempDB Model DB MSDB User DB Data FG Index FG Log File(s)
Oracle Database Instance SQL Server Instance
Data file Data file Data file Data file Data file Tablespace Group Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Redo Log Redo Log Redo Log Log file Data file Log file Data file Log file Data file Log file Data file User Database Data file Data file Data file Data file Log file Log file Data file
Demonstration: Viewing an Instance’s System
Databases
Demonstration:
Logging Model
Oracle uses online redo logs to record changes made to the database by transactions and undo segments to
capture the ‘before image’ of data
SQL Server implements both of these functions using transaction logs. Each transaction record contains the undo and redo image of the transaction.
Data Dictionary
In Oracle the data dictionary is stored under the SYS schema in the SYSTEM tablespace
In SQL Server the data dictionary consists of:
Catalog View—the best way to access system metadata
Backward Compatibility Views—All system tables from previous releases are provided as backward compatibility views
Dynamic Management Views—to view the current state of the SQL Server system. Provide real-time snapshots of internal memory structures indicating the server state.
INFORMATION_SCHEMA views—SQL-99 method to view system metadata
SQL Server’s Resource database contains the metadata for system stored procedures
Demonstration: View the Data Dictionary
Demonstration:
Review
Defined schema and identified core schema objects Examined the storage architecture and its physical (datafiles, logfiles, and so on) and logical structures (blocks, extents, segments, and tablespaces)
Defined the hierarchy in the logical structures
Compared Oracle and SQL Server in terms of schema vs. databases, and tablespaces vs. filegroups
Examined the structures used in the implementation of the logging model (redo logs and rollback segments in Oracle vs. transaction logs in SQL Server)
Brief look at the location and composition of the data dictionary/system catalog
