How do I upgrade a replicating pair of 9.0.2 databases to version 11

[Former Member]

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.

[Former Member]

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.)

[VolkerBarth]

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...

[Former Member]

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.

[VolkerBarth · Accepted solution]

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).

[Former Member]

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

[VolkerBarth]

But take care of the particular steps necessary to rebuild a database involved in replication, cf. http://dcx.sybase.com/1101en/dbusage_en11/wrtluu.html.

[Former Member]

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.

[VolkerBarth]

@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.

[Former Member]

@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.

[Former Member]

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.

[VolkerBarth]

@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.

[Former Member]

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

[VolkerBarth]

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.