US20090300075A1 - Method and System for Data Definition Language (DDL) Replication - Google Patents
Method and System for Data Definition Language (DDL) Replication Download PDFInfo
- Publication number
- US20090300075A1 US20090300075A1 US12/131,679 US13167908A US2009300075A1 US 20090300075 A1 US20090300075 A1 US 20090300075A1 US 13167908 A US13167908 A US 13167908A US 2009300075 A1 US2009300075 A1 US 2009300075A1
- Authority
- US
- United States
- Prior art keywords
- ddl
- triggers
- replication
- command text
- filter
- 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.)
- Granted
Links
Images
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
-
- 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/25—Integrating or interfacing systems involving database management systems
Definitions
- the present invention relates to database systems, and more particularly to replication of data definition languages (DDL).
- DDL data definition languages
- Data replication is the process of maintaining multiple copies of a database object in a distributed database system. Performance improvements can be achieved when data replication is employed, since multiple access locations exist for the access and modification of the replicated data. For example, if multiple copies of a data object are maintained, an application can access the logically “closest” copy of the data object to improve access times and minimize network traffic. In addition, data replication provides greater fault tolerance in the event of a server failure, since the multiple copies of the data object effectively become online backup copies if a failure occurs.
- DDL data definition language
- DML Data Manipulation Language
- the data definition language comprises commands used to create and destroy databases and database objects.
- database administrators and users can utilize the data manipulation language (DML) to insert, retrieve and modify the data included within it.
- DML data manipulation language
- databases may assist replication of DDL commands, keeping a log of DDL command text.
- databases may assist replication of DDL commands, keeping a log of DDL command text.
- not all databases support this feature.
- the invention includes system, method, computer program product embodiments and combinations and sub-combinations thereof for enabling replication of data definition languages (DDL).
- DDL data definition languages
- a DDL trigger is used to capture a DDL event.
- a primary database logs the DDL operations into a log file and the corresponding DDL events occur automatically.
- the DDL trigger captures the events, analyzes the events and retrieves related information for later use, including command text, user information and event type.
- the DDL trigger inserts DDL event related information into a DDL shadow table.
- all operations performed by the DDL trigger are logged into a transaction log by the database.
- a replication agent then reads and processes the transaction log. The replication agent then sends a DDL command to the replication server to execute the command in a replicate database.
- DDL commands are replicated in environments where DDL command text is not logged, stored or recorded.
- FIG. 1 is a block diagram of an exemplary replication environment, according to an embodiment of the invention.
- FIG. 2 is a diagram illustrating a primary database, according to an embodiment of the invention.
- FIG. 3 is a diagram illustrating a replication agent, according to an embodiment of the invention.
- FIGS. 4A , 4 B and 4 C are a flowcharts illustrating an exemplary method for DDL replication, according to an embodiment of the invention.
- FIG. 5 is a flowchart illustrating the operation of a transaction filter, according to an embodiment of the invention.
- FIG. 6 is a flowchart illustrating the operation of a repository filter, according to an embodiment of the invention.
- FIG. 7 illustrates an example computer useful for implementing components of the invention.
- the present invention relates to systems, methods and computer program products for the replication of DDL commands using DDL triggers.
- This section describes a general system architecture for DDL replication according to an embodiment of the invention as illustrated in FIG. 1 .
- FIG. 1 includes primary database 110 , replication agent 140 , replication server 130 and replicate database 120 .
- DDL data definition language
- Primary database 110 may be any form of database and can include, but is not limited to, a device having a processor and memory for executing and storing instructions.
- MICROSOFT SQL Server may be used as primary database 110 .
- Such a database may include software, firmware, and hardware or some combination thereof.
- the software may include one or more applications and an operating system.
- the hardware can include, but is not limited to, a processor, memory and user interface display. An optional input device, such as a mouse, stylus or any other pointing device, may be used.
- primary database 110 is a source of transactions that modify data in the replicate database 120 .
- Tables marked for replication in a primary database 110 may be known as primary tables (not shown).
- a primary table must be marked for replication so that replication agent 140 can identify and replicate the transactions that affect the data in that table.
- Replication agent 140 may capture replicated transactions in primary database 110 , and then may send those transactions to a replication server 130 for distribution to replicate database 120 .
- replication agent 140 may update a transaction log in the primary database 110 and may generate an output. This output may be used to distribute replicated transactions throughout system 100 .
- replication agent 140 runs as a stand-alone application, independent of the primary database server 110 , the replication server 130 , and any other replication system components.
- replication agent 140 may reside on the same host machine, for example, primary database 110 or any other replication system component, or it can reside on a machine separate from any other replication system components. Replication agent 140 is described in greater detail below.
- Replication server 130 may receive replicated transactions from replication agent 140 .
- replication server 130 may send replicated transactions to replicate database 120 in response to one or more commands from replication agent 140 .
- the replicated transaction is processed successfully by replicate database 120
- the replicate database 120 is synchronized with data in primary database 110 .
- Network 102 can be any type of network or combination of networks such as, but not limited to, a local area network, wide area network or the Internet.
- Network 102 may be a form of a wired network or a wireless network, or a combination thereof.
- Network 110 may allow primary database 110 , replication server 130 , replication agent 140 and replicated database 120 to communicate with each other.
- replication agent 140 may create DDL trigger 202 in primary database 110 .
- replication agent 140 connects to primary database 110 , creates a DDL trigger, for example, DDL trigger 202 , during initialization.
- Replication agent 140 also creates a DDL shadow table 204 .
- An exemplary DDL shadow trigger is described in detail further below.
- DDL triggers may also record other information replication agent 140 needs to replicate a given transaction, for example, a transaction ID that identifies each operation associated with the transaction.
- DDL trigger 202 may record data to be replicated in one or more transaction log tables, for example, transaction log 206 in primary database 110 .
- DDL trigger 202 could capture information of DDL events and record such information into DDL shadow table 204 .
- the information for one DDL event can be split into two rows in DDL shadow table 204 .
- replication agent 140 may store DDL trigger 202 in primary database 110 when a database table is marked for replication by user 104 .
- DDL trigger 202 may be created in primary database 110 when replication agent 140 is being initialized.
- DDL trigger 202 may initiate system stored procedures in response to an event. DDL trigger 202 may fire in response to a variety of DDL statements. These statements may start with SQL commands such as CREATE, ALTER, and DROP. DDL triggers may also be used for administrative tasks such as auditing and regulating operations associated with primary database 110 . As an example, not intended to limit the invention, DDL trigger 202 may be used to prevent certain changes to a schema of primary database 110 , to initiate an operation in the database in response to a change in the schema of primary database 110 or to record changes in the schema of primary database 110 .
- replication agent 140 may use DDL trigger 202 for DDL replication.
- a DDL trigger for example trigger 202 A, may be used to capture one or more DDL events and store DDL information in shadow table 204 used by replication agent 140 .
- DDL information includes event time, event user, event login, schema name, schema id, event type, object name, command text and/or a flag which indicates the command text is complete or not.
- a DDL event will fire a DDL trigger, for example DDL trigger 202 A.
- DDL trigger 202 A may then use an “event data” function to get the DDL information which will be saved in shadow table 204 .
- two operations one of which is DDL event and the other, an insert operation against shadow table 204 may be in the same transaction.
- an insert operation may be used by replication agent 140 to detect firing of a DDL event.
- DDL command information is inserted into shadow table 204 and logged into transaction log 206 by primary database 110 .
- Replication agent 140 may then read DDL related information from transaction log 206 .
- DDL information may be saved by DDL trigger 202 in shadow table 204 .
- DDL information includes event time, event user, event login, schema name, schema id, event type, object name, command text and/or a flag which indicates the command text is complete or not.
- an identity column as the primary key of shadow table 204 may be included.
- Table 1 illustrates an exemplary shadow table named “ra_ddl_log_”. In the above table:
- ddlid_ is the primary key of the DDL shadow table. It may be identified by (1, 1).
- postTime_ is the execute time of a DDL event.
- all DDL events have this element.
- dbUser_ is the user who executes DDL.
- the system needs to know the user name to switch session context in rdb.
- event_ is a type of DDL event which may be used to identify DDL type.
- tsql_ is the command text of DDL. In an embodiment, if the text of DDL includes a password, tsql will be NULL.
- objname_ is the name of DDL related object.
- An example of a DDL replicated object is provided below.
- loginName_ is the login name of the dbUser_.
- schemaName_ is the schema name of the object.
- schemaId_ is the schema id of the object.
- idLast_ indicates whether the DDL command text is complete or not. If the command text is too long, for example, more than three thousand bytes, the command text will be divided into several rows instead of one row. idLast_ is used to indicate whether it is the last row for the command text.
- a data source log reader 310 in the replication agent 140 may detect an insert operation against shadow table 204 . This indicates that at least one DDL event has occurred.
- transaction log 206 may provide data source log reader 310 with the following information that data source log reader 310 needs:
- replication agent 140 may construct DDL command text immediately; otherwise it may wait for the next insert operation to obtain another portion of command text. Based on information received by data source log reader 310 , transaction filter 320 and repository filter 330 may determine whether a DDL command should be filtered. In an embodiment, all unexpected DDL commands will be filtered by transaction filter 320 and repository filter 330 . Filtering of DDL commands is described below.
- a session may start when user 104 logs in primary database 110 and ends when user 104 logs off. All operations during a session are subject to permission checks against user 104 or any other user.
- an “EXECUTE AS” statement is running, the execution context of the session is switched by replication agent 140 to the specified login or user name. After the context switch, permissions may be checked by replication agent 140 . Such permissions may be checked against the login and user security tokens for that account instead of another user initiating the “EXECUTE AS” statement.
- the user or login account may be impersonated for the duration of the session or module execution, or the context switch may be explicitly reverted.
- the change in execution context may remain in effect until at least one of the following occurs: (i) another EXECUTE AS statement is run, (ii) a REVERT statement is run, or (iii) the session is dropped.
- An exemplary session context switch is described further in the description.
- primary database 110 may log DDL operations into transaction log 206 .
- step 404 the log operations that are carried out by primary database 110 in step 402 , may cause corresponding DDL events to occur automatically.
- primary database 110 may log the DDL operations into transaction log 206 , causing corresponding DDL events to occur automatically.
- DDL trigger 202 A may capture events generated in step 404 .
- DDL trigger 202 A may analyze the events captured in step 406 .
- DDL trigger 202 A may retrieve all related information for use based on the analysis of events in step 408 .
- DDL trigger may retrieve command text, user information, event type and so on.
- DDL trigger 202 A may insert DDL event related information into shadow table 204 .
- step 414 operations performed by DDL trigger 202 A will be logged into transaction log 206 by primary database 110 and used by replication agent 140 .
- replication agent 140 may read log records from transaction log 206 and then process them.
- step 418 replication agent 140 processes log records read from transaction log 206 in step 416 .
- step 420 replication agent 140 checks for a DML operation to system tables associated with primary database 110 . If replication agent 140 finds a DML operation to the system tables, step 422 is performed. If replication agent 140 does not find a DML operation to the system tables, method 400 returns and is complete.
- step 422 replication agent 140 checks if a DDL command begins or not. If a DDL command begins, step 424 is performed. If a DDL command does not begin control returns to step 418 .
- replication agent 140 adds a DDL context to generate schema changes in primary database 110 .
- step 426 replication agent 140 checks for any insert operations in shadow table 204 . If replication agent 140 identifies any insert operations in the shadow table, step 428 is performed. If replication agent does not identify any insert operations, step 430 is performed.
- replication agent 140 ends the current DDL context.
- replication agent 140 may filter out all unexpected DDLs.
- replication agent 140 may filter out unexpected DDLs using transaction filter 320 and repository filter 330 .
- replication agent 140 may modify DDL command text so that the DDL can be executed by the proper user. This step is performed because, when a DDL command is executed in replicate database 120 , the session context needs to be switched to appropriate user session context.
- step 434 replication agent 140 sends the command modified in step 432 to replication server 130 .
- step 436 replicate database 120 executes the DDL command under appropriate user session context.
- DDL may be replicated according to an embodiment of the invention.
- DDL trigger 202 catches DDL events associated with primary database 110 , including user DDL commands and system DDL commands.
- a system DDL command for example, is a DDL command used to create, alter or drop a system object.
- a system DDL command it may be necessary to identify a system object. After an object is created or altered, if the object is a system object, a flag bit (not shown) associated with the system object will be toggled. Using the flag bit, a system object may be identified by replication agent 140 . The system object is then used to identify a system DDL command.
- system level DDL commands are not to be replicated to replicate database 120 . Thus, it may be necessary to filter system DDL commands. Besides system DDL commands, some events associated with DDL commands may include a password which may be caught by DDL trigger 202 . Since, in such scenarios, command text may be null, these DDL commands may need to be filtered.
- replication agent 140 may include transaction filter 320 and repository filter 330 to filter DDL commands before they are sent to replication server 130 .
- transaction filter 320 and repository filter 330 The operation of transaction filter 320 and repository filter 330 is described in the following sections.
- transaction filter 320 receives DDL command text from replication agent 140 .
- transaction filter 320 checks if the command text is null. If command text is null, step 520 is performed, wherein the DDL command text is filtered out as it may not be supported. If the command text is not null, step 506 is performed.
- transaction filter 320 checks if the DDL command type is associated with the creation of an object. As an example, transaction filter 320 may check if the DDL command type is associated with the creation of an object. If the DDL command type is associated with the creation of an object, step 508 is performed.
- transaction filter 320 checks if an object related to the DDL command is a system object. If the related object is a system object, step 522 is performed, wherein transaction filter 320 filters system level DDL command text
- step 510 is performed.
- replication agent 140 ends a current DDL context.
- step 512 is performed.
- transaction filter 320 checks whether the object is in the list or set. If the object is in the list or set, step 516 is performed. If the object is not in the list or set, step 518 is performed.
- replication agent 140 sends the command to the replication server 130 .
- the command will be executed on replicate database 120 under appropriate user session context.
- step 518 the DDL command text is sent to repository filter 330 for further processing.
- transaction filter 320 may filter DDL command text.
- a DDL command processed by transaction filter 320 may then be sent to repository filter 330 for further filtering.
- repository filter 330 may receive a DDL event from transaction filter 320 .
- step 604 repository filter 330 checks whether DDL command type is object related.
- object related DDL commands include “create object”, “alter object” and “drop object”. If the DDL command type is object related, step 606 is performed.
- repository filter 330 checks if the DDL command type is associated with the creation of an object. If the DDL command type is associated with the creation of an object, step 612 is performed, wherein repository filter 330 applies schema changes to repository 308 . Thereafter, in step 614 , the DDL command text is sent to replication server 130 .
- step 608 is performed.
- step 608 repository filter 330 checks whether the object related to the current DDL command exists in repository 308 . If the object related to the current DDL command exists in repository 308 , step 612 is performed (described above). If the object related to the current DDL command does not exist in repository 308 , step 610 is performed.
- step 610 repository filter 330 filters unexpected DDL commands.
- step 614 is performed (described above).
- repository filter 330 filters DDL commands, according to an embodiment of the invention.
- an exemplary DDL trigger named “safety” will fire whenever a DROP TABLE or ALTER TABLE event occurs in the database:
- a DDL trigger prints a message if any CREATE LOGIN, ALTER LOGIN, or DROP LOGIN event occurs in a current server instance. It uses the EVENTDATA function to retrieve the text of the corresponding data manipulation language statement. As an example, Transact-SQL statements may be used.
- the following is exemplary SQL script generated by replication agent 140 during initialization to create a DDL trigger and a shadow table.
- SELECT SUSER_NAME( ), USER_NAME( ); --result is: user1, user1 REVERT; --Display current execution context.
- SELECT SUSER_NAME( ), USER_NAME( ); --result is: the user before user1, the user before user1
- each kind of DDL command may have its own DDL type. Examples are presented in Table 2.
- one DDL command may be related to one object. In other words, if one DDL command is executed, it will affect one object.
- a DDL command “CREATE TABLE” illustrated in the example below:
- the DDL command is related to the table TEST.
- this DDL command creates an index on table TEST, and this DDL command is related to the table TEST.
- single DDL transaction may include one DDL.
- TRANSACTION1 there is only CREATE TABLE statement. It does not include other DDL or DML, so TRANSACTION1 is a single DDL transaction.
- a user transaction can include more than one DDL, like:
- the system and components of embodiments described herein are implemented using well known computers, such as computer 702 shown in FIG. 7 .
- computer 702 shown in FIG. 7 .
- the operation of flowcharts in FIGS. 4A-4C , 5 and 6 can be implemented using computer(s) 702 .
- the computer 702 can be any commercially available and well known computer capable of performing the functions described herein, such as computers available from International Business Machines, Apple, Sun, HP, Dell, Compaq, Digital, Cray, etc.
- the computer 702 includes one or more processors (also called central processing units, or CPUs), such as a processor 706 .
- the processor 706 is connected to a communication bus 704 .
- the computer 702 also includes a main or primary memory 708 , such as random access memory (RAM).
- the primary memory 708 has stored therein control logic 728 A (computer software), and data.
- the computer 702 also includes one or more secondary storage devices 710 .
- the secondary storage devices 710 include, for example, a hard disk drive 712 and/or a removable storage device or drive 714 , as well as other types of storage devices, such as memory cards and memory sticks.
- the removable storage drive 714 represents a floppy disk drive, a magnetic tape drive, a compact disk drive, an optical storage device, tape backup, etc.
- the removable storage drive 714 interacts with a removable storage unit 716 .
- the removable storage unit 716 includes a computer useable or readable storage medium 724 having stored therein computer software 728 B (control logic) and/or data.
- Removable storage unit 716 represents a floppy disk, magnetic tape, compact disk, DVD, optical storage disk, or any other computer data storage device.
- the removable storage drive 714 reads from and/or writes to the removable storage unit 716 in a well known manner.
- the computer 702 also includes input/output/display devices 722 , such as monitors, keyboards, pointing devices, etc.
- the computer 702 further includes a communication or network interface 718 .
- the network interface 718 enables the computer 702 to communicate with remote devices.
- the network interface 718 allows the computer 702 to communicate over communication networks or mediums 724 B (representing a form of a computer useable or readable medium), such as LANs, WANs, the Internet, etc.
- the network interface 718 may interface with remote sites or networks via wired or wireless connections.
- Control logic 728 C may be transmitted to and from the computer 702 via the communication medium 724 B. More particularly, the computer 702 may receive and transmit carrier waves (electromagnetic signals) modulated with control logic 730 via the communication medium 724 B.
- carrier waves electromagtic signals
- Any apparatus or manufacture comprising a computer useable or readable medium having control logic (software) stored therein is referred to herein as a computer program product or program storage device.
- the invention can work with software, hardware, and/or operating system implementations other than those described herein. Any software, hardware, and operating system implementations suitable for performing the functions described herein can be used.
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Stored Programmes (AREA)
Abstract
Description
- 1. Field of the Invention
- The present invention relates to database systems, and more particularly to replication of data definition languages (DDL).
- 2. Background Art
- Data replication is the process of maintaining multiple copies of a database object in a distributed database system. Performance improvements can be achieved when data replication is employed, since multiple access locations exist for the access and modification of the replicated data. For example, if multiple copies of a data object are maintained, an application can access the logically “closest” copy of the data object to improve access times and minimize network traffic. In addition, data replication provides greater fault tolerance in the event of a server failure, since the multiple copies of the data object effectively become online backup copies if a failure occurs.
- Languages used for data manipulation and replication, such as SQL, can be divided into two main sublanguages, namely the data definition language (DDL) and the Data Manipulation Language (DML).
- The data definition language comprises commands used to create and destroy databases and database objects. After the database structure is defined with DDL, database administrators and users can utilize the data manipulation language (DML) to insert, retrieve and modify the data included within it.
- In some cases, databases may assist replication of DDL commands, keeping a log of DDL command text. However, not all databases support this feature.
- Therefore, what is needed is a system, method and computer program product that allows replication of DDL commands in environments where DDL command text is not logged, stored or recorded.
- Briefly stated, the invention includes system, method, computer program product embodiments and combinations and sub-combinations thereof for enabling replication of data definition languages (DDL).
- In an embodiment, a DDL trigger is used to capture a DDL event. When a user makes any schema changes to a database, such as creating a table or altering a view, a primary database logs the DDL operations into a log file and the corresponding DDL events occur automatically. After the DDL events occur, the DDL trigger captures the events, analyzes the events and retrieves related information for later use, including command text, user information and event type. In an embodiment, the DDL trigger inserts DDL event related information into a DDL shadow table. In an embodiment, all operations performed by the DDL trigger are logged into a transaction log by the database. A replication agent then reads and processes the transaction log. The replication agent then sends a DDL command to the replication server to execute the command in a replicate database.
- In this way, DDL commands are replicated in environments where DDL command text is not logged, stored or recorded.
- The accompanying drawings, which are incorporated herein and form part of the specification, illustrate embodiments of the present invention and, together with the description, further serve to explain the principles of the invention and to enable a person skilled in the relevant art(s) to make and use the invention.
-
FIG. 1 is a block diagram of an exemplary replication environment, according to an embodiment of the invention. -
FIG. 2 is a diagram illustrating a primary database, according to an embodiment of the invention. -
FIG. 3 is a diagram illustrating a replication agent, according to an embodiment of the invention. -
FIGS. 4A , 4B and 4C are a flowcharts illustrating an exemplary method for DDL replication, according to an embodiment of the invention. -
FIG. 5 is a flowchart illustrating the operation of a transaction filter, according to an embodiment of the invention. -
FIG. 6 is a flowchart illustrating the operation of a repository filter, according to an embodiment of the invention. -
FIG. 7 illustrates an example computer useful for implementing components of the invention. - The features and advantages of the present invention will become more apparent from the detailed description set forth below when taken in conjunction with the drawings. In the drawings, like reference numbers generally indicate identical, functionally similar, and/or structurally similar elements. Generally, the drawing in which an element first appears is indicated by the leftmost digit(s) in the corresponding reference number.
- The present invention relates to systems, methods and computer program products for the replication of DDL commands using DDL triggers.
- While the present invention is described herein with reference to illustrative embodiments for particular applications, it should be understood that the invention is not limited thereto. Those skilled in the art with access to the teachings provided herein will recognize additional modifications, applications, and embodiments within the scope thereof and additional fields in which the invention would be of significant utility.
- This detailed description of embodiments of the present invention is organized into several sections as shown by the following table of contents.
-
Table Of Contents 1. System 2. DDL Triggers 3. Replication Agent 3.1 Capturing a DDL Event 3.1.1 Exemplary Schema of DDL Shadow Table 3.2 Constructing DDL Command Text 3.3 Session Context Switching 3.4 Exemplary Overall Operation of DDL Replication 4. Filtering DDL Commands 4.1 Transaction Filter 4.2 Repository Filter 5. Exemplary Database Level Triggers and Server Level Triggers 6. Exemplary Creation of a DDL Trigger and Shadow Table 7. Exemplary Switch Session Context 8. Exemplary DDL Type and Create Object 9. Exemplary DDL and Object Relation 10. Exemplary Single DDL and Multi-DDL User Transaction 11. Example Computer Embodiment 12. Conclusion - This section describes a general system architecture for DDL replication according to an embodiment of the invention as illustrated in
FIG. 1 . -
FIG. 1 includesprimary database 110,replication agent 140,replication server 130 andreplicate database 120. (While the following is described in terms of data definition language (DDL), the invention is not limited to this embodiment and other definition languages may be used. The invention is applicable to any system having generally the structure ofFIG. 1 , or that would benefit from the operation, methods and functions as described herein.) -
Primary database 110 may be any form of database and can include, but is not limited to, a device having a processor and memory for executing and storing instructions. In an example, not intended to limit the invention, MICROSOFT SQL Server may be used asprimary database 110. Such a database may include software, firmware, and hardware or some combination thereof. The software may include one or more applications and an operating system. The hardware can include, but is not limited to, a processor, memory and user interface display. An optional input device, such as a mouse, stylus or any other pointing device, may be used. - In an embodiment,
primary database 110 is a source of transactions that modify data in the replicatedatabase 120. Tables marked for replication in aprimary database 110 may be known as primary tables (not shown). A primary table must be marked for replication so thatreplication agent 140 can identify and replicate the transactions that affect the data in that table. -
Replication agent 140 may capture replicated transactions inprimary database 110, and then may send those transactions to areplication server 130 for distribution to replicatedatabase 120. In an embodiment,replication agent 140 may update a transaction log in theprimary database 110 and may generate an output. This output may be used to distribute replicated transactions throughoutsystem 100. - In an embodiment,
replication agent 140 runs as a stand-alone application, independent of theprimary database server 110, thereplication server 130, and any other replication system components. In another embodiment,replication agent 140 may reside on the same host machine, for example,primary database 110 or any other replication system component, or it can reside on a machine separate from any other replication system components.Replication agent 140 is described in greater detail below. -
Replication server 130 may receive replicated transactions fromreplication agent 140. In an embodiment,replication server 130 may send replicated transactions to replicatedatabase 120 in response to one or more commands fromreplication agent 140. As an example, when the replicated transaction is processed successfully by replicatedatabase 120, the replicatedatabase 120 is synchronized with data inprimary database 110. -
Network 102 can be any type of network or combination of networks such as, but not limited to, a local area network, wide area network or the Internet.Network 102 may be a form of a wired network or a wireless network, or a combination thereof.Network 110 may allowprimary database 110,replication server 130,replication agent 140 and replicateddatabase 120 to communicate with each other. - Referring to
FIG. 2 ,replication agent 140 may createDDL trigger 202 inprimary database 110. In an embodiment,replication agent 140 connects toprimary database 110, creates a DDL trigger, for example,DDL trigger 202, during initialization.Replication agent 140 also creates a DDL shadow table 204. An exemplary DDL shadow trigger is described in detail further below. - DDL triggers, for example,
DDL trigger 202, may also record otherinformation replication agent 140 needs to replicate a given transaction, for example, a transaction ID that identifies each operation associated with the transaction. When fired,DDL trigger 202 may record data to be replicated in one or more transaction log tables, for example,transaction log 206 inprimary database 110.DDL trigger 202 could capture information of DDL events and record such information into DDL shadow table 204. Furthermore, in an embodiment, the information for one DDL event can be split into two rows in DDL shadow table 204. - In an embodiment,
replication agent 140 may storeDDL trigger 202 inprimary database 110 when a database table is marked for replication by user 104.DDL trigger 202 may be created inprimary database 110 whenreplication agent 140 is being initialized. -
DDL trigger 202 may initiate system stored procedures in response to an event.DDL trigger 202 may fire in response to a variety of DDL statements. These statements may start with SQL commands such as CREATE, ALTER, and DROP. DDL triggers may also be used for administrative tasks such as auditing and regulating operations associated withprimary database 110. As an example, not intended to limit the invention,DDL trigger 202 may be used to prevent certain changes to a schema ofprimary database 110, to initiate an operation in the database in response to a change in the schema ofprimary database 110 or to record changes in the schema ofprimary database 110. - This section describes the operation of
replication agent 140 according to an embodiment of the invention. In an embodiment,replication agent 140 may useDDL trigger 202 for DDL replication. - 3.1 Capturing a DDL Event
- In order to obtain DDL information, a DDL trigger, for example trigger 202A, may be used to capture one or more DDL events and store DDL information in shadow table 204 used by
replication agent 140. As an example, DDL information includes event time, event user, event login, schema name, schema id, event type, object name, command text and/or a flag which indicates the command text is complete or not. When a DDL statement is executed, a DDL event will fire a DDL trigger, for example DDL trigger 202A. DDL trigger 202A may then use an “event data” function to get the DDL information which will be saved in shadow table 204. In an embodiment, two operations, one of which is DDL event and the other, an insert operation against shadow table 204 may be in the same transaction. As an example, if an insert operation occurs against shadow table 204, it may be used byreplication agent 140 to detect firing of a DDL event. - In this way, one or more DDL events are captured by
DDL trigger 202. DDL command information is inserted into shadow table 204 and logged intotransaction log 206 byprimary database 110.Replication agent 140 may then read DDL related information fromtransaction log 206. - 3.1.1 Exemplary Schema of a DDL Shadow Table
- In an embodiment, DDL information may be saved by
DDL trigger 202 in shadow table 204. As an example, DDL information includes event time, event user, event login, schema name, schema id, event type, object name, command text and/or a flag which indicates the command text is complete or not. Additionally, an identity column as the primary key of shadow table 204 may be included. -
TABLE 1 column_name data_type Null? ddlid Int N postTime Datetime Y dbUser nvarchar(128) Y event nvarchar(100) Y objname nvarchar(128) Y tsql narchar(3000) Y loginName nvarchar(128) Y schemaName nvarchar(128) Y schemaid Int Y isLast tinyint Y - Table 1 illustrates an exemplary shadow table named “ra_ddl_log_”. In the above table:
- ddlid_ is the primary key of the DDL shadow table. It may be identified by (1, 1).
- postTime_ is the execute time of a DDL event. In an embodiment, all DDL events have this element.
- dbUser_ is the user who executes DDL. The system needs to know the user name to switch session context in rdb.
- event_ is a type of DDL event which may be used to identify DDL type.
- tsql_ is the command text of DDL. In an embodiment, if the text of DDL includes a password, tsql will be NULL.
- objname_ is the name of DDL related object. An example of a DDL replicated object is provided below.
- loginName_ is the login name of the dbUser_.
- schemaName_ is the schema name of the object.
- schemaId_ is the schema id of the object.
- idLast_ indicates whether the DDL command text is complete or not. If the command text is too long, for example, more than three thousand bytes, the command text will be divided into several rows instead of one row. idLast_ is used to indicate whether it is the last row for the command text.
- 3.2 Constructing DDL Command Text
- Referring to
FIG. 3 , a datasource log reader 310 in thereplication agent 140 may detect an insert operation against shadow table 204. This indicates that at least one DDL event has occurred. - As an example, not intended to limit the invention, when data source
log reader 310 detects an insert operation in shadow table 204, transaction log 206 may provide datasource log reader 310 with the following information that datasource log reader 310 needs: - (a) postTime
- (b) dbUser
- (c) event
- (d) tsql
- (e) objName
- (f) loginName
- (g) schemaName
- (h) schemaId
- (i) isLast
- In an embodiment, if “isLast” is true,
replication agent 140 may construct DDL command text immediately; otherwise it may wait for the next insert operation to obtain another portion of command text. Based on information received by datasource log reader 310,transaction filter 320 andrepository filter 330 may determine whether a DDL command should be filtered. In an embodiment, all unexpected DDL commands will be filtered bytransaction filter 320 andrepository filter 330. Filtering of DDL commands is described below. - 3.3 Session Context Switching
- In an embodiment, a session may start when user 104 logs in
primary database 110 and ends when user 104 logs off. All operations during a session are subject to permission checks against user 104 or any other user. As an example, when an “EXECUTE AS” statement is running, the execution context of the session is switched byreplication agent 140 to the specified login or user name. After the context switch, permissions may be checked byreplication agent 140. Such permissions may be checked against the login and user security tokens for that account instead of another user initiating the “EXECUTE AS” statement. As an example, the user or login account may be impersonated for the duration of the session or module execution, or the context switch may be explicitly reverted. The change in execution context may remain in effect until at least one of the following occurs: (i) another EXECUTE AS statement is run, (ii) a REVERT statement is run, or (iii) the session is dropped. An exemplary session context switch is described further in the description. - 3.4 Exemplary Overall Operation of DDL Replication
- An exemplary method for DDL replication according to an embodiment of the invention will now be described in detail with reference to
flowchart 400 inFIGS. 4A , 4B and 4C. - In
step 402,primary database 110 may log DDL operations intotransaction log 206. - In
step 404, the log operations that are carried out byprimary database 110 instep 402, may cause corresponding DDL events to occur automatically. As an example, when users make any schema changes to database, such as creating a table, altering a view, dropping a trigger and so on,primary database 110 may log the DDL operations intotransaction log 206, causing corresponding DDL events to occur automatically. - In
step 406, DDL trigger 202A may capture events generated instep 404. - In
step 408, DDL trigger 202A may analyze the events captured instep 406. - In
step 410, DDL trigger 202A may retrieve all related information for use based on the analysis of events instep 408. As an example, DDL trigger may retrieve command text, user information, event type and so on. - In
step 412, DDL trigger 202A may insert DDL event related information into shadow table 204. - In
step 414, operations performed by DDL trigger 202A will be logged intotransaction log 206 byprimary database 110 and used byreplication agent 140. - In
step 416,replication agent 140 may read log records fromtransaction log 206 and then process them. - In
step 418,replication agent 140 processes log records read fromtransaction log 206 instep 416. - In
step 420,replication agent 140 checks for a DML operation to system tables associated withprimary database 110. Ifreplication agent 140 finds a DML operation to the system tables,step 422 is performed. Ifreplication agent 140 does not find a DML operation to the system tables,method 400 returns and is complete. - In
step 422,replication agent 140 checks if a DDL command begins or not. If a DDL command begins,step 424 is performed. If a DDL command does not begin control returns to step 418. - In
step 424,replication agent 140 adds a DDL context to generate schema changes inprimary database 110. - In
step 426,replication agent 140 checks for any insert operations in shadow table 204. Ifreplication agent 140 identifies any insert operations in the shadow table,step 428 is performed. If replication agent does not identify any insert operations, step 430 is performed. - In
step 428,replication agent 140 ends the current DDL context. - In
step 430,replication agent 140 may filter out all unexpected DDLs. As an example,replication agent 140 may filter out unexpected DDLs usingtransaction filter 320 andrepository filter 330. - In
step 432,replication agent 140 may modify DDL command text so that the DDL can be executed by the proper user. This step is performed because, when a DDL command is executed in replicatedatabase 120, the session context needs to be switched to appropriate user session context. - In
step 434,replication agent 140 sends the command modified instep 432 toreplication server 130. - In
step 436, replicatedatabase 120 executes the DDL command under appropriate user session context. - In this way, according to
method 400, DDL may be replicated according to an embodiment of the invention. - In an embodiment, DDL trigger 202 catches DDL events associated with
primary database 110, including user DDL commands and system DDL commands. - A system DDL command, for example, is a DDL command used to create, alter or drop a system object. In order to recognize a system DDL command, it may be necessary to identify a system object. After an object is created or altered, if the object is a system object, a flag bit (not shown) associated with the system object will be toggled. Using the flag bit, a system object may be identified by
replication agent 140. The system object is then used to identify a system DDL command. - In some cases, system level DDL commands are not to be replicated to replicate
database 120. Thus, it may be necessary to filter system DDL commands. Besides system DDL commands, some events associated with DDL commands may include a password which may be caught byDDL trigger 202. Since, in such scenarios, command text may be null, these DDL commands may need to be filtered. - Referring to
FIG. 3 ,replication agent 140 may includetransaction filter 320 andrepository filter 330 to filter DDL commands before they are sent toreplication server 130. The operation oftransaction filter 320 andrepository filter 330 is described in the following sections. - 4.1
Transaction Filter 320 - The operation of an exemplary transaction filter, according to an embodiment of the invention will be described in detail with reference to
flowchart 500 inFIG. 5 , according to an embodiment of the invention. - In
step 502,transaction filter 320 receives DDL command text fromreplication agent 140. - In
step 504,transaction filter 320 checks if the command text is null. If command text is null,step 520 is performed, wherein the DDL command text is filtered out as it may not be supported. If the command text is not null,step 506 is performed. - In
step 506,transaction filter 320 checks if the DDL command type is associated with the creation of an object. As an example,transaction filter 320 may check if the DDL command type is associated with the creation of an object. If the DDL command type is associated with the creation of an object,step 508 is performed. - In
step 508,transaction filter 320 checks if an object related to the DDL command is a system object. If the related object is a system object,step 522 is performed, whereintransaction filter 320 filters system level DDL command text - If, in
step 508, it is determined that the related object is not a system object,step 510 is performed. Instep 510,replication agent 140 ends a current DDL context. - Referring again to step 506, if the DDL command type is not associated with creation of an object,
step 512 is performed. - In
step 512,transaction filter 320 checks whether the object is in the list or set. If the object is in the list or set,step 516 is performed. If the object is not in the list or set,step 518 is performed. - In
step 516,replication agent 140 sends the command to thereplication server 130. As an example, the command will be executed on replicatedatabase 120 under appropriate user session context. - In
step 518, the DDL command text is sent torepository filter 330 for further processing. - In this way,
transaction filter 320 may filter DDL command text. A DDL command processed bytransaction filter 320 may then be sent torepository filter 330 for further filtering. - 4.2
Repository Filter 330 - The operation of an
exemplary repository filter 330, according to an embodiment of the invention will be described in detail with reference toflowchart 600 inFIG. 6 . - In
step 602,repository filter 330 may receive a DDL event fromtransaction filter 320. - In
step 604,repository filter 330 checks whether DDL command type is object related. As an example, object related DDL commands include “create object”, “alter object” and “drop object”. If the DDL command type is object related,step 606 is performed. - In
step 606,repository filter 330 checks if the DDL command type is associated with the creation of an object. If the DDL command type is associated with the creation of an object,step 612 is performed, whereinrepository filter 330 applies schema changes torepository 308. Thereafter, instep 614, the DDL command text is sent toreplication server 130. - If it is determined in
step 606 that the DDL command type is not associated with the creation of an object,step 608 is performed. - In
step 608,repository filter 330 checks whether the object related to the current DDL command exists inrepository 308. If the object related to the current DDL command exists inrepository 308,step 612 is performed (described above). If the object related to the current DDL command does not exist inrepository 308,step 610 is performed. - In
step 610,repository filter 330 filters unexpected DDL commands. - Returning to step 604, if it is determined that the DDL command type is not object related,
step 614 is performed (described above). - In this way,
repository filter 330 filters DDL commands, according to an embodiment of the invention. - In the following example, an exemplary DDL trigger named “safety” will fire whenever a DROP TABLE or ALTER TABLE event occurs in the database:
-
CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT ‘You must disable Trigger “safety” to drop or alter tables!’ ROLLBACK; - In the next example, a DDL trigger prints a message if any CREATE LOGIN, ALTER LOGIN, or DROP LOGIN event occurs in a current server instance. It uses the EVENTDATA function to retrieve the text of the corresponding data manipulation language statement. As an example, Transact-SQL statements may be used.
-
CREATE TRIGGER DDL _trig_login ON ALL SERVER FOR DDL _LOGIN_EVENTS AS PRINT ‘Login Event Issued.’ SELECT EVENTDATA( ).value(‘(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]’,‘ nvarchar(max)’) GO DROP TRIGGER DDL _trig_login ON ALL SERVER GO; - The following is exemplary SQL script generated by
replication agent 140 during initialization to create a DDL trigger and a shadow table. -
--go to pdb USE pdb; GO --create shadow table “DDL _log” CREATE TABLE [dbo].[ra_ddl_trig_]( [ddlid] [int] IDENTITY(1,1) NOT NULL, [posttime] [datetime] NULL, [dbuser] [nvarchar](128) NULL, [event] [nvarchar](100) NULL, [objname] [nvarchar](128) NULL, [tsql] [nvarchar](3000) NULL, [loginname] [nvarchar](128) NULL, [objid] [int] NULL, [schemaid] [int] NULL, [schemaname] [nvarchar](128) NULL, [islast] [tinyint] NULL ); GO --create DDL trigger CREATE TRIGGER [ra_ddl_trig_] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @objectname nvarchar(100) DECLARE @eventType nvarchar(100) DECLARE @data XML DECLARE @tsql nvarchar(MAX) DECLARE @STEP int DECLARE @i bigint DECLARE @tsqllen bigint DECLARE @islast tinyint SET @STEP = 3000; SET @i = 1; SET @data=EVENTDATA( ); SET @tsql = @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(max)’); SET @tsqllen = len(@tsql); WHILE @i < @tsqllen BEGIN IF @i + @STEP < @tsqllen BEGIN SET @islast = 0; END ELSE BEGIN SET @islast = 1; END INSERT ra_ddl_trig— (posttime, dbuser,loginname,event, objname, objid, schemaname, schemaid, tsql, islast) VALUES (GETDATE( ), CONVERT(nvarchar(100), CURRENT_USER), CONVERT(nvarchar(100), SYSTEM_USER), @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(100)’), @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(255)’), object_id(@data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘nvarchar(255)’)+ ‘.’ + @data.value(‘(/EVENT_INSTANCE/ bjectName)[1]’, ‘nvarchar(255)’) ) , @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘nvarchar(255)’), schema_id(@data.value(‘(/EVENT_INSTANCE/ SchemaName)[1]’, ‘nvarchar(255)’) ) , SUBSTRING (@tsql,@i,@STEP), @islast) ; SET @i = @i + @STEP; DELETE from ra_ddl_trig_; END GO --Test the trigger CREATE TABLE TestTable (a int) DROP TABLE TestTable ; GO SELECT dbuser, objname, event , tsql, isLast FROM ra_ddl_trig— ; GO --result is: DBUser objectName Event TSQL isLast dbo TestTable CREATE_TABLE CREATE TABLE TestTable (a int) 1 dbo TestTable DROP_TABLE DROP TABLE TestTable ; 1 - In an example,
replication agent 140 may use a command such as, “execute as user=userName” to switch session context. The following is an example. -
USE rdb GO --Create two temporary principals CREATE LOGIN login1 WITH PASSWORD = ‘J345#$) thb’; CREATE LOGIN login2 WITH PASSWORD = ‘Uor80$23b’; GO CREATE USER user1 FOR LOGIN login1; CREATE USER user2 FOR LOGIN login2; GO --Give IMPERSONATE permissions on user2 to user1 --so that user1 can successfully set the execution context to user2. GRANT IMPERSONATE ON USER:: user2 TO user1; GO --Display current execution context. SELECT SUSER_NAME( ), USER_NAME( ); -- Set the execution context to login1. EXECUTE AS LOGIN = ‘login1’; --Verify the execution context is now login1. SELECT SUSER_NAME( ), USER_NAME( ); --result is: user1,user1 --Login1 sets the execution context to login2. EXECUTE AS USER = ‘user2’; --Display current execution context. SELECT SUSER_NAME( ), USER_NAME( ); --result is: user2, user2 -- The execution context stack now has three principals: the originating caller, login1 and login2. --The following REVERT statements will reset the execution context to the previous context. REVERT; --Display current execution context. SELECT SUSER_NAME( ), USER_NAME( ); --result is: user1, user1 REVERT; --Display current execution context. SELECT SUSER_NAME( ), USER_NAME( ); --result is: the user before user1, the user before user1 - In an embodiment, each kind of DDL command may have its own DDL type. Examples are presented in Table 2.
-
TABLE 2 DDL Statement DDL Type Create table test(id int) create table Create index on test(id) create index Create view myview as select * from test create view Create trigger mytrigger create trigger Create procedure mypro as . . . create procedure Create function myfun . . . create function - In an embodiment, one DDL command may be related to one object. In other words, if one DDL command is executed, it will affect one object. Consider a DDL command “CREATE TABLE” illustrated in the example below:
- CREATE TABLE TEST (ID INT, NAME VARCHAR(50))
- In this instance, the DDL command is related to the table TEST.
- In another example, consider a DDL command:
- CREATE INDEX MYINDEX ON TEST(ID).
- As an example, this DDL command creates an index on table TEST, and this DDL command is related to the table TEST.
- As an example, single DDL transaction may include one DDL.
- Consider the DDL that appears below:
- Begin Transaction Transaction1
- CREATE TABLE TEST (ID INT, NAME VARCHAR(50))
- COMMIT.
- In “TRANSACTION1”, there is only CREATE TABLE statement. It does not include other DDL or DML, so TRANSACTION1 is a single DDL transaction.
- In another example, a user transaction can include more than one DDL, like:
- BEGIN TRANSACTION TRANSACTION2
- CREATE TABLE TEST (ID INT, NAME VARCHAR(50))
- CREATE INDEX MYINDEX ON TEST(ID)
- COMMIT
- In an embodiment of the present invention, the system and components of embodiments described herein are implemented using well known computers, such as
computer 702 shown inFIG. 7 . For example, the operation of flowcharts inFIGS. 4A-4C , 5 and 6 can be implemented using computer(s) 702. - The
computer 702 can be any commercially available and well known computer capable of performing the functions described herein, such as computers available from International Business Machines, Apple, Sun, HP, Dell, Compaq, Digital, Cray, etc. - The
computer 702 includes one or more processors (also called central processing units, or CPUs), such as aprocessor 706. Theprocessor 706 is connected to acommunication bus 704. - The
computer 702 also includes a main orprimary memory 708, such as random access memory (RAM). Theprimary memory 708 has stored therein controllogic 728A (computer software), and data. - The
computer 702 also includes one or moresecondary storage devices 710. Thesecondary storage devices 710 include, for example, ahard disk drive 712 and/or a removable storage device or drive 714, as well as other types of storage devices, such as memory cards and memory sticks. Theremovable storage drive 714 represents a floppy disk drive, a magnetic tape drive, a compact disk drive, an optical storage device, tape backup, etc. - The
removable storage drive 714 interacts with aremovable storage unit 716. Theremovable storage unit 716 includes a computer useable or readable storage medium 724 having stored thereincomputer software 728B (control logic) and/or data.Removable storage unit 716 represents a floppy disk, magnetic tape, compact disk, DVD, optical storage disk, or any other computer data storage device. Theremovable storage drive 714 reads from and/or writes to theremovable storage unit 716 in a well known manner. - The
computer 702 also includes input/output/display devices 722, such as monitors, keyboards, pointing devices, etc. - The
computer 702 further includes a communication ornetwork interface 718. Thenetwork interface 718 enables thecomputer 702 to communicate with remote devices. For example, thenetwork interface 718 allows thecomputer 702 to communicate over communication networks or mediums 724B (representing a form of a computer useable or readable medium), such as LANs, WANs, the Internet, etc. Thenetwork interface 718 may interface with remote sites or networks via wired or wireless connections. -
Control logic 728C may be transmitted to and from thecomputer 702 via the communication medium 724B. More particularly, thecomputer 702 may receive and transmit carrier waves (electromagnetic signals) modulated withcontrol logic 730 via the communication medium 724B. - Any apparatus or manufacture comprising a computer useable or readable medium having control logic (software) stored therein is referred to herein as a computer program product or program storage device. This includes, but is not limited to, the
computer 702, themain memory 708,secondary storage devices 710, theremovable storage unit 716 and the carrier waves modulated withcontrol logic 730. Such computer program products, having control logic stored therein that, when executed by one or more data processing devices cause such data processing devices to operate as described herein, represent embodiments of the invention. - The invention can work with software, hardware, and/or operating system implementations other than those described herein. Any software, hardware, and operating system implementations suitable for performing the functions described herein can be used.
- It is to be appreciated that the Detailed Description section, and not the Summary and Abstract sections, is intended to be used to interpret the claims. The Summary and Abstract sections may set forth one or more but not all exemplary embodiments of the present invention as contemplated by the inventor(s), and thus, are not intended to limit the present invention and the appended claims in any way.
- The present invention has been described above with the aid of functional building blocks illustrating the implementation of specified functions and relationships thereof. The boundaries of these functional building blocks have been arbitrarily defined herein for the convenience of the description. Alternate boundaries can be defined so long as the specified functions and relationships thereof are appropriately performed.
- The foregoing description of the specific embodiments will so fully reveal the general nature of the invention that others can, by applying knowledge within the skill of the art, readily modify and/or adapt for various applications such specific embodiments, without undue experimentation, without departing from the general concept of the present invention. Therefore, such adaptations and modifications are intended to be within the meaning and range of equivalents of the disclosed embodiments, based on the teaching and guidance presented herein. It is to be understood that the phraseology or terminology herein is for the purpose of description and not of limitation, such that the terminology or phraseology of the present specification is to be interpreted by the skilled artisan in light of the teachings and guidance.
- The breadth and scope of the present invention should not be limited by any of the above-described exemplary embodiments, but should be defined only in accordance with the following claims and their equivalents.
Claims (15)
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US12/131,679 US9582558B2 (en) | 2008-06-02 | 2008-06-02 | Method and system for data definition language (DDL) replication |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US12/131,679 US9582558B2 (en) | 2008-06-02 | 2008-06-02 | Method and system for data definition language (DDL) replication |
Publications (2)
Publication Number | Publication Date |
---|---|
US20090300075A1 true US20090300075A1 (en) | 2009-12-03 |
US9582558B2 US9582558B2 (en) | 2017-02-28 |
Family
ID=41381106
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US12/131,679 Active 2032-06-06 US9582558B2 (en) | 2008-06-02 | 2008-06-02 | Method and system for data definition language (DDL) replication |
Country Status (1)
Country | Link |
---|---|
US (1) | US9582558B2 (en) |
Cited By (13)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
GB2486914A (en) * | 2010-12-30 | 2012-07-04 | Johannes Hendrik Barnard | Source code control of relational databases |
US20120317076A1 (en) * | 2011-06-07 | 2012-12-13 | Sybase, Inc. | DDL Command Filtering |
US8566280B2 (en) | 2011-05-31 | 2013-10-22 | International Business Machines Corporation | Grid based replication |
US20140304229A1 (en) * | 2012-12-31 | 2014-10-09 | Sybase, Inc. | Continuous Change Data Capture Without Disruption of DDL |
US8924398B2 (en) * | 2011-03-23 | 2014-12-30 | Bmc Software, Inc. | Log-based DDL generation |
US9372855B1 (en) * | 2012-05-22 | 2016-06-21 | Amazon Technologies, Inc. | Transactional control of RDBMS database definition language operations |
US9734190B1 (en) * | 2015-12-07 | 2017-08-15 | Gravic, Inc. | Method of ensuring real-time transaction integrity |
US9922074B1 (en) | 2015-12-07 | 2018-03-20 | Gravic, Inc. | Method of ensuring real-time transaction integrity in the indestructible scalable computing cloud |
CN108427699A (en) * | 2017-09-22 | 2018-08-21 | 平安科技(深圳)有限公司 | The method, apparatus and storage medium of fast initialization system database |
US10394798B1 (en) | 2015-12-07 | 2019-08-27 | Gravic, Inc. | Method of ensuring transactional integrity of a system that includes a first subsystem and a second subsystem |
US10417257B2 (en) * | 2015-10-21 | 2019-09-17 | Oracle International Corporation | Non-blocking database table alteration |
US10452648B1 (en) | 2015-12-07 | 2019-10-22 | Gravic, Inc. | Method of ensuring transactional integrity of a system that includes a plurality of subsystems, one of which takes an action upon a loss of transactional integrity |
CN110597823A (en) * | 2019-09-20 | 2019-12-20 | 四川长虹电器股份有限公司 | Method for supporting online DDL operation of MySQL database |
Citations (26)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5799306A (en) * | 1996-06-21 | 1998-08-25 | Oracle Corporation | Method and apparatus for facilitating data replication using object groups |
US5889674A (en) * | 1994-04-05 | 1999-03-30 | Advanced Micro Devices, Inc. | Method and system for generating product performance history |
US6122640A (en) * | 1998-09-22 | 2000-09-19 | Platinum Technology Ip, Inc. | Method and apparatus for reorganizing an active DBMS table |
US6405212B1 (en) * | 1999-09-27 | 2002-06-11 | Oracle Corporation | Database system event triggers |
US6421677B1 (en) * | 1999-07-19 | 2002-07-16 | International Business Machines Corporation | Extension of data definition language (DDL) capabilities for relational databases for applications issuing DDL statements |
US20030130985A1 (en) * | 2001-12-17 | 2003-07-10 | Volker Driesen | Systems, methods and articles of manufacture for upgrading a database with a shadow system |
US20040098425A1 (en) * | 2002-11-15 | 2004-05-20 | Sybase, Inc. | Database System Providing Improved Methods For Data Replication |
US20050125430A1 (en) * | 2002-08-01 | 2005-06-09 | Oracle International Corporation | Asynchronous actions using flashback |
US20050138160A1 (en) * | 2003-08-28 | 2005-06-23 | Accenture Global Services Gmbh | Capture, aggregation and/or visualization of structural data of architectures |
US20050165858A1 (en) * | 2004-01-09 | 2005-07-28 | Tom Richard W. | Well-known transactions in data replication |
US20050246338A1 (en) * | 2004-04-30 | 2005-11-03 | International Business Machines Corporation | Method for implementing fine-grained access control using access restrictions |
US20050278709A1 (en) * | 2004-06-15 | 2005-12-15 | Manjula Sridhar | Resource definition language for network management application development |
US20050289186A1 (en) * | 2004-06-29 | 2005-12-29 | Microsoft Corporation | DDL replication without user intervention |
US20060047713A1 (en) * | 2004-08-03 | 2006-03-02 | Wisdomforce Technologies, Inc. | System and method for database replication by interception of in memory transactional change records |
US20060069688A1 (en) * | 2004-09-16 | 2006-03-30 | International Business Machines Corporation | Methods and computer programs for database structure comparison |
US7031974B1 (en) * | 2002-08-01 | 2006-04-18 | Oracle International Corporation | Replicating DDL changes using streams |
US7072911B1 (en) * | 2001-07-27 | 2006-07-04 | Novell, Inc. | System and method for incremental replication of changes in a state based distributed database |
US20070005600A1 (en) * | 2005-06-29 | 2007-01-04 | Microsoft Corporation | Security execution context for a database management system |
US20070050391A1 (en) * | 2002-04-12 | 2007-03-01 | Oracle Corporation | Method and apparatus for redefining a group of related objects in a relational database system |
US20070112456A1 (en) * | 2002-04-12 | 2007-05-17 | Ravi Sanka | Electronic device history record and product release system |
US20070130231A1 (en) * | 2005-12-06 | 2007-06-07 | Brown Douglas P | Closed-loop supportability architecture |
US7236993B2 (en) * | 2003-04-16 | 2007-06-26 | Oracle International Corporation | On-demand multi-version denormalized data dictionary to support log-based applications |
US20080034014A1 (en) * | 2006-08-03 | 2008-02-07 | Sybase, Inc. | Replication System with Methodology for Replicating Stored Procedure Calls |
US20080098045A1 (en) * | 2006-10-20 | 2008-04-24 | Oracle International Corporation | Techniques for automatically tracking and archiving transactional data changes |
US20080243936A1 (en) * | 2007-03-30 | 2008-10-02 | Microsoft Corporation | Tracking file system namespace changes during transactions |
US20090125359A1 (en) * | 2007-07-09 | 2009-05-14 | Robert Knapic | Integrating a methodology management system with project tasks in a project management system |
-
2008
- 2008-06-02 US US12/131,679 patent/US9582558B2/en active Active
Patent Citations (27)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5889674A (en) * | 1994-04-05 | 1999-03-30 | Advanced Micro Devices, Inc. | Method and system for generating product performance history |
US5799306A (en) * | 1996-06-21 | 1998-08-25 | Oracle Corporation | Method and apparatus for facilitating data replication using object groups |
US6122640A (en) * | 1998-09-22 | 2000-09-19 | Platinum Technology Ip, Inc. | Method and apparatus for reorganizing an active DBMS table |
US6421677B1 (en) * | 1999-07-19 | 2002-07-16 | International Business Machines Corporation | Extension of data definition language (DDL) capabilities for relational databases for applications issuing DDL statements |
US6405212B1 (en) * | 1999-09-27 | 2002-06-11 | Oracle Corporation | Database system event triggers |
US7072911B1 (en) * | 2001-07-27 | 2006-07-04 | Novell, Inc. | System and method for incremental replication of changes in a state based distributed database |
US20030130985A1 (en) * | 2001-12-17 | 2003-07-10 | Volker Driesen | Systems, methods and articles of manufacture for upgrading a database with a shadow system |
US20070112456A1 (en) * | 2002-04-12 | 2007-05-17 | Ravi Sanka | Electronic device history record and product release system |
US20070050391A1 (en) * | 2002-04-12 | 2007-03-01 | Oracle Corporation | Method and apparatus for redefining a group of related objects in a relational database system |
US7031974B1 (en) * | 2002-08-01 | 2006-04-18 | Oracle International Corporation | Replicating DDL changes using streams |
US20050125430A1 (en) * | 2002-08-01 | 2005-06-09 | Oracle International Corporation | Asynchronous actions using flashback |
US20040098425A1 (en) * | 2002-11-15 | 2004-05-20 | Sybase, Inc. | Database System Providing Improved Methods For Data Replication |
US7236993B2 (en) * | 2003-04-16 | 2007-06-26 | Oracle International Corporation | On-demand multi-version denormalized data dictionary to support log-based applications |
US20050138160A1 (en) * | 2003-08-28 | 2005-06-23 | Accenture Global Services Gmbh | Capture, aggregation and/or visualization of structural data of architectures |
US20070143375A1 (en) * | 2004-01-09 | 2007-06-21 | Microsoft Corporation | Well-known transactions in data replication |
US20050165858A1 (en) * | 2004-01-09 | 2005-07-28 | Tom Richard W. | Well-known transactions in data replication |
US20050246338A1 (en) * | 2004-04-30 | 2005-11-03 | International Business Machines Corporation | Method for implementing fine-grained access control using access restrictions |
US20050278709A1 (en) * | 2004-06-15 | 2005-12-15 | Manjula Sridhar | Resource definition language for network management application development |
US20050289186A1 (en) * | 2004-06-29 | 2005-12-29 | Microsoft Corporation | DDL replication without user intervention |
US20060047713A1 (en) * | 2004-08-03 | 2006-03-02 | Wisdomforce Technologies, Inc. | System and method for database replication by interception of in memory transactional change records |
US20060069688A1 (en) * | 2004-09-16 | 2006-03-30 | International Business Machines Corporation | Methods and computer programs for database structure comparison |
US20070005600A1 (en) * | 2005-06-29 | 2007-01-04 | Microsoft Corporation | Security execution context for a database management system |
US20070130231A1 (en) * | 2005-12-06 | 2007-06-07 | Brown Douglas P | Closed-loop supportability architecture |
US20080034014A1 (en) * | 2006-08-03 | 2008-02-07 | Sybase, Inc. | Replication System with Methodology for Replicating Stored Procedure Calls |
US20080098045A1 (en) * | 2006-10-20 | 2008-04-24 | Oracle International Corporation | Techniques for automatically tracking and archiving transactional data changes |
US20080243936A1 (en) * | 2007-03-30 | 2008-10-02 | Microsoft Corporation | Tracking file system namespace changes during transactions |
US20090125359A1 (en) * | 2007-07-09 | 2009-05-14 | Robert Knapic | Integrating a methodology management system with project tasks in a project management system |
Non-Patent Citations (1)
Title |
---|
"Using Triggers to Track Databse Action History" by Tal Kelley. Published Wednesday, September 19, 2001. http://www.4guysfromrolla.com/webtech/091901-1.shtml * |
Cited By (21)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
GB2486914A (en) * | 2010-12-30 | 2012-07-04 | Johannes Hendrik Barnard | Source code control of relational databases |
US8924398B2 (en) * | 2011-03-23 | 2014-12-30 | Bmc Software, Inc. | Log-based DDL generation |
US8566280B2 (en) | 2011-05-31 | 2013-10-22 | International Business Machines Corporation | Grid based replication |
US8862544B2 (en) | 2011-05-31 | 2014-10-14 | International Business Machines Corporation | Grid based replication |
US20120317076A1 (en) * | 2011-06-07 | 2012-12-13 | Sybase, Inc. | DDL Command Filtering |
US9372855B1 (en) * | 2012-05-22 | 2016-06-21 | Amazon Technologies, Inc. | Transactional control of RDBMS database definition language operations |
US20140304229A1 (en) * | 2012-12-31 | 2014-10-09 | Sybase, Inc. | Continuous Change Data Capture Without Disruption of DDL |
US9305070B2 (en) * | 2012-12-31 | 2016-04-05 | Sybase, Inc. | Continuous change data capture without disruption of DDL |
US10417257B2 (en) * | 2015-10-21 | 2019-09-17 | Oracle International Corporation | Non-blocking database table alteration |
US9996578B1 (en) | 2015-12-07 | 2018-06-12 | Gravic, Inc. | Method of ensuring near real-time transaction integrity with rollback of committed transaction upon detection of incorrect transaction processing after the commit |
US9922074B1 (en) | 2015-12-07 | 2018-03-20 | Gravic, Inc. | Method of ensuring real-time transaction integrity in the indestructible scalable computing cloud |
US10013452B1 (en) | 2015-12-07 | 2018-07-03 | Gravic, Inc. | Method of ensuring transactional integrity of a new subsystem that is added to a system that includes a trusted subsystem |
US10095730B1 (en) | 2015-12-07 | 2018-10-09 | Gravic, Inc. | Apparatus for ensuring real-time transaction integrity in the indestructible scalable computing cloud |
US10152506B1 (en) | 2015-12-07 | 2018-12-11 | Gravic, Inc. | Method of ensuring real-time transaction integrity |
US10394798B1 (en) | 2015-12-07 | 2019-08-27 | Gravic, Inc. | Method of ensuring transactional integrity of a system that includes a first subsystem and a second subsystem |
US9734190B1 (en) * | 2015-12-07 | 2017-08-15 | Gravic, Inc. | Method of ensuring real-time transaction integrity |
US10452648B1 (en) | 2015-12-07 | 2019-10-22 | Gravic, Inc. | Method of ensuring transactional integrity of a system that includes a plurality of subsystems, one of which takes an action upon a loss of transactional integrity |
US10706040B1 (en) | 2015-12-07 | 2020-07-07 | Gravic, Inc. | System for ensuring transactional integrity thereof that includes a plurality of subsystems, one of which takes an action upon a loss of transactional integrity |
CN108427699A (en) * | 2017-09-22 | 2018-08-21 | 平安科技(深圳)有限公司 | The method, apparatus and storage medium of fast initialization system database |
WO2019056707A1 (en) * | 2017-09-22 | 2019-03-28 | 平安科技(深圳)有限公司 | Method and device for quickly initializing system database and storage medium |
CN110597823A (en) * | 2019-09-20 | 2019-12-20 | 四川长虹电器股份有限公司 | Method for supporting online DDL operation of MySQL database |
Also Published As
Publication number | Publication date |
---|---|
US9582558B2 (en) | 2017-02-28 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US9582558B2 (en) | Method and system for data definition language (DDL) replication | |
US9720994B2 (en) | Replicated database structural change management | |
EP2641191B1 (en) | Grid computing system alongside a distributed database architecture | |
US7707183B2 (en) | Methods, systems and computer program products for monitoring changes to objects on a computer system using information models and baselines | |
CN106991035A (en) | A kind of Host Supervision System based on micro services framework | |
CN102968374B (en) | A kind of data warehouse method of testing | |
US9679037B2 (en) | System and method for implementing database replication configurations using replication modeling and transformation | |
US20140195602A1 (en) | System and method of employing a client side device to access local and remote data during communication distruptions | |
US9998544B2 (en) | Synchronization testing of active clustered servers | |
EP3707635B1 (en) | Online determination of result set sensitivity | |
US11797495B2 (en) | Simulating data definition triggers in a database system | |
US9047354B2 (en) | Statement categorization and normalization | |
JP2024026143A (en) | Switching to eventually consistent database replication | |
CN112231654A (en) | Operation and maintenance data isolation method and device, electronic equipment and storage medium | |
CN110119422B (en) | Data processing system and equipment for data warehouse of micro-credit tenant | |
US11657168B2 (en) | Error detection of data leakage in a data processing system | |
US20040181510A1 (en) | System and method for cooperative database acceleration | |
US11782953B2 (en) | Metadata access for distributed data lake users | |
EP3117584A1 (en) | Client side device accessing data during communication distruptions | |
Kalmukov et al. | Hadoop as a Service: Integration of a Company’s Heterogeneous Data to a Remote Hadoop Infrastructure | |
US20210374119A1 (en) | Data update apparatus and data update method | |
Prusinski et al. | Expert Oracle GoldenGate | |
US20230229582A1 (en) | Information processing apparatus, processing method for information processing apparatus, and storage medium | |
Mehrabani | MongoDB High Availability | |
Ward | The Meat and Potatoes of SQL Server |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
AS | Assignment |
Owner name: SYBASE, INC.,CALIFORNIA Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:GUAN, RUIFENG;YANG, QI;REEL/FRAME:021150/0522 Effective date: 20080526 Owner name: SYBASE, INC., CALIFORNIA Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:GUAN, RUIFENG;YANG, QI;REEL/FRAME:021150/0522 Effective date: 20080526 |
|
FEPP | Fee payment procedure |
Free format text: PAYOR NUMBER ASSIGNED (ORIGINAL EVENT CODE: ASPN); ENTITY STATUS OF PATENT OWNER: LARGE ENTITY |
|
STCF | Information on status: patent grant |
Free format text: PATENTED CASE |
|
MAFP | Maintenance fee payment |
Free format text: PAYMENT OF MAINTENANCE FEE, 4TH YEAR, LARGE ENTITY (ORIGINAL EVENT CODE: M1551); ENTITY STATUS OF PATENT OWNER: LARGE ENTITY Year of fee payment: 4 |