Migrating databases from Sybase to SQL Server

Brief introduction

I recently participated in a project to migrate a Sybase database to Microsoft SQL Server 2000, and my experience with this project will help the Sybase database administrator migrate the Sybase database to the SQL Server 2000 platform.

Although some of the differences are quite large, for example, stored procedures in the Sybase database management system cannot be compiled in SQL Server, while other differences are not significant. It is necessary to test the behavior and results of programming logic in script files and stored procedures before completing this transformation.

In the following sections, we will discuss some of the major differences between these two database systems, which we must examine carefully during the migration planning phase.

Data compatibility mode

A temporary solution to some of the compatibility differences between SQL Server 2000 and Sybase is to change the database compatibility level in SQL Server to align with Sybase. To do this, we can use the sp_dbcmptlevel stored procedure.

The statements and results in the following table show the differences between different versions of the database:

(Untitled-1)

Attention:

1. When the compatibility mode is set to 70 o'clock, the following words cannot be used as object names and identifiers: BACKUP, DENY, precent, restore, and top.

2. When the compatibility mode is set to 65 o'clock, the following words cannot be used as object names and identifiers: AUTHORIZATION, Cascase, CROSS, distributed, ESCAPE, full, INNER, JOIN, left, OUTER, privileges, RESTRICT, right, schema and work.

Here is the syntax for sp_dbcmptlevel:

sp_dbcmptlevel [[@dbname =] name][,[@new_cmptlevel =]version]

@dbname is the database name used to check and change the compatibility level.

@new_cmptlevel determine the level of compatibility that the database is set (set it to 70, 65, 60, and the default value is null).

For example:

sp_dbcmptlevel pubs

This line of code returns the following result: