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
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.
In the License Management dialog box, click the license registration page link.
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
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.
In the File Download dialog box, click Save.
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.
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:
Basic Steps for Migration of Sybase ASE to SQL Server
To start a migration, you’ll need to perform the following high level steps:
Create a new project.
Connect to a Sybase database.
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.
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:
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.
Here is an example of the Assessment Report for the pubs database.
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.
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.
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.
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.
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.
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.
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.
Comments
Anonymous
June 09, 2011
Good article. would like to read. sqlserverrider.wordpress.comAnonymous
November 13, 2012
Is SSMA can help me for betch jobs? I am currently working in project where we need to migrate/point batch jobs,Access(.MDB) files, Sybase Stored procs and Tables to SQL server 2005. At this moment they are pointing to Sybase. Batch jobs are in .bat and .exe format. These jobs read in an external file, does some reformatting, and then loads it into 2 sybase tables. These jobs are auto scheduled through Control-M application. Any idea please ?Anonymous
November 15, 2012
Hi, I have to migrate Sybase to SQL server 2005, is it ok if I will work with SSMA 5.2 version or I needed older version of it? Thanks, ChiragAnonymous
June 07, 2013
Is it possible to create Schema and data from a .DB file obtained from Sybase rather than from a Sybase server?Anonymous
June 22, 2013
Why does SSMA spin-up Sybases tempdb? I ask because my ASE is throwing various errors in tempdb when migrating data. I can BCP out the table without problems, and I can SELECT the table without problems. Only SSMA seems to be doing something on the SELECT side which is exercising tempdb. Does anyone know what SSMA may be doing other than selecting the data (it's not sorting it, is it?), or how to turn-off whatever it's doing? On a side note, If find both database's handling of datetime formats adolescent. Throw in an identity column and it's enough to pull your hair out.Anonymous
July 22, 2013
Hi, While generating SSMA tester report i was displayed with error message as " Cannot connect to Sybase ASE via SQL Server tester extensions". Please clarify....Anonymous
July 25, 2013
Hello, I want to migrate data from sybase 12.5 to sql server 2008 r2, I realize all the above but when I click the button will migrate in the output window out the following messages: Migrating data .... Analyzing metadata ... preparing table dbcredito.dbo.Acces_User available during limited hours pass and does nothing. may be happening?Anonymous
August 15, 2013
I am trying to connect to Sysbase via ODBC but getting error Sybase Data Access Connectivity libraries are not found.. Where can I find these Sybase Library files? Please Help Thanks in Advance.Anonymous
August 28, 2013
www.microsoft.com/.../confirmation.aspxAnonymous
January 11, 2014
Instale el software y al solicitar la licencia dice que el archivo no se encuentra alguien lo tiene? GraciasAnonymous
June 11, 2015
Is there a way to prevent SSMA to format the code? After the code is formatted, it is no longer easy to read. Some comments are out of place, etc.. Thanks in advance, Tiago.