Clean Migration from SQL Anywhere 12 to SQL Anywhere 17

[former_SQLA_member1694871]

I'm curious if anybody has some information on migrating a database from SQL Anywhere 12 to SQL Anywhere 17 when it comes to the new roles feature that was implemented in SQL Anywhere 16.

enter code here

First off, I unloaded the full database and schema out of our version 12 database (12.0.1.3942)

Opened SQL Anywhere 17.0.4.2053

No active connections in SQL Central.

1. Select to Create a new database
2. Select to Create a new database on this computer
3. Create name for my new database in SA17 (dbname.db)
4. Select to Maintain the following transaction log file (dbname.log)
5. Select that no transaction log mirror is needed
6. For the sake of the migration, I set the minimum password length to 1
7. Set the dba user and use the old default password
8. No jConnect metadata needed (using ODBC)
9. Leave encryption disabled
10. Select the 4k page size. No space preallocation.
11. Select to use SQL Anywhere defaults
12. Select the default collation: 1252LATIN1
13. Select the default collation sequence (Ignore accents)
14. Leave the collation tailoring options set to default

Now comes the tricky part. Choose the Security Model for the system procedures

Based on my guess, because my database is coming from SQL Anywhere 12, I need to select the second option, to execute the system procedures as the definer.

Finally I give it the server name and the database name, and tell it to open the database after created.

Once in the new database in 17, I want to select the reload script that was created when I unloaded my SQL Anywhere 12 database.

READ "C:\\Users\\Jeff Gibson\\Documents\\Extraction\\reload.sql"

Immediately at line 52, I hit the following error...

Could not execute statement.
Invalid user ID or role name 'dbo' specified
SQLCODE=-1536, ODBC 3 State="HY000"
C:\\Users\\Jeff Gibson\\Documents\\Extraction\\reload.sql:52

You can continue executing or stop.

GRANT CONNECT,DBA,GROUP,RESOURCE TO "dbo" AT 3

From there I just hit stop. Because I'm not sure if there is something critical that needs to be handled with what was the old dbo system user.

I'm not even sure I have my brain totally wrapped around roles yet. But from what I can tell, it looks like the dbo system user was moved into roles.

Is this an error/warning that I can skip? Since dbo gets created under roles? Or do I need to modify something so the user gets handled correctly on the creation of the SQL Anywhere 17 database.

Any information on this would be greatly appreciated! Thanks in advance for your help!

Jeff Gibson
Intercept Solutions
Nashville, TN

[chris_keating · Accepted solution]

This issue is caused by unloading using v12 software. That creates a reload script that is valid for v12 but may not be for other versions.You should use the v17 unload tool to create the unload script. Or as Reimer has suggested directly unloading into a new database.

In v16 and later, the statement:

GRANT <...> TO "<user>"

is only valid for an USER and "dbo" is a System Role not an user.

[VolkerBarth · Accepted solution]

You mean "is only valid for an user", right?

[chris_keating · Accepted solution]

Correct. The statement is valid only for an user.

[VolkerBarth]

We have also migrated several databases from 12.0.1 to v16 and v17 without issues, including replicated databases, generally via dbunload. So we still use the old privilege system (with a few adaptions). We also still use dbo as schema for most user tables, and that was no problem during the migration.

In your case, do you also connect as dbo (i.e. with its own credentials) or do you only use that to (say, via SETUSER) to create unqualified database objects?

[VolkerBarth]

In short: Using DBUNLOAD with one of -an or -ar should prevent you from answering all those questions asked by SQL Central...

[reimer_pods]

To be honest, I didn't look that much into new features, when we started converting client databases from SA12 to SA17. So we trusted in the frequently cited Watcom rule and just did an unload into a new database.

dbunload -v -c "<connection string for old db>" -an "<path for new db" -ap 4096 -ea None -ii

The defaults worked perfectly, so we continued using this approach. The only downside: I don't have any advice for your specific question