Migrating from Sybase ASE to SQL Server – How to use SSMA for Sybase

[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]

 

In this blog, I’m going to walk you through the process of converting a derivation of the Sybase pubs2 (referred to as pubs in this blog) sample database to SQL Server 2008 R2 using the SQL Server Migration Assistant for Sybase v5.0 [Updated: Please obtain the lastest SSMA for Sybase](SSMA for Sybase). The new version of SSMA for Sybase also allows you to migrate your Sybase databases to SQL Azure. The SSMA client requires the Sybase OLEDB, ADO.Net, or ODBC provider to connect to your Sybase ASE database server. You can install these providers from the Sybase ASE product media. To help you in your planning, you will want to download the “Guide to Migrating from Sybase to SQL Server 2008” white paper.

If you don’t already have the pubs2 database for Sybase installed, you can follow along with any database.

Using SSMA for Sybase

SSMA for Sybase lets you quickly convert Sybase database schemas to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server Code Named “Denali” and SQL Azure, upload the resulting schemas the target instance and migrate the data using a single tool.

Installing the SSMA for Sybase extension pack

The SSMA for Sybase extension pack is available if you want to use the Server-side data migration feature (recommended) and/or the Tester component within SSMA. The installation instructions are located on the SSMA for Sybase download page.

Licensing SSMA

SSMA is a free tool, but does require you to associate a Microsoft Live ID for identification purposes. You must download a registration key. To help you with the registration process, a License Key Required dialog box opens the first time that you start the SSMA program. Use the following instructions to download a license key and associate the key with SSMA.

To license SSMA

  1. Click Start, point to All Programs, point to Microsoft SQL Server Migration Assistant for Sybase, and then select Microsoft SQL Server Migration Assistant for Sybase.

  2. In the License Management dialog box, click the license registration page link.

  3. On the Sign In Web page, enter your Windows Live ID user name and password, and click Sign In.

    A Windows Live ID is a Hotmail e-mail address, MSN e-mail address, or Microsoft Passport account. If you do not have one of these accounts, you will have to create a new account. To create a new account, click the Sign up now button

  4. On the SQL Server Migration Assistant for Sybase License Registration Web page, fill in at least the required fields, which are marked with a red asterisk, and then click Finish.

  5. In the File Download dialog box, click Save.

  6. In the Save As dialog box, locate the folder that is shown in the License Management dialog box, and then click Save.

    The default location is C:\Users\<user_name>\AppData\Roaming\Microsoft SQL Server Migration Assistant\Sybase.

  7. In the License Management dialog box, click Refresh License.

SSMA for Sybase User Interface

After SSMA is installed and licensed, you can use SSMA to migrate Sybase databases to SQL Server 2008 or SQL Azure. It helps to become familiar with the SSMA user interface before you start. The following diagram shows the user interface for SSMA, including the metadata explorers, metadata, toolbars, output pane, and error list pane:

clip_image002

Basic Steps for Migration of Sybase ASE to SQL Server

To start a migration, you’ll need to perform the following high level steps:

  1. Create a new project.

  2. Connect to a Sybase database.

  3. After a successful connection, Sybase schemas will appear in Sybase Metadata Explorer. Right-click objects in Sybase Metadata Explorer to perform tasks such as create reports that assess conversions to SQL Server 2008 R2. You can also perform these tasks by using the toolbars and menus.

You’ll then connect to your instance of SQL Server 2008 R2. After a successful connection, a hierarchy of your existing databases will appear in SQL Server Metadata Explorer. After you convert Sybase schemas to SQL Server schemas, select those converted schemas in SQL Server Metadata Explorer, and then synchronize the schemas with SQL Server.

After you synchronize converted schemas with SQL Server 2008 R2, you can return to Sybase Metadata Explorer and migrate data from Sybase schemas into target database.

Let’s walk through the specifics.

Create a Sybase Migration Project

To get started, you’ll create your new project using the File | New Project command.

clip_image004

You’ll enter in your project name and then confirm that you are migrating to SQL Server. The Migrate To dropdown also allows other servers. The default option is SQL Server 2008 which is also used for migration to SQL Server 2008 R2 instances. Once you make your selection, you are locked into the target backend.

Connect to a Sybase Database

To Connect to your Sybase instance, you’ll issue the File | Connect to Sybase command or click on the tool bar button that launches the following dialog:

clip_image006

Create Report of Potential Conversion Issues

Once you are connected, you’ll see the Sybase instance in the Sybase Metadata Explorer. You’ll want to expand the Databases node along with the pubs database node and then check the box next to pubs. This selects the database you want to migrate. Next, right click on the pubs database and select the Create Report command or press the Create Report command on the toolbar as shown below.

clip_image007

Here is an example of the Assessment Report for the pubs database.

clip_image009

The Assessment Report window contains three panes:

  • The left pane contains the hierarchy of objects that are included in the assessment report. You can browse the hierarchy, and select objects and categories of objects to view conversion statistics and code.

  • The content of the right pane depends on the item that is selected in the left pane.

    If a group of objects is selected, such as schema, the right pane contains a Conversion statistics pane and Objects by Categories pane. The Conversion Statistics pane shows the conversion statistics for the selected objects. The Objects by Categories pane shows the conversion statistics for the object or categories of objects.

    If a function, procedure, table or view is selected, the right pane contains statistics, source code, and target code.

    • The top area shows the overall statistics for the object. You might have to expand Statistics to view this information.

    • The Source area shows the source code of the object that is selected in the left pane. The highlighted areas show problematic source code.

    • The Target area shows the converted code. Red text shows problematic code and error messages.

  • The bottom pane shows conversion messages, grouped by message number. You can click Errors, Warnings, or Info to view categories of messages, and then expand a group of messages. Click an individual message to select the object in the left pane and display the details in the right pane.

Connect to SQL Server

It’s time to connect SSMA to your SQL Server 2008 R2 instance. For the Server name, you’ll need the server name and instance for the target server. You can select an existing database to migrate to using the Database control. You can also type in the name of a new database. For a new database, SSMA prompts you to create it.

When connecting to SQL Server Express instances or an instance not running SQL Server Agent, you’ll receive the following warning indicating that you won’t be able to use the Server-side data migration engine. The Server-side data migration engine is the preferred way for migrating data into SQL Server directly from the source, but requires SQL Server Agent on your destination server.

clip_image010

You can continue from this dialog to start the actual migration process.

Modifying the Target Database and Schema

Once you connect to SQL Server, you have the option of mapping the given Sybase database or database.schema combination to a database or database.schema combination on SQL Server. The Schema Mapping tab is available when you click on a database or schema in the Sybase Metadata Explorer. To make a change for a specific mapping, select the line in the Schema Mapping tab and click the Modify command. You can then choose the desired target on your SQL Server. You can use the Reset to Default command to go back to the original settings that SSMA chose.

Convert Schema

Now that you’ve determined the schema mapping to the target SQL Server instance, you are now ready to convert the schema. Click the Convert Schema command on the toolbar to begin the process. Once the conversion is finished, you should see the SQL Server Metadata Explorer populated with the tables listed in bold as shown below.

clip_image012

Synchronize with Database

To write the tables to the target, select the dbo node in the SQL Server Metadata Explorer and then issue the Tools | Synchronize with Database command. SSMA displays the Synchronize with Database dialog as shown below. In this example, the Tables node was manually expanded to show that no tables are actually on the database at this time.

clip_image014

When you click OK, SSMA issues the CREATE TABLE and other DDL statements to create the objects on the SQL Server target.

Migrate Data

The last step is to migrate the data into the tables. To complete the migration, select the Tables node within the Sybase Metadata Explorer for the pubs database. Then issue the Tools | Migrate Data command or press the command on the toolbar. The Data Migration process requires you to connect to the Sybase database and to the SQL Server database again. SSMA then proceeds with the data migration process and displays the Data Migration Reports as shown below.

clip_image016

The Data Migration report will display error icons if there were any problems in the data migration as show above.

You can use SSMA to do a quick comparison of the data migration by clicking on the table name of interest and the selecting the Data tab as shown below.

clip_image018

By default, SSMA shows the first 100 records in each table. You can adjust this setting using the Project Settings dialog within the GUI tab.

For large data migration projects, you will need to use the Server Side Data Migration Engine and the ASE ADO.NET Data Provider. For more information on how to configure SSMA for Sybase for faster Sybase data migration, refer to the blog post “How to migrate data fast from Sybase to SQL Server”.

Migration of Adhoc Statements

SSMA for Sybase has a feature that allows you to migrate script files and statements that may be embedded in application code through the Statements folder. In the Sybase Metadata Explorer under a schema, you can find the Statements folder. Statements are stored per project and associated with the current database. You can add a statement window using the Add Statement command as shown below.

clip_image020

You can then paste the script into the SQL window and then issue the Create Report command to see the results.

Resources

For more information on migrating from Sybase ASE to SQL Server, check out the following resources:

· SQL Server Migration Assistant 2008 for Sybase PowerBuilder Applications v1.0. This utility helps in migrating PowerBuilder applications designed for Sybase to SQL Server 2008.

· SSMA Team Blog for Sybase. Provides the latest information on Sybase from the SSMA product team.

· SQL Server 2008 R2 Migration site. Provides links to downloads, white papers, and a list of partners that help in your migration project.