Data migration from ASE 11.9.2 to 16.0

[Former Member]

Hi all,

I'm at a customer site running Sybase ASE 11.9.2 on Windows 2000. I wish to migrate their set of databases to ASE 15.7 or 16.0 hosted on Server 2012 (for obvious reasons). I've downloaded and installed ASE 16.0 developer edition on a new Windows 2012 virtual server and and am moving to the database migration stage....

From what I have read, a single step migration from 11.9.2 to 15.7 is possible, thus I assume a migration to 16.0 might also be possible.

What I have done:

i) Dumped the existing database and transaction logs from ASE 11.9.2 to some files on disk.

ii) Created a datastore for data and logs and created a database with 'load on' the ASE 16.0 environment

iii) loaded a 11.9.2 database and transaction logs into the blank database on ASE 16.0 environment (successful)

iv) run an 'online database DBNAME' (failing)

From what I have read, schema conversion etc. occurs during the online database action, not the initial load. This is the error i'm getting:

1> online database Cust201213
2> go
Started estimating recovery log boundaries for database 'Cust201213'.
Database 'Cust201213', checkpoint=(677861, 3), first=(677861, 3), last=(677861, 3).
Completed estimating recovery log boundaries for database 'Cust201213'.
Started ANALYSIS pass for database 'Cust201213'.
Completed ANALYSIS pass for database 'Cust201213'.
Recovery of database 'Cust201213' will undo incomplete nested top actions.
Database 'Cust201213' appears to be at an older version '12.5' than the present installation at version '16.0'; ASE will assess it, and upgrade it as required.
Database 'Cust201213': beginning upgrade step [ID 2]: validate basic system type data
Database 'Cust201213': beginning upgrade step [ID 3]: alter table (table sysindexes)
(182 rows affected)
Msg 644, Level 21, State 5:
Server 'SYBASE', Line 1:
Index row entry for data row id (232233, 0) is missing from index page 232225 of index id 2 of table 'sysindexes' in database 'Cust201213'. Xactid is (677873,8). Drop and re-create the i
Msg 3469, Level 20, State 1:
Server 'SYBASE', Line 1:
Database 'Cust201213': upgrade failed to create index 2 on table 'csysindexes'. Please refer to previous error messages to determine the problem. Fix the problem, then try again.
Msg 3461, Level 20, State 1:
Server 'SYBASE', Line 1:
Database 'Cust201213': upgrade could not install required upgrade item '3'. Please refer to previous error messages to determine the problem. Fix the problem, then try again.
Msg 3452, Level 20, State 1:
Server 'SYBASE', Line 1:
Database 'Cust201213': upgrade item 1134 depends on item 3, which could not be installed. Please refer to previous messages for the cause of the failure, correct the problem and try agai
Msg 3451, Level 20, State 1:
Server 'SYBASE', Line 1:
Database 'Cust201213': upgrade has failed for this database. Please refer to previous messages for the cause of the failure, correct the problem and try again.
Msg 3454, Level 20, State 1:
Server 'SYBASE', Line 1:
Database 'Cust201213': ASE could not completely upgrade this database; upgrade item 1134 could not be installed.
ASE could not bring database 'Cust201213' online.

As the database is not online of ASE 16.0 i've been unable to conduct must troubleshooting on that version, so I have cloned a copy of the database on the 11.9.2 instance instead to work on the 644 error.

From the error I assume that the sysindexes system table is corrupt. I've read various forum entries that talk about running a dbcc reindex etc. however the Sybase knowledge base states that such a command cannot be run on the sysindexes table.

Can anyone give me some advice on rebuilding a corrupt sysindexes table please. Go easy on me; I've only been exposed to Sybase ASE for 2 days so i'm learning .

Regards,

Mike Squirrell

[former_member188958 · Accepted solution]

Hi Mike,

The 16.0 documentation for the LOAD DATABASE command stats that it only formally supports loads from version 12.5.4 and higher.  I'm a little bothered that the upgrade message seems to think the dump is from 12.5 rather than 11.9.

It might be best to try first upgrading to 15.7 before going to 16.0.  However, lets check the consistency of sysindexes first.

On your 11.9 server, start by running dbcc checktable on the sysindexes table to see if it actually reports a problem there.

use <dbname>

go

dbcc checktable(sysindexes)

go

-bret

[Former Member · Accepted solution]

Hi Bret,

The 'checktable' run on the 11.9.2 cloned DB looks ok. So perhaps the error message is a result of incompatibility rather than corruption.

1> use Cust201213mds

2> go

1> dbcc checktable(sysindexes)

2> go

Checking sysindexes

The total number of data pages in this table is 19.

Table has 182 data rows.

DBCC execution completed. If DBCC printed error messages, contact a user with

System Administrator (SA) role.

1>

Based on your comments, i'll try and find an older version of Sybase (at least 12.5.4) and do an interim migration to that platform, then another hop to 16.0. I'll report back with the results. Let me know if you think i'm on the wrong track and should try something else....

Regards,

Mike

[former_member188958 · Accepted solution]

You're on track.  You may still get an error, but at least you'd be trying something that is documented as supported.

[Former Member · Accepted solution]

Hi Brad,

Banging my head against a brick wall here. Constructing an ASE 12.5.4 environment on a server 2012 VM (likely not supported) is proving challenging. It half works in XP compatibility mode but java is not happy.... To get a 12.5.4 staging environment working properly I would need to build a windows server 2003 VM and a windows XP client, get the ODBC drivers working properly etc. and test it. Software of this age is extremely hard to track down and license (particularly without an up to date support agreement with SAP)...

I am working on an option that is looking positive so far, though it's the last thing I wanted to do.... namely migrate the data from Sybase 11.9.2 to SQL server 2012. The SQL Server migration tool does support this early version of Sybase, and fortunately the 9 databases I need to convert are not too big or too complex so i'm cautiously optimistic. Analysis and dry-runs to date suggests this may be the least painful solution. Keen to rip the data out of this ancient environment before it dies.

Thanks for you help. This just demonstrates the folly of running a production system in a 16 year old environment; all good fun.

[former_member182259 · Accepted solution]

It attempting that route...I almost wonder if sybmigrate might be a solution for you - it can copy schema and data from one server to another.....not sure if ever tested/certified with 11.9.x, but the CIS components are all there for it.   You'd have to run it from the 15.7 side....

[Former Member · Accepted solution]

Thanks for the advice Jeff,

I had a quick look a sybmigrate, and although I can't find anything that definitively says that sybmigrate supports conversion 11.9.2 it's a pretty good option, and potentially less risky that migrating to SQL Server. I'm going from windows env. to windows env. too which helps.

At least I now have a plan a and a plan b, so thanks all for your advice. I'll report back with what eventually worked .....

[Former Member · Accepted solution]

I can confirm that the SQL Server migration assistant for Sybase works well in the following environment:

Source: Sybase 11.9.2 on Windows 2000

Client: Windows 8 running SQL Server Migration assistant for Sybase v5.3.0

Destination: SQL Server 2008_R2 on Server 2012

Migration works well with the following caveats:

Sybase procedures, such as data backup will not convert. This is not an issue in my case as SQL server backups would be initiated through a different method to Sybase.  This may be an issue for other Sybase customers and the procedures would thus need to be rewritten.

The tool also usefully validates many elements of the schema before conversion, including the SQL underpinning views, and SQL referencing other databases; it highlighted some coding errors in the existing databases which was very helpful.

sybmigrate was not tested due to the success of this method, and that I have a little more experience with SQL Server than Sybase (nothing against Sybase)

[Former Member · Accepted solution]

Hi all,

I'm still playing with this issue. Even through database migration to SQL server worked, the legacy hand-built application is not able to access SQL Server through ODBC, thus i'm looking at sybmigrate as the plan B. I've got it running on a 15.7 environment. I've run an sp_addserver, 'servername', local on the 11.9.2 instance so that sybmigrate can see it, but it's now complaining that the major versions numbers are different. Is there any way to fool sybmigrate so that is can migrate data between server versions? If not; a plan C. I've got everything migrated onto SQL Server 2008_R2 using the SQL Server Migration Assistant; is there an idiot proof way to migrate this back onto Sybase 15.7?

[jong-un_seo · Accepted solution]

Hi Mike,

Why don't you use bcp and ddlgen utilities to migrate data?

"ddlgen" supports 11.9.2 and you can extract DDL statements from 11.9.2
and then create them to new ASE 16 server.

For bcp-out scripts, you can create bcp-out scripts from 11.9.2 server like below.

use <user db>
go

select "bcp " + db_name() + ".<owner>." + name + " out " + name + ".out -c -U<user name> -P<password> &" from sysobjects where type = 'U'
go

For bcp-in you can make them using "in" instead of "out" from above statement.

select "bcp " + db_name() + ".<owner>." + name + " in " + name + ".out -c -U<user name> -P<password> &" from sysobjects where type = 'U'
go