MS-SQL Server and database replication

so … to get what this topic’s all about, think of your public library. you’ve visited the library, right? it has multiple locations, right? ok … i’m gonna assume affirmative … assume you’re not a complete neanderthal. anyyyyywayyyy … think library. and searching for your favourite book or DVD or whatever. how’d they do it? how’d they keep it all sync’d? i mean … all those books … spread across town in all those different book depositories we call libraries. how’d they keep track of what books comes … what books go … where a book’s located?

database … and … data REPLICATION. that’s how it happens. it can take place in virtual real time or in static time, and updating of replicated data can occur immediately or in a queued process. so … let’s get to the official-type stuff. WTF is replication? essentially, its the process of distributing a database’s data and database objects to other database engines that exist across an organization or enterprise. clearly, the benefits seem obvious: (1) flexibility with respect to data manipulation, sharing and collaboration; (2) concurrent use of data~which likely leads to increased efficiency, scope, productivity of said enterprise or organization; (3) facilitating ease of mobile users.

how does replication work? to understand this … we must understand the paradigm used to discuss replication theory: the publisher~ distributor~ subscriber paradigm. publisher refers to the source server … subscriber refers to the receiving server … distributor refers to the system database, which can reside on its own, on the subscriber side or on the publisher side. one-to-one OR one-to-many can describe the relationship between the publisher and subscriber.

check out the three replication models. (1) snapshot – just like it sounds ~ a snapshot of data @ a particular moment in time; no updates; (2) transactional – real-time; transactions stored on distributor and send to subscriber; can replicate entire table or filter; (3) merge – most complex; offers most flexibility and autonomy; connection not required for update … just to merge update data (for syncing dbase). associated with conflicts.

so … how to choose which model? how to tackle replication? well, start by considering the following: does the database have a compound primary key? do we know the when~what~where of rec’d publications? what level of transactional consistency do we require? what about latency? i.e. how long does updated data wait to get replicated/received by subscribers? latency decreases network speed and increases congestion … so less is more. and, then, what about site autonomy? i.e. do sites cascade from one another? or do they exist reasonably independently of one another? and what of security? system/server performance? how will data get updated? is there a schedule? what of data source(s) availability? and the subscriber’s capacity to update data? does the replication or update require a 2PC process? if so … preparation and commitment of servers must be anticipated (to avoid data conflicts, transaction inconsistencies).