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.


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


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


import xml using openrowset

SSIS, import of table from diff server


Maintaining databases


Index Fragmentation/statistics/shrink files/dbcc checkdb

Backup database


format option

append/overwrite existing media

device/backup filegroup



Profiler / trace/


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