Sybase ASE to SQL Server migration – Challenges, Tips & Tricks

Here are all the technical tips for migrating from Sybase ASE to SQL Server detailed in the document:

String Concatenation:

• Use rtrim function in SQL Server to match Sybase behavior of trimming trailing spaces from nullable character columns before concatenation.

Binary to Numeric Conversion:

• Modify queries to convert binary to integer before using it in numeric operations.

Group By Clause:

• Modify queries to aggregate or include required columns in the group by clause.

Emulated Functions:

• Replace emulated functions created by SSMA with native SQL Server functions or logic to avoid performance impact.

Remote Procedure Calls:

• Set up linked servers in SQL Server to access Sybase procedures using four-part naming convention.

Temporary Tables:

• Use ##tablename syntax in SQL Server for temporary tables instead of tempdb.schema.tablename used in Sybase.

Inserting Timestamps:

• Use SSMA to create a new ssma_timestamp column and migrate data. Update applications to use the new column.

Default Column Nullability:

• Explicitly specify NOT NULL for required columns during migration since SQL Server defaults to NULL.

Variable Behavior in Update Statements:

• Restructure update logic in SQL Server since it updates variables before columns, unlike Sybase.

Data Truncation During Inserts:

• Ensure data doesn’t violate column length limitations before inserting into SQL Server.

Default Date/Datetime Format:

• Convert source data to the target format before migration.

Cursor Behavior:

• Reorganize cursor logic in SQL Server to declare and open inner cursor within outer cursor’s open block. Explicitly deallocate cursors.

Data Types:

• For dates before 1753, use native Sybase handling or implement special handling in SQL Server.
• Use SSIS or change data type for numerics with scale > 26 in SQL Server.

Functionality:

• Use + for string concatenation instead of || in SQL Server.
• Handle NULL comparisons/concatenations explicitly in SQL Server if needed.
• Avoid deep CASE nesting in SQL Server.
• Use explicit casting when mixing binary and character data in SQL Server.

SQL Commands:

• Use @@TRANCOUNT checks for transaction management in SQL Server.
• Use DELETE TOP (0) WITH (TABLOCK) for locking instead of LOCK TABLE.
• Adjust RAISERROR usage for argument handling and formatting.
• Use isolation hints (WITH (READUNCOMMITTED)) for isolation levels.
• Make adjustments for DISTINCT with ORDER BY.

Data Migration:

• Map timestamps to binary(8) and add timestamp column with @@DBTS default.
• Set Check Constraint=true to check constraints/bound rules.
• Verify KeepNulls and Fire Triggers settings in SSMA.

Other Issues:

• Declare cursors as local or rewrite to deallocate explicitly.
• Enclose reserved keywords in square brackets (e.g. [FUNCTION]).
• Replace Syb_identity with IDENTITYCOL.
• Replace identity(precision) with identity(numeric(precision)).
• Rewrite dynamic SQL manually or use Statements window in SSMA.
• Use linked servers, views, four-part names for proxy tables.
• Move variable declarations before open and add DEALLOCATE for cursors.