SQL Server Replication - SQL Server 2008

SQL Server version 6.0 introduced replication as a built-in feature, and each version since then has made significant improvements or additions to the base functionality. SQL Server 2008 introduces major improvements to conflict detection in peer-to-peer replication (something that has been requested for a long time) and adds graphic replication management capabilities for the administrator.

At a very basic level, replication exists to move data and database objects between servers or between clients and servers using a wide variety of connection types, including the Internet. SQL Server Compact Edition plays an integral part when communicating with devices such as handheld scanners, Pocket PCs, and laptops that may not require, or are not capable of running, a full version of SQL Server 2008.

SQL Server implements three kinds of replication; snapshot replication, merge replication, and transaction replication. For all three types, SQL Server uses a publishing metaphor in which publishers, subscribers, distributors, publications, subscriptions, and articles all work as a coordinated unit to accomplish replication. Figure 4-12 illustrates the components of replication.

Components of replication architecture

Components of replication architecture

Snapshot Replication

Snapshot replication distributes a point-in-time copy of the articles within the publication. Although snapshot replication is sometimes used by itself, it is most often used to initialize a subscriber for merge or transaction replication. It is important to consider the overall size of the dataset to be replicated as part of the snapshot, as the generation of the snapshot could require substantial resources on the publisher, and the application of the snapshot could require substantial resources on the subscriber.

Merge Replication

Merge replication is most often utilized in situations where data and objects are being synchronized between clients and servers that are not always in a connected state. Such is the case with sales force automation systems, point-of-sale systems, and field automation systems.

Merge replication allows many sites/devices to work independently while either connected or disconnected from the network. When the site/device is reconnected to the network, changes are synchronized between the publisher and subscriber. Consider an inventory tracking system within a large warehouse where radio frequency identification (RFID) scanners and barcode readers are used to track and count inventory. Many of these devices may be continually connected to a network; however, it is quite possible that some of these devices will maintain a local data store using SQL Server Compact Edition and are only periodically connected to the network, at which time changes are synchronized with the publisher.

Traditionally, merge replication has allowed advanced conflict detection and processing. This is changing some what with SQL Server 2008, as advanced conflict detection can now be programmed within peer-to-peer transaction replication.

Transaction Replication

Transaction replication is typically used when replicating data between servers. Transaction replication is also the preferred method for replicating large amounts of data or when very low latency between the publisher and subscriber is required. Transaction replication is capable of functioning when the publisher or subscriber is a non-SQL Server database, such as Oracle.

Transaction replication can be configured with a number of different publication types, one of which is peer-to-peer transactional replication. In peer-to-peer replication, data can be maintained across a number of servers, or nodes, within the replication topology. Although transaction replication is typically used to scale out read-intensive applications, it is also possible for updates to take place within each node in the topology.

Traditionally, it has been very important to avoid conflicts for peer-to-peer replication by ensuring that the same record could not be updated on many nodes at the same time. Although it is still important to ensure that conflicts don’t occur, SQL Server 2008 introduces the ability to perform conflict detection within a peer-to-peer topology. When a conflict is detected within the topology, the Distribution Agent will report a critical error, and the conflict will need to be handled manually before the topology returns to a consistent state. Although not ideal, this solution does provide an extra layer of protection by ensuring that transactions are not lost as a result of conflicts within a peer-to-peer topology.

A new Peer-to-Peer Topology Wizard makes the configuration of peer-to-peer topologies ridiculously easy. Once an initial publication is created on one of the nodes within the topology, you can use the wizard to graphically configure the remaining nodes within the topology.

Peer-to-Peer Topology Wizard

Peer-to-Peer Topology Wizard

Replication and Mirroring

There may be times when replication will make more sense than database mirroring for your data availability needs. For example, if you want a scale-out, read-only reporting solution, peer-to-peer replication is a much better solution than mirroring, since replication can support many peers in its topology. With mirroring, you can have only a single mirror. However, replication does not natively support failover transparently, as mirroring does. You must write your applications in such a way to understand failures in a replicated environment. Plus, replication is at the Data Manipulation Language (DML) level, while mirroring is at the log level, so you may find that mirroring has better performance in certain scenarios.

That said, you can combine mirroring and replication. You could replicate the principal using replication or even log shipping. However, this introduces a bunch of complexities that you may not want to deal with. For example, if the principal fails over and then comes back up as the mirror, replication will break, since the database will not be readable.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics