Sybase ASE to Azure SQL full and incremental data copy using ASE Transfer Table Tool and ADF

[vishal]

Introduction

Heterogenous (different source and target DBMS) Database Migrations are usually challenging due to the inherent complexity with data type conversions and limited availability of tools for data migration for both offline and online migration scenarios. This challenge is further increased if the size of database to be migrated is large for initial load and/or frequent incremental data (Change Data Capture) copy is required from source to target database. Online Database migration from SAP ASE (Sybase ASE) to Azure SQL is a challenge as ASE does not offer any CDC table feature which can assist in an online migration, so there is a need to rely on 3rd party solutions.

In this BLOG we will look at the SAP ASE Data Transfer Utility, which offers T-SQL command ‘transfer table … to’ for initial and incremental data loads. It was observed that the throughput for Transfer Table is close to 3 or 4 times that of 'SAP Sybase bcp out' command. The transfer table feature can be chosen as an alternative to 3rd party solutions.

What is Transfer Table (Data Transfer Utility of ASE)?

Transfer table provides a means to extract data from tables marked eligible for incremental transfer to a flat file, selecting only the rows which have changed since previous transfer was executed on the same table. The file can be created on the ASE host or a File Share/NFS filesystem.

The extracted rows can be written to the output file in a format fit for the target system. The 'for' clause of the command can specify the formats SAP IQ, SAP ASE, bcp file, or character-coded output. The selected rows are extracted by default without encryption and if the source table has encrypted columns, then these rows are decrypted before being transmitted.

Once enabled for incremental transfer, the routine keeps track of the changed data instead of scanning every row in the table unlike replication which monitors the transaction log. The first execution would extract the complete table to the output file. A table eligible for incremental transfer will only send data which has been changed or new data inserted during the subsequent execution and has been committed at the time the transfer is initiated. Tables are marked as eligible using the option ‘set transfer table on’ to the create table or alter table command. They continue to remain in same mode until the option 'set transfer table off' is used to disable it in the alter table command.

The incremental transfer is tracked by a sequence marker which is created in the table row when the table is enabled for incremental transfer. Every row in the eligible table has a marker. The marker along with the transfer history stored in an internal table 'spt_TableTransfer' tracks the changes in the table data. Since the marker is stored, eligible tables require more space. Transfer table fetches the table data minus the usual locks and does not impact the ongoing transactions.

License Requirements

The Data Transfer Utility feature comes bundled with the ASE IMDB (In-Memory Databases) license. Please refer to the Sybase ASE documentation for further details on licensing.

Transfer table of Non-Eligible Tables

The Transfer table command can also be utilized for tables that are not enabled for incremental transfer. However, this does not capture the incremental data. This can be used in the case of One-time migration.

The transfer table extracts the entire table, but subsequent executions do not refer to the previous iteration.

In the case of updates on the table during the transfer, these are not captured.

Limitation

• The transfer table utility does not capture hard deletes. Hence this solution can be implemented for only soft deletes. 
• Uncommitted data will not be transferred. The utility only sends data which are committed before the transfer starts. In case a transaction commits when the transfer is in progress, the data is not transferred, i.e. If a user updates the table while the transfer is in progress, the updated rows will not be transferred. Subsequent re-runs will capture this data/updates.
• Simultaneous execution of transfer table on a table will not start multiple transfers. Only one active transfer will be in progress and other executions will be in sleep state.

Monitoring and Tracking Transfers

• The spt_TableTransfer table displays completed transfers of eligible tables. Each database has this table and records the stats of successful and of unsuccessful transfers of each eligible table.
• The monTableTransfer displays information about ongoing transfers of tables from memory. It displays information about all the eligible tables and non-eligible tables which are currently being transferred.

Enabling Transfer table

• Syntax:

  transfer table [[db.]owner.]table [to | from] destination_file
              [ for { ase | bcp | iq | csv } ]
              [ with {column_separator=string}, {column_order=option},
              {encryption=option}, {row_separator=string},
              {resend=id}, {progress=sss}, {tracking_id=nnn}
              {sync = true | false]}, {fixed_length = true | false}
                          , null_byte = true | false}]

• Adding the incremental transfer attribute to the table:

  create table <table_name> …. with transfer table on
  or
  alter table <table_name> set transfer table on

• The ‘transfer table … to’ command which is the focus of this document:

  transfer table <table_name> to <destination_file> for csv with column_separator ='|'

  • csv – a format of character-coded values. Rows are output as character-coded data. Columns are separated by a designated column separator, and rows are terminated by a designated row terminator. Separators and terminators are user-defined.
  • destination_file - any file or path name that is valid for the operating system, and that Adaptive Server can access. If the file is a relative file path, Adaptive Server provides its absolute path.
• Managing destination directory path

The ‘transfer table … to’ Command, will not write output files to any directory, but to a defined output directory or its subdirectories. This directory path can be modified using the 'sp_file_path' stored procedure for the specific database. If it is not specified, the default is $SYBASE/data.

Syntax:

• sp_file_path '<database_name>', 'dtu path root', '<directory_path>'

Security and Permissions

By default, ‘transfer table’ permission is allocated to the owner of that table and to System Administrators. The table owners can grant this permission to specific users. However, this grant does not give permission to decrypt data within that table. Table owners need to specifically provide decrypt permission.

Demonstrating Data migration test using Transfer table and ADF

Database:

• Source: SAP ASE version 16.0 SP03 running on Standard D8ds v4 (8 vCPUs, 32 GiB memory)
• Target: Azure SQL Database – General Purpose, Gen5 80vCores

ELT Platform:

• ADF

Requirements:

• Create a table ‘columns_info’ on the source side at ASE which will hold the table column names.

  CREATE TABLE columns_info (
   NAME VARCHAR (200) NOT NULL
  )

• Create a table ‘tablekey’ on the target side at Azure SQL DB which will hold the table name and its primary key.

  CREATE TABLE tablekey (
  TABLENAME    VARCHAR (200)   PRIMARY KEY,
  KEYCOLUMN    VARCHAR (200)  NOT NULL
  )

• Execute the system procedure sp_setup_table_transfer. Run the procedure once in each database containing the tables marked for incremental transfer to create the spt_TableTransfer table.
• Create the below stored procedure on the source side i.e., SAP ASE. The procedure does the following: - BCP out the table ‘columns_info’ into a csv file
  - Enable transfer table on the required tables
  - Execute transfer table on the above tables to csv files
  - The script will prefix the appropriate sequence id into the file name based on the first transfer or the subsequent incremental transfer
  - The backup location can be specified in the path variable in the procedure  

  create procedure proc_transfertable
   as
  -- declare local variables
   DECLARE @tab VARCHAR(255)
   DECLARE @path VARCHAR(255)
   DECLARE @fileName VARCHAR(255)
   DECLARE @cmd VARCHAR(255)
   DECLARE @extn VARCHAR(255)
   DECLARE @spconf VARCHAR(255)
   DECLARE @xp VARCHAR(255)
   DECLARE @spfilepath VARCHAR(255)
   DECLARE @fp VARCHAR(255)
   DECLARE @tton VARCHAR(255)
   DECLARE @ttexec VARCHAR(255)
   DECLARE @sequence_id VARCHAR(255)
   DECLARE @sequenceid INT
   DECLARE @movecsv VARCHAR(255)
  
  -- setting permission to execute xp_cmdshell   
           
   SET @spconf = "sp_configure 'xp_cmdshell context', "
   SET @xp = @spconf + "0" 
   exec (@xp)
  
  -- retrieve the list of table names eligible for transfer
  
   DECLARE cur_tablename cursor for 
   select name as table_name from sysobjects where type = 'U' and name not in ('spt_TableTransfer', 'columns_info')
  
  -- open cursor and fetch each tablename into the cursor for the while loop
  
   open cur_tablename
   fetch next from cur_tablename into @tab
   while @@fetch_status = 0
  
   BEGIN   
   -- truncate the columns_info table and insert the table column names 
  
          IF OBJECT_ID('edrive..columns_info') IS NOT NULL
          BEGIN
                  DELETE edrive..columns_info
          END                    
                  insert edrive..columns_info
                  select syscolumns.name   
                  from sysobjects 
                  join syscolumns on sysobjects.id = syscolumns.id
                  where sysobjects.name = @tab
                  
  -- setting path for bcp out 
  -- executing bcp out of table columns_info if the outfile does not exist       
                  
                  SET @path = "D:\SybaseData\"
                  SET @extn = " -U sa -P abc123 -c -r ""|" + """"
                  SET @fileName = @path + @tab + "_" + "columns_info.csv"
                  SET @cmd = "master..xp_cmdshell 'IF NOT EXIST " + @fileName + " (bcp edrive..columns_info out " + @fileName + @extn + ") ELSE (echo file exists)'"                
                  print @tab
                  print @cmd
                  exec (@cmd)
                  
  -- setting filepath for the transfer table destination              
                  
                  SET @spfilepath = "sp_file_path 'edrive', 'dtu path root', "
                  SET @fp = @spfilepath + "'" + @path + "'"                 
                  print @fp
                  exec (@fp)
                  
  -- enable transfer table on for the table               
                  
                  SET @tton = "alter table "+ @tab + " set transfer table on"                
                  print @tton 
                  exec (@tton)
  
  -- fetch sequence id for the table 
  -- prefix the appropriate sequence id into the file name
  
                  select @sequenceid = isnull (max (sequence_id),0) + 1 from sysobjects a, spt_TableTransfer b where a.name = @tab and a.id = b.id
                  SET @sequence_id = CONVERT(VARCHAR(255), @sequenceid)
                  print "sequence_id = %1! ", @sequence_id 
                  
  -- execute the transfer table command               
                  
                  SET @ttexec = "transfer table " + @tab + " to '" + @path + @tab + "___" + @sequence_id + ".csv'" + " for csv with column_separator ='|'"                
                  print @ttexec
                  exec (@ttexec)                
                  fetch next from cur_tablename into @tab         
          END        
          close cur_tablename
          
  -- remove permission to execute xp_cmdshell   
  
          SET @spconf = "sp_configure 'xp_cmdshell context', "
          SET @xp = @spconf + "1" 
          exec (@xp)
      
  return

• Setup an Azure File Share on the host where the ASE is running. For eg: sybasefileshare as listed below:

• Create the ADF pipeline as below:

1. ADF Pipeline overview:
2. Copy Data Source:
3. Source Dataset:
4. Copy Data Sink:
5. Sink Dataset:
6. Copy Header Source:
7. Copy Header Sink:   
8. Sink Dataset for Header:
9. Get Metadata:
10. For Each activity:
11. Lookup activity:
12. Lookup Source Dataset:
13. Dataflow Settings
    
14. Data Flow Parameters: 
15. Data Flow Stream:
16. Data Flow Header Source Settings: 
17. Data Flow Header Source Options:
18. Data Flow Data Source Settings:
19. Data Flow Data Source Options:
20. Data Flow Union Settings:
21. Data Flow Alter Row Settings for Upsert:
22. Data Flow Final Sink:   
23. Data Flow Final Sink Settings

Test run:

(Please note the mentioned timings for the test may vary based on the machine/environment configuration and workload)

Data Set:

• Two candidate tables for migration on SAP ASE 16.0 SP03

• Executed the procedure to back up the initial load. 1GB each of total table data took roughly 4mins.

• Updates ran against the table.

• Executed the procedure again.

• Files are generated in the path specified.

• Copy the csv files to Azure File Share

• Inserted the key column values in the tablekey table on SQL DB

  insert into tablekey ( tablename, keycolumn )
  values ('train','TRIP_ID') 
  
  insert into tablekey ( tablename, keycolumn )
  values ('trip','ID') 

• Target SQL DB is configured on Business Critical: Gen5, 80 vCores
• Executed the ADF pipeline. The pipeline took 6 mins to complete the execution.

• On Target SQL DB side:

 

Conclusions:

The tests results demonstrated the following:

• This feature helps to extract table data to csv files with the least load on the source server.
• It provides CDC like features to track and extract incremental data.
• Combining this with the ADF tool/pipeline we were successfully able to apply the updates on the target SQL DB.
• Initial transfer table extract of two tables of size 1113 MB and 1670 MB to csv took 243 seconds
• Re-executed the procedure after updates were done against the tables of 200 MB and 325 MB and this took 2.3 seconds
• The ADF pipeline took 6 mins to complete the execution.

(Note: These results are from our internal tests. Please note the above-mentioned times may vary based on the machine/environment configuration and workload.)

Feedback and suggestions:

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!