I've been testing a mirror system set up in the usual manner which is working really well. The one problem I'm having is that for legacy reasons I'm using some "shadow tables" to do pseudo application-side auto inc columns. Each shadow table has no rows but the application uses the GET_IDENTITY function to get the next id in the sequence. The incrementing number is not being replicated to the mirror which is causing some problems.
The following example shows how it occurs:
Any help would be appreciated. I can write a procedure to correct the Ids when the Db starts up but that doesn't feel like a very good solution. I could set a trigger to insert/update or delete a row from the shadow table to make it replicate but that doesn't feel right either.
Thanks for your help.
SQL Anywhere 11 220.127.116.1137
I've added this line to the function that returns the shadow table id
This forces the table to update and thus send the data to the mirror at the next checkpoint (or right away if I COMMIT it). It keeps one row in each Shadow Table continually updated with the last value returned by GET_IDENTITY. I might work on the slightly clunky query though, even though there will only ever be one row in those tables so it shouldn't slow it down too much.
It's working great even in situation where the primary and secondary are going up and down repeatedly.
Thanks to Breck and Volker for clearing up how this all works.
Something about how triggers don't replicate....
Write the data to a local table first, then code an event that fires frequently which copies the data to your replicated table.
answered 23 Dec '14, 09:22