Migration guide: SAP ASE to SQL Server

Applies to: SQL Server

In this guide, you learn how to migrate your SAP ASE databases to SQL Server by using SQL Server Migration Assistant for SAP ASE (SSMA for SAP ASE).

For other migration guides, see Azure Database Migration Guides.

Prerequisites

Before you begin migrating your SAP ASE database to SQL Server:

Pre-migration

After you meet the prerequisites, you're ready to discover the topology of your environment and assess the feasibility of your migration.

Assess

By using SSMA for SAP ASE, you can review database objects and data, assess databases for migration, migrate Sybase database objects to SQL Server, and then migrate data to SQL Server. To learn more, see SQL Server Migration Assistant for Sybase (SybaseToSQL).

To create an assessment:

  1. Open SSMA for SAP ASE.

  2. On the File menu, select New Project.

  3. Enter a project name and a location to save your project. Then select SQL Server as the migration target from the dropdown list, and select OK.

  4. In the Connect to Sybase dialog box, enter values for SAP connection details.

  5. Right-click the SAP database you want to migrate, and then select Create Report to generate an HTML report.

  6. Review the HTML report to understand conversion statistics and any errors or warnings. You can also open the report in Excel to get an inventory of SAP ASE objects and the effort required to perform schema conversions. The default location for the report is in the report folder within SSMAProjects, as shown here:

    drive:\<username>\Documents\SSMAProjects\MySAPMigration\report\report_<date>.

Validate the type mappings

Before you perform a schema conversion, validate the default datatype mappings or change them based on requirements. You can go to the Tools menu and select Project Settings, or you can change type mapping for each table by selecting the table in SAP ASE Metadata Explorer.

Convert the schema

To convert the schema:

  1. (Optional) To convert dynamic or ad hoc queries, right-click the node, and select Add Statement.

  2. Select the Connect to SQL Server tab, and enter SQL Server details. You can choose to connect to an existing database or enter a new name, in which case a database will be created on the target server.

  3. Right-click the database or object you want to migrate in SAP ASE Metadata Explorer, and select Migrate Data. Alternatively, you can select the Migrate Data tab. To migrate data for an entire database, select the check box next to the database name. To migrate data from individual tables, expand the database, expand Tables, and then select the check boxes next to the tables. To omit data from individual tables, clear the check boxes.

  4. Compare and review the structure of the schema to identify potential problems.

    After the schema conversion finishes, you can save this project locally for an offline schema remediation exercise. On the File menu, select Save Project. This step gives you an opportunity to evaluate the source and target schemas offline and perform remediation before you publish the schema to SQL Server.

To learn more, see Converting SAP ASE database objects (SybaseToSQL).

Migrate

After you have the necessary prerequisites in place and have completed the tasks associated with the pre-migration stage, you're ready to perform the schema and data migration.

To publish your schema and migrate the data:

  1. Publish the schema by right-clicking the database in SQL Server Metadata Explorer and selecting Synchronize with Database. This action publishes the SAP ASE schema to the SQL Server instance.
  2. Migrate the data by right-clicking the database or object you want to migrate in SAP ASE Metadata Explorer and selecting Migrate Data. Alternatively, you can select the Migrate Data tab. To migrate data for an entire database, select the check box next to the database name. To migrate data from individual tables, expand the database, expand Tables, and then select the check boxes next to the tables. To omit data from individual tables, clear the check boxes.
  3. After the migration finishes, view the Data Migration Report.
  4. Connect to your SQL Server instance by using SQL Server Management Studio (SSMS), and validate the migration by reviewing the data and schema.

Post-migration

After you've successfully completed the migration stage, you need to complete a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible.

Remediate applications

After you migrate the data to the target environment, all the applications that formerly consumed the source need to start consuming the target. Accomplishing this task will require changes to the applications in some cases.

Perform tests

The test approach for database migration consists of the following activities:

  1. Develop validation tests: To test database migration, you need to use SQL queries. You must create the validation queries to run against both the source and the target databases. Your validation queries should cover the scope you've defined.
  2. Set up a test environment: The test environment should contain a copy of the source database and the target database. Be sure to isolate the test environment.
  3. Run validation tests: Run validation tests against the source and the target, and then analyze the results.
  4. Run performance tests: Run performance tests against the source and the target, and then analyze and compare the results.

Optimize

The post-migration phase is crucial for reconciling any data accuracy issues, verifying completeness, and addressing performance issues with the workload.

Note

For more information about these issues and the steps to mitigate them, see the Post-migration Validation and Optimization Guide.

Migration assets

For more assistance with completing this migration scenario, see the following resource. It was developed in support of a real-world migration project engagement.

Title Description
Optimization Guide for Mainframe App/Data recompiled to .NET & SQL Server This guide offers optimization advice for executing point-lookups against SQL Server from .NET as efficiently as possible. Customers who want to migrate from mainframe databases to SQL Server might want to migrate existing mainframe-optimized design patterns, especially when they use third-party tools (such as Raincode Compiler) to automatically migrate mainframe code (such as COBOL/JCL) to T-SQL and C# .NET.

Note

The Data SQL Engineering team developed these resources. This team's core charter is to unblock and accelerate complex modernization for data platform migration projects to Microsoft's Azure data platform.