TencentDB for SQL Server supports Microsoft SQL Server's native publish-subscribe replication feature. Users can self-create, modify, and delete publishers and subscribers on the TencentDB for SQL Server console, meeting the needs for data replication and synchronization.
Note
TencentDB for SQL Server single-node (formerly Basic Edition) does not support publish-subscribe.
Concepts
SQL Server uses publishing industry terminology to represent components in a replication topology, including publisher, distributor, subscriber, publication, item, and subscription. The concept of magazines can help understand Microsoft SQL Server replication:
A magazine publisher (publisher) produces one or more publications (publications)
A publication (publication) contains articles (items)
A publisher (publisher) can distribute magazines directly or use a distributor (distributor)
"Replication topology" defines the relationship between servers and data replicas and explains the logic that decides how data flows between servers. Several replication processes (called "agents") are responsible for copying and moving data between publishers and subscribers. The following diagram shows the components and processes involved in replication.
Publisher
A publisher is a database instance that provides data to other locations through replication. A publisher can have one or more publications, each defining a set of logically related objects and data to be replicated.
Distributor
A distributor is also a database instance that acts as a storage area for replication-specific data associated with one or more publishers. Each publisher is associated with a single database in the distributor (called the distribution database).
The distribution database stores replication status data and metadata about publications and, in some cases, queues data moving from the publisher to the subscriber.
In many cases, a single database server instance acts as both the publisher and the distributor, which is referred to as a "local distribution server". When the publisher and the distributor are configured as separate database server instances, the distributor is called a "remote distribution server".
Subscription Server
A subscription server is a database instance that receives replicated data. It can receive data from multiple publishing servers. Depending on the replication type selected, it can also propagate data changes back to the publishing server or re-publish data to other subscription servers.
Item
An item identifies a database object included in the publication. A publication can include different types of items.
Release
A publication is a collection of one or more items from a database. Grouping multiple items into a publication makes it easier to specify a logically related set of database objects and data to be replicated as a unit.
Subscription
A subscription is a request to copy the publication to the subscription server. The subscription definition includes the publication to receive and the time and place to receive it.
Feature Overview
1. TencentDB for SQL Server publication/subscription uses transaction replication by default.
Transaction replication usually begins with a snapshot of the published database objects and data. After creating the initial snapshot, subsequent data changes and schema modifications made on the publishing server are propagated to the subscription server almost in real-time. Data changes are applied to the subscription server in the order they occur on the publishing server, ensuring transactional consistency within a publication.
Transaction replication is typically used in server-to-server environments and is appropriate in the following scenarios:
When incremental changes need to be propagated to the subscription server.
When an application requires short lag time between changes made on the publishing server and those changes reaching the subscription server.
When an application needs to access the intermediate data state. For example, if a row changes five times, transaction replication allows the application to respond to each change (triggering triggers, etc.) instead of just responding to the final data change of that row.
When the publishing server has a high volume of insert, update, and delete activities.
2. TencentDB for SQL Server publication/subscription configuration uses a "Remote Distribution Server" to ensure automatic recovery of the publication subscription linkage after a publishing server failover, without affecting the subscription server. However, should the subscription server fail, the linkage needs to be manually repaired.
Feature Limits
This feature is available only when both the publishing and subscribing instances are TencentDB for SQL Server instances.
A read-only instance cannot be used as a publishing or subscribing server.
The publishing and subscribing instances must belong to the same version and be in the same region (but they can be in different AZs).
Data tables without a primary key cannot be subscribed to. You can use the following code to check whether the database to be published contains such tables.
use dbname
select name from sys.sysobjects where xtype='U' and id not in(select parent_obj from sys.sysobjects where xtype='PK')
If the publishing and subscribing instances have a database with the same name, the database cannot be subscribed to.
After a publish/subscribe linkage is created, if a database in the linkage is deleted, the linkage will also be deleted.
If either the publishing or subscribing instance is terminated, the publish/subscribe linkage will also be deleted.
