Database Replication/Synchronization Issue

[Former Member]

Hello!

I am currently monitoring database replication between a consolidated database and several remote databases as well as performing basic database administration.  Back in November 2014, an issue occurred on the database server (owned by our customer) which resulted in the server being rolled back to a snapshot however prior to the server being reverted, a database replication event had occurred.  As a result, the consolidated is no longer in sync with the remote databases.  Previous database administrators are no longer employed with the company.  How would I go about getting assistance for this issue?  I have tried calling support however they require license information which I do not have.  We would like to have an expert assist us (either in person or remotely) with resolving this issue.

A little background information:

I am a subcontractor performing basic database administration duties on customer-owned servers.  Although I was tasked to perform these duties, I am not a database administrator in the true sense of the title since I do not have the skillset to perform in-depth database work nor am I familiar with dealing with logs, creating complex queries and such.  The product is Sybase SQL Anywhere 11 on the consolidated and Sybase 9 on the remote databases. I do not have any license/purchaser information.  Products were purchased and installed prior to me being tasked to perform database administration duties.

Any advice would be greatly appreciated.

Thank you and have a great day!

Norlito Quinto

RRMNA

Reston, VA

[Former Member · Accepted solution]

need a bit more clarification was the database rolled back at the remote or the consolidated database?

either way your offsets are not in sync and if it was the remote then you have some log problems.

for your scenario I would change the replication offset at the remote and consolidated databases to match.

at the remote site you can run dbtran many times on the log files to get some offsets, also make sure your log files match up from one to the next all beginning and ending offsets matching ... any logs not part of the sequence need to be moved to another directory and not a sub directory(you will probably have one or two logs off due to the replication and reverting back).  once you have an offset picked to set it to you will need to set the remote and the consolidated.  you can look at the log back when it was replicating to get the commands to set the offset on the remote database it will be something like this.

call sa_sync_sub( 1, '4ad600f2-aaaa-bbbb-cccc-6de6fe73430b:test', 'SET PROGRESS', '60984435111' );

call sa_sync_sub( 1, '4ad600f2-aaaa-bbbb-cccc-6de6fe73430b:test', 'SET LOG_SENT', '60984435111' );

the consolidated database is just a record update in I believe the table is ml_subscription, you will need to figure out the rid remote site look for the offset.

also you can run replication with (verbose) -v+ that may give more information on what is going on.

[Former Member · Accepted solution]

Hi and thanks for your response!  The rollback occurred on the consolidated database and you are right...the offsets are not in sync (what I've been told). 

Thanks for your suggestions but I believe they may be beyond my current skillset.  Your suggestions definitely make sense (from what I can understand anyways) and I can follow directions well however I'm a little hesitant to try anything on the database now for fear of making things worse. Is this a problem that can be resolved remotely or will we have to visit all the remote databases as well?  We have had someone with relatively good knowledge of Sybase database replication attempt to tweak the offsets but unfortunately, it did not work and this person had very limited time to work with us so we're no better off than when the problem first occurred. 

Thanks again for responding!

[former_member188493]

Are you using MobiLink or SQL Remote (dbmlsync.exe or dbremote.exe running on the remotes)?

[Former Member]

Hi and thanks for your response!  We are using dbremote on the remotes.

[former_member188493]

It depends on the application, but in many SQL Remote setups the response to broken synchronization is to start over with the remote database: Throw away the existing remote database, and run the extract process on the consolidated database to recreate it.

That won't work if the remote database contains critical data that does not participate in synchronization and/or will not be recreated on the remote by the extract process.

But if the remote database is entirely a subset of data on the consolidated database, re-extract is the generally the way to go... trying to repair a broken sync setup is fraught with error.