Replication in SQL Server

[Two kinds of replication ]：

• Eager Replication：also known as synchronous replication
  
  
  Detail description as follow：The synchronous nature of data transfer in eager replication facilitates real-time data transfer, and in this situation it would be useful to use eager replication. However, eager replication is not a good choice for a remote or mobile environment, since it reduces update performance. Also, in a mobile environment the nodes are not always connected. It is also not supported in SQL Server 2008.
  
  
• Lazy Replication：also known as asynchronous replication
  
  
  Detail description as follow：With this type of replication, it is possible for two different sites to update the same data on the same destination site. This will lead to a conflict in the updating of the data. Such update conflicts need to be resolved in lazy replication, and this is done by associating timestamps with each of the transaction objects. Each object carries the timestamp associated with the previous update of that data. So when a transaction is sent to the destination site, it first checks to see whether the timestamp associated with the local copy of the replicated data matches the incoming transaction’s old timestamp for that data. Only if they match will the changes, including the transaction’s new timestamp, be applied. If the timestamps do not match at the initial stage, the updated transaction is rejected. SQL Server has a conflict-resolution viewer that deals with updates, inserts, and deletes.
  
  

[Replication in SQL Server]：

• SQL Server follows asynchronous (lazy) replication. It permits three different kinds of asynchronous replication: snapshot, transactional, and merge replication.
• Autonomy and data latency from lowest to highest are 1. distributed transactions、2. transactional replication、3. snapshot replication、4. merge replication.
• In case of snapshot replication, all tables to be published share a lock, so you cannot make any changes during the snapshot generation. In transactional replication there are no shared locks in place during the snapshot generation, so you can work unhindered while replication is generating the initial snapshot files in the background. This process, known as concurrent snapshot processing, is the default for transactional replication.
• Concurrent snapshot of transactional replication processing allows users to make changes while a snapshot is being generated. The Snapshot Agent places a lock on the publication tables at the start of the synchronization process, and an entry is made in the publication database’s transaction log. Until that time, the lock is held in place, but as soon as the entry is made in the log, the lock is released and you can start making data modifications again. Once the snapshot is completed, a second entry is made in the transaction log to mark the completion of the process. Any changes associated with the tables during the process are captured, and the Log Reader Agent forwards these transactions to the distribution database. It is worth pointing out at this stage that backups of replicated databases must be restored on the same server. Otherwise you will lose the replication settings and will have to re-create publications and subscriptions once the backup has been restored.