Updating a replicating database from ASA10 to 17.

[Former Member]

My company is planning to upgrade from SQL Anywhere 10 to 17, and I recently did a test where I rebuilt the consolidated database in version 17, and attempted to see if it could continue replicating against version 10 remotes. Not only did it refuse to accept message files from the version 10 remotes in dbremote (it stated there was a missing message for all the remotes that sent), the consolidated was unable to send, complaining that a transaction log was missing. I translated and examined all of the transaction logs, and everything looked at it should, nothing appeared to be missing.

Am I missing something about how to upgrade a replicating database between versions? I recently did a full rebuild of the same consolidated database in version 10 (to shrink it) and it had no problem replicating after.

[JimDiaz]

We have moved from 9 to 12 and 12 to 16 without issue. I'm not sure 17 is ready for prime time yet from what I understand there was no beta. Thanks for the post it will be interesting to see if there is a compatibility issue. Also check out the posts on passthrough issues when moving to new versions, I'm not sure if it effects 10 but when going from 9 to 12 we were not able to convert all remotes simultaneously and was forced to wrap all passthrough statement inside an execute immediate procedure so 12 would not change the syntax.

Good Luck,

Jim

[regdomaratzki]

Version 17 is ready for prime time.

Can you please explain exactly how you upgraded the consolidated database from v10 to v17?

Thanks, Reg

[Former Member]

I followed the following steps (my own notes), this set of steps was used to successfully rebuild the same database in order to shrink it in ASA10 a month ago without effecting replication.

Copy the database and active log to the "olddb" directory.

Copy all other logs to a "logs" directory.

Run dbtran against the active log of the db to find the ending offset F:>"C:\\Program Files\\SQL Anywhere 17\\Bin64\\dbtran.exe" -ek <key> F:\\T ESTING\\MOTHER\\olddb\\db.log SQL Anywhere Log Translation Utility Version 17.0.0.1359 Transaction log "F:\\TESTING\\MOTHER\\olddb\\db.log" starts at offset 01218889486281 100% complete Transaction log ends at offset 01218889490423

Rename the active log so it isn't modified during the rebuild, then place it in the directory logs with the other backup logs. F:\\TESTING\\MOTHER\\olddb>rename db.log 160601AJ.LOG When this is done, only the db file should remain in the olddb folder.

Rebuid the database (remove line breaks): F:\\TESTING\\MOTHER\\olddb>"C:\\Program Files\\SQL Anywhere 17\\Bin64\\dbunload.exe" -c "UID=DBA;PWD=SQL;Autostop=YES;dbkey=<key>;DBF=F:\\TESTING\\MOTHER\\olddb \\db.db" -an "F:\\TESTING\\MOTHER\\newdb\\db.db"

Erase the log created for the new database.

Use dblog to set the offset on the new database to the one saved from step 4:

F:\\TESTING\\MOTHER\\newdb>"C:\\Program Files\\SQL Anywhere 17\\Bin64\\dblog.exe" -ek <key> -x 0 -z 01218889490423 db.db SQL Anywhere Transaction Log Utility Version 17.0.0.1359 "db.db" is using log file "db.log" "db.db" is using no log mirror file Transaction log starting offset is 01218889490423 SQL Remote truncation offset is 0015164592 Transaction log current relative offset is 0000000000

Now start the database in order to generate the db.log.

Stop the database. In the directory C:\\db Server\\db, combine the contents of newdb and logs together, then take everything from db, except the main log and the database, and copy into backups.

Start the service, and then run one or two hourly backups.

Grab the backups you just ran, and the several from before that (before the rebuild). Translate them all, then compare the offsets. The should all match up like normally, with no gaps or overlaps in the offsets.

I did the above and nothing seemed wrong. After the send failed, I translated all of the backup logs and the offsets all matched where they should, no missing logs..

[VolkerBarth]

Hm, while I think the cited steps are correct according to that description for a manual rebuild, you might try to use the simpler, one-step rebuild with option -ar.

As to the missing messages: Can you confirm that the contents of SYSREMOTEUSER is the same for the old v10 and the rebuilt v17 database? Could it be the remotes have replicated with the real database inbetween, and that explains the missing messages as seen by the new database?

FWIW, we are using some v17 remotes with a v12 consolidated with no problems so far - except that one fixed with the latest v17 EBF 17.0.4.2053...

[regdomaratzki]

Like Volker, those steps look OK, although I haven't followed them step by step. You've ensured access to the renamed logs, and ran dblog to zap the transaction log offset of the newly rebuilt database.

1) Is it safe to assume that you did not change the dbkey during the upgrade? I believe that's a safe assumption, given the commands you listed, but just wanted to double check that you hadn't left out a step since you clearly don't want to post a database key to the forum.

2) Can you please post the content of the SYS.SYSHISTORY table of the v10 database you are upgrading (F:\\TESTING\\MOTHER\\olddb\\db.db) ? If you don't want to post to the forum, you can e-mail me the results. My e-mail address is simply [firstname].[lastname]@sap.com, where you clearly use my real first and last name.

Thanks, reg.domaratzki (easier to cut-and-paste)

[regdomaratzki]

In case anyone at home/work is following along and wondering why when they use dbunload -ar they are getting a very non-helpful error saying :

Unable to open database file "C:\\full\\path\\cons.db" - - C:\\full\\path\\cons.db no database specified

it's becuase of a bug that has existed since version 11. I'm in the process of submitting a fix. You will run into the problem if all the following are true :

1) You are using dbunload -ar.

2) The major verion of dbunload is "X".

3) The format of the database file being unloaded is less than version "X".

4) The database has been involved in replication or synchronization.

5) The database has been encrypted.

I'll post back on the forum with the build numbers in v16 and v17 when I've submitted the fix (and a new test to catch this in the future).

Reg

[regdomaratzki]

Tim, thanks for responding by e-mail with the contents of the SYSHISTORY table. I was mostly curious whether the database had been intialized in version "X" and then possibily only dbupgraded to version 10, but the SYSHISTORY table shows that the database was created with v10.

However, you did not answer whether the dbkey changed during the upgrade. That would be the simplest answer to the problem you're having, since dbremote would not be able to open and read the old log files that used a different dbkey (the dbkey is case sensitive), resulting in "missing transaction log" error. I would like to confirm this isn't the case before spending any more time on the issue.

Thanks, Reg

[Former Member]

Reg

The encryption key was not changed for the rebuild, I kept the same key as for the version 10 database. I'm still able to connect to the db with that key so I don't think that's the issue.

I'm attempting to repeat the test to see if I get the same result, unfortunately that takes a couple of days on the equipment I have available.

[VolkerBarth]

Just to understand: The OP has used dbunload -an in his code - does the error apply to that as well?

[Former Member]

I just finished completely re doing the entire test and got the exact same result. All appears fine with the logs, the offsets match up, but when I try sending, It claims to be missing data between the very oldest log and the very newest:

I. 2016-06-09 11:17:59. SQL Remote Message Agent Version 17.0.0.1359
I. 2016-06-09 11:17:59. 
I. 2016-06-09 11:17:59. Copyright © 2015 SAP SE or an SAP affiliate company.
I. 2016-06-09 11:17:59. All rights reserved.
I. 2016-06-09 11:17:59. Use of this software is governed by the SAP Software Use Rights Agreement.
I. 2016-06-09 11:17:59. Refer to http://global.sap.com/corporate-en/our-company/agreements/index.epx.
I. 2016-06-09 11:17:59. 
I. 2016-06-09 11:17:59. 1: -ek
I. 2016-06-09 11:17:59. 2: ***************
I. 2016-06-09 11:17:59. 3: -c
I. 2016-06-09 11:17:59. 4: *******************************************
I. 2016-06-09 11:17:59. 5: -s
I. 2016-06-09 11:17:59. 6: -t
I. 2016-06-09 11:17:59. 7: -l
I. 2016-06-09 11:17:59. 8: 500k
I. 2016-06-09 11:17:59. 9: -qc
I. 2016-06-09 11:17:59. 10: -u
I. 2016-06-09 11:17:59. 11: -m
I. 2016-06-09 11:17:59. 12: 512M
I. 2016-06-09 11:17:59. 13: -v
I. 2016-06-09 11:17:59. 14: -o
I. 2016-06-09 11:17:59. 15: C:\\scripts_directory\\dbremotelog_cons_send.txt
I. 2016-06-09 11:17:59. 16: C:\\db Server\\backups
I. 2016-06-09 11:18:00. Scanning logs starting at offset 01219341884480
I. 2016-06-09 11:18:00. Transaction log "F:/TESTING/MOTHER/db/db.log" starts at offset 01219374878507
I. 2016-06-09 11:18:00. Processing transaction logs from directory "C:\\db Server\\backups"
I. 2016-06-09 11:18:00. Transaction log "C:\\db Server\\backups\\160530AN.LOG" starts at offset 01218654613708
I. 2016-06-09 11:18:00. Processing transactions from transaction log "C:\\db Server\\backups\\160530AN.LOG"
I. 2016-06-09 11:18:01. Transaction log ends at offset 01218657434244
E. 2016-06-09 11:18:01. Missing transaction log(s) in between file "C:\\db Server\\backups\\160530AN.LOG" (ending at offset 01218657434244) and file "C:\\db Server\\backups\\160609AA.LOG" (starting at offset 01219374870104).
I. 2016-06-09 11:18:01. We are looking for a transaction log file starting at offset 01218657434244.
E. 2016-06-09 11:18:01. Sending messages failed
I. 2016-06-09 11:18:01. Execution completed

I am able to translate the first 2 logs (last time I translated and checked offsets on ALL logs and nothing was wrong) as shown below:

Transaction log "160530AN.LOG" starts at offset 01218654613708 100% complete Transaction log ends at offset 01218657434244 Transaction log "160530AO.LOG" starts at offset 01218657434244 100% complete Transaction log ends at offset 01218657551157

Not sure where to go from here..

[Breck_Carter]

FWIW there's more than just 160530AO.LOG that is "invisible" to dbremote, there are other file(s) missing.

               
 gap
           
 starts
            
 ends
               
 
160530AN.LOG
   
 
              
 1,218,654,613,708
   
 1,218,657,434,244
  
 
  
160530AO.LOG
   
 0
              
 1,218,657,434,244
   
 1,218,657,551,157
  
 invisible to SQL Remote
???.LOG
   
 
   
 
   
 
                   
 other logs must also be invisible
160609AA.LOG
   
 717,318,947
   
 1,219,374,870,104
   
 
                   
 

[Former Member]

Yeah, I figured it would just take up space to include the offsets for all 127 backup logs, but as was the case last time, they all translate and the offsets match where they should.

[Former Member]

I'm unable to post all the log offsets (too many characters) but nothing appears missing.

I do note that the translation of the logs created after the upgrade have more info after translating than the ones leftover from ten.

             Transaction log ends at offset 01219331493573

Transaction log "160607AP.LOG" starts at offset 01219331493573

             Transaction log ends at offset 01219341884606

Transaction log "160607AQ.LOG" starts at offset 01219341884606

             Transaction log ends at offset 01219343195737

Transaction log "160608AA.LOG" starts at offset 01219343195737

             Transaction log ends at offset 01219347337034

Transaction log "160608AB.LOG" starts at offset 01219347337034

             Transaction log ends at offset 01219374861645

Transaction log "160608AC.LOG" starts at offset 01219374861645

             Transaction log ends at offset 01219374865962

Transaction log "160608AD.LOG" starts at offset 01219374865962

             Transaction log ends at offset 01219374870104

SQL Anywhere 17 logs begin here**
Transaction log "160609AA.LOG" starts at offset 01219374870104 Current timeline GUID: 83d37ed5-643a-4e11-bdcc-a7f974dbac93 Current timeline UTC creation time: 2016-06-08 19:31:24.316000+00:00 Current transaction log GUID: 00000000-0000-0000-0000-000000000000 Transaction log ends at offset 01219374874293 Transaction log "160609AB.LOG" starts at offset 01219374874293 Current timeline GUID: 83d37ed5-643a-4e11-bdcc-a7f974dbac93 Current timeline UTC creation time: 2016-06-08 19:31:24.316000+00:00 Current transaction log GUID: 00000000-0000-0000-0000-000000000000 Transaction log ends at offset 01219374878507

[VolkerBarth]

So according to your postings,

• the required logs are all there in the offline-log directory "C:\\db Server\\backups" and their offsets match,
• SQL Remote does not recognize all of them and claims a missing log - I guess it stops at the first missing log and will not tell whether further ones are missing, as well.

Therefore I would conclude that SQL Remote cannot open these files. May this be an permission problem? - I.e. can you still translate the logs when running under the same account that SQL Remote is run from?

Or could it be a problem due to encryption? (You might try to remove the encryption of the problematic logs via CREATE DECRYPTED FILE and then replace the encrypted log files with the decrypted ones...)

Just wild guesses, apparently.

---

FWIW, with an earlier v17 build, we had problems to read simply-encrypted v10 logs, as documented here - however, I don't think that this is related to you situation. Nevertheless, it might be worthwhile to test whether a migration to v16 has the same log problems, as that error had not shown up with v16...

[regdomaratzki]

I followed your instructions as best I could and could not reproduce your issue, using either a simply encrypted v10 database or an AES encrypted database that I ugrade to v17. I insert a row in the consolidated database before the rebuild, rebuild the database, then insert another row in the consolidated. Both rows replicate to the remote databases and there are no errors about missing logs.

I'm pretty sure you should be able to download my sample here :

https://mdocs.sap.com/mcm/public/v1/open?shr=HGxbA5bB4OFxiLKVvW4mKWICkDxYMA9goV_G4p2e6KI

In theory, if you change a few variables in the common.bat file to match your system, you should be able to run my repro as well, although it's only really been tested on my machine. As opposed to running the repro, you can simply look at the rep.bat and search for the following section to see if you do anything differently during the upgrade that might be causing the problem :

@rem @rem Upgrade the consolidated to version 17 @rem

To run the repro (if you want) :

1) Unzip the contents of the ZIP file into an EMPTY directory. 2) Open a DOS prompt and CD into the directory where you unzipped the files. 3) Edit the common.bat file to point to your location for the v10 and v17 executables and binaries. 4) Type "rep"

You should be able to re-run the repro again and again. It starts by cleaning up the last iteration of the test so that every iteration of the test starts from the same point.

Reg

[Former Member]

Reg, thanks for the samples and I will definitely look at them.

However, I just completely re ran the whole test yet again, but this time with Version 16 and the current EBF and it just works. The consolidated sent without any errors.

[JimDiaz]

Interesting thanks for the update. Initially you mentioned missing messages from the remotes, did you try applying on the V16 database?

Jim

[Former Member]

Yes, replication is working in both directions, so at this point we've decided to go with ASA 16.

[VolkerBarth]

So you are saying the exact same steps

• do work when migrating your consolidated from v10 to v16 and
• do not work when migrating your consolidated from v10 to v17?

(I can certainly understand that you stick with v16 when that does work, however it would be helpful for others if you could test with v17 again - y'know "ready for prime time or not"...)

[Former Member]

I get it, I actually DID run the upgrade test to v17 twice, and it was unable to replicate both times. Since then, I've tested upgrading twice to v16 (the first time I forgot to save a v10 remote so I had to start over), the first time the consolidated was able to send, and the second time (now that I have remotes to test with) I've got it working in both directions with v10 remotes.

I can try the test again with 17, but with the equipment I've got available it takes a day and a half to run a test.

[regdomaratzki]

Sorry Volker, missed this reply. The problem only occurs with -ar.

[regdomaratzki]

This problem has been fixed in the following versions :

v16.0.0 build 2303 v17.0.4 build 2086

Reg

[regdomaratzki]

I'm unable to reproduce the same problem here in either version 16 or version 17. Either there is something specific to your environment that is the root cause (unlikely), or there is a mis-understanding between the exact steps that you take. I had hoped that sending you my working sample would highlight what my sample is doing differently than yours, but if that's not the case, you might need some one-on-one support with an actual tech support issue to get someone here to take a look at your files pre and post upgrade to get to the bottom of this issue.

Reg

[Former Member]

Thanks for your help Reg. I've repeated the test 3 times with 17, and 3 times with 16, and I've gotten the same result.

After doing precisely the same steps, the 16 rebuilds are able to send (I figured out why it was getting a missing message from the remotes and that no longer happens), and the 17 rebuilds cannot.

At this point I'm under pressure to move on and start further testing with the rebuilt 16 database. At some point after we've upgraded and all the version 10 logs have been deleted as they are no longer needed, I will do another test to see if the same thing happens to me rebuilding the V16 db in V17 with V16 logs..

[Breck_Carter]

Warning! Thread Drift!

Based on anecdotal evidence from the field, SQL Anywhere 16 is a solid and reliable version for conservative enterprise database installations. This includes large MobiLink and SQL Remote installations.

In fact, it is entirely possible V16 may earn a spot in the SQL Anywhere Best Versions Ever Hall Of Fame (SABVEHOF) alongside 5.5 and 9, even though "16" is an even number 🙂

Personally speaking: The next version of Foxhound will support target databases running on SQL Anywhere 17 but Foxhound's own embedded database will continue to ship as V16 (it will also run on V17, but it has not been upgraded).

Question for SAP: Are you dogfooding V17?

I.e., for those existing SAP products that run earlier versions of SQL Anywhere (not just 16 but earlier), have you upgraded and shipped them using V17?

Here's an thought-provoking discussion of dogfooding (and beta testing, for that matter): How Proper "Dogfooding" Might Have Saved Facebook Home.

...and for the tldr crowd, here's the graphic novel version :)...

[VolkerBarth]

I.e., for those existing SAP products that run earlier versions of SQL Anywhere (not just 16 but earlier), have you upgraded and shipped them using V17?

So this very forum - "Powered by SQL Anywhere 17 and OSQA" - will not classify as an answer, right?

(Great link, anyway...)

[Breck_Carter]

> will not classify as an answer

I would like to say "yes, it does"... but it's not even behind the paywall so it's not a product in the eyes of Wall Street.

AFAIK SQL Anywhere engineers practiced extensive dogfooding internally for SQL Anywhere 16, but the GA release of the Cockpit indicates "not so much" for 17.

[VolkerBarth]

AFAIK SQL Anywhere engineers practiced extensive dogfooding internally...

Well, some of them still love to use dbisqlc, isn't it? You expect them to use a, ahem, "GUI for monitoring databases"? I'm sure they KNOW what their databases do and don't need a cockpit...:)