Migration from Sybase ASE using SSMA CLI

[IdaliaL]

Recently I learned a lesson through multiple testing around SSMA for Sybase. Let’s suppose you need to migrate your data from Sybase ASE to SQL Server. You could use the SSMA GUI tool to connect to both destinations and select schemas or objects to convert and migrate. Now what if you needed to automate this process?

You can use SSMA for Sybase. This tool includes a CLI (Command Line Interface) version that allows you to run assessment reports, converts ASE database objects to SQL Server database objects and then migrates data like the GUI version. I found this very useful as there is not much documentation, so we decided to share this information to help your migration for this specific scenario.

You can use the command to programmatically perform your migration seamlessly without the need to use the SSMA GUI as you can also handle repetitive tasks easily.

To use the command line, you run the executable SSMAforSybaseConsole.EXE and use xml file to modify the parameters. There are many options that you can change based off the templates included when you install the tool. By default, the templates are in the below location:

C:\Program Files\Microsoft SQL Server Migration Assistant for Sybase\Sample Console Scripts\

In the location you can find some scripts very useful

• AssessmentReportGenerationSample.xml: This sample enables the user to generate an xml assessment report which can be used by the user for analysis before they begin to convert and migrate data.
• ConversionAndDataMigrationSample.xml: This sample enables the user to perform an end to end migration from conversion to data migration. You can include the connection directly on the conversion or assessment file xml.
• ServersConnectionFileSample.xml: This sample gives the different modes of connection available to the source and target database and the user can select any mode as per the requirement. This sample contains the Server definitions.

I suggest checking the files as they include good examples. More information at Work with Sample Console Script FilesExecuting the SSMA Console - SQL Server | Microsoft Learn

Below are some examples of the files I used to make some dry run testing:

1. Command to run assessment report:
   C: >SSMAforSybaseConsole.EXE -s "C:\SSMA\AssesmentReportGeneration.xml" -c "C:\SSMA\ ServersConnectionFileSample.xml"
   
   
2. Command to run migration:

C: >SSMAforSybaseConsole.EXE -s "C:\SSMA\ConversionAndDataMigration.xml" -c "C:\SSMA\ ServersConnectionFileSample.xml"

ServersConnectionFileSample.xml

<?xml version="1.0" encoding="utf-8"?>

<servers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="..\Schemas\S2SSConsoleScriptServersSchema.xsd">

  <sql-server name="PUB1\SQL2017ST">

    <windows-authentication>

      <server value="PUB1\SQL2017ST" />

      <database value="sybase" />

      <encrypt value="false" />

      <trust-server-certificate value="false" />

    </windows-authentication>

  </sql-server>

  <sybase name="SYBASE">

    <standard-mode>

      <provider value="Odbc Provider"/>

      <server-name value="SYBASE" />

      <server-port value="5000" />

      <user-id value="sa" />

      <password value="mypasswordhere" />

    </standard-mode>

  </sybase>

</servers>

AssessmentReportGenerationSample.xml:

<?xml version="1.0" encoding="utf-8"?>

<ssma-script-file xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="..\Schemas\S2SSConsoleScriptSchema.xsd">

  <config>

    <output-providers>  

      <output-window suppress-messages="false"

                     destination="stdout" />

      <upgrade-project action="yes" />

      <progress-reporting enable="false"

                          report-messages="false"

                          report-progress="off" />

    </output-providers>

  </config>

  <script-commands>

       <create-new-project project-folder="C:\SSMA"

                        project-name="SSMACLI"

                        project-type="sql-server-2017"

                        overwrite-if-exists="true" />

  <connect-source-database server="SYBASE" />

    <generate-assessment-report>

      <metabase-object object-name="pubs2.dbo.Procedures"

                       object-type="category" />

    </generate-assessment-report>

    <force-load object-name="pubs2.dbo" object-type="Schemas" metabase="source" />

    <save-project/>

    <close-project/>

  </script-commands>

</ssma-script-file>

ConversionAndDataMigrationSample.xml

<?xml version="1.0" encoding="utf-8"?>

<ssma-script-file

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="..\Schemas\S2SSConsoleScriptSchema.xsd">

              <config>

                             <output-providers>

                                           <output-window suppress-messages="false"

                     destination="stdout" />

                                           <prerequisites strict-mode="false"/>

                                           <user-input-popup mode="ask-user" />

                                           <upgrade-project action="yes" />

                                           <data-migration-connection source-use-last-used="true"

                                 target-server="PUB1\SQL2017ST" />

                                           <progress-reporting enable="false"

                          report-messages="false"

                          report-progress="off" />

                                           <reconnect-manager on-source-reconnect="reconnect-to-last-used-server"

                         on-target-reconnect="reconnect-to-last-used-server" />

                                           <object-overwrite action="skip" />

                                           <log-verbosity level="error" />

                             </output-providers>

              </config>

              <script-commands>

                             <create-new-project project-folder="C:\SSMA"

                        project-name="SSMACLI"

                        project-type="sql-server-2017"

                        overwrite-if-exists="true" />

                             <connect-source-database server="SYBASE" />

                             <connect-source-database server="SYBASE">

                                           <object-to-collect object-name="sales" />

                             </connect-source-database>

                             <connect-target-database server="PUB1\SQL2017ST" />

                             <map-schema source-schema="pubs2.dbo"

                sql-server-schema="dbo" />

                             <refresh-from-database object-name="pubs2.dbo"

                           object-type="Schemas"

                           on-error="fail-script"

                           report-errors-to="C:\SSMA\SourceDBRefreshReport.xml" />

                             <convert-schema object-name="sybase.dbo.sales"

                        object-type="Tables"

                        report-errors="true"

                        write-summary-report-to="C:\SSMA\SchemaConversionReport.xml" />

                             <synchronize-target>

                                           <metabase-object object-name="sybase.dbo.sales"

                          object-type="Tables" />

                             </synchronize-target>

                             <migrate-data write-summary-report-to="C:\SSMA\DataMigrationReport.xml"

                  verbose="true">

                                           <metabase-object object-name="pubs2.dbo.sales" />

                             </migrate-data>

                             <save-project/>

                             <close-project/>

              </script-commands>

</ssma-script-file>

Below are answers to some frequently asked questions from customers on how to modify the templates:

Q: How can I run an assessment report for different types of objects? For example, migrate 3 tables and 2 stores procedures.

A: Within the tag <generate-assessment-report> we add “metabase-object object-name” by object. For example:

<generate-assessment-report>

   <metabase-object object-name="pubs2.dbo.Tables" object-type="category"/>                                                                                              

   <metabase-object object-name="pubs2.dbo.Procedures" object-type="category"/>                                                                                                          

</generate-assessment-report>

Q: Is there a way to run an assessment report only for tables and not include the indexes?

A: You cannot select only the tables. When tables are selected in console scripts, the associated triggers and Indexes are automatically selected.

Q: Can I run the assessment report by object type? For example, only stored procedures

A: Yes, an example below

<generate-assessment-report>

<metabase-object object-name="test.dbo.Procedures"

                       object-type="category" />

 </generate-assessment-report>

Q: Can I include multiple objects in one report?

A: Yes, example below

<generate-assessment-report>

<metabase-object object-name="pubs2.dbo.byroyalty" object-type="Procedures" />                                                                                      

<metabase-object object-name="pubs2.dbo.title_proc" object-type="Procedures" />  

<metabase-object object-name="pubs2.dbo.sales object-type="Tables" />                                                                                                                                                                     

</generate-assessment-report

Q: How can I run conversion and migration for multiple objects?

A: “metabase-object object-name” tag is also used in conversion and migration. Examples below

<!—Convert objects -->

<convert-schema>

             <metabase-object object-name="pubs2.dbo.byroyalty"

                       object-type="Procedures" />                                                                                      

             <metabase-object object-name="pubs2.dbo.title_proc"

                       object-type="Procedures" />  

             <metabase-object object-name="pubs2.dbo.sales"

                       object-type="Tables" />      
   </convert-schema>

<!—Migrate Data -->

<migrate-data>

             <metabase-object object-name="pubs2.dbo.byroyalty"

                       object-type="Procedures" />                                                                                      

             <metabase-object object-name="pubs2.dbo.title_proc"

                       object-type="Procedures" />  

             <metabase-object object-name="pubs2.dbo.sales"

                       object-type="Tables" />      
   </migrate-data>

Happy migration!