Migrating Sybase ASE to SQL Server: Similarities and Differences to Help in Planning a Migration Project

  • Article

[Updated 2/7/2012 Jiafei Yuan - Microsoft SQL Server Migration Assistant (SSMA) for Sybase v5.2.  The information provided below is still valid for SSMA for Sybase v5.2.  Users should download the lastest SSMA for Sybase]

This blog post explores the similarities and differences between Sybase Adaptive Server Enterprise (ASE) database applications and administration with Microsoft SQL Server to help you in your planning effort for migrating to SQL Server. The free Microsoft SQL Server Migration Assistant (SSMA) for Sybase v5.0 [Updated:  Please obtain the lastest SSMA for Sybase] provides a set of tools to help in your Sybase migration effort. In addition to the database migration tool, there is a free SQL Server Migration Assistant 2008 for Sybase PowerBuilder Applications v1.0 tool that reduces the effort, cost, and risk of migrating PowerBuilder applications to use SQL Server. You will also want to download and review the white paper “Guide to Migrating from Sybase ASE to SQL Server 2008” for a full understanding of how SSMA handles the migration of your Sybase database to SQL Server.

Overview of Similarities between Sybase ASE and SQL Server 2008 R2

Microsoft SQL Server was spawned originally from the Sybase code base. Since then, Microsoft has rewritten the engine and it no longer contains any of the original Sybase code. There are still similarities because the SQL language itself was born from Transact-SQL. In addition, many of the stored procedures interfaces used to manage the database remained the same to ease DBAs through the initial migration.

Similarities in the Database

Like SQL Server, Sybase uses a Database as a container for the database objects specific to an application on a Sybase ASE server. Database objects include: tables, rules, defaults, stored procedures, triggers, views, referential integrity and check constrains, functions, computed columns, and partition conditions.

Sybase ASE includes system databases like master and model that perform similar roles in SQL Server. SQL Server DBAs will recognize some of the similarities in the system catalog tables with Sybase ASE. Sybase ASE and SQL Server also share similar Database Consistency Checker (dbcc) commands.

The Sybase ASE security model is also similar to SQL Server. Logins control access to the server. Users control access to a specific database. Sybase ASE has the concept of a Role that is similar to the roles in SQL Server.

Sybase ASE uses Monitoring and Diagnostic (MDA) tables that are similar to SQL Server’s Dynamic Management Views (DMV) for monitoring and troubleshooting.

Similarities in the Tools

There is a summary of how some of the Sybase ASE tools match up to SQL Server.

  • ISQL – Interactive SQL Parser. Similar to SQLCMD for command line execution of Transact SQL statements. SQL Server Management Studio provides a rich development, debugging and management experience for SQL Server.
  • BCP – Bulk Copy Utility. Very similar to BCP with SQL Server. SQL Server Integration Services (SSIS) provides a comprehensive set of tools for moving data.
  • Job Scheduler. Similar to SQL Server Agent for defining and scheduling database administration and maintenance tasks.
  • Sybase WorkSpace 2.0. Similar to SQL Server Management Studio for developing, tuning, and debugging queries.

Similarities in Connectivity

Both Sybase ASE and SQL Server use an application layer request/response protocol called Tabular Data Street (TDS) for communicating between server and clients. The protocol is not compatible between the two server products, but is the basis for how data providers like ODBC, OLE DB, JDBC, and ADO.NET talk to both servers. For Sybase applications that use ODBC, OLE DB, JDBC or ADO.NET, the job of remediating the application is easier because of the availability of data providers for both servers. Often the process is as simple as changing the connection string attributes to point to SQL Server for Windows based applications. More information regarding how to remediate your application connectivity APIs can be found in the white paper Guide to Migrating Sybase Application Interfaces to SQL Server 2008.

For Sybase ASE applications that use the CT-Library, you want to consider mapping CT-Library calls to ODBC. The Guide to Migrating from Sybase ASE to SQL Server 2008 white paper provides guidance on how to remediate your application without having to do a complete rewrite.

Overview of Differences between Sybase ASE and SQL Server 2008 R2

SSMA provides help in migrating a vast majority of Sybase ASE objects and Transact-SQL code with a minimal amount of manual intervention. In order to support the migration effort, SSMA provides an extension pack that must be installed on the target SQL Server database instance. Instructions for installing the support pack can be found on the Microsoft SQL Server Migration Assistant (SSMA) for Sybase v5.0 [Updated:  Please obtain the lastest SSMA for Sybase] download page.

Here are some of the differences that you should be aware of that are detailed in the Guide to Migrating from Sybase ASE to SQL Server 2008 white paper.

Unsupported Sybase ASE System Functions

Many of the system functions in Sybase ASE have equivalent functions in SQL Server. SSMA can also emulate non-equivalent functions as user-defined functions installed into the sysdb database as part of the SSMA extension pack. The following list of system functions cannot be easily emulated in SQL Server because of physical organization and security model differences:

curunreservedpgs, data_pgs, derived_stat, get_appcontext*, host_id, is_sec_service_on, lct_admin, license_enabled, list_appcontext*, lockscheme, mut_excl_roles, proc_role, ptn_data_pgs, reserved_pgs, rm_appcontext*, role_contain, role_id, role_name, set_appcontext*, show_role, show_sec_services, syb_quit, syb_sendmsg, tempdb_id, used_pgs, valid_name, valid_user, rowcnt, tsequal

* This application context feature can be implemented by using temporary tables, but this is not recommended due to security issues.

Sybase ASE Transact-SQL Command Differences

The following table represents Sybase ASE Transact-SQL statements that are different than SQL Server 2008 R2. The table shows the level of support for migrating the command to SQL Server. In cases where there is partial or no support, please refer to the  Guide to Migrating from Sybase ASE to SQL Server 2008 white paper for suggestions on how to work around the problem. For cases where SSMA has partial or no support, the SSMA error report indicates the estimated time to fix the issue to help in planning your migration effort.

Sybase ASE Command Issue SSMA Support
COMMIT

The COMMIT command can be executed without a prior BEGIN TRANSACTION statement.

 Yes
Different COMMIT Syntax

Sybase can use COMMIT transaction_name and COMMIT WORK transaction_name syntax that does not exist in SQL Server 2008 R2.

 Yes

Quoted data type for CONVERT and CAST functions

 Sybase allows writing the target data type in CONVERT and CAST functions in single quotes and double quotes. None
DEALLOCATE CURSOR SQL Server does not support the DEALLOCATE CURSOR command. Yes
LOCK TABLE <T> IN SHARE | EXCLUSIVE MODE SQL Server 2008 does not support the LOCK TABLE <T> IN SHARE | EXCLUSIVE MODE command. Yes
PRINT command SQL Server cannot use the PRINT command with a format string and arguments. In addition, Sybase interprets double percent (%%) in a format string as a single percent. Yes
RAISERROR RAISERROR has different syntax and error code numbering. Partial
ROLLBACK The ROLLBACK command can be executed without a prior BEGIN TRANSACTION statement Yes

SELECT/INSERT/DELETE/UPDATE AT ISOLATION clause

SQL Server does not support the SELECT/INSERT/DELETE/UPDATE AT ISOLATION clause.

 Yes
DISTINCT with ORDER BY

SQL Server requires that all ORDER BY items appear in a SELECT list if ORDER BY is specified in a query with the DISTINCT keyword.

 Yes
FOR readonly | update clause The FOR clause is not part of SELECT query syntax in SQL Server. Partial
GROUP BY clause

In a SELECT list, Sybase can use nonaggregated columns not included in the GROUP BY clause.

 Partial
HAVING clause

Sybase can use the HAVING clause without a GROUP BY, or a HAVING clause with nonaggregate columns missing in GROUP BY.

 Partial
HOLDLOCK hint SQL Server does not use the HOLDLOCK hint syntax. Yes
INDEX hint SQL Server uses the INDEX hint differently from Sybase. Yes
Non-standard outer JOIN

Sybase queries can use non-ANSI outer join syntax (*= or =*).

 Partial
NOHOLDLOCK hint SQL Server does not use NOHOLDLOCK hint syntax. Yes
READPAST keyword SQL Server does not support READPAST. Yes
SHARED keyword

SQL Server does not support SHARED.

 Yes
LIKE operator The LIKE operator in Sybase and SQL Server treats the trailing blanks differently. Partial
SET ANSINULL SQL Server does not support the SET ANSINULL command. Yes
SET CHAINED SQL Server 2008 does not support SET CHAINED. Yes
SET TRANSACTIONISOLATION LEVEL Sybase uses transaction-level identifiers that are different from those used in SQL Server. Yes
UPDATE aliases In an UPDATE statement, SQL Server does not allow the use of a table name if that name is duplicated in a FROM clause with an alias Yes
ROLLBACK syntax

Sybase can use ROLLBACK transaction_name and ROLLBACK WORK transaction_name syntax that does not exist in SQL Server.

 Yes
Aggregate functions in UPDATE

Sybase can use aggregate functions in the SET clause of an UPDATE statement, which is invalid in SQL Server.

 Yes
Table Hints Sybase can use several hints with one table. If each hint is converted independently, SQL Server cannot support the result. Yes
ORDER BY with table name and column alias In Sybase, it is possible to specify the ordering when a table name (or alias) is used together with a column alias. SQL Server does not support this syntax. Yes
CHAR column allowing NULLs If a Sybase column is of char type and is defined with a null specifier, it is treated as varchar. The column value is right-trimmed every time you retrieve it. That is not applicable to Sybase variables. This is also true for nchar and unichar column types. None
Inserting Default Values

In Sybase if all columns have defaults, the following INSERT command can add a row to the table without specifying any value:

INSERT INTO <a_table> VALUES ( )

SQL Server does not support this syntax.

 Yes
Nested aggregates in SELECT list

Sybase allows nesting aggregate functions like this:

SELECT x, SUM(y), MAX(SUM(y)) FROM tab GROUP BY x

In this case, SUM(y) is the sum per each value of x, and MAX(SUM(y)) is maximum for the entire table replicated in each row of the result set.

 None
DELETE aliases In a DELETE statement, SQL Server 2008 does not allow the use of a table name if the FROM clause contains duplicates of the table and the table's first occurrence in the FROM clause has an alias. None
Named constraint on a Temp table SQL Server does not allow multiple PK (PRIMARY KEY) constraints with the same name, even on different tables on different user sessions. Yes

Dealing with Case Sensitive Databases

Sybase identifiers and object names are case sensitive. If you transfer the source to a case-insensitive target, name conflicts may arise. SSMA for Sybase v5.0 fully supports migrating a case sensitive Sybase database to a case sensitive SQL Server database. Here is a summary of what you will need to consider based on your target SQL Server configuration.

  • Sybase case sensitive database migrated to a SQL Server case sensitive database on a case insensitive server (system catalog files are case insensitive). SSMA provides partial support. Variables differing only in case inside of stored procedures need to be updated manually. Other objects like table names and column names that differ in case are correctly handled by SSMA.
  • Sybase case sensitive database migrated to a SQL Server case insensitive database. SSMA does not provide support. The expectation is that the target database should be setup to match in case sensitivity. If you are forced to migrate to a case insensitive server, object names and variables differing in case need to be updated manually to make them unique. One technique you could use is to script an entire database into a single file and then use a text editor to find and replace ambiguous names.

Wrap Up

Database Administrators and Database Developers should find plenty in common between Sybase ASE and SQL Server 2008 R2. The free Microsoft SQL Server Migration Assistant (SSMA) for Sybase v5.0 [Updated:  Please obtain the lastest SSMA for Sybase] can help you in migrating your Sybase ASE database and data to SQL Server 2008 R2. For more migration resources, please check out https://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#Sybase.

If you are interested in migrating a Sybase ASA based application to SQL Server, please check out the white paper Guide to Migrating from Sybase ASA to SQL Server 2008.

Comments