SQL Server 2014: Transactional Replication (incl. Peer-to-peer replication)

Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:

  • You want incremental changes to be propagated to Subscribers as they occur.
  • The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
  • The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
  • The Publisher has a very high volume of insert, update, and delete activity.
  • The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

Publication Types for Transactional Replication

  • Standard transactional publication

    Appropriate for topologies in which all data at the Subscriber is read-only (transactional replication does not enforce this at the Subscriber).

  • Transactional publication that supports updating subscriptions in a hierarchical topology
  • Transactional publication in a peer-to-peer topology

    • Each location has identical data and acts as both a Publisher and Subscriber.
    • The same row can be changed only at one location at a time.
    • This topology is best suited for server environments requiring high availability and read scalability.

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

Notes:

  • Peer-to-peer replication is available only in Enterprise versions of SQL Server.
  • A publication must be enabled for peer-to-peer replication before any subscriptions are created.
  • Subscriptions must be initialized by using a backup or with the 'replication support only' option. That means Initialization and reinitialization cannot be done with a snapshot.
  • If you add a new node to a peer-to-peer topology, you should restore only from backups that were created after the new node was added.

Sources:
Transactional Replication
Publication Types for Transactional Replication
Peer-to-Peer Transactional Replication

Keywords:
Replication, Sharding, Redundancy, Disconnected Data, SQL Server, SQL Server 2014, Microsoft

Comments

Post a Comment

Popular posts from this blog

SQL Server Setup: Windows Firewall warning (Ports)

Image
What was it again SQL server setup throws out, when you have your Windows Firewall on?! Rule "Windows Firewall" generated a warning. The Windows Firewall is enabled. Make sure the appropriate ports are open to enable remote access. See the rules documentation at http://go.microsoft.com/fwlink/?LinkId=94001 for information about ports to open for each feature. Keywords: Setup, Firewall, Ports, SQL Server, Microsoft
Read more

SQL Server 2014 Enterprise Edition: Server Setup: Feature Selection

Note: Still needs Microsoft .NET Framework 3.5. Instance Features The configuration and operation of each instance feature of a SQL Server instance is isolated from other SQL Server instances. SQL Server instances can operate side-by-side on the same computer. Database Engine Services Includes the Database Engine, the core service for storing, processing and securing data. The Database Engine provides controlled access and rapid transaction processing and also provides rich support for sustaining high availability. The Database Engine also provides support for the utility control point in the SQL Server Utility. Only Database Engine Services and Analysis Services can be clustered. SQL Server Replication Includes a set of technologies for copying and distributing data and database objects from one database to another and synchronizing between the databases for consistency. You can use replication to distribute data to different locations, and to remote a
Read more

How to read an assembly.dll.config

There is the common misconception that only a app.exe.config can be read in .NET C#. The truth is that the app.config is automatically read, while any other (correctly formed) app.config XML file can be read "manually". To open a named config file: Reference System.Configuration.dll assembly Using System.Configuration Create code like: Configuration GetDllConfiguration(Assembly targetAsm) { var configFile = targetAsm.Location + ".config"; var map = new ExeConfigurationFileMap { ExeConfigFilename = configFile }; return ConfigurationManager.OpenMappedExeConfiguration(map, ConfigurationUserLevel.None); } Sources: stackoverflow: Reading dll.config (not app.config!) from a plugin module Keywords: .NET, C#, app.config, dll.config, ConfigurationManager
Read more