Another term for

Database

Eight pages

Page is 8KB

8KB * 8 = 64 KB

All objects are stored here; basic unit in the databases

One object to the 8 pages is a UNIFORM EXTENT

MIXED EXTENT is when objects are too small they just go (many of them) onto one Extent

8KB

The basic unit of the DB

This is a physical disk drive

It can have several or just one partition

Very

Large

Data

Base

OnLine

Transaction

Processing

gets data into the DB

OnLine

Analytical

Processing

focuses on getting info out of the DB

default extension of the database file

This is the primary

database files

take up how many databases

It is resized to 25% of the size

of your data file request

You use this to create a logical filegrouping

of the DB

It's to improve admin on VLDBs

It doesn't improve performance

-if you work with small DBs with RAID5

You separate them into:

data or tables

nonclustered indexes

primary default (where the system files must go and the first data file goes)

user defined

you can add additional filegroups using

ALTER DATABASE

 Can a filegroup contain:

indexes

tables

text

ntext

image data?

Can a file be a member of

more than one filegroup

at a time?

What are the components of

RDBMS?

maintain relationships

among the data

in the DB

ensures data is being stored correctly and follows rules defining the relationships

recovers data in case of failure

What must you specify when you create a DB

In SQL Server?

at least one file to store data

at least one file to hold transaction log

Where can you store data files

and log files?

SAN Storage Area Network

iSCSI-based network

locally attached drive

Redundant

Array of

Independent

Disks

striping

writes data

across multiple hard disk PARTITIONS

in a

STRIPE SET

best speed but

no fault tolerance

mirroring

this is slow because you must write to disk twice

this is when you place a separate controller

for each drive in the mirror

parity checksums

are written across all disks in the stripe set

However

if more than one disk in the

stripe set fails

you lose all your data

RAID 10

or

RAID 1+0

mission critical systems

incredible speed and fault tolerance

However

you use more than twice the disk space of RAID 1

MS recomends 5 and 1: 5 for data files and 1 for log files so they can be mirrored

Other RAID

2

3

4

2 uses Hamming code to recover from 2 disk drive failures in the array

3 uses a single check per group

4 supports independent reads and writes

These are much faster and are called Hardware Solutions

actual database record

8192KB

8060 is for data storage

except for variable length, rows don't span more than one page they stay in one

These store

index keys and levels making up the entire index tree

On a single page you can have many entries (unlimited)

contains a 16-Byte pointer

to a linked list of text pages

that holds the actual data

GAM

Global Allocation Map

pages

maps the data pages

what's filled and what's available for filling

IAM

Index

Allocation

Map

PFS

Page

Free

Space

keeps track of free space on the other pages

up to 8000 pages

it does so in

8KB chunks

since SQL server handles memory in 8KB chunks and disk in 64KB chunks BUT

Microsoft OS Windows write to disk I/O in 4KB Chunks,

this may result

"torn pages"

this occurs in page units

However they are held in (cached) memory until

an extent (8 pages) has been reached

Data cache is divided into 8KB buffers

select the DB

in Management Studio

right click

choose reports

this reports the internal DB identifier

Gives detail about your DB

system stored procedures

an example of the detail available about your DB

under DB's properties and options

this will let you choose the language and whether it is case sensitive

log records almost nothing

from last back up to present

used for simple DBs like OLAP

SELECT INTO

BCP

BULK INSERT

only bulk operations may be lost

you set this option just before doing Bulk Insert

You need to do back up just before doing Bulk Insert

80 means SQL Server 2000

90 means 2005

100 means 2008

None

Checksum

Torn_Page_Detection

Read-Only (true or false)

State:

OFFLINE

NORMAL

EMERGENCY

DB becomes read-only

logging is disabled

sysadmin fixer server role has limited access

Used for troubleshooting purposes

Only set by sysadmin fixer server role

• MULTI_USER

• SINGLE_USER (one user at a time with a single connection)

• RESTRICTED_USER

restricts access to:

db_owner

db_creator

sysadmin security roles