FWH 18.11: MARIAROWSET Table Replication Features

[nageswaragunupudi]

FWH 18.11 introduces simple means to replicate changes (edit,append and delete) effected to a table through fwmariarowset object to another remote server, without any effort from the programmer's side. This feature is useful when it is intended to replicate changes to one or more tables to the tables with the same names on another remote server.

It is necessary that the remote server already has identical tables with identical structures and records with the same names. This can be setup initially by using

Code: Select all  Expand view  RUN

oCn:CopyTableToServer( cTable, oRemoteServer )
 

Using the replication feature:

Open connections to main server and also replication server.

Code: Select all  Expand view  RUN

oCn := maria_connect( <mainserver>, .... )
oRemote := maria_connect( <remote server>, ... )
 

Open the rowset for the table to edit in the usual way.

Code: Select all  Expand view  RUN

oRs := oCn:RowSet( <mytable> )
 

The only additional work we need to do for the replication is to assign oRs:oCn2 with the connection to remote server

Code: Select all  Expand view  RUN

oRs:oCn2 := oRemote
 

This is enough.
All changes made to <mytable> through the methods of RowSet are automatically written simultaneously to the same table on the remote server also.

Conditions:
Do not change the table with your own SQL statements.
Do not directly modify the replicated tables on the remote server.

\fwh\samples\mariarpl.prg

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oMain    := FW_DemoDB()
   local oRepl    := FW_DemoDB( 6 )
   local oRsMain, oRsRepl
   local oDlg, oFont, oBold, oBrwMain, oBrwRepl

   SetGetColorFocus()

   oRsMain        := oMain:RowSet( "states" )
   oRsMain:oCn2   := oRepl
   oRsRepl        := oRepl:RowSet( "states", , .t. ) // readonly

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   oBold    := oFont:Bold()

   DEFINE DIALOG oDlg SIZE 900,500 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "FWH 18.11 : TABLE REPLICATION"

   @ 20, 20 XBROWSE oBrwMain SIZE 425,-20 PIXEL OF oDlg ;
      DATASOURCE oRsMain ;
      AUTOCOLS CELL LINES NOBORDER

   @ 20,455 XBROWSE oBrwRepl SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRsRepl ;
      AUTOCOLS CELL LINES NOBORDER

   WITH OBJECT oBrwMain
      :SetGroupHeader( "MAIN SERVER", 1, 3, oBold )
      :nEditTypes    := EDIT_GET
      :bOnChanges    := { || oRsRepl:ReSync(), oBrwRepl:RefreshCurrent() }
      :lColChangeNotify := .t.
      :bChange       := { || oBrwRepl:BookMark := oBrwMain:BookMark, ;
                             oBrwRepl:nRowSel  := oBrwMain:nRowSel,  ;
                             oBrwRepl:nColSel  := oBrwMain:nColSel,  ;
                             oBrwRepl:Refresh() }
      //
      :CreateFromCode()
   END

   WITH OBJECT oBrwRepl
      :SetGroupHeader( "REPLICATION SERVER", 1, 3, oBold )
      :bClrSel       := { || { CLR_WHITE, CLR_GREEN } }

      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont, oBold

   oRsMain  := nil
   oRsRepl  := nil

   oMain:Close()
   oRepl:Close()

return nil
 

Note: It is not necessary to open the table on the remote server. This is opened in the above sample to demonstrate simultaneous changes to the remote table.

[shri_fwh]

Dear Rao Sir ,

Just wanted know if in case Replication sever is off/failed and after some time when it becomes live then how it is replicated the in between changes ? OR we need to manually runs any jobs to update ? OR may be some other way work around ?


Thanks
Shridhar

[nageswaragunupudi]

If the write to replication server fails, the transaction is rolled back on the main server too. Thereby it is ensured that either the transaction is written to both servers or none at all.

In the case of single server, nothing can be written if that server is off. Same way in the case of replicated rowset, nothing can be written if both the servers are not working, i.e., if one of the servers is off.

Replication can be turned off by setting oRs:oCn2 to nil. Then the transactions are written to the main server only. Subsequent resumption of replication results in totally unreliable results. Only way is to copy the entire table again from the main server to the replication server and resume replication.

[shri_fwh]

Dear Rao Sir ,

Excellent feature...!

If the write to replication server fails, the transaction is rolled back on the main server too. Thereby it is ensured that either the transaction is written to both servers or none at all.

Any Function that does truncate all the tables of replication server and copy all table's data from Main to Replication ?

Then the transactions are written to the main server only. Subsequent resumption of replication results in totally unreliable results. Only way is to copy the entire table again from the main server to the replication server and resume replication.

Thanks
Shridhar

[nageswaragunupudi]

Use

Code: Select all  Expand view  RUN

oMainCn:CopyTableToServer( cTable, oRemoteCn )
 

This will overwrite the remote table with the present table on the main server and makes both the tables identical.
The time taken depends on the size of the table.

[nageswaragunupudi]

We have added a new method

Code: Select all  Expand view  RUN

UpdateTableToServer( cTable, oDestServer, cTimeStampFld ) --> lSuccess
 

If the table contains a timestamp field which is updated everytime a row is updated or inserted, this method updates the destination table with the latest changes from the table on the current server. This is a faster way to update the destination table after failure or discontinuation of replication.

Please note that it is easy to create such a timestamp field using datatype "=" using oCn:CreateTable() method instead of creating the table with your own sql.

[nageswaragunupudi]

We request interested users of FWH 18.11 to put this feature to intensive test and provide feedback and suggestions to help us to improve this feature.

[shri_fwh]

Dear Rao Sir ,

Good Idea Data Sync On TimeStamp Based ..!

Code: Select all  Expand view  RUN

UpdateTableToServer( cTable, oDestServer, cTimeStampFld ) --> lSuccess

Will Replication manage automatically, if Table Structure columns count is diff between source and target ? e.g. source table has extra column compare to target table

Thanks
Shridhar

[Maurizio]

Hello RAO
1) I am testing Table Replication Features.
If I make changes in the xbrowse oRsMain all works ok .
But if at the same time I make changes to the oRsMain table with another program or procedure the oRsRepl table
it is not updated.
So this function only works if I have the 2 tables open in the same function?

2) I Try the function UpdateTableToServer( cTable, oDestServer, cTimeStampFld ) --> lSuccess

I modify my table ans create a field TIMESTAMP
ALTER TABLE arti ADD modificato_il TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

when I use
UpdateTableToServer( 'arti', oDestServer, 'modificato_il' )

In the oRsRepl only the field 'modificato_il' change ,the other fields are not updated



Regards Maurizio

[nageswaragunupudi]

1) You have to keep both servers open.
2) When you open a table on the main server, you need to set oRs:oReplServer := <replserverconnection>

The corresponding table on the replication server should be identical with the main table.

Then any changes made to the main table will be replicated to the same table on the replication server.
This is the functionality we attempted to create.

Please test and share your observations.

[Maurizio]

RAO
1 ) Ok , i check

can you answer me for point 2 ?
2) I Try the function UpdateTableToServer( cTable, oDestServer, cTimeStampFld ) --> lSuccess

I modify my table ans create a field TIMESTAMP
ALTER TABLE arti ADD modificato_il TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

when I use
UpdateTableToServer( 'arti', oDestServer, 'modificato_il' )

In the oRsRepl only the field 'modificato_il' change ,the other fields are not updated

Thanks

Maurizio

[reinaldocrespo]

Hello Mr. Rao, hello everyone else, and my apologies for being absent for so long;

Let me suggest a different approach at achieving replication:

1. have a way to declare a "publication". A publication describes what tables & columns are to be replicated. A single database could potentially contain various publications with different sets of tables to be replicated.

2. have a way to declare a "subscription". A subscription describes what servers are to receive a publication. A single database could potentially have various subscriptions.

3. Any insert/delete/update to a "publicated" table executes some code via a table trigger that writes to a queue an operation to be executed on any subscription that contains this "publicated" table.

4. Now have a windows service running on the server that takes care of connecting to all subscriptions to post all pending transactions on the queue. In this manner it doesn't matter if the replication server is up or down or connection has been lost. If a target database is not available, the source server will maintain updates in a queue and transmit them to the target when it becomes available.

***Keep in mind that connection to remote servers are bound to be broken at some point. Therefore, unless replication is impelled in such way that allows for interruptions, this replication attempt becomes futile.

***Also, because it is a table trigger that writes to a queue; it doesn't matter if changes to a table are done with or without our applications. Any 3rd party app making changes to the table will pull the trigger.

This is how Advantage implements replication. It works flawlessly. I have been using it since 2004. For example; in heavy traffic servers with hundreds of users, I like to install a "reporting" server. That's a server that only servers SQL selects for reports that queries millions of records and you have accountants constantly querying data. You can have one server replicating to many servers or you can also have many servers replicating to one single "central" server. It doesn't matter if internet connection is lost. Once connection is back, replication server is updated and synched in just a few seconds.

Just an idea stolen from ADS.


Reinaldo.