MS SQL Basics Tutorial: Overview and terminology for replication

For a geographically dispersed large enterprise organization, it is always necessary to solve a difficult problem when building a large enterprise management information system with typical distributed computing features; How to ensure the integrity, security, and availability of shared data among multiple different database servers. The problem is that business organizations have such data processing and requirements: modifying local databases with the same structure at different locations, but ensuring that the modified database has the same results. The essence of this is that the changes to the local database are reflected in other remote databases with the same structure.

So how do we achieve consistency in this data? There may be many answers, but most database products, including SQL Server, use a replication technology to address this problem. The purpose of this chapter is to introduce replication technology for SQL Server. Let's start with the overview of replication.

SQL Server provides built-in replication capabilities, and replication components are not add-on products but part of the core engine. With the help of replicating this important technology that supports distributed data processing capabilities, we can maintain multiple copies of data on different database servers across LANs, WANs, or the Internet, automatically synchronizing or asynchronously ensuring data consistency across multiple copies of data. In essence, replication is the copying of data from one source database to multiple target databases.

16.1.1 replication model for SQL Server

SQL Server uses the term publish and order to describe its replication activities. Publishing is the copying of data to other database servers (subscribers). Ordering is the receipt of replicated data from another server (publisher). Although publications and subscriptions are intended to replicate data, publishing and ordering are not the same data operations (copying data) of different angles (publishers and subscriptions), but rather reflect a certain level and order (always publish first and then order). The replication components of SQL Server are Publishers, Subscribers, distributors, publications and papers, push orders, and pull subscriptions.