Validate database objects after migrating from SAP ASE to Amazon RDS for SQL Server or Microsoft SQL Server

Migration of SAP ASE (previously known as Sybase ASE) to Amazon Relational Database Service (Amazon RDS) for SQL Server is heterogeneous in nature and consists of major phases such as pre-migration assessments, conversion of database schema, and post-migration validation. Because the migration process involves code conversion, it involves the crucial task of validating the converted database schema objects. The post-migration validation of database objects is an essential step that determines the success factor in the overall migration.

Because even a fully automated database migration process may involve manual conversion sometimes, there is always a chance of objects not getting converted wholly or a mismatch of key object features in the target database. This post is part of the series in which we have previously covered database object validation for migrations from SAP ASE as source to target databases like Amazon RDS for MySQL, Amazon RDS for MariaDB, MySQL or Amazon Aurora MySQL-Compatible Edition and Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. In the final post of this series, we focus on how to validate the database schema objects migrated from SAP ASE to Amazon RDS for SQL Server with the help of SQL queries to avoid such shortfalls.

Amazon RDS for SQL Server is a preferred choice for modernizing SAP ASE workloads with minimal changes because both databases have Transact-SQL (T-SQL) as their database programming language and Tabular Data Stream (TDS) as their communication protocol.

Prerequisites

The following prerequisites are required to perform the post-migration validation provided in this post:

• An SAP ASE source database.
• An RDS for SQL Server target database post migration.
• An Amazon Elastic Compute Cloud (Amazon EC2) instance or a SQL client with necessary permissions to connect to both source and target databases and run the validation SQL queries.
• A database user with public access to the primary database on the source SAP ASE database. The user must have SELECT permissions on the following system tables:
  • [source_db].dbo.sysobjects
  • [source_db].dbo.syscolumns
  • [source_db].dbo.sysconstraints
  • [source_db].dbo.syspartitionkeys
  • [source_db].dbo.sysindexes
  • [source_db].dbo.sysreferences
  • [source_db].dbo.sysusers
  • master.dbo.spt_values
• Database user with necessary privileges used to create the database objects in the target database.

Database object validation

Performing schema validation should always be the first step after you convert the source schema objects from SAP ASE to their equivalent SQL Server schema objects. To perform these validations, we first need to understand the different types of database objects to consider for post-migration validation.

The following list contains the database objects that you can compare between SAP ASE and the corresponding SQL Server database. Validating these objects thoroughly reduces issues during later stages of database migration and ensures minimal impact for the applications working with the migrated data.

• Schemas
• Tables
• Views
• Functions
• Stored procedures
• Indexes
• Triggers
• Constraints
  • Primary key constraints
  • Foreign key constraints
  • Check constraints
  • Default constraints

In the following sections, we look into each of these object types and validate migration using their corresponding SQL queries to help us identify any mismatch in the migrated schema objects.

If you find differences for any of the schema objects, identify the reason of failure. Convert the objects to the target database equivalent manually and apply the converted code on the target database. You need to manually correct the SQL scripts to replace the target equivalent syntax of the database object before running them on the target database.

Note that we have excluded system schemas in the source and target databases. We cover both summary-level as well as detail-level validations depending on the database object type, and you can further modify these queries to include more attributes or aggregates as needed. Precede the SQL Server validation scripts with “USE <DB>” to query the specific database being validated.

Schemas

Schemas are used to represent a set of database objects that serve a related functionality in an application. In SAP ASE, there is default schema called dbo, and if any user-defined schema is present, it’s the same as the user name of that specific user who is authorized to access objects in that schema. All database objects in SAP ASE have an owner; this object owner is effectively the same as a schema in SQL Server.

You can validate the schema at the source (SAP ASE) and target (SQL Server) using the following SQL queries.

Use the following SQL query for SAP ASE.

select distinct su.name as SchemaName
from sysusers su
inner join sysobjects so
on su.uid = so.uid
order by su.name

The following screenshot is an example of the SAP ASE output.

The following is a similar query for SQL Server.

select s.name as SchemaName
from sys.schemas s 
    inner join sys.sysusers u 
    on u.uid = s.principal_id 
where schema_id not in (2,3,4)  --(ignoring guest, INFORMATION_SCHEMA,sys schemas) 
and schema_id < 16380 -- (including only user schemas)

You get the following output.

Verify if the schema results match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, we have an additional schema sap created by the conversion tool along with the main schema dbo. We have excluded the sap schema in all other validation queries because it’s captured in this section.

Tables

In this section, we discuss tables under each schema (or owner) and detailed information for each table.

Tables under each schema

If you are using any migration tool for the conversion, it might convert source SAP ASE tables to the equivalent SQL Server target tables with appropriate data types and relative table definitions as per the target database syntax. To ensure that all the tables were migrated successfully, we can use the following script, which returns the schema and count of all the tables under it.

Use the following SQL query for SAP ASE.

select user_name(uid) as SchemaName,
       count(name) TableCount 
from sysobjects 
where type='U'
group by uid
order by user_name(uid)

You can see a sample output in the following screenshot.

The following is a similar query for SQL Server.

select s.name as SchemaName,
    count(t.name) as TableCount 
from sys.tables t 
    inner join sys.schemas s 
    on s.schema_id = t.schema_id 
group by s.name

We get the following output.

Verify if the table count match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly.

Detailed information for each table

Validating the count of the tables under the schema or object owner is not enough to conclude that all tables are migrated properly. The following SQL queries help in verifying a few key features of the tables involved in the migration, such as the presence of LOB columns, computed columns, partitions, and column count.

Use the following SQL query for SAP ASE.

select user_name(so.uid) SchemaName,
       so.name TableName,
       count(sc.name) ColumnCount,
       case when max(sc.computedcol) is not null then 'Y' 
            else 'N' 
       end ComputedColumn,
       case when max(sc.encrkeyid) is not null then 'Y' 
       else 'N' 
       end EncryptedColumn,
       case when max(case when sc.type in (34,35,174) then 1 
        else null 
        end) is not null then 'Y' 
            else 'N' end LOBColumn,
       case when max(sp.id) is not null then 'Y' 
            else 'N' 
       end PartitionTable
from sysobjects so
     inner join syscolumns sc 
     on so.id = sc.id  -- to get count
     left join syspartitionkeys sp 
     on so.id = sp.id --check partitions
where so.type = 'U' 
group by so.uid,so.name
order by user_name(so.uid),so.name

The following screenshots show our output.

Additionally, you can check the locking scheme of your migrated SAP ASE tables to ensure the lock scheme remains the same post-migration as well.

The following is a similar query for SQL Server.

select s.name as SchemaName,
       t.name as TableName,
       count(distinct col.name) as ColumnCount,
       case when c.is_computed = 0 or c.is_computed is null then 'N'
            when c.is_computed = 1 or c.is_computed is not null then 'Y' 
       end as ComputedColumn,
       case when col.encryption_type_desc is not null then 'Y'
            when col.encryption_type_desc is null then 'N' 
       end as EncryptedColumn,
       case when t.lob_data_space_id = 0 then 'N'
            when t.lob_data_space_id = 1 then 'Y' 
       end as LOBColumn,
       case when max(p.partition_number) > 1 then 'Y'
            when max(p.partition_number) = 1 then 'N' 
       end as PartitionTable
from sys.tables t  
     inner join sys.schemas s 
     on s.schema_id = t.schema_id
     left join sys.computed_columns c 
 on c.object_id = t.object_id
 inner join sys.columns col 
 on col.object_id = t.object_id
 inner join sys.partitions p 
      on p.object_id = t.object_id
group by t.name,s.name,lob_data_space_id,t.lock_escalation_desc,
         c.is_computed,col.encryption_type_desc,col.encryption_algorithm_name
order by s.name,t.name

The following screenshots show the output.

Verify if the table details match between the source and target database using the preceding SQL queries. If any differences are found, it indicates the mismatch or missing of any of the features compared, identify the cause of failure using deployment logs and fix them accordingly.

Views

A view is a virtual table created by a query joining one or more tables. You can validate the views by using the following queries on the source and target databases.

Use the following SQL query for SAP ASE.

select user_name(uid) SchemaName,
       name ViewName
from sysobjects
where type='V'
  and name not like 'sys%' --to filter system views
order by user_name(uid),name

The following screenshot shows the sample output.

The following is a similar query for SQL Server.

select sc.name SchemaName,
       v.name ViewName 
from sys.views v 
     inner join sys.schemas sc 
       on sc.schema_id = v.schema_id
order by sc.name

We get the following output.

Verify if the views match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure using deployment logs and fix them accordingly.

Functions

Functions implement specific business logic based on the given input and return a predefined format of output. The following queries give you the details of functions for both the source and target databases.

Use the following SQL query for SAP ASE.

select user_name(uid) SchemaName,
       so.name FunctionName
from sysobjects so
where type='SF' or type='F'

The following screenshot shows our output.

The following is a SQL query for SQL Server.

select schema_name(schema_id) AS 'SchemaName',
       name as 'FunctionName'
from sys.objects
where type in ('FN', 'IF', 'TF', 'AF', 'FS', 'FT');

The following screenshot shows the query output.

Verify if the function results match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly.

Stored procedures

A stored procedure is a collection of pre-compiled SQL statements stored in a database memory for quick access from an application program. Stored procedures have better performance compared to inline queries because the SQL queries are pre-compiled with reusable run plans. It also improves productivity because similar SQL statements or the business logic are consolidated and reused across applications or other programs. The following queries give you the count and details of stored procedures for both the source and target databases.

Use the following SQL query for SAP ASE.

select user_name(uid) SchemaName,
       so.name ProcedureName
from sysobjects so
where type='P'
  and name  not like 'sys%'
order by user_name(uid),so.name

You get the following output.

The following is a similar query for SQL Server.

select s.name as SchemaName,
       p.name as ProcedureName
from sys.procedures p  
     join sys.schemas s 
       on s.schema_id = p.schema_id
order by  s.name,p.name 

The output is as follows.

Verify if the procedure information match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly.

Indexes

Indexes in the database system play an important role in overall system performance. Although indexes can improve performance of read operations, it hinders write operations. Choosing the right indexing strategy is the key. There are several types of indexes available; the number of indexes and their type may differ from SAP ASE to SQL Server based on your use case.

With the following queries, you can get the list of indexes and their types in both SAP ASE and SQL Server databases.

Primary keys are not included in the following SQL query; we discuss them in a later section.

Use the following SQL query for SAP ASE.

select user_name(so.uid) SchemaName,
       so.name TableName,
       i.name IndexName,
       case when i.indid = 1 then 'CLUSTERED'
            when i.indid > 1 and i.status2 & 512 = 512 then 'CLUSTERED'
            when i.indid > 1 and i.status = 2 then 'UNIQUE'
            when i.indid > 1 then 'NON-CLUSTERED'
       end IndexType,
       index_col(so.name, i.indid, c.colid, so.uid) as ColumnName
from sysobjects so
       inner join sysindexes i
          on so.id = i.id
       inner join syscolumns c on c.id = so.id
where so.type='U' 
  and i.indid > 0
  and i.status & 2048 != 2048 -- to make sure not to include declarative PK
  and index_col(so.name, i.indid, c.colid, so.uid) is not null
order by user_name(so.uid), so.name, i.name

The following screenshots show the output.

You can use the following SQL query to fetch index information for SQL Server.

select s.name as SchemaName,
       t.name as TableName,
       i.name as IndexName ,
       case when i.type_desc = 'CLUSTERED' then 'CLUSTERED'
            when i.type_desc = 'NONCLUSTERED' and i.is_unique = 1 then 'UNIQUE'
            when i.type_desc = 'NONCLUSTERED' and i.is_unique = 0 then 'NON-CLUSTERED' 
       END AS IndexType,
       col.name ColumnName
from sys.tables t 
       inner JOIN sys.indexes i 
           on t.object_id = i.object_id
       inner join sys.schemas s 
           on s.schema_id = t.schema_id
       inner join sys.index_columns ic 
           on  i.object_id = ic.object_id 
           and i.index_id = ic.index_id
       inner join sys.columns col 
           on ic.object_id = col.object_id 
          and ic.column_id = col.column_id
where i.is_primary_key = 0 and (i.type_desc <> 'HEAP' or i.name is not null)
order by s.name,t.name,i.name

The following screenshots show our output.

Verify if the index details match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly.

Triggers

Triggers define a set of actions that are performed in response to an insert, update, or delete operation on a specified table. The following queries give you the details of triggers for both the SAP ASE and SQL Server databases.

Use the following SQL query for SAP ASE.

select user_name(so.uid) TableSchema,
       user_name(tr.uid) TriggerSchema,
       so.name TableName ,
       tr.name TriggerName
from sysobjects so
       inner join sysobjects tr
          on (tr.id = so.deltrig or tr.id = so.instrig or tr.id=so.updtrig)
where so.type = 'U'
order by user_name(so.uid),so.name

You get the following output.

Use the following SQL query for SQL Server database.

select Schema_name(t.schema_id) TableSchema,
       Schema_name(o.schema_id) TriggerSchema,
       Object_name(o.parent_object_id) TableName,
       o.name AS TriggerName
from sys.objects o
        inner join sys.objects t
          on o.parent_object_id = t.object_id
where o.type = 'TR'
order by Schema_name(o.schema_id),Object_name(o.parent_object_id)

You get the following output.

Verify if the trigger details match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly.

Constraints

Constraints offer the advantages of defining integrity controls in one step during the table creation process and simplifying the creation of those integrity controls. However, integrity constraints are more limited in scope and less comprehensive than defaults, indexes, and triggers.

Primary key constraint

Use the following SQL query for SAP ASE.

select user_name(so.uid) SchemaName,
       so.name TableName,
       i.name PK_ConstraintName,
       index_col(so.name,i.indid,c.colid,so.uid) as ColumnName
from sysobjects so
      inner join sysindexes i
        on so.id = i.id
      inner join syscolumns c
        on c.id = so.id
where so.type = 'U'
  and i.indid > 0
  and i.status2 & 2 = 2 -- for declarative constraints
  and i.status & 2048 = 2048 -- for primary Key
  and index_col(so.name, i.indid, c.colid, so.uid) IS NOT NULL
order by user_name(so.uid), so.name, i.name

You get the following output.

Use the following SQL query for SQL Server database.

select schema_name(t.schema_id) SchemaName,
       t.name as TableName,
       i.name as PK_ConstraintName,
       col.name ColumnName
from sys.objects t
        left outer join sys.indexes i 
           on t.object_id = i.object_id
        left outer join sys.key_constraints c 
           on i.object_id = c.parent_object_id 
          and i.index_id = c.unique_index_id
        inner join sys.index_columns ic 
           on i.object_id = ic.object_id 
          and i.index_id = ic.index_id
        inner join sys.columns col 
           on ic.object_id = col.object_id 
          and ic.column_id = col.column_id
where c.[type] = 'PK'
order by SchemaName,t.name,i.name

The following screenshot shows the output.

Foreign key constraint

Use the following SQL query for SAP ASE.

select object_name(sr.tableid) TableName,
       object_name(constrid) FK_Name, 
       object_name(sr.reftabid) Ref_TableName,
       index_col(so.name, sr.indexid, sc.colid, so.uid) as Ref_ColumnName
from sysreferences sr
        inner join syscolumns sc
          on sr.reftabid = sc.id
        inner join sysobjects so
          on so.id = sc.id
where index_col(so.name, sr.indexid, sc.colid, so.uid) is not null
order by object_name(sr.tableid), object_name(constrid), object_name(sr.reftabid)

You get the following output.

Use a similar SQL query for SQL Server database.

select tab1.name as TableName,
       obj.name AS FK_NAME,
       tab2.name AS Ref_TableName,
       col2.name AS Ref_ColumnName
from sys.foreign_key_columns fkc
        inner join sys.objects obj
          on obj.object_id = fkc.constraint_object_id
        inner join sys.tables tab1
          on tab1.object_id = fkc.parent_object_id
        inner join sys.schemas sch
          on tab1.schema_id = sch.schema_id
        inner join sys.columns col1
          on col1.column_id = parent_column_id 
         and col1.object_id = tab1.object_id
        inner join sys.tables tab2
          on tab2.object_id = fkc.referenced_object_id
        inner join sys.columns col2
          on col2.column_id = referenced_column_id 
         and col2.object_id = tab2.object_id
order by tab1.name,obj.name, tab2.name

You get the following output.

Check constraint

Use the following SQL query to get a summary of check constraints on the SAP ASE database.

select user_name(o.uid) SchemaName,
       object_name(c.tableid) TableName,
       count(o.name) CheckConstraintCount
from sysconstraints c
     inner join sysobjects o
       on c.constrid = o.id
where (o.sysstat & 15 = 7)
group by o.uid,c.tableid
order by user_name(o.uid),object_name(c.tableid)

You get the following output.

Use a similar query for SQL Server.

select Schema_name(t.schema_id) AS SchemaName,
   t.name as TableName,
   count(cns.name) as CheckConstraintCount
from sys.check_constraints cns
left outer join sys.objects t
on cns.parent_object_id = t.object_id
left outer join sys.all_columns col
on cns.parent_column_id = col.column_id
    and cns.parent_object_id = col.object_id
where cns.name not like '%rule%'
group by t.schema_id,t.name
order by Schema_name(t.schema_id),t.name

You get the following output.

Default constraint

Use the following SQL query for SAP ASE.

select user_name(o.uid) SchemaName,
       object_name(c.id) TableName,
       c.name Defaulted_ColumnName
from syscolumns c
 inner join sysobjects o
   on c.cdefault = o.id
order by object_name(c.id), o.name, c.name

You get the following output.

Use a similar query for SQL Server.

select schema_name(o.schema_id) as SchemaName,
object_name(o.parent_object_id) as TableName,
c.name as ColumnName
from sys.objects o
inner join sys.default_constraints d
     on o.name = d.name
inner join sys.columns c
     on c.column_id = d.parent_column_id
and c.object_id = o.parent_object_id
where o.TYPE = 'D'
order by TableName,o.name

You get the following output.

Verify if the constraint details match between the source and target database using the preceding SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly.

Conclusion

In this post, we discussed validation of different database objects with queries for SAP ASE and SQL Server database. These queries work for SAP ASE 15.7+ and SQL Server 2019. You can use this post as a reference guide for related database object validation to help you plan your database migration journey.

Database migration should always be followed by testing, including unit testing, functional testing, and regression testing for all the objects irrespective of automatic or manual migration. This saves a lot of reworking when you conduct integration testing with your applications or interface.

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

About the authors

Pavithra Balasubramanian is a Database Consultant with Amazon Web Services in India. She is passionate about helping customers in their cloud adoption journey with a focus on legacy database migrations to AWS cloud.

Nishad Mankar is a Database Consultant with AWS Professional Services. He helps customers to migrate and modernize their databases on AWS Cloud.

Amit Kumar is a Database Consultant with AWS Professional Services based out of Hyderabad, India. With good knowledge on relational databases adding hands-on in homogenous and heterogenous database migrations and optimization in AWS cloud.