Exam

Installing/Upgrade and configure SQL 2005

Security : Principals/securables/permission/encryption

Linked Server : security model of data source

=========

Database Mirroring

create endpoints/database partners/witness sever/operating mode

==

Database Mirroring.

Full recovery model for the database

As soon as transaction is done on primary it is loaded into the secondary

Operation Modes.

High Availability

Requires witness.

synchrones

Transaction commit is first done at mirror and then the principal

High Protection.

Transaction commit is first done at mirror and then the principal

No witness

High Performance

Asynchronous.

After commit on principal it is sent to mirror

no witness

==

Logshipping /monitoring

Database Snapshot, reverting from snapshot

SQL Select queries/ formatting / collation details

Handle exeptions and errors

Manage XML data

retrive/modify/convert/indexing/load xml schema

HTTP Endpoint

Implement sever broker.

receive msg from service

create service/queue/contract/conversation/message types

send msg to service/route a msg

bulk logged

bcp

import xml using openrowset

SSIS, import of table from diff server

Replication

Maintaining databases

jobs/operators

Index Fragmentation/statistics/shrink files/dbcc checkdb

Backup database

full/diff/tran

format option

append/overwrite existing media

device/backup filegroup

=======

Performance

Profiler / trace/

DETA

build workload file using profiler

save recommendation index

Block and deadblok

sys.dm_exec_requests system view

terminate process

identify transaction block.

==

connect to non-responsive server using dac

sql sever startup logs

event logs

SQL Server agent history

gather per/opt using dmv