SQL Anywhere DB migration from 16 to 17

[former_member1148180]

Hi all,

I have a Sql Anywhere Version 16 databases which I'm trying to upgrade to Version 17 in a network using commands, but I'm not in the server.

I'm thinking to unload and reload the database into a new file with dbunload and then replacing with dbbackup (I don't want to use dbupgrade).

Questions:

1 - Is there any other better or automatic way to do this, remembering that I'm on a client on the network and not on the server?

2 - Is there any way to stop the remote server (dbsrvXX) from a client on the network or to rebuild the database without needing to stop the database server (dbsrvXX)?

Any help would be very much appreciated!

Regards, Mailson.

[moshenaveh]

Welcome and thanks for visiting SAP Community to get answers to your questions. Check out our tutorial to get started in SAP Community: https://developers.sap.com/tutorials/community-start.html
By adding a picture to your profile you encourage readers to respond to your question. Learn more about your profile here: https://developers.sap.com/tutorials/community-profile.html

[hhelbig]

1:

An unload is not necessary for this.
If you want to use the new possibilities of SA17, you should update the database with the tool 'dbupgrad.exe'.
Otherwise you can use the SA16 database even in SA17.

2:

Yes: 'dbstop.exe'

[chris_keating · Accepted solution]

You can run a database created with sa10 or newer on a v17 server but its functionality will be limited to features and functionality that are not dependent on an updated system schema or database file format.

Ungrading the database (i.e., dbupgrad or alter database upgrade) updates system schema that is not dependent on a new file format and provides partial access to new features. That access is limited to features not dependent on a new database file format.

DBUnload provides access to all new features.

It is relatively easy to get a rebuild database without being at the server but it will be more expensive and challenges in making that rebuilt database accessible.

I strongly encourage that the rebuild be completed on the host that currently has the database. If that is not possible, a couple of options are available but not recommended. If these are used, you will need to ensure that new connections are prevented and existing connections are dropped. Any work performed at the client will be more expensive resulting in more downtime while rebuilding.

If you do this work on the server, it is simple using this single command

dbunload -c "host=DbHost:2638;server=SrvName;dbn=demo;uid=dba;pwd=sql" -ar

Option 1:

You could backup the database to the client machine and rebuild at the client and then replace the database at the server side with that rebuilt database. This requires that the software on the client is v17 (ideally with the same build as the server software)

Option 2:

Unload the database to a new file on the same machine as the server. The client does not need to match the server version. That new file cannot be of the same name as the database on the server. Please note that the database name (DBN) must carefully set and used to ensure correctness of the resulting new database.

Lets assume that the database server is running on a host DbHost on port 2638 with the database demo.db as

    dbsrv17 -x tcpip(port=2638)  -n SrvName demo.db -n demo

-- from the client
-- optional, check the version that initialized the source database
dbisql -c "host=DbHost:2638;server=SrvName;dbn=demo;uid=dba;pwd=sql" select version from syshistory where operation = 'INIT'
	version
	-----------
	16.0.0.2798

-- need to unload to a different filename, not able to provide a different directory 
dbunload -c "host=DbHost:2638;server=SrvName;dbn=demo;uid=dba;pwd=sql" -an demo17.db
	SQL Anywhere Unload Utility Version 17.0.11.6933
	...
-- start the new database on the server with a new database name
dbisql -c "host=DbHost:2638;server=SrvName;dbn=demo;uid=dba;pwd=sql" start database 'demo17.db' as demo17 autostop off
-- stop the old database
dbisql -c "host=DbHost:2638;server=SrvName;dbn=demo17;uid=dba;pwd=sql" stop database demo on SrvName unconditionally
-- confirm that the database was created with v17 (optional but confirms the database was created by v17)
dbisql -c "host=DbHost:2638;server=SrvName;server=SrvName;dbn=demo17;uid=dba;pwd=sql" select version from syshistory where operation = 'INIT'
	version
	------------
	17.0.11.6800

If you need to keep the old database name, you would need some additional steps

• start the old database i.e., demo.db with a name such as TmpDbName
• connect to the database TmpDbName
• stop the demo17.db
• restart demo17.db with the database name that was originally used i.e., demo
• stop the database TempDbName

Note that connections that are not using the best practice of supplying db server name and database name for connections will likely fail as the default database is no longer running.

[former_member1148180 · Accepted solution]

Perfect! That's exactly what i wanted.

Thanks so much!