Database incremental ddl synchronization method and device based on fusion of cdc and extended events

Abstract

The present application relates to the technical field of database, in particular to a database incremental DDL synchronization method and device based on CDC and extended event fusion, the present application breaks through the limitation that traditional CDC can only capture limited DDL operation by CDC and extended event fusion, realizes all types of DDL capture such as creation, deletion and structure modification of core objects such as table and index by combining extended event, does not need to rely on third-party tools and manual operation, and greatly reduces the implementation complexity and error risk.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a database incremental DDL synchronization method and apparatus based on the fusion of CDC and extended events. Background Technology

[0002] SQL Server is one of the most commonly used relational databases, widely used in various applications. Its Data Manipulation Language (DML) incremental synchronization technology is relatively mature. With the increasing frequency of table structure changes in applications due to enterprise digital transformation, the synchronization of Data Definition Language (DDL) operations is becoming increasingly crucial. DDL, as a core component for building data structures, manages database objects through statements such as Create and Alter. SQL Server incremental DDL synchronization refers to the ability to synchronize structured changes made in the source database to other destination databases, ensuring that the source and destination databases have the same structure. In DML+DDL application scenarios, the order in which the logs received by the destination database are received is particularly important; improper ordering can lead to DML or DDL execution failures, causing the entire application or project to malfunction. SQL Server Extended Events, as a next-generation database monitoring technology, can capture structure change events of database objects, fully recording details such as the personnel and time of DDL operations, providing security for database monitoring.

[0003] Currently, incremental DDL synchronization of SQL Server databases mainly involves manually performing synchronization on the target database. However, traditional manual synchronization methods are not only inefficient, but also prone to errors due to omissions in the operation, which can lead to system failure. Furthermore, operators need to have certain professional knowledge and experience, which is too costly.

[0004] Another method is to capture DDL operation logs through CDC (Change Data Capture). After enabling CDC on a table, the table-related DDL operation logs can be obtained from the ddl_history table in the CDC mode of the database. However, this method can only obtain a limited number of DDL operation types, and common table and index creation and deletion operations cannot be obtained.

[0005] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0006] The technical problem that this invention aims to solve is: how to address the issue of incomplete DDL operation types captured by CDC in existing technologies.

[0007] The present invention adopts the following technical solution: Firstly, a database incremental DDL synchronization method based on the fusion of CDC and extended events is provided, including the following steps: Configure the CDC and extended events for the source database, and determine the starting LSN for incremental synchronization; Read the current LSN of the source database. If the current LSN of the source database is greater than the starting LSN, trigger the DDL log reading process with the starting LSN to the current LSN of the source database as the preset range. Read the first DDL log within the preset range from the extended event log, read the second DDL log within the preset range from the DDL log storage table associated with CDC, and merge and sort the first DDL log and the second DDL log after processing. The merged and sorted DDL logs are sent to the target database in a preset order to complete incremental DDL synchronization.

[0008] Preferably, the configuration of the CDC and extended events of the source database specifically includes: Enable SQL Server Agent service, and enable database-level CDC and table-level CDC features; Create an extended event session, add events to capture database object creation and deletion operations, set event filtering conditions to exclude system database and statistics object related events, and configure the storage parameters of the extended event log.

[0009] Preferably, determining the starting LSN for incremental synchronization specifically includes: Query the lsn_time_mapping table in SQL Server under CDC mode, filter out records where tran_id is not 0, and take the largest start_lsn as the starting LSN for incremental synchronization.

[0010] Preferably, the method further includes: When the current LSN of the source database read in this round is less than or equal to the initial LSN, wait for the preset sleep time and then reread the current LSN of the source database. When the current LSN of the source database read in this round is greater than the initial LSN, after the DDL logs within the current preset range are sent out, the current LSN of the source database can be read again directly without hibernation.

[0011] Preferably, reading the first DDL log within the preset range from the extended event log specifically includes: Convert the preset range from the LSN range to a time range; Execute extended event XML parsing SQL to read the first DDL log within the stated time range from the extended event log; The extended event XML parsing SQL uses the DATEADD function to convert the log timestamp to local time, and after filtering, extracts the timestamp, operation type, database name, SQL text, object name, object type, and transaction ID information.

[0012] Preferably, reading the second DDL log within the preset range from the DDL log storage table associated with the CDC specifically includes: Using the preset range as the filtering condition, query the ddl_history table in CDC mode, and filter the target DDL operation type through the preset whitelist to obtain the second DDL log; Specifically, the table ID, SQL statement, and LSN information are extracted, and the extracted information is encapsulated into an object and stored in the first collection.

[0013] Preferably, the step of merging and sorting the first DDL log and the second DDL log after processing specifically includes: Call the SQL Server system function fn_cdc_map_time_to_lsn, set the function parameter to largestless than, and after inputting the execution time of the first DDL log, the system function outputs the largest start_lsn in the lsn_time_mapping table whose tran_begin_time is less than the execution time of the first DDL log, and uses start_lsn as the mapping LSN of the first DDL log; Store the mapping LSNs of all first DDL logs into the second set, merge the first set and the second set, and sort the merged DDL logs in ascending order of LSNs based on the LSNs corresponding to the first and second DDL logs to obtain the third set.

[0014] Preferably, the method further includes reading DML logs according to a preset range, and sending the merged and sorted DDL logs to the target database in a preset order, specifically including: Before each transaction is started from the current DML log, the third set is traversed, and all DDL logs in the third set whose LSN is less than the LSN of the current DML log are selected. The LSNs of the selected DDL logs are sent to the target database in ascending order. After the execution is completed, the current DML log sending transaction is started.

[0015] Secondly, a database incremental DDL synchronization device based on CDC and extended event fusion is provided, the database incremental DDL synchronization device based on CDC and extended event fusion includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the database incremental DDL synchronization method based on CDC and extended event fusion.

[0016] Thirdly, a non-volatile computer storage medium is provided, the computer storage medium storing computer-executable instructions, which are executed by one or more processors to perform the database incremental DDL synchronization method based on CDC and extended event fusion described in the first aspect.

[0017] Fourthly, a chip is provided, comprising: a processor and an interface for calling and running a computer program stored in memory from memory, executing the database incremental DDL synchronization method based on CDC and extended event fusion as described in the first aspect.

[0018] Fifthly, a computer program product containing instructions is provided that, when executed on a computer or processor, causes the computer or processor to perform the database incremental DDL synchronization method based on CDC and extended event fusion as described in the first to fourth aspects and any one of them.

[0019] Compared with the prior art, the beneficial effects of the present invention are as follows: This invention effectively overcomes the limitation of traditional CDC, which can only capture a limited number of DDL operations, by integrating CDC with extended events. It combines extended events to capture all types of DDL operations, such as the creation, deletion, and structural modification of core objects like tables and indexes, without relying on third-party tools or manual operations, thus significantly reducing implementation complexity and error risk. Attached Figure Description

[0020] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0021] Figure 1 This is a flowchart illustrating a database incremental DDL synchronization method based on the fusion of CDC and extended events provided in an embodiment of the present invention. Figure 2 This is a schematic diagram of a process for configuring CDC and extended events provided by an embodiment of the present invention; Figure 3 This is a schematic diagram of a process for reading a first DDL log provided by an embodiment of the present invention; Figure 4 This is a schematic diagram of a process for combining and sorting DDL logs according to an embodiment of the present invention; Figure 5 This is a more detailed flowchart of a database incremental DDL synchronization method based on the fusion of CDC and extended events provided in an embodiment of the present invention; Figure 6 This is a schematic diagram of the structure of a database incremental DDL synchronization device based on the fusion of CDC and extended events provided in an embodiment of the present invention. Detailed Implementation

[0022] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0023] Unless the context otherwise requires, throughout the specification and claims, the term "comprising" is interpreted as openly inclusive, meaning "including, but not limited to." In the description of the specification, terms such as "one embodiment," "some embodiments," "exemplary embodiment," "example," "specific example," or "some examples" are intended to indicate that a particular feature, structure, material, or characteristic associated with that embodiment or example is included in at least one embodiment or example of this disclosure. The illustrative representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics mentioned may be included in any suitable manner in any one or more embodiments or examples; that is, although they may be incorporated into embodiments or examples using the above terms for reasons such as order and position, it does not limit them to be incorporated in combination by a single embodiment or example.

[0024] In the description of this invention, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of embodiments of this disclosure, unless otherwise stated, "a plurality of" means two or more. Furthermore, for example, the description may use the prefix "A" or "B" to describe the same type of nouns as two independent entities. In this case, the corresponding features defined with "A" and "B" are used only to distinguish between similar entities and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features.

[0025] In describing some embodiments, the terms "coupled," "coupled," and "connected," and their derivative expressions, may be used. For example, the term "connected" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact with each other. Similarly, the term "coupled" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact. However, the terms "connected" or "coupled" may also refer to two or more components that do not have direct contact with each other but still cooperate or interact with each other, such as "optical coupling," "wireless connection," etc. The embodiments disclosed herein are not necessarily limited to the scope of this invention.

[0026] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0027] Example 1: To address the issue that existing technologies cannot obtain all types of DDL logs using only CDC (Continuous Data Acquisition) methods, this embodiment proposes a database incremental DDL synchronization method based on the fusion of CDC and extended events. In one embodiment, the method includes: configuring CDC and extended events for the source database and determining the starting LSN (Last Number of Periods) for incremental synchronization; reading the current LSN of the source database; when the current LSN of the source database is greater than the starting LSN, triggering a DDL log reading process with a preset range from the starting LSN to the current LSN of the source database; reading a first DDL log within the preset range from the extended event log, reading a second DDL log within the preset range from the DDL log storage table associated with CDC, and merging and sorting the first and second DDL logs; and sending the merged and sorted DDL logs to the target database in a preset order to complete the incremental DDL synchronization.

[0028] This embodiment effectively overcomes the limitation of traditional CDC, which can only capture a limited number of DDL operations, by integrating CDC with extended events. It combines extended events to capture all types of DDL operations, such as the creation, deletion, and structural modification of core objects like tables and indexes, without relying on third-party tools or manual operations, thus significantly reducing implementation complexity and error risk.

[0029] In one embodiment, such as Figure 1 As shown, the database incremental DDL synchronization method based on the fusion of CDC and extended events specifically includes the following steps: Step 101: Configure the CDC and extended events for the source database and determine the starting LSN for incremental synchronization.

[0030] Extended Events (EAST) is a lightweight performance monitoring and event capture tool built into SQL Server. It allows for the capture of specific database events (such as DDL operations), consumes few resources, and offers flexible capture scope. Change Data Capture (CDC) is SQL Server's change data capture technology, which relies on SQL Server Agent service. It can capture DML operations and some DDL operations on data tables and store the change logs for later synchronization.

[0031] In one embodiment, such as Figure 2 As shown, the configuration of the source database's CDC and extended events specifically includes: Step 1011: Start the SQL Server Agent service, enable database-level CDC and table-level CDC features.

[0032] Specifically, the SQL Server Agent service for the source database (CDC depends on this service to run), database-level CDC (enable CDC for the entire source database), and table-level CDC (enable CDC for the target data tables that need to be synchronized to ensure that table-related changes are captured) are started in sequence.

[0033] Step 1012: Create an extended event session, add events for capturing database object creation and deletion operations, set event filtering conditions to exclude system database and statistics object related events, and configure the storage parameters of the extended event log.

[0034] This involves creating an extended event session, adding SQLSERVER.OBJECT_CREATED (capture object creation operation) and SQLSERVER.OBJECT_DELETED (capture object deletion operation) events, setting filter conditions (excluding system databases and statistics objects) and log storage parameters (such as a maximum of 50MB per file and a maximum of 10 rolling files), and finally enabling the extended event session.

[0035] In one embodiment, the extended event capture for Create and Delete operations is enabled, and the relevant SQL includes: CREATE EVENT SESSION [session name] ON SERVER ADD EVENT SQLSERVER.OBJECT_CREATED( ACTION (SQLSERVER.DATABASE_NAME,SQLSERVER.CLIENT_APP_NAME,SQLSERVER.CLIENT_HOSTNAME,SQLSERVER.SQL_TEXT,SQLSERVER.USERNAME) WHERE (([DATABASE_ID]>(4))) AND ([OBJECT_TYPE]<>(21587)) ), ADD EVENT SQLSERVER.OBJECT_DELETED( ACTION (SQLSERVER.DATABASE_NAME,SQLSERVER.CLIENT_APP_NAME,SQLSERVER.CLIENT_HOSTNAME,SQLSERVER.SQL_TEXT,SQLSERVER.USERNAME) WHERE (([DATABASE_ID]>(4))) AND ([OBJECT_TYPE]<>(21587)) ) ADD TERGET PACKGE0.EVENT_FILE (SET FILENAME=N'file name',MAX_FILE_SIZE=(50),MAX_ROLLOVER_FILES=(10)) WITH (STARTUP_STATE=ON); The condition (([DATABASE_ID]>(4)) is intended to remove the system database; The condition `([OBJECT_TYPE]<>(21587)` removes statistical information objects. The purpose of setting MAX_FILE_SIZE=(50) and MAX_ROLLOVER_FILES=(10) is to set the maximum file size to 50M and to allow a maximum of 10 files to be stored in a rolling manner.

[0036] In one embodiment, determining the starting LSN for incremental synchronization specifically includes: querying the lsn_time_mapping table in CDC mode of SQL Server, filtering out records in the table where tran_id is not 0, and taking the largest start_lsn among them as the starting LSN for incremental synchronization.

[0037] In one embodiment, since the extended event stores the captured logs in an XML file, it is necessary to first obtain the XML file path of the extended event. Specifically, this includes: based on the filename specified when creating the extended event, executing SQL: SELECT s.name as session_name, cast (st.target_data as xml) astarget_data FROM sys.dm_xe_sessions s inner join sys.dm_xe_session_targets stON s.address = st.event_session_address WHERE s.name='file name'; To obtain the target_data value, the path to the extended event XML file is obtained based on the File name tag, so that the corresponding DDL log can be retrieved from the XML file later.

[0038] Step 102: Read the current LSN of the source database. When the current LSN of the source database is greater than the starting LSN, trigger the DDL log reading process with the starting LSN to the current LSN of the source database as the preset range.

[0039] Specifically, by using SQL Server system functions or views (such as in conjunction with the CDC table), the latest log sequence number of the source database is read in real time and recorded as the current LSN of the source database.

[0040] To determine whether a read operation is triggered, in one embodiment, when the current LSN of the source database being read in this round is less than or equal to the initial LSN, the current LSN of the source database is reread after waiting for a preset sleep time; when the current LSN of the source database being read in this round is greater than the initial LSN, after the DDL logs within the current preset range are sent out, the current LSN of the source database is reread directly without needing to sleep.

[0041] Specifically, the current LSN of the source database is compared with the initial LSN. If the current LSN of the source database is greater than the initial LSN, it means that new incremental logs have been generated since the initial LSN, and DDL log reading needs to be triggered. If the current LSN of the source database is less than or equal to the initial LSN, the current LSN of the source database is reread after a preset sleep time until the triggering condition is met.

[0042] In one embodiment, the starting LSN is used as the starting point and the current LSN of the source database is read as the ending point to define the range of DDL logs to be read (i.e., the preset range), and only the incremental DDL logs within the preset range are read subsequently.

[0043] Step 103: Read the first DDL log within the preset range from the extended event log, read the second DDL log within the preset range from the DDL log storage table associated with CDC, and merge and sort the first DDL log and the second DDL log after processing.

[0044] First, the method of reading the first DDL log is introduced. In one embodiment, such as... Figure 3 As shown, reading the first DDL log within the preset range from the extended event log specifically includes: Step 1031: Convert the preset range from LSN range to time range.

[0045] The process involves reading captured DDL logs from the extended event log based on a preset range and filtering them according to a specified whitelist. However, since the extended events do not record LSNs, it is not possible to directly query based on the preset range (which is obtained from LSNs). Therefore, the preset range needs to be converted into a time range first. In one embodiment, the `lsn_time_mapping` table in CDC mode is queried, and the `[start_lsn]` column is used to query the `[tran_begin_time]` corresponding to the record. This converts the starting LSN in the preset range and the current LSN in the source database into the corresponding time, thus obtaining the time range. The specific conversion SQL code includes: SELECT [tran_begin_time] FROM [library name].cdc.lsn_time_mapping WHERE[start_lsn]=?; Step 1032: Execute the extended event XML parsing SQL to read the first DDL log within the time range from the extended event log.

[0046] The process involves converting a preset range to a time range, and then reading the first DDL log captured within that time range from the extended events, based on the time range. The specific SQL code includes: SELECT DATEADD(hour, 8, [XML Data].value('( / event[@package=''sqlserver''] / @timestamp)[1]', 'DATETIME')) AS [timeStamp], [XML Data].value('( / event[@package=''sqlserver''] / @name)[1]', 'DATETIME')) AS [type], [XML Data].value('( / event / action[@name=''database_name''] / value)[1]', 'SYSNAME') AS [database Name], [XML Data].value('( / event / action[@name=''sql_text''] / value)[1]', 'SYSNAME') AS [SQL_Text], [XML Data].value('( / event / data[@name=''object_name''] / value)[1]', 'SYSNAME') AS [object_name], [XML Data].value('( / event / data[@name=''object_type''] / text)[1]', 'SYSNAME') AS object_type, [XML Data].value('( / event / data[@name=''transaction_id''] / value)[1]', 'BIGINT') AS [transaction_id] FROM (SELECT CONVERT(XML, event_data) AS [XML Data] FROM sys.fn_xe_file_target_read_file(N'XML path', NULL, NULL, NULL)) AS v WHERE DATEADD(hour, 8, [XML Data].value('( / event[@package=''sqlserver''] / @timestamp)[1]', 'DATETIME')) >=? AND DATEADD(hour,8,[XML Data].value('( / event[@package=''sqlserver''] / @timestamp)[1]','DATETIME'))<=?order by [TimeStamp] DESC; The SQL code above only parses a portion of the log information from the XML file. For complete log information, please refer to the generated XML file. The extended event XML parsing SQL uses the DATEADD function to convert the log timestamp to local time, filters it, and extracts the timestamp, operation type, database name, SQL text, object name, object type, and transaction ID.

[0047] In one embodiment, to facilitate subsequent log sorting, and since the first DDL logs captured in the extended event all contain transaction IDs, all the first DDL logs captured in the extended event are first sorted by transaction ID number from smallest to largest and then initially saved.

[0048] The following describes how to read the second DDL log. In one embodiment, reading the second DDL log within the preset range from the DDL log storage table associated with CDC specifically includes: querying the ddl_history table in CDC mode using the preset range as a filtering condition, filtering the target DDL operation type through a preset whitelist to obtain the second DDL log; wherein, the table ID, SQL statement, and LSN information are extracted, and the extracted information is encapsulated into an object and stored in the first set.

[0049] When the library-level CDC function is enabled, the `ddl_history` table is automatically created in CDC mode. This table can capture DDL operations that modify table columns, and it contains information such as LSN, SQL, and table name. Based on a preset range, the table ID, SQL, and LSN can be directly retrieved from the `ddl_history` table. If relevant DDL logs exist within the preset range, and after filtering according to a whitelist, the retrieved second DDL logs are encapsulated into an object and saved to the first set.

[0050] In one embodiment, such as Figure 4 As shown, the process of merging and sorting the first DDL log and the second DDL log specifically includes: Step 201: Call the SQL Server system function fn_cdc_map_time_to_lsn, setting the function parameter to largest less than. After inputting the execution time of the first DDL log, the system function outputs the largest start_lsn in the lsn_time_mapping table whose tran_begin_time is less than the execution time of the first DDL log, and uses start_lsn as the mapping LSN of the first DDL log.

[0051] Specifically, by calling the SQL Server native system function `fn_cdc_map_time_to_lsn`, the function parameter is first set to `largest less than` (meaning "take the maximum value less than the target time"), and then the actual execution time recorded in the first DDL log is passed as the input parameter to the system function. The specific SQL code is: `select sys.fn_cdc_map_time_to_lsn('largest less than','time');` The system function automatically queries the lsn_time_mapping table associated with CDC, filters out all records in the table whose tran_begin_time (corresponding to the transaction start time) is less than the execution time of the first DDL log, extracts the largest start_lsn (transaction start log sequence number), and uses this start_lsn as the mapping LSN of the first DDL log. This assigns the same LSN identifier to the first DDL log that originally had no LSN as to the second DDL log, providing a unified basis for subsequent merging and sorting.

[0052] Step 202: Store the mapping LSNs of all first DDL logs into the second set, and merge the first set and the second set. Sort the merged DDL logs in ascending order of LSNs based on the LSNs corresponding to the first and second DDL logs to obtain the third set.

[0053] First, the mapping LSNs of all the first DDL logs obtained in step 201 are stored in a predefined second set (this set is associated with the first set storing detailed information of the first DDL logs and can be identified by the unique identifier of the corresponding mapping LSN by the log); then, the first set storing detailed information of the first DDL logs is associated with the second set associated with the mapping LSN, so that each first DDL log corresponds to its own mapping LSN; then, the processed first DDL logs are merged with the second DDL logs with their own native LSNs.

[0054] Finally, using the LSNs corresponding to the two types of logs (the first DDL log uses the mapped LSN, and the second DDL log uses the native LSN) as the unified sorting criterion, all merged DDL logs are sorted in ascending order of LSN value, ultimately forming an ordered set of DDL logs (i.e., the third set), ensuring that the log order is consistent with the actual order of DDL operations in the source database.

[0055] Step 104: Send the merged and sorted DDL logs to the target database in a preset order to complete incremental DDL synchronization.

[0056] In addition to DDL operations, the source database also contains DML operations, generally requiring simultaneous DDL synchronization and DML synchronization. In one embodiment, the method further includes reading DML logs according to a preset range, i.e., a DML log reading thread reads all DML logs according to the preset range. To avoid conflicts between DDL synchronization and DML synchronization, in one embodiment, the step of sending the merged and sorted DDL logs to the target database in a preset order specifically includes: before each current DML log sending transaction starts, traversing the third set and filtering out all DDL logs in the third set whose LSN is less than the LSN of the current DML log; sending the LSNs of the filtered DDL logs to the target database in ascending order; and after execution is complete, starting the current DML log sending transaction.

[0057] During the log delivery phase, it is necessary to ensure that the target database completes data structure changes (DDL) before performing data operations (DML) to avoid execution failure due to disordered order.

[0058] Therefore, in one embodiment, before each transaction corresponding to the current DML log is initiated, the third set (the merged full DDL log set) sorted by LSN is fully traversed. By comparing the LSN of the DDL log with the LSN of the current DML log to be issued, all DDL logs with LSN values ​​less than the LSN of the current DML log are filtered out. The basis of this filtering logic is that the smaller the LSN, the earlier the log was generated and should be executed first.

[0059] In one embodiment, the selected DDL logs are sequentially sent to the target database in ascending order of their LSNs. Once the target database reports that all selected DDL logs have been executed (ensuring the table structure has been updated according to the historical order of the source database), the current DML log sending transaction is officially initiated, allowing the target database to perform data operations based on the latest synchronized table structure. This entire process uses LSN comparison to ensure that DDL execution takes precedence over DML, fundamentally avoiding conflict scenarios such as operating on data before creating tables or modifying data before adding columns, thus guaranteeing the consistency of structure and data content between the source and target databases.

[0060] In summary, this embodiment effectively overcomes the limitation of traditional CDC, which can only capture a limited number of DDL operations, by integrating CDC with extended events. It combines extended events to capture all types of DDL operations, such as the creation, deletion, and structural modification of core objects like tables and indexes, without relying on third-party tools or manual operations, thus significantly reducing implementation complexity and error risk.

[0061] Example 2: To further illustrate the incremental DDL synchronization method for databases based on the fusion of CDC and extended events proposed in Example 1, in one example, as follows: Figure 5 As shown, the method mainly includes three major steps: step 1 is preparation, step 2 is DDL log reading and processing, and step 3 is DDL log distribution and processing. Step 1: Preparation.

[0062] Step 1.1: Enable CDC and extended events.

[0063] This includes enabling the SQL Server Agent service, database-level CDC, and table-level CDC. It also includes creating and enabling extended event capture for Create and Delete operations.

[0064] Step 1.2: Read the relevant parameters.

[0065] Read the current LSN (denoted as startLsn) from the source database. Read the largest start_lsn in the lsn_time_mapping table of the specified SQL Server database under CDC mode, where tran_id is not 0, as the starting LSN for incremental synchronization.

[0066] Step 2: DDL log reading and processing.

[0067] Step 2.1: The log reading thread reads the current LSN (denoted as curLsn) of the source database, and according to the specified sleep time, after each execution of this step, if the curLsn read in this round is less than or equal to startLsn, it will sleep before executing again; if the curLsn read in this round is greater than startLsn, then no sleep is required before reading again after the log is sent.

[0068] Step 2.2: Compare whether the read value curLsn is greater than startLsn. If yes, start reading the DDL log and proceed to step 2.3. If not, return to step 2.1.

[0069] Step 2.3: Based on the startLsn and curLsn ranges, read the captured DDL logs from the extended events and filter them according to the specified whitelist. Since the extended events do not record LSNs, it is not possible to directly query based on the startLsn and curLsn ranges. Here, it is necessary to first convert the LSN range into an execution time range. Then, based on the time range, read the first DDL log captured within this time range from the extended events.

[0070] If a DDL log exists, proceed to step 2.4; otherwise, proceed directly to step 2.7.

[0071] Step 2.4: Parse the valid information from the first DDL log obtained from the extended event, including the SQL statement, execution time, database name, table name, DDL operation type, transaction ID, table ID, file offset, etc., and encapsulate it into an object for storage.

[0072] Step 2.5: To facilitate subsequent log sorting, since the first DDL log captured from the extended event contains transaction IDs, we will first sort all the DDLs captured from the extended event in ascending order of transaction IDs and then save them.

[0073] Step 2.6: Sorting and distributing DDL and DML logs requires the use of LSNs. Now, the execution time (denoted as time) of the DDL log captured from the extended event is mapped to an LSN. The system function fn_cdc_map_time_to_lsn() can be used to find the largest [start_lsn] in the lsn_time_mapping table whose column [tran_begin_time] is less than time. This [start_lsn] is recorded as the LSN of the DDL log and saved to the eventDdls collection.

[0074] Step 2.7: After enabling the database-level CDC feature, the `ddl_history` table will be automatically created in CDC mode. This table can capture DDL operations that modify table columns. The `ddl_history` table contains LSN, SQL, and table-related information. Based on the ranges `startLsn` and `curLsn`, relevant log information such as table ID, SQL, and LSN can be retrieved from the table. If relevant DDL logs exist within this LSN range, and after filtering according to a whitelist, they are encapsulated into an object and saved to the `historyDdls` collection, proceed to step 2.8. If no DDL logs exist within this LSN range, proceed to step 2.9.

[0075] Step 2.8: Since the second DDL log stored in historyDdls has LSN information, merge eventDdls (i.e., the second set) and historyDdls (i.e., the first set) and sort them according to LSN.

[0076] Step 2.9: Read the incremental DML logs according to the LSN range. There are already relevant articles on this part, so this embodiment will not be described in detail.

[0077] Step 3: DDL log delivery and processing.

[0078] Step 3.1: Determine whether the DML logs read in the current LSN range have been completely distributed. If yes, return to step 2.1. If no, prepare to start a transaction and distribute the DML logs in transaction order.

[0079] Step 3.2: Before the transaction starts, it is necessary to check whether there are any DDL logs with LSNs less than the current transaction's LSN in the current LSN range. If so, all DDL logs with LSNs less than the current transaction's LSN are retrieved and sent to the target database for execution in ascending LSN order, before proceeding to Step 3.3. If not, proceed directly to Step 3.3.

[0080] Step 3.3: Start the DML log delivery transaction and deliver the DML logs within this transaction. Once the DML logs are delivered, return to Step 3.1 to obtain the next transaction. This process enables incremental DDL synchronization.

[0081] For the specific process of the database incremental DDL synchronization method based on the fusion of CDC and extended events, please refer to Example 1, which will not be repeated in this example.

[0082] In summary, this embodiment effectively overcomes the limitation of traditional CDC, which can only capture a limited number of DDL operations, by integrating CDC with extended events. It combines extended events to capture all types of DDL operations, such as the creation, deletion, and structural modification of core objects like tables and indexes, without relying on third-party tools or manual operations, thus significantly reducing implementation complexity and error risk.

[0083] Example 3: In Embodiment 1, a database incremental DDL synchronization method based on CDC and extended event fusion was provided. In this embodiment, a database incremental DDL synchronization device based on CDC and extended event fusion will be proposed. The database incremental DDL synchronization device based on CDC and extended event fusion includes: a processor and a memory for storing processor-executable instructions; wherein, the processor is configured to execute the database incremental DDL synchronization method based on CDC and extended event fusion described in Embodiment 1.

[0084] like Figure 6 As shown, the database incremental DDL synchronization device based on CDC and extended event fusion includes a processor 21 and a memory 22, wherein the processor 21 and the memory 22 can be connected by a bus or other means.

[0085] Processor 21 can be a Central Processing Unit (CPU). Processor 21 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations of the above types of chips.

[0086] The memory 22, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the database incremental DDL synchronization method based on CDC and extended event fusion in Embodiment 1 of this invention. The processor executes various functional applications and training processes by running the non-transitory software programs, instructions, and modules stored in the memory.

[0087] The memory 22 may include a program storage area and a training storage area. The program storage area may store the operating system and applications required for at least one function; the training storage area may store training data created by the processor. Furthermore, the memory may include high-speed random access memory and non-transitory memory, such as at least one disk storage device, flash memory, or other non-transitory solid-state storage device. In some embodiments, the memory 22 may optionally include memory remotely located relative to the processor, which can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof. The one or more modules stored in the memory 22, when executed by the processor 21, perform functions such as... Figure 1 The incremental DDL synchronization method for databases based on the fusion of CDC and extended events shown in Example 1 is illustrated. For specific details regarding the above-described incremental DDL synchronization method based on the fusion of CDC and extended events, please refer to the relevant documentation. Figure 1 , Figure 2 and Figure 3 The relevant descriptions and effects in the embodiments shown are for reference only and will not be repeated here.

[0088] This embodiment also provides a computer storage medium storing a computer program that can be executed by a processor to complete the database incremental DDL synchronization method based on CDC and extended event fusion described in Embodiment 1.

[0089] The computer storage medium stores computer-executable instructions, which can execute the incremental database DDL synchronization method based on CDC and extended event fusion in any of the above method embodiments. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0090] The specific steps of the database incremental DDL synchronization method based on the fusion of CDC and extended events are described in Example 1, and will not be repeated in this example.

[0091] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A database incremental DDL synchronization method based on CDC and extended event fusion, characterized in that, include: Configure the CDC and extended events for the source database, and determine the starting LSN for incremental synchronization; Read the current LSN of the source database. If the current LSN of the source database is greater than the starting LSN, trigger the DDL log reading process with the starting LSN to the current LSN of the source database as the preset range. Read the first DDL log within the preset range from the extended event log, read the second DDL log within the preset range from the DDL log storage table associated with CDC, and merge and sort the first DDL log and the second DDL log after processing. The merged and sorted DDL logs are sent to the target database in a preset order to complete incremental DDL synchronization. 2.The CDC and extended event fusion based database incremental DDL synchronization method of claim 1, wherein, The configuration source database's CDC and extended events specifically include: Enable SQL Server Agent service, and enable database-level CDC and table-level CDC features; Create an extended event session, add events to capture database object creation and deletion operations, set event filtering conditions to exclude system database and statistics object related events, and configure the storage parameters of the extended event log. 3.The CDC and extended event fusion based database incremental DDL synchronization method of claim 1, wherein, The determination of the starting LSN for incremental synchronization specifically includes: Query the lsn_time_mapping table in SQL Server under CDC mode, filter out records where tran_id is not 0, and take the largest start_lsn as the starting LSN for incremental synchronization.

4. The database incremental DDL synchronization method based on CDC and extended event fusion according to claim 1, characterized in that, The method further includes: When the current LSN of the source database read in this round is less than or equal to the initial LSN, wait for the preset sleep time and then reread the current LSN of the source database. When the current LSN of the source database read in this round is greater than the initial LSN, after the DDL logs within the current preset range are sent out, the current LSN of the source database can be read again directly without hibernation.

5. The database incremental DDL synchronization method based on CDC and extended event fusion according to claim 1, characterized in that, The step of reading the first DDL log within the preset range from the extended event log specifically includes: Convert the preset range from the LSN range to a time range; Execute extended event XML parsing SQL to read the first DDL log within the stated time range from the extended event log; The extended event XML parsing SQL uses the DATEADD function to convert the log timestamp to local time, and after filtering, extracts the timestamp, operation type, database name, SQL text, object name, object type, and transaction ID information.

6. The database incremental DDL synchronization method based on CDC and extended event fusion according to claim 1, characterized in that, The step of reading the second DDL log within the preset range from the DDL log storage table associated with the CDC specifically includes: Using the preset range as the filtering condition, query the ddl_history table in CDC mode, and filter the target DDL operation type through the preset whitelist to obtain the second DDL log; Specifically, the table ID, SQL statement, and LSN information are extracted, and the extracted information is encapsulated into an object and stored in the first collection.

7. The database incremental DDL synchronization method based on CDC and extended event fusion according to claim 6, characterized in that, The process of merging and sorting the first and second DDL logs specifically includes: Call the SQL Server system function fn_cdc_map_time_to_lsn, set the function parameter to largest lessthan, and after inputting the execution time of the first DDL log, the system function outputs the largest start_lsn in the lsn_time_mapping table whose tran_begin_time is less than the execution time of the first DDL log, and uses start_lsn as the mapping LSN of the first DDL log; Store the mapping LSNs of all first DDL logs into the second set, merge the first set and the second set, and sort the merged DDL logs in ascending order of LSNs based on the LSNs corresponding to the first and second DDL logs to obtain the third set.

8. The database incremental DDL synchronization method based on CDC and extended event fusion according to claim 7, the method further includes reading DML logs according to a preset range, characterized in that, The step of sending the merged and sorted DDL logs to the target database in a preset order specifically includes: Before each transaction is started from the current DML log, the third set is traversed, and all DDL logs in the third set whose LSN is less than the LSN of the current DML log are selected. The LSNs of the selected DDL logs are sent to the target database in ascending order. After the execution is completed, the current DML log sending transaction is started.

9. A database incremental DDL synchronization device based on the fusion of CDC and extended events, characterized in that, The database incremental DDL synchronization device based on CDC and extended event fusion includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the database incremental DDL synchronization method based on CDC and extended event fusion as described in any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which are executed by one or more processors to perform the database incremental DDL synchronization method based on CDC and extended event fusion as described in any one of claims 1-8.

Images