Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I have two databases that are replicating via SQL remote. (It's working beautifully -- thank you Breck!). But they are running on 9.0.2.

The reason I have two databases is that I need to have some applications live 24x7 -- and if we have a catastrophic failure of a database server, I can move the apps to the other server in minutes, rather than the hours it would take to restore a backup to another machine. We can also point reporting applications (big slow queries) to a different physical machine than the transactions which must be fast.

The current situation is that all apps are pointed to the remote database. The consolidated database does nothing all day long.

So, I'm thinking I could do this:

1) shut off SQL Remote on both machines
2) upgrade the consolidated database to version 10 (or do I skip it and go straight to version 11??)
3) restart SQL Remote at both ends and get the databases back in sync
4) test the apps against the new database version (I have test versions of the most important apps)
5) point the production apps to the upgraded server,
6) shut off SQL Remote again
7) upgrade the remote database. 
8) restart SQL Remote.

I think it really all depends on whether SQL Remote can handle replication across version levels.

Thoughts? Thank you.

asked 17 May '10, 22:08

Ron%20Hiner's gravatar image

Ron Hiner
accept rate: 9%

I remember the pain of figuring out sync switch settings and paths with embedded spaces that worked fine up thru 9.0.2 and didn't work the same under 10 and above. (We didn't succeed making our 9.0.2 sync talk to 10, so I'll let others give the right answer for that.)

(17 May '10, 22:58) Bill Aumen
Comment Text Removed
Comment Text Removed

Not as an answer to your actual question: If your only concern for the usage of SQL Remote is high availability, then I would recommend to test the "High Availability" (aka database mirroring) feature of SA 10/11. Note that with SA 11 you can also use the mirror for long-running queries. - This might make your schema updates much simpler than with SQL Remote...

(18 May '10, 06:49) Volker Barth

Volker... new capabilities are the reason we want to upgrade! HA is but one of them. But we haven't had any real urgency, I had been planning on diving into this when 12.0.1 is released. But there isn't much point in waiting, and my guess there will be licensing changes and pricing changes upon release 12, particularly if SAP's acquisition goes through.

(18 May '10, 14:01) Ron Hiner

As to the underlying question "Must all SQL Remote sites be upgraded at the same time?", the docs definetely say: No. Cf. the topic Upgrading SQL Remote , as cited here

Software upgrades can be one site at a time

Older Message Agents (dbremote) can exchange messages with version 11 Message Agents. For version 5 of SQL Remote, the version 5 Message Agents can exchange messages with version 11 Message Agents, as long as the compression database option is set to a value of -1. There is no need to upgrade software throughout the installation simultaneously.

And if it were differently, how could anyone with, say, a few hundred remotes ever upgrade to a newer SQL Anywhere version...

The main caveat (besides the necessary steps to rebuild a database involved in replication, see my comment above) is to make sure the following file is included in the setup if SQL Remote at an upgraded site has to access older (i.e. pre V10) log files:

"The Pre-10 physical store library" dboftsp.dll (on Windows).

permanent link

answered 18 May '10, 14:10

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Make sure replication is caught up, unload/reload the consolidated, re-extract the remote.

permanent link

answered 17 May '10, 23:17

Calvin%20Allen's gravatar image

Calvin Allen
accept rate: 25%


But take care of the particular steps necessary to rebuild a database involved in replication, cf.

(18 May '10, 06:51) Volker Barth

Doesn't this answer mean downtime? We obviuosly can't use the consolidated for applications when unload/reload is running. I haven't tested this lately, but due to the size of the db, unload/reload will likely take hours.

I'd like a solution with near-zero downtime.

(18 May '10, 11:27) Ron Hiner

@Ron: When going from SA 9 or older to SA 10 or newer, you HAVE TO REBUILD the database because the SA 10/11/12er engines can't start older databases. But you should be able to rebuild each database separately and need not to re-extract the remote. (Otherwise folks using huge SQL Remote setups wouldn't be able to upgrade versions!). So you could rebuild one database while the other is in use by your applications.

(18 May '10, 12:22) Volker Barth

@Volker: Ok... that makes perfect sense. I think you are confirming my root question -- can SQL Remote sync across two versions. I have to unload/reload and upgrade each database before I restarting SQL remote. I can achieve my goal of near-zero downtime if I (1) shut down the cons database then upgrade/unload/reload it... (2) point the apps to the upgraded cons database, (3) upgrade/unload/reload the remote database. Then restart SQL Remote.

(18 May '10, 13:19) Ron Hiner

It seems to me like I have to REVOKE and GRANT REMOTE to users on both sides before I can get the replication up and running again. That cannot be correct, since I think it would be mentioned somewhere if I had to...

I tried to upgrade a consolidated database from ASA9 to SA12, when I run SQL Remote afterwards it looks for the log offset in the old transaction log, which naturally does not exist in the new transaction log file.

permanent link

answered 09 Dec '11, 07:47

OskarEmil's gravatar image

accept rate: 50%

@OskarEmil: Is that just a remark or a question? - If the latter, I would recommend to ask a new question...).

When upgrading/rebuilding a database involved in replication (or as MobiLink remote), you need to take care of the steps noted in my answer above - otherwise you log offsets won't fit.

(09 Dec '11, 07:57) Volker Barth

It was a remark but seems to evolve into a question so I think I'll just post a new thread on the subject.

(09 Dec '11, 08:37) OskarEmil
Replies hidden

So we'll see...

To your initial remark: When the replicated databases are upgraded according to the documented steps, there should be no need to "get replication up and running again" - it should just work as before.

(09 Dec '11, 08:45) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 17 May '10, 22:08

question was seen: 2,072 times

last updated: 09 Dec '11, 08:45