Migrating from Sybase to Sql Server

The last days I've been working on a project where I tested conversion of some databases.

The source was Sybase version 11.9, and I wanted to migrate to Sql Server 2008. As I found, this was not an easy job!

Sql Server Migration Assistant for Sybase (SSMA) only support Sybase version 12.5 or newer, and Sql Server 2005.

So I set up a temporary Sql Server 2005 for the migration job, and then I wanted to test how far I could get with a version 11.9 database.


Connection problems

The first problem was connecting to the Sybase database. I had installed the Sybase ODBC driver, but it wouldn't show up in the list in the Data Sources (ODBC) tool. After a search on the web, I found that when using a 64 bits server (Windows Server 2008), the system would have two folders for ODBC drivers; one for 32 bits drivers, and one for 64 bits drivers. The driver had installed itself in the 64 bits folder, while the Data Sources (ODBC) tool would only look up drivers in the 32 bits folder. Bummer!

I then installed SSMA on a Windows Server 2003, and could finally create an ODBC DSN. But I still could not connect to the Sybase database from the SSMA. After asking a Sybase DBA for help, we noticed that SSMA used the native Sybase driver and not the ODBC, and that required some .ini files in the Sybase folder to be configured to be pointed to the correct database and server. We finally had a connection! :)

Connecting to the Sql Server went off course smooth!


Converting a small database

I first tried to convert a small database with few objects and small amounts of data. This went relatively well. We had one single error converting the objects.

When installing the SSMA, it creates a conversion database on the Sql Server, containing some informaiton that is needed in the conversation process. Among other things it contains system functions that are treated differently in Sybase and Sql Server. For instance the datediff function is implemented as a user function called ssma_datediff so it is not confused with the original datediff system function in Sql Server.

When converting stored procedures containing calls to the datediff function, it would translate to:

sysdb.ssma_syb.ssma_datediff(...)

This returned an error in the SSMA engine because it could not validate the object.

What I did to solve this was that I imported the function into the database I was converting to, and changed the migration script so it called an internal user function instead:

dbo.ssma_datediff(...)

After that we migrated the data content, and there were no hiccups. Mission Accomplished!


Going for a larger database

OK. Now for a greater challenge: A Sybase database containing more than 450 tables, 150 views, 400 stored procedures, 200 triggers, 100 user defined data types, and many of the tables containing several millions of rows.

This time, when migrating the database objects with SSMA, I got a sql script with over 400 000 lines and with more than 2000 errors! Sigh!

I then started to edit the sql script, and found 4 kinds of problems:

1) System function calls

As with the small database, this one had a lot of references to user defined functions in the sysdb database. Solution: Import the functions and rewrite the call.

2) Parameters and Variables in Stored Procedures and Triggers

SSMA didn't just put brackets around object names like tables, schemes, database etc, but also around parameters and variables in the SPs. I think I had like 40 000 in the script that looked like this: [@myVariable]

Editing these by hand was out of question, so how do I use a search/replace without removing the brackets around the objects that should have them? I started to play around with Regular Expressions and ended up with this:

Search: \[{\@[A-Za-z0-9_]+}\]
Replace: \1

That hit the spot!

3) Old outer join syntax

A lot of procedures in the sybase database had old outer join expressions, and the SSMA couldn't translate them, so I had to do that manually.

Old syntax:

select * from
[TableA] a, [TableB] b
where
b.[a_ID]=*a.[ID]

Corrected syntax:

select * from
[TableA] a
left outer join [TableB] b
on b.[a_ID]=a.[ID]

4) Aggregates on aggregated values

Several of the procedures had queries using aggregates on aggregated values, like this:

declare @maxDate int

select @maxDate = isnull(max(max(a.[Date])),0)
from [TableA] a
group by a.[Date]
having sum(a.[Amount])!=0

First of all, this is not a valid TSQL statement in SQL Server. You will get an exception saying "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Second, this statement doesn't make much sense to me! You are grouping by the Date column and then selecting the max value of just the same column... What is the point of using an aggregate on that at all? Perhaps it has some obscure meaning in Sybase, but it gives no meaning to me!

Anyway, I rewrote the statements to what I believed they were meant to do; simply getting the latest date where the sum of amount was not zero:

select @maxDate =
isnull(max(sub.[Date]),0)
from
(select a.[Date]
from [TableA] a
group by a.[Date]
having sum(a.[Amount])!=0) sub

If any Sybase gurus out there read this, please comment on this part!

After importing user defined data types and defaults manually, the new script ran successfully on the Sql Server.

Migrating data content:

Another challenge! Some tables could not be converted because of casting problems, and others just dropped the connection to the Sybase server when trying to read from them. But luckily most tables migrated with no problems.

For the tables that had casting problems, I decided to use Sql Server Integration Services (SSIS).

The first problem was again to connect to the Sybase server. But after reading some tips on the web, I found that the way to do this was to create an ADO.NET connection that the DataReader could use, then configure the ADO.NET connection to use ODBC driver and point it to the ODBC DSN I already had created when trying to get a connection from the SSMA.

After that it was rather easy to migrate table by table using a Data Reader source, a Data Conversion, and a SQL Server Destination.

Then I was left with five tables that could not be migrated. Every time I tried, the connection was dropped and I had to restart the Sybase service to be able to connect to it again.

I thought there had to be something wrong with the source, so I logged into ISQL on the Sybase server and made a simple select * from the problem tables. And after a while it also dropped the connection with a read error, so I guess the tables were corrupt.


Conclusion:

Except from the five supposedly corrupt tables, I managed to migrate all objects and data from the Sybase v.11.9 database to Sql Server 2008, using SSMA and SSIS. So I guess I can call that a success, and
Mission Accomplished! :)