Da Notes - 70-028 - L

Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender.

Passing Score: 680, Questions: 52,  Time: 90 Minutes

Da Notes Index 

[ 70-028 - B ] [ 70-028 - C ] [ 70-028 - D ] [ 70-028 - F ] [ 70-028 - H ] [ 70-028 - I ] [ 70-028 - L ] [ 70-028 - M ] [ 70-028 - N ] [ 70-028 - P ] [ 70-028 - R ] [ 70-028 - S ] [ 70-028 - U ] [ 70-028 - W ]

License

Per-server: Numbers of Licenses on server (N licenses = N connections). Very confusing. Most use the per-seat license.

Per-seat: License for each computer

Internet Connector: License for using SQL Server through IIS or MS Transaction Server

Linked Servers

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers.  Replaces remote servers (SQL 6.5). 

Advantages:

Remote server access.

The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

Freedom from the need to address diverse data sources differently.

Security: 
Local SQL server logs on to linked (remote) server on behalf of user. 

If login (and password, depending on security mode) exist on both machines, existing accounts used. 

Login IDs and passwords may be mapped between local and linked servers using sp_addlinkedsrvlogin.

Many-to-one mappings can exist. 

Locking

SQL Server™ uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time

Types of Locking 
Shared Allows concurrent transactions to read (SELECT) on datas, not modify

Exclusive It prevents others from viewing or modifying datas

Update It prevents others from modifying datas during update

Intent Indicates that SQL Server wants to acquire a shared or exclusive lock on some of the resources 'lower' down in the hierarchy

Lock Types
RID Row ID: Used to lock a single row within a table.

Key: Row lock within an index.

Page: 8 kilobyte (KB) data page or index page.

Extent: Contiguous group of eight data pages or index pages.

Table: Entire table, including all data and indexes.