1 / 16

Module 2: Database Architecture

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

caia
Download Presentation

Module 2: Database Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 2:Database Architecture

  2. 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

  3. 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 Comparison of Core Schema and Data Structures (Objects)

  4. 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 Physical Data File Data File Data File Data File Data File Data File Temporary Tablespace Groups Tablespace Tablespace Filegroup Filegroup Logical Segment Segment Heap/Index Heap/Index Extent Extent Extent Extent Extent Extent Extent Blocks Blocks Blocks Blocks Pages Pages Pages ORACLE SqlsERVER

  5. Data Blocks, Extents, and Segments

  6. Storage Allocation Fundamental difference in storage allocation between Oracle and SQL Server

  7. 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

  8. Managing Extents and Pages (Continued) File Header Extent Extents in SQL Server GAMs and SGAMs

  9. Tablespaces and Data files • Oracle and SQL Server store data in data files • 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 data files

  10. SQL Server Data Files Three file types supported by SQL Server: • Primary Data Files • Secondary Data Files • Log Data Files

  11. Mapping of Tablespaces and Filegroups Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Data file Log file Data file Log file Tablespace Group Data file Log file Data file Log file Data file Log file Data file User Database Log file Redo Log Redo Log Redo Log SQL Server Instance Oracle Database Instance

  12. Demonstration 1: Viewing an Instance’s System Databases In this demonstration you will see how to: • Connect to an instance • Review system databases • Review application or user databases

  13. 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.

  14. 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

  15. Demonstration 2: View the Data Dictionary In this demonstration you will learn how to: • View the dictionary with object explorer • View the data dictionary with catalog views • View the data dictionary within a database • View the data dictionary via system stored procedures

  16. 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

More Related