The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I need to migrate a database to a new server. The problem is, that the old server is Adaptive Server Anywhere 8 and the new one is just SQL Server Anywhere 11.

I've read this document: http://www.sybase.com/files/Technical_Documents/ias_wp_MigratingSAToASE.pdf But I do not think it applies here.

I created a backup in archive format, but this can not be restored. I created a backup with images, but this also can not be restored.

I unloaded the database to a reload-sql file, but I have no idea what to do with it next. Trying to execute this in isql gives me authentication errors on lines that start with "GRANT ...".

I'm new to sql anywhere, so any information that can help me, would be greatly appreciated.

Greetings, Andy

asked 02 Oct '13, 08:13

AndyPPW's gravatar image

AndyPPW
85339
accept rate: 0%

Wow, your question has started a run to give advice - Justin is the winner:)

(02 Oct '13, 08:39) Volker Barth

REM If you're a command-line kinda guy, here is my template Windows 
REM batch file run_dbunload_upgrade_V8_to_v11.bat. It does the 
REM unload-reload-all-in-one-dbunload-step, then starts dbeng11 
REM and dbisql so you can make sure the new database is up and running.

REM Be sure to stop *all* database engines on the computer you're using, 
REM before starting the upgrade.

REM dbunload...
REM -an ...  where to put new database
REM -ap ...  new database page size
REM -c ...   old database startup connection string
REM -o ...   where to put unload console display text file
REM -v       verbose mode for console display

PAUSE MAKE SURE YOU DO NOT HAVE ANY ENGINES RUNNING.

"%SQLANY11%\bin32\dbunload.exe"^
  -an "C:\temp\asademo.db"^
  -ap 4096^
  -c "DBF=asademo.db;UID=dba;PWD=sql"^
  -o "C:\temp\dbunload_asademo_log.txt"^
  -v

PAUSE dbunload/reload done...

REM dbeng11...
REM -c ...     initial RAM cache size
REM -o ...     where to put server console display text file
REM -os ...    when to rename and restart server console display text file
REM demo11.db  new database to start

"%SQLANY11%\bin32\dbspawn.exe"^
  -f "%SQLANY11%\bin32\dbeng11.exe"^
  -o "C:\temp\dbeng11_asademo_log.txt"^
  "C:\temp\asademo.db"

PAUSE dbeng11 started...

REM dbisql...
REM -c ...  new database connection string

"%SQLANY11%\bin32\dbisql.com"^
  -c "ENG=asademo;DBN=asademo;UID=dba;PWD=sql"

PAUSE All done...
permanent link

answered 02 Oct '13, 09:26

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

Thank you for this script. It's very clear and seems to do the necessary conversion.

But I get an authentication violation when it's creating the new database. Do you have any idea what could be the cause of this? The password and UID are the same as in the script posted here. This is also what is being used on the system.

(03 Oct '13, 05:32) AndyPPW
Replies hidden
(03 Oct '13, 08:46) Breck Carter

http://www.sybase.com/files/Technical_Documents/ias_wp_MigratingSAToASE.pdf

That's your problem - it's about ASE - not the same thing at all as SQL Anywhere.

There's a wizard to do what you want: have a look at this, the step-by-step guide is fairly far down the page:

http://dcx.sybase.com/index.html#1101/en/sachanges_en11/unloading-reloading-upgrading-newjasper.html

permanent link

answered 02 Oct '13, 08:36

Justin%20Willey's gravatar image

Justin Willey
6.4k101132197
accept rate: 20%

edited 02 Oct '13, 08:36

As a quick start, you may have a look at the following topic from the v11 docs:

SQL Anywhere 11 - Changes and Upgrading ยป Upgrading to SQL Anywhere 11: Upgrading SQL Anywhere, particularly the section "Upgrading version 9 and earlier databases".

You may try to use the "Unload Database Wizard" in Sybase Central to do so.

I'd suggest to read those pages carefully and to ask for more help if required.


Note: The doc you have cited deals with migrating to Sybase ASE (Adaptive Server Enterprise), a completely different database system, so you are very correct in your assumption that this will not be appropriate here:)

permanent link

answered 02 Oct '13, 08:38

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

You have several options available to upgrade from v8 to v11.

  • rebuild your database using dbunload -an or -ar option - this will rebuild your database in one step
  • unload your current database data using dbunload (to generate a reload.sql file (which is what you have done) and then create a new v11 database using dbinit and load your schema and data into this database using dbisql (or dbisqlc).

More information is available in the v11 documentation

permanent link

answered 02 Oct '13, 08:38

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262
accept rate: 40%

Thank you for this information.

It's a lot of work, both versions need to be installed on the same computer and the db will go offline. Not what I was hoping for, but I know what to do now.

permanent link

answered 02 Oct '13, 08:54

AndyPPW's gravatar image

AndyPPW
85339
accept rate: 0%

1

both versions need to be installed on the same computer

No, that's not true, you only need to install the new version on the new box, v11 can certainly unload v8 databases.

and the db will go offline

That's true. However, how would it be possible to switch from one box to another box and to switch versions with a permanently running database?

Just as a note: v10 has introduced a new file format so upgrading from pre-v10 to v10 or above does always require a rebuild of the database. In contrast, when moving from v8 to v9 or from v10 to v11/v12/v16, you would not need to rebuild the database (though it would be recommended) - you could simply stop the database on the old version engine and start it again on the new version engine.

(02 Oct '13, 09:01) Volker Barth

It's a lot of work

I would like to add that it's usually necessary to test your application on the new version - and it's highly recommended to read the "Behaviour changes" sections in the v11 docs to find out whether things may work differently in the new versions. Please note that each new major/minor version has its own list of behaviour changes so there are sections for 11.0.1, 11.0.0, 10.0.1 etc.

In contrast to these tests, setting up a new system and rebuilding the database is usually not that much work:)

(02 Oct '13, 09:05) Volker Barth

Depending on the size of the db and the speed of your server, the unload / reload need not take long. A recent 40GB one we did took 20 mins (fast disk system though).

(02 Oct '13, 09:07) Justin Willey

Something is still not clear to me, so I have another question:

The old and the new server are not connected to the same network. So can I do use the following method:

  • create a backup with images (this creates a version 8 mydatabase.db and mydatabase.log)
  • move those files to the new server with version 11 on it
  • use sybase central to unload the database and point it to the old files, reload into a new location

That seems like a good way to do it, but is that correct?

(03 Oct '13, 04:52) AndyPPW
Replies hidden

Yes, that's reasonable.

Instead of making an image backup of the old database, you can also stop the database and simply copy the current database files to a different folder (both .db and .log) and move/copy these to the new server.

(Apparently, when doing upgrades, it's highly recommended to have a working backup of the database both before and after the upgrade...)

(03 Oct '13, 09:11) 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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×25
×16
×14

question asked: 02 Oct '13, 08:13

question was seen: 1,669 times

last updated: 03 Oct '13, 09:13