Publish / Subscribe Publish / Subscribe Database Log Shipping over Database Log Shipping over

Bittorent P2PBittorent P2PCS 848 CS 848

Fall 2006 Fall 2006

Univeristy of WaterlooUniveristy of Waterloo

Project Presentation by N. TProject Presentation by N. T cc hh ee rr vv ee nn ss kk ii

IntroIntro

Implemented a tool to facilitate publish / Implemented a tool to facilitate publish / subscribe of databases. subscribe of databases.

Technologies used:Technologies used: Log shippingLog shipping BittorrentBittorrent RSSRSS

MotivationMotivation

Looking for an easy and quick way to create Looking for an easy and quick way to create read-only replicated databases using minimum read-only replicated databases using minimum new infrastructure and minimum overheadnew infrastructure and minimum overhead

Instead of keeping a standby replica, can use it Instead of keeping a standby replica, can use it for queriesfor queries

Log shipping can be performed on many of the Log shipping can be performed on many of the popular DB systems – DB2, Oracle, MS SQL popular DB systems – DB2, Oracle, MS SQL Server, Postgres, Teradata, etc.Server, Postgres, Teradata, etc.

Transferring large amounts of data can be done Transferring large amounts of data can be done using P2P like Bittorrentusing P2P like Bittorrent

ArchitectureArchitecture

DB Server Archived Logs & backup images

Archived logs directory

Publishing Tool

DB Replica Archived logs directory

Db restore and rollforward

RSS Feeds of tracker data BitTorrent seeder

Subscription Tool

BitTorrent client

RSS client

Internet

DB Log management

tool

commands

FeaturesFeatures

Minimum impact on the serverMinimum impact on the server No need to capture dataNo need to capture data Can be part of regular backup / replication processCan be part of regular backup / replication process Can send data to as many or as few peers as neededCan send data to as many or as few peers as needed

Log shipping is popular – existing scripts and Log shipping is popular – existing scripts and infrastructure can be reusedinfrastructure can be reused

Sharing through Bittorrent is flexible – can limit Sharing through Bittorrent is flexible – can limit upload speed, number of connections, disable upload speed, number of connections, disable IPs, etc.IPs, etc.

Current LimitationsCurrent Limitations Database backups are not cross-platform / cross-database-version Database backups are not cross-platform / cross-database-version

portableportable Moving the whole database, rather than just the dataMoving the whole database, rather than just the data

need similarly configured machines (access control, paths, etc. )need similarly configured machines (access control, paths, etc. ) Delay when bringing up the database up after rollforward ( index Delay when bringing up the database up after rollforward ( index

rebuilding, etc. ). To include new logs, need to rollback and then rebuilding, etc. ). To include new logs, need to rollback and then rollforward again – this cannot be done too often.rollforward again – this cannot be done too often. Not suitable for databases with lots of updatesNot suitable for databases with lots of updates

When LOAD is done (DB2), tablespace backup needs to be When LOAD is done (DB2), tablespace backup needs to be provided or data location be available to the remote DBprovided or data location be available to the remote DB

SecuritySecurity Authorization to downloadAuthorization to download Bittorrent transfers can be slowed down by malicious peers sending Bittorrent transfers can be slowed down by malicious peers sending

garbage datagarbage data

Related WorkRelated Work

DPROPR - IBM DataPropagator DPROPR - IBM DataPropagator Relational Relational Clients subscribe to particular rows / columns Clients subscribe to particular rows / columns

of tablesof tables Can receive full refresh or just updatesCan receive full refresh or just updates For updates only mode, capture control tables For updates only mode, capture control tables

are usedare used

TestingTesting

Testing and implementation is done usingTesting and implementation is done using DB2 V9DB2 V9 Linux – UbuntuLinux – Ubuntu Bittorrent client – Enchanced CTorrentBittorrent client – Enchanced CTorrent

ConclusionConclusion

Based on gluing together existing Based on gluing together existing technologiestechnologies

A way to use standby replicaA way to use standby replica Legitimate use of BitTorrent Legitimate use of BitTorrent Hope this will stir more related researchHope this will stir more related research Ideal for public databasesIdeal for public databases

ReferencesReferences

[1] DPROPR Planning and Design Guide, [1] DPROPR Planning and Design Guide, http://www.redbooks.ibm.com/abstracts/sg24477http://www.redbooks.ibm.com/abstracts/sg244771.html1.html

DB2 Replication Guide and Reference, DB2 Replication Guide and Reference, ftp://ftp.software.ibm.com/ps/products/db2/info/vrftp://ftp.software.ibm.com/ps/products/db2/info/vr82/pdf/en_US/db2e0e82.pdf82/pdf/en_US/db2e0e82.pdf

Warm Standby Servers for High Availability, Warm Standby Servers for High Availability, http://www.postgresql.org/docs/8.2/static/warm-http://www.postgresql.org/docs/8.2/static/warm-standby.htmlstandby.html