Distributed Database Management Systems Lecture 21 In the

Distributed Database Management Systems

Lecture # 21

In the previous lectures Fragmentation: One of the possible way we can implement a DDBS

Types of Fragmentation • Horizontal Fragmentation • Vertical Fragmentation

Types of Horizontal Fragmentation 1. 2. Primary Horizontal Fragmentation Derived Horizontal Fragmentation

Vertical Fragmentation is relatively more complex. Why ?

In today’s Lecture Replication

Storing a separate copy of database at each of two or three or more sites

• Advantages of Replication –Reliability. –Fast response. –May avoid complicated distributed transaction integrity routines (if replicated data is refreshed at scheduled intervals. )

–De-couples nodes (transactions proceed even if some nodes are down. ) –Reduced network traffic at prime time (if updates can be delayed. )

• Disadvantages of Replication –Additional requirements for storage space. –Additional time for update operations.

–Complexity and cost of updating. –Integrity exposure of getting incorrect data if replicated data is not updated simultaneously. • Therefore, better when used for non-volatile data.

Replication Architecture in SQL Server

Replication uses a publish-subscribe model for distributing data

Publication A Publication is group of related data and objects that we want to replicate together.

Publisher A Publisher is a server that is the source of data to be replicated.

Subscriber A Subscriber is a server that receives the data replicated by the publisher.

• The Subscriber defines a subscription to a particular publication

• A Distributor is a server that performs various tasks when moving articles from Publishers to Subscribers. • The actual tasks performed depend on the type of replication performed.

SQL Server Replication Agents are the software components used in replication

Types Of SQL Server Replication Agents • Snapshot Agent • Merge Agent • Distribution Agent • Log Reader Agent

• Snapshot Agent: Runs at least once in all replicas • Distribution Agent: performs different activities, mainly distributes publication to subscribers • Log reader Agent: used in transactional rep, reads log files of all servers involved • Merge Agent: Meld changes from different servers made since last snapshot

Push/Pull subscriptions; depends where the distributor is running.

Replication Models

Publ/Dist Sever Central Publisher / Distributor

Remote Distr Publisher Central Publisher / Remote Distributor

Replication Methods • Snapshot Replication • Transaction Replication. • Merge Replication.

Snapshot Replication

• Preferred when subscribers need readonly access • Higher latency (working without updated data) low Bandwidth

• Used in all types, initially • Can be push or pull • How to do it.

Summary