MS SQL Server: Understanding Instances, Profiler, BCP, and Architecture, Slides of Database Management Systems (DBMS)

Download MS SQL Server: Understanding Instances, Profiler, BCP, and Architecture and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

DBA

Architecture

Intro

Introduction

MS 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)

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)

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 Objects

A SQL Server database has lot of objects

like

◦ Tables

◦ Views

◦ Stored Procedures

◦ Functions

◦ Rules

◦ Defaults

◦ Cursors

◦ Triggers

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

Data Blocks, Extents, and Segments

Structure Oracle SQL Server 2008

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

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 Pages

(Continued)

Current Use of Extent

GAM Bit

Setting

SGAM Bit

Setting

Free, not being used 1 0

Uniform extent, or full

mixed extent

Mixed extent with free

pages

File Header Extent

Extents in

SQL Server

GAMs and SGAMs

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

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

Data file Log file

Data file Log file

Data file Log file

Data file Log file

User Database

Data file Data file

Data file Data file

Log file

Data file Log file

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.