DBAArchitectureIntro

IntroductionMS SQL Server is a database server

Product of Microsoft

Enables user to write queries and other SQL statements and execute them

Consists of several features. A few are:◦ Query Analyzer

◦ Profiler

◦ Service Manager

◦ Bulk Copy Program (BCP)

Profiler

Monitoring tool

Used for performance tuning

Uses traces – an event monitoring protocol

Event may be a query or a transaction like logins etc

Service Manager

Helps us to manage services

More than one instance of SQL server can be installed in a machine

First Instance is called as default instance

Rest of the instances (16 max) are called as named instances

Service manager helps in starting or stopping the instances individually

Instances

Each instance is hidden from another instance

Enhances security

Every instance has its own set of Users, Admins, Databases, Collations

Advantage of having multiple instance is ◦ Multi company support (Each company can have its

own instance and create databases on the same server, independent on each other)

◦ Server consolidation (Can host up to 10 server applications on a single machine)

BCP

Bulk Copy Program

A powerful command line utility that enables us to transfer large number of records from a file to database

Time taken for copying to and from database is very less

Helps in back up and restoration

Query Analyzer

Allows us to write queries and SQL statements

Checks syntax of the SQL statement written

Executes the statements

Store and reload statements

Save the results in file

View reports (either as grid or as a text)

SQL Database ObjectsA SQL Server database has lot of objects like◦ Tables

◦ Views

◦ Stored Procedures

◦ Functions

◦ Rules

◦ Defaults

◦ Cursors

◦ Triggers

System DatabasesBy default SQL server has 4 databases◦ Master : System defined stored procedures, login details,

configuration settings etc

◦ Model : Template for creating a database

◦ Tempdb : Stores temporary tables. This db is created when the server starts and dropped when the server shuts down

◦ Msdb : Has tables that have details with respect to alerts, jobs. Deals with SQL Server Agent Service

Architecture OverviewSchema 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 Identity Columns

Procedure Stored Procedure

Function Function

Package N/A

Queue in Streams Advanced Queuing Service Broker Queue

Object Type Type

XML DB XML Schema Collection

Comparison of Core Schema and Data Structures (Objects)

Storage ArchitectureDatabase 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

ExtentExtent

FilegroupTablespace

Segment Segment

ExtentExtent

Filegroup

Heap/Index Heap/Index

ExtentExtent Extent

BlocksBlocksBlocks Blocks Pages Pages Pages

Data Blocks, Extents, and Segments

Structure Oracle SQL Server 2008

Smallest unit of logical storage

Block Page

Block size Variable 8 KB fixed

Storage allocationPerformed in multiple blocks; are ‘extents’

Performed in multiple pages; are ‘extents’

Extent size Variable 64 KB fixed

SegmentAny logical structure

that is allocated storage

No equivalent structure

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 PagesIn 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 ExtentGAM BitSetting

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

GAMs and SGAMs

Tablespaces and Data filesOracle 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

SQL Server Data Files

Three file types supported by SQL Server:

Primary Data Files

Secondary Data Files

Log Data Files

Mapping of Tablespacesand 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 fileData file

Data fileData file

Tablespace Group

Data fileData file

Data fileData file

Data fileData file

Data fileData file

Data fileData file

Redo LogRedo Log

Redo Log

Log fileData file

Log fileData file

Log fileData file

Log fileData file

User Database

Data fileData file

Data fileData file

Log file

Log fileData file

Logging ModelOracle 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 DictionaryIn 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