AWS Database Blog

Migrate your SAP ASE (Sybase ASE) database to Amazon RDS for SQL Server

Customers running their workload on SAP Adaptive Server Enterprise (Sybase ASE) databases often ask us how they can modernize their workload as they move to AWS with minimum application changes. Customers who want to keep Transact-SQL (T-SQL) as their preferred database programming language and Tabular Data Stream (TDS) as their communication protocol can take advantage of the managed database service Amazon Relational Database Service (Amazon RDS) for SQL Server. Amazon RDS for SQL Server makes it easy to set up and operate SQL Server deployments in the cloud by managing time-consuming database administration tasks, including provisioning, backups, software patching, monitoring, and hardware scaling.

In this post, we examine a use case in which you migrate your Sybase ASE database to Amazon RDS for SQL Server using SQL Server Migration Assistant (SSMA) for Sybase. We show you how to use SSMA to migrate the schema and data from Sybase ASE to Amazon RDS for SQL Server.

SQL Server Migration Assistant (SSMA) for Sybase is a tool for migrating Sybase ASE databases to Microsoft SQL Server. You can use SSMA for Sybase to convert Sybase ASE database objects to SQL Server database objects, create those objects in Amazon RDS for SQL Server, and then migrate data from Sybase ASE to Amazon RDS for SQL Server.

As of this writing, AWS Schema Conversion Tool (AWS SCT) does not have the support to use Sybase ASE as source and Amazon RDS for SQL Server as target. If you’re an application owner or migration specialist working on migrating Sybase ASE to Amazon RDS for SQL Server, you may have wondered which tool you can use to convert the schema and migrate the data.

Solution overview

The SSMA-based migration solution has the following major components:

The following diagram illustrates the solution architecture.

For our use case, we take the scenario in which our Sybase ASE database is running on Amazon EC2, and see how we migrate the user databases to Amazon RDS for SQL Server using SSMA for Sybase. The following diagram illustrates our architecture.

This solution has the following characteristics:

  • Sybase ASE is installed and configured to run on an Amazon EC2 server, which we refer to as the source database.
  • SSMA for Sybase tool is installed on an Amazon EC2 Windows Server. The user needs to Remote Desktop to the Amazon EC2 Windows Server to complete the migration. In our solution, we refer to Amazon EC2 Windows Server as the SSMA host.
  • Amazon RDS for SQL Server, which we refer to as the target database.

Prerequisites

To test this scenario, you must have the following prerequisites:

  • An AWS account
  • An EC2 instance with Sybase ASE installed and a user database (pubs2) created with tables and procedures
  • The Sybase ASE account, which we use to migrate the schema and data, created and granted the required permissions
  • SSMA for Sybase software downloaded on the SSMA host
  • An RDS for SQL Server instance created, and a user database created using the following command through SSMS: 
    CREATE DATABASE pubs2;

Install SSMA for Sybase

To install SSMA for Sybase, complete the following steps:

  1. Connect to the SSMA host using Remote Desktop.
  2. In the Download section, choose Install.
  3. Choose Next.

    You can ignore the warnings on missing components. We will install the drivers separately.
  4. Choose Next.
  5. Select I accept the agreement on the End-User License Agreement page and choose Next.
  6. Choose Complete on the Setup Type menu.
  7. On the next page, choose Install.
  8. On the SSMA for Sybase Installation Complete page, choose Finish to complete the installation and close the window.
  9. Download and install the latest ADO.NET Data Provider for Windows on the SSMA host.

Migrate the schema

To migrate the schema from Sybase ASE to Amazon RDS for SQL Server, complete the following steps:

  1. Connect to the SSMA host using Remote Desktop.
  2. On the Windows Start menu, navigate to Microsoft SQL Server Migration Assistant for Sybase and open it.
  3. On the File menu, open a new project and enter a name and location of your choice.
  4. For Migrate To, choose the same engine version you picked for Amazon RDS for SQL Server.
  5. Choose OK.
  6. On the Provider menu, choose Connect to Sybase.
  7. Choose ADO.NET Provider.
  8. Enter a server name, server port, user name, and password.
  9. Choose Connect.
  10. Select the databases you want to migrate.
    The databases master and subsystemprocs are selected by default and can’t be deselected.
  11. Choose OK.
  12. Choose Connect to SQL Server.
  13. For Server name, enter your RDS for SQL Server endpoint name.
  14. Enter a server port and database.
  15. For Authentication, choose SQL Server Authentication.
    For this post, we use SQL Server authentication, but SSMA also supports Windows Authentication. If you created the RDS for SQL Server instance with Microsoft SQL Server Windows Authentication enabled using the AWS Managed Microsoft AD option, you can choose Windows Authentication on the Authentication menu.
  16. Enter a user name and password.
  17. Select Encrypt Connection and Trust Server Certificate.
  18. Choose Connect.

    Now you can see the panels Sybase Metadata Explorer and SQL Server Metadata Explorer.
  19. Select the database you want to migrate in the Sybase Metadata Explorer panel and on the Tools menu, choose Create Report.

    An assessment report is generated, which can help you understand the level of complexity for this database migration.
    You can find a detailed view on the Details tab of the Microsoft SQL Server Migration Assistant report, where it’s broken down by object conversion and syntax conversion (see the following screenshots).
  20. On the Tools menu, choose Convert Schema.

You can see the converted schema saved in the SQL Server Metadata Explorer panel.

Here you have option to choose Synchronize with Database or Save as Script. If you want to manually review and deploy the schema changes, choose Save as Script. Choose Synchronize with Database for the SSMA tool to connect to the target database and create the database objects for you.

Before you complete the migration, you can review the database objects side by side between your source and target database.

In the Output panel, you can see the “Synchronization operation complete” message:

Synchronizing pubs2.dbo ...
Loading to database new table pubs2.dbo.au_pix ...
Loading to database new table pubs2.dbo.authors ...
Loading to database new table pubs2.dbo.blurbs ...
Loading to database new table pubs2.dbo.discounts ...
Loading to database new table pubs2.dbo.publishers ...
Loading to database new table pubs2.dbo.roysched ...
Loading to database new table pubs2.dbo.sales ...
Loading to database new table pubs2.dbo.salesdetail ...
Loading to database new table pubs2.dbo.stores ...
Loading to database new table pubs2.dbo.titleauthor ...
Loading to database new table pubs2.dbo.titles ...
Loading to database new procedure pubs2.dbo.byroyalty ...
Loading to database new trigger pubs2.dbo.titles.deltitle ...
Loading to database new procedure pubs2.dbo.discount_proc ...
Loading to database new procedure pubs2.dbo.history_proc ...
Loading to database new procedure pubs2.dbo.insert_sales_proc ...
Loading to database new procedure pubs2.dbo.insert_salesdetail_proc ...
Loading to database new procedure pubs2.dbo.storeid_proc ...
Loading to database new procedure pubs2.dbo.storename_proc ...
Loading to database new procedure pubs2.dbo.title_proc ...
Loading to database new procedure pubs2.dbo.titleid_proc ...
Loading to database new trigger pubs2.dbo.salesdetail.totalsales_trig ...
Loading to database new view pubs2.dbo.titleview ...
Loading to database new index pubs2.dbo.authors.auidind ...
Loading to database new index pubs2.dbo.titleauthor.auidind ...
Loading to database new index pubs2.dbo.authors.aunmind ...
Loading to database new index pubs2.dbo.publishers.pubind ...
Loading to database new index pubs2.dbo.salesdetail.salesdetailind ...
Loading to database new index pubs2.dbo.sales.salesind ...
Loading to database new index pubs2.dbo.titleauthor.taind ...
Loading to database new index pubs2.dbo.titleauthor.titleidind ...
Loading to database new index pubs2.dbo.salesdetail.titleidind ...
Loading to database new index pubs2.dbo.titles.titleidind ...
Loading to database new index pubs2.dbo.roysched.titleidind ...
Loading to database new index pubs2.dbo.titles.titleind ...
Loading to database new table pubs2.dbo.publishers check constraints ...
Loading to database new table pubs2.dbo.salesdetail check constraints ...
Loading to database new table pubs2.dbo.titles check constraints ...
Loading to database new table pubs2.dbo.authors columns default ...
Loading to database new table pubs2.dbo.titles columns default ...
Synchronization operation is complete.

Migrate the data

To migrate the data from Sybase ASE to Amazon RDS for SQL Server, complete the following steps:

  1. In the Sybase Metadata Explorer panel, on the Tools menu, choose Migrate Data.
  2. Select the source databases you want to migrate from.
  3. In the SQL Server Metadata Explorer panel, select the target databases you want to migrate to.

A Data Migration Report window opens with the migration details. You can save this report in CSV format for your reference.

The Output window also shows the progress and details for each table.

Validate the migration

To validate the migration, follow the instructions in Testing Migrated Database Objects (SybaseToSQL).

Best practices

The following best practices are recommended:

  • Create the SSMA host in the same AWS Region and Availability Zone as Amazon RDS for SQL Server to have minimum network latency between them.
  • Right-size the SSMA host in terms of CPU, memory, and storage based on your database size and data types in the database.
  • Navigate to the SSMA Tools menu and choose Project Settings to edit type mapping as needed.
  • Consider reducing the batch size when working on large tables with BLOB data types from 10000 to 7500.
  • Consider changing the data migration timeout value from 15 to a higher number for large databases to avoid timeout errors.

Clean up

To remove all the components created by this solution and avoid future charges, complete the following steps:

  1. Sign in to the AWS Management Console.
  2. Choose the Region where your EC2 instance and RDS for SQL Server instance reside.
  3. On the Amazon RDS console, choose Databases.
  4. Select the RDS for SQL Server instance.
  5. On the Actions menu, choose Delete.
  6. On the Amazon EC2 console, choose Instances.
  7. Select the EC2 instances used as source data and SSMA host.
  8. On the Instance state menu, choose Terminate instance.

Summary

In this post, we demonstrated how to install and configure the SSMA for Sybase tool on Amazon EC2 running Windows Server followed by schema and data migration from Sybase ASE to Amazon RDS for SQL Server using SSMA. Try out Amazon RDS for SQL Server and migrate your Sybase ASE database workload to AWS.

If you have any comments or feedback, please leave them in the comments section.

About the Author

Rajib Sadhu is Senior Database Specialist Solutions Architect with over 15 years of experience in Microsoft SQL Server and other database technologies. He helps customers architect and migrate their database solutions to AWS. Prior to joining AWS, he supported production and mission-critical database implementation across financial and travel and hospitality industry segments.