重置SQL Remote消息

The Extraction Utility

For instructions on using the extraction utility, please refer to the following section of the documentation:

Replication and Syncronization Guide
PART 3. SQL Remote Administration
CHAPTER 17. Deploying and Synchronizing Databases
Using the extraction utility
URL: http://manuals.sybase.com:80/onlinebooks/group-sas/awg0700e/dbrsen7/@Generic__BookView
 
 

Appendix B - Data Reconciliation Using Proxy Tables

The purpose of reconciling data is to provide a fixed point in time at which the data content in a consolidated database agrees with the data content in a remote database. Once this fixed point in time is defined, replication can be initiated. The choice of a reconciliation method needs to be made considering the completeness of the reconciliation and the effort that is required to complete the process. One possible approach to reconciling the data is to use proxy tables. It should be emphasized that this is not the only possible technique for reconciling data between replicated databases. Any procedure that creates a consistent image of the data, on both the consolidated and remote database(s), is valid.
In order to reconcile the data using proxy tables, you must have a usable copy of the databases from both the remote and consolidated nodes. This technique does not require that you have a copy of the log file from either node. The drawback of using this technique is that you need to reconcile inserts, updates, and deletes independently. You will also require the involvement of the data owner(s) to determine which rows and/or column values should be considered correct.
A proxy table allows a table in a separate database to be viewed and manipulated as if it was part of the database you were currently connected to. By defining proxy tables to connect your copy of the remote database to the consolidated database, you can use SQL statements to compare the rows that in remote and consolidated databases.
For more information on proxy tables, please refer to the following section of the documentation:
ASA User's Guide
PART 5. Database Administration and Advanced Use
CHAPTER 29. Accessing Remote Data
Basic concepts
Remote table mappings
URL: http://manuals.sybase.com:80/onlinebooks/group-sas/awg0700e/dbugen7/@Generic__BookView

Identifying Rows to be Reconciled

Once you have defined the proxy tables linking the two databases, you can perform select statements to compare the primary keys of rows in the remote database to the rows in the consolidated database. Consider the following example:

Consolidated Database Table_1 Row_ID Row_Text 1 Value1 2 Value2_cons 4 Value4

Remote Database Table_1 Row_ID Row_Text 1 Value1 2 Value2_remote 3 Value3

If Table_1 in the Remote Database is configured, on the consolidated database, as a proxy table with the name Proxy_Table_1, then a select statement to identify the rows that exist in the remote database but not the consolidated database would be:
SELECT PT1.Row_ID FROM Proxy_Table_1 PT1
WHERE PT1.Row_ID NOT IN (SELECT T1.Row_ID FROM Table_1 T1);

A similar select statement to identify the rows that exist in the consolidated database but not the remote database would be:
SELECT T1.Row_ID FROM Table_1 T1
WHERE T1.Row_ID NOT IN (SELECT PT1.Row_ID FROM Proxy_Table_1 PT1);

Select statements such as the two shown above allow you do identify rows which exist at one but not both of the nodes that you are reconciling. This provides you with the basic information that you need to reconcile inserts and deletes between the two nodes. The final set of rows that you need to identify is the set of rows which have been updated at one node or other and for which the updates had not yet replicated. Using the data in the sample tables above a select statement to determine which rows require reconciliation of updates would be:

SELECT T1.Row_ID, T1.Row_Text AS cons_row_text, PT1.Row_Text AS remote_row_text
 FROM Table_1 T1,  Proxy_Table_1 PT1
WHERE T1.Row_ID = PT1.Row_ID
     AND T1.Row_Text != PT1.Row_Text;

*Note that in practice, you will have to include restriction criteria based on your publication and subscription definitions when performing the comparisons. In the above example, the publication specified the entire table for simplicity.

At this point you are able to identify all of the rows that require some form of reconciliation. You still need to determine the correct action to be taken with each row. If a row exists at the remote node and the not the consolidated node then there are 2 possible scenarios:

1) The row was recently inserted at the remote node and the insert has not yet replicated up to the consolidated node.
OR
2) The row was recently deleted at the consolidated node and the delete has not yet replicated down to the remote node.

Similarly, there is no arbitrarily correct way to reconcile updates.
The correct reconciliation will depend on the business rules of your SQL Remote set up. When reconciling inserts and deletes it is important to know at which nodes users are allowed to insert and delete rows from each table. One common scenario would be for rows in specific tables to be inserted only at the consolidated node or only at the remote nodes. If the business rules governing data manipulation and movement are understood, then you will be able to determine which differences represent inserts as opposed to updates.
One source of information on the existing business rules for handling updates is your conflict resolution triggers. Conflict resolution triggers only fire when update conflicts are detected while SQL Remote is applying updates. They will not automatically resolve conflicts in a recovery situation. Keep in mind that if you have not defined a conflict resolution trigger then the default behavior is that the last operation to be executed on the consolidated database "wins". The translation of this behavior is that if you have not defined a conflict resolution trigger, then you do not have a preference as to which version of a row is kept.
For more information on how SQL Remote w

上一页  [1] [2] [3] [4]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18