Cơ sở dữ liệu - Chapter 6: Data replication

Chapter 6Data ReplicationOverview of ReplicationSQL Server replication is a set of technologies for:copying and distributing data and database objects from one database to another.Then, synchronizing between databases to maintain consistency.Types of replication: Snapshot replication. Transactional replicationMerge replicationPeer – to – peer replicationOracle Publishing replicationAll of these types rely on a number of standalone programs – agents.Replication Scenario Replication is analogous to the publishing industryArticle: a database object that you want to replicate; it can be a Table, View, or Store procedure, or other objects.Publication: A collection of articles.One article can be in one or more publications.Publication can be treated as a logical grouping for the articlesFor example: To replicate only the employee master data, you can include employee table article, address article, and any other relevant views to the publicationReplication Scenario Subscription:A request for a copy from the publication. Define where to receive the publication, what the frequency for the receiving is, PublisherA database instance that holds publications for other database instances to receive. There can be several different types of publication in one publisher.Replication Scenario Distributor:Each publisher has a distributor to store replication status data, and metadata about the publication. Sometimes the distributor acts as a data queue for data moving from the publisher to the subscribers and vice versa. There are two types of distributors:local and remote. Local distributor is where the publisher and distributor are in one database server instance.Subscriber:A database instance that receives replicated data from the publisher. In some replication types the subscriber also passes data to the publisher.Replication TypesSnapshot:Distribute data as it appears in the publisher at a specific moment of time. Whenever the Subscriber called for subscription, it will send the snapshot of the current Publisher and send it to the Subscribers.Limitations for using Snapshot Replication:Replicating a small volume of data. Infrequently changing dataSnapshot replication does not require primary article tablesReplication TypesComponents in Snapshot Replication:Two agents are used in Snapshot Replication, snapshot and distribution.Snapshot Agent:A shared agent for all replication types.The snapshot agent’s job is to generate data scripts for the objects you are replicating. These scripts are written to a folder in the file system called a snapshot folder.Distribution Agent:This agent will read from a snapshot folder and distribution database to propagate to the subscriber end.Replication TypesTransactional:The most used replication typeThe most valuable feature of the transactional replication is the ability to replicate incremental changes rather than applying the all-data set.Use transactional log to generate transactions for replication.Here are the scenarios where you can use transactional replication:Replicate huge volumes of dataFor real-time applicationReplicate data between non-SQL Server databasesReplication TypesTransactional:The most used replication typeThe most valuable feature of the transactional replication is the ability to replicate incremental changes rather than applying the all-data set.Use transactional log to generate transactions for replication.Here are the scenarios where you can use transactional replication:Replicate huge volumes of dataFor real-time applicationReplicate data between non-SQL Server databasesReplication TypesPeer-to-Peer ReplicationPeer-to-peer replication is an option with transactional replicationAll nodes in a peer-to-peer replication topology subscribe and publish from and to all other nodes. A transaction originating at one node will be replicated to all other nodes, but not replicated back to originator. This replication model is intended for use in applications to have multiple databases or database servers participating in a scale-out solution. Replication TypesMerge ReplicationStart from the initial snapshot. Afterward, changes at both publisher and subscriber(s) are tracked with triggers. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.