CN110222121A - A kind of SQL Server database increment synchronization realization method and system based on CDC mode - Google Patents
A kind of SQL Server database increment synchronization realization method and system based on CDC mode Download PDFInfo
- Publication number
- CN110222121A CN110222121A CN201910514708.1A CN201910514708A CN110222121A CN 110222121 A CN110222121 A CN 110222121A CN 201910514708 A CN201910514708 A CN 201910514708A CN 110222121 A CN110222121 A CN 110222121A
- Authority
- CN
- China
- Prior art keywords
- data
- cmsp
- sql server
- database
- cdc
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 25
- 238000000605 extraction Methods 0.000 claims abstract description 22
- 230000001360 synchronised effect Effects 0.000 claims abstract description 14
- 230000005540 biological transmission Effects 0.000 claims abstract description 12
- 230000008859 change Effects 0.000 claims description 14
- 239000000284 extract Substances 0.000 claims description 8
- 238000013075 data extraction Methods 0.000 claims description 6
- 230000007246 mechanism Effects 0.000 claims description 6
- 230000008569 process Effects 0.000 claims description 5
- 238000009434 installation Methods 0.000 claims description 4
- 238000012360 testing method Methods 0.000 description 3
- 230000008901 benefit Effects 0.000 description 2
- 238000013481 data capture Methods 0.000 description 2
- 238000011161 development Methods 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 238000003780 insertion Methods 0.000 description 2
- 230000037431 insertion Effects 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000009471 action Effects 0.000 description 1
- 238000013473 artificial intelligence Methods 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000001419 dependent effect Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 230000010354 integration Effects 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 238000012545 processing Methods 0.000 description 1
- 230000008439 repair process Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 230000009466 transformation Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F16/275—Synchronous replication
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/60—Software deployment
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Software Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Computing Systems (AREA)
- Data Mining & Analysis (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention provides a kind of SQL Server database increment synchronization realization method and system based on CDC mode, belong to information data field of synchronization, the present invention is by opening SQL Server CDC function, the increment delta data of extraction database table from database, and then CMSP transmission is combined to realize that the incremental data of SQL Server database is extracted and synchronized.
Description
Technical field
The present invention relates to information data simultaneous techniques more particularly to a kind of SQL Server databases based on CDC mode
Increment synchronization realization method and system.
Background technique
With the fast development of modern information technologies, the world has striden into internet+big data era, artificial intelligence technology
Advancing by leaps and bounds is also the result of big data tremendous development in recent years.Information is just changing the thinking, production and life of people deeply
Mode living, the depth integration of data and all trades and professions will generate unprecedented society and commercial value.As the information age
Important component, data are also increasingly paid attention to by numerous enterprises and unit.Meanwhile business data show enormous amount,
It is many kinds of, storage it is single the features such as, this certainly will bring security risk to data.Therefore, the synchronization of data and backup first obtain
It is more and more important.
Existing frequently-used SQL Server incremental data synchronization method has following several:
1, trigger mode
Trigger mode is a kind of more generally existing method of synchronization, and which is established according to the requirement of extraction in source table
Insertion updates, deletes three kinds of triggers, when whenever source, table changes, delta data will be written by corresponding trigger
To Incremental Log table.
The method to update, delete operation when, need support in former table that there are major key fields, otherwise cannot achieve corresponding note
The update of record, delete operation.
2, timestamp mode
When timestamp mode refers to increment extraction, process is extracted by comparing the timestamp word of system time and extraction source table
Which data is the value of section determine to extract.This mode needs to increase on the table of source a timestamp field, updates and repairs in system
When changing table data, while the value of modification time stamp field.SQL Server supports timestamp to automatically update, i.e., table is other
When the data of field change, at the time of the value of timestamp field can be automatically updated into record change.
The insertion of source table can be normally captured using timestamp mode and updates operation, but then incapability is for delete operation
Power needs to combine other mechanism that could complete.
3, full table deletes inserted mode
Full table delete inserted mode refer to extract every time before first delete target table data, when extraction completely newly loads data.It should
Increment extraction is actually equal to full dose and extracted by mode.Little for data volume, the time cost that full dose extracts, which is less than, to be executed
When the algorithm and condition cost of increment extraction, which can be used.
Several methods of synchronization compares as follows above:
CDC (Change Data Capture), i.e. transformation data capture, are very important in DB Backup content
One ring.The advantage and disadvantage of existing way are used for reference, therefore develop the SQL Server incremental data synchronization method based on CDC mode.
Summary of the invention
In order to solve the above technical problems, the invention proposes a kind of, the SQL Server database based on CDC mode increases
Synchronization realizing method is measured, the acquisition that SQL Server database table incremental data may be implemented is synchronous, by means of the efficient of CMSP
Property realize database data Fast synchronization and backup operation.
Major function of the present invention is the acquisition of SQL Server incremental data, that is, realizes the incremental data of SQL Server table
Extraction and synchronization.
Incremental data of the CDC to capture database, when affairs are submitted, database finger daemon can be according to journal change
It monitors and captures incremental data.
The technical scheme is that
A kind of SQL Server database increment synchronization implementation method based on CDC mode, by opening SQL Server
CDC function extracts the increment delta data of database table from database, and then CMSP transmission is combined to realize SQL Server number
It is extracted according to the incremental data in library and synchronous.
SQL Server provides CDC mechanism and is used to capture incremental data, when affairs are submitted, database finger daemon meeting
It monitors according to journal change and captures incremental data；The variation (DML/DDL) of database is all recorded in database journal, CDC
It is namely based on log realization.
Variation is monitored in SQL Server agent process (capture operation) asynchronous scanning log, extracts change information, and
It inserts it into corresponding CDC change table.
In corresponding change table every data can all record a transaction journal start sequence number _ _And operation
Type _ _
Finally, can realize the consistency of source system Yu goal systems data by handling these records.
The present invention is based on the acquisitions that CDC mode carries out SQL Server incremental data.Increment extraction is done when being based on CDC mode
When, can first record one starting when sequence number (_ _), it will record at the end of one at the end of inquiring increment
Sequence number (_ _).When inquiring increment next time, can at the end of last sequence number (_ _) do
Continue to search incremental data for starting sequence number, to guarantee the continued synchronization of incremental data.
In addition, the present invention also provides a kind of, the SQL Server database increment synchronization based on CDC mode realizes system,
Mainly be made of three components: extraction assembly, enters library component at CMSP；
Wherein,
Extraction assembly is realized from SQL Server database and extracts incremental data, places data into CMSP；
CMSP is responsible for the intermediate conveyor of data-message；
Enter library component, data are obtained from CMSP, and inserts data into purpose library.
It is as follows that implementation steps are installed:
1) SQL Server database broker service is opened；
2) SQL Server database CDC function is opened；
3) the CDC function of needing synchronous table is opened；
4) first installation data extraction assembly, enter library component, CMSP；
5) configuration data extraction assembly configuration file, configuration need the source database information acquired, the table information of acquisition, match
Set the CMSP information etc. of transmission；
6) it configures into library component；
7) CMSP is configured；
8) start CMSP, and create Topic information；
9) start into library component, finally start extraction assembly；
10) Synchronization Component can real-time high-efficiency source library data are synchronized to purpose library.
The beneficial effects of the invention are as follows
The reality of SQL Server incremental data is realized in conjunction with CMSP high efficiency of transmission using SQL Server CDC mechanism
When it is synchronous.
The high efficiency of transmission that message is carried out by means of CMSP, greatly improves the efficiency of transmission of data.Only need in Yuan Ku
The acquisition of data can be thus achieved in front end processor server disposition extraction assembly, CMSP.
The present invention enters library component, CMSP in storage server disposition, and the rapidly and efficiently storage of acquisition data can be realized.It borrows
The transmission for helping CMSP demonstrates the performance indicators such as the treatment effeciency of system, the accuracy of processing.
SQL Server database increment may be implemented in conjunction with CMSP high efficiency of transmission using SQL Server CDC mechanism
Real time data synchronization has very inside the business scenario more demanding to application CMSP and SQL Server database increment synchronization
Broad application prospect.
Detailed description of the invention
Fig. 1 is system architecture diagram of the invention.
Specific embodiment
In order to make the object, technical scheme and advantages of the embodiment of the invention clearer, below in conjunction with the embodiment of the present invention
In attached drawing, technical scheme in the embodiment of the invention is clearly and completely described, it is clear that described embodiment is
A part of the embodiment of the present invention, instead of all the embodiments, based on the embodiments of the present invention, those of ordinary skill in the art
Every other embodiment obtained without making creative work, shall fall within the protection scope of the present invention.
The present invention is the SQL Server database increment extraction based on CDC mode, is transmitted in conjunction with CMSP, realizes that data increase
Measure synchronous method.SQL Server provides CDC mechanism for capturing incremental data.The variation (DML/DDL) of database is all remembered
In database journal, CDC is namely based on log realization for record.SQL Server agent process (capture operation) is asynchronous to be swept
Log is retouched, variation is monitored, extracts change information, and insert it into corresponding CDC change table.Every in corresponding change table
Data can all record a transaction journal start sequence number _ _With action type _ _Finally, passing through
The consistency of source system Yu goal systems data can be realized by handling these records.
The present invention is based on the acquisitions that CDC mode carries out SQL Server incremental data.Increment extraction is done when being based on CDC mode
When, can first record one starting when sequence number (_ _), it will record at the end of one at the end of inquiring increment
Sequence number (_ _).When inquiring increment next time, can at the end of last sequence number (_ _) do
Continue to search incremental data for starting sequence number, to guarantee the continued synchronization of incremental data.
Present invention deployment is divided into three components: extraction assembly, enters library component at CMSP
Extraction assembly is realized from SQL Server database and extracts incremental data, places data into CMSP；
CMSP is responsible for the intermediate conveyor of data-message；
Enter library component, obtains data from CMSP, and insert data into purpose library.
Installation is implemented as follows:
1) SQL Server database broker service is opened；
2) SQL Server database CDC function is opened；
3) the CDC function of needing synchronous table is opened；
4) first installation data extraction assembly, enter library component, CMSP；
5) configuration data extraction assembly configuration file, configuration need the source database information acquired, the table information of acquisition, match
Set the CMSP information etc. of transmission；
6) it configures into library component；
7) CMSP is configured；
8) start CMSP, and create Topic information；
9) start into library component, finally start extraction assembly；
10) Synchronization Component can real-time high-efficiency source library data are synchronized to purpose library.
Test result:
The synchronous efficiency of data is dependent on conditions such as data bulk, server performance, network transmission efficiencies, in use process
In, incremental data amount can be synchronized to destination in real time, and it is as follows that local test describes result:
Source database: SQL Server；Former table: data_pk (sno is major key), data_nopk (no major key)
Target database: Oracle；Purpose table: DBO_DATA_PK, DBO_DATA_NOPK
(1) data_pk operates (major key sno), purpose table DBO_DATA_PK
Before autoincrementing operation (source table is identical with purpose table data, and it is sky that CDC, which changes table)
(1) it is inserted into Insert
(2) Update is updated
(3) Delete is deleted
(2) data_nopk operates (no major key)
Before autoincrementing operation (source table is identical with purpose table data, and it is sky that CDC, which changes table)
(1) it is inserted into Insert
(2) Update is updated
(3) Delete is deleted
For the table of no major key, delete operation does not do synchronization.
Test result: by comparison source table and purpose table content it is found that in conjunction with CMSP, the SQL based on CDC mode
Real-time synchronization incremental data may be implemented in Server increment synchronization method.
The foregoing is merely presently preferred embodiments of the present invention, is only used to illustrate the technical scheme of the present invention, and is not intended to limit
Determine protection scope of the present invention.Any modification, equivalent substitution, improvement and etc. done all within the spirits and principles of the present invention,
It is included within the scope of protection of the present invention.
Claims (9)
1. a kind of SQL Server database increment synchronization implementation method based on CDC mode, which is characterized in that
By opening SQL Server CDC function, the increment delta data of database table is extracted from database, and then combine
CMSP transmission realizes that the incremental data of SQL Server database is extracted and synchronized.
2. the method according to claim 1, wherein
SQL Server provides CDC mechanism for capturing incremental data, and when affairs are submitted, database finger daemon can foundation
Journal change is monitored and captures incremental data；The variation of database is all recorded in database journal, and CDC is realized based on log.
3. according to the method described in claim 2, it is characterized in that,
When doing increment extraction based on CDC mode, the sequence number (_ _ $ start_lsn) when starting can be recorded first, when looking into
Sequence number (_ _ $ start_lsn) at the end of will record one at the end of asking increment.
4. according to the method described in claim 3, it is characterized in that,
When inquiring increment next time, can be continued with the sequence number (_ _ start_lsn) at the end of the last time as starting sequence number
Carry out lookup incremental data.
5. according to the method described in claim 4, it is characterized in that,
Work step is as follows:
1) variation is monitored in the asynchronous scanning log of SQL Server agent process, extracts change information, and insert it into corresponding
CDC is changed in table；
2) every data can all record a transaction journal and start sequence number _ _ start_lsn and operation in corresponding change table
Type _ _ $ operation；
3) finally, by handling these consistency for recording realization source systems and goal systems data.
6. a kind of SQL Server database increment synchronization based on CDC mode realizes system, which is characterized in that
Mainly be made of three components: extraction assembly, enters library component at CMSP；
Wherein,
Extraction assembly is realized from SQL Server database and extracts incremental data, places data into CMSP；
CMSP is responsible for the intermediate conveyor of data-message；
Enter library component, data are obtained from CMSP, and inserts data into purpose library.
7. system according to claim 6, which is characterized in that
It is as follows that implementation steps are installed:
(1) SQL Server database broker service is opened；
(2) SQL Server database CDC function is opened；
(3) the CDC function of needing synchronous table is opened；
(4) first installation data extraction assembly, enter library component, CMSP；
(5) configuration data extraction assembly configuration file configures the source database information for needing to acquire, the table information of acquisition, configuration
The CMSP information of transmission；
(6) it configures into library component；
(7) CMSP is configured；
(8) start CMSP, and create Topic information；
(9) start into library component, last log-on data acquisition component；
(10) source library data are synchronized to purpose library in real time by Synchronization Component.
8. system according to claim 7, which is characterized in that
In source library, front end processor server disposition extraction assembly, CMSP are to realize the acquisitions of data.
9. according to claim 7 or 8 or the system, which is characterized in that
Enter library component, CMSP in storage server disposition, to realize the storage of acquisition data.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910514708.1A CN110222121A (en) | 2019-06-14 | 2019-06-14 | A kind of SQL Server database increment synchronization realization method and system based on CDC mode |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910514708.1A CN110222121A (en) | 2019-06-14 | 2019-06-14 | A kind of SQL Server database increment synchronization realization method and system based on CDC mode |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110222121A true CN110222121A (en) | 2019-09-10 |
Family
ID=67817026
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910514708.1A Pending CN110222121A (en) | 2019-06-14 | 2019-06-14 | A kind of SQL Server database increment synchronization realization method and system based on CDC mode |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110222121A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111026760A (en) * | 2019-12-11 | 2020-04-17 | 宏图智能物流股份有限公司 | CDC data acquisition method based on multidimensional service time |
| CN112527799A (en) * | 2020-12-17 | 2021-03-19 | 杭州玳数科技有限公司 | Method for realizing distributed real-time synchronization of SqlServer database based on flink |
| CN112948420A (en) * | 2021-03-05 | 2021-06-11 | 浪潮云信息技术股份公司 | Method for realizing SQL Server increment synchronization |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO1998050868A1 (en) * | 1997-05-02 | 1998-11-12 | Informatica Corporation | Apparatus and method for capturing and propagating changes from an operational database to data marts |
| CN105488187A (en) * | 2015-12-02 | 2016-04-13 | 北京四达时代软件技术股份有限公司 | Method and device for extracting multi-source heterogeneous data increment |
| CN105975502A (en) * | 2016-04-25 | 2016-09-28 | 南京优测信息科技有限公司 | Method for realizing incremental data extract based on CDC (Change Data Capture) mode |
| CN109739931A (en) * | 2018-12-21 | 2019-05-10 | 浪潮软件股份有限公司 | A kind of increment synchronization method of the MySQLBinlog log parsing based on CMSP |
-
2019
- 2019-06-14 CN CN201910514708.1A patent/CN110222121A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO1998050868A1 (en) * | 1997-05-02 | 1998-11-12 | Informatica Corporation | Apparatus and method for capturing and propagating changes from an operational database to data marts |
| CN105488187A (en) * | 2015-12-02 | 2016-04-13 | 北京四达时代软件技术股份有限公司 | Method and device for extracting multi-source heterogeneous data increment |
| CN105975502A (en) * | 2016-04-25 | 2016-09-28 | 南京优测信息科技有限公司 | Method for realizing incremental data extract based on CDC (Change Data Capture) mode |
| CN109739931A (en) * | 2018-12-21 | 2019-05-10 | 浪潮软件股份有限公司 | A kind of increment synchronization method of the MySQLBinlog log parsing based on CMSP |
Non-Patent Citations (2)
| Title |
|---|
| 戴浩等: "ETL中的数据增量抽取机制研究", 《计算机工程与设计》 * |
| 王玉标等: "异构环境下数据库增量同步更新机制", 《计算机工程与设计》 * |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111026760A (en) * | 2019-12-11 | 2020-04-17 | 宏图智能物流股份有限公司 | CDC data acquisition method based on multidimensional service time |
| CN112527799A (en) * | 2020-12-17 | 2021-03-19 | 杭州玳数科技有限公司 | Method for realizing distributed real-time synchronization of SqlServer database based on flink |
| CN112527799B (en) * | 2020-12-17 | 2022-09-13 | 杭州玳数科技有限公司 | Method for realizing distributed real-time synchronization of SqlServer database based on flink |
| CN112948420A (en) * | 2021-03-05 | 2021-06-11 | 浪潮云信息技术股份公司 | Method for realizing SQL Server increment synchronization |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| KR101917806B1 (en) | Synchronization Error Detection AND Replication Method of Database Replication System Using SQL Packet Analysis | |
| CN110347746B (en) | Heterogeneous database synchronous data consistency checking method and device | |
| CN110222121A (en) | A kind of SQL Server database increment synchronization realization method and system based on CDC mode | |
| CN110879813B (en) | Binary log analysis-based MySQL database increment synchronization implementation method | |
| Garfinkel et al. | A general strategy for differential forensic analysis | |
| CN110175213A (en) | A kind of oracle database synchronization system and method based on SCN mode | |
| TWI521363B (en) | Method, device and system for implementing incremental data extraction | |
| CN104050276B (en) | The method for caching and processing and system of a kind of distributed data base | |
| CN112685433B (en) | Metadata updating method and device, electronic equipment and computer-readable storage medium | |
| CN110502490A (en) | A kind of MongoDB database in phase system and implementation method | |
| CN111625552B (en) | Data collection method, device, equipment and readable storage medium | |
| CN105512284A (en) | MySQL data protection method based on affair form data and binlog file | |
| CN112835918A (en) | MySQL database increment synchronization implementation method | |
| CN112231407A (en) | DDL synchronization method, device, equipment and medium of PostgreSQL database | |
| CN111897813A (en) | Flow control method and device for database resources | |
| CN116975159B (en) | Incremental data synchronization processing method | |
| US20180240053A1 (en) | System and Method for Associating a Multi-segment Component Transaction | |
| CN109669988B (en) | Splitting and synchronizing method and system for inquiring table-building transaction | |
| CN112182065A (en) | Asset management system and method based on automatic acquisition and multi-source import | |
| CN111914028A (en) | Method and device for synchronizing data relation of heterogeneous data sources based on graph increment | |
| CN106682141A (en) | Data synchronization method based on business operation logs | |
| CN113961546B (en) | Real-time query library design method supporting online analysis and statistics | |
| CN110704316A (en) | Office software and hardware testing method in domestic environment | |
| CN113032368A (en) | Data migration method and device, storage medium and platform | |
| Hu et al. | Design and implementation of oracle database incremental data capture based on trigger and identification table |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20190910 |
|
| RJ01 | Rejection of invention patent application after publication |