A database migration project from a non-SAP database to an SAP database has many aspects to be considered:
- Schema migration
- Data migration
- Server-side SQL code conversion
- Client-side SQL code conversion
- Client application conversion and connectivity
- DBA tools
- Functional and performance testing
- …
As mentioned in previous blogs an other related links SAP Advanced SQL Migration is a SQL dialect conversion tool whose main target (even though able to help in other areas like schema conversion) is convert SQL code, other aspects were out of the scope of the tool initially. But as SAP Advanced SQL Migration is evolving and used in different projects, as we get more real experience with it and we are in touch with customers we are seeing some needs/requests/areas it can help at least in part. One of them is the data migration and that will be the focus in this blog.
There are many different ways to perform the data migration both using SAP and non-SAP technologies, for example you can extract data to files and load them into the target SAP HANA database, you can use an ETL tool like SAP Data Services, or use SAP Replication Server that will allow you to perform the initial load plus real time replication if needed, or maybe SAP HANA features like Smart Data Access (known as SDA) and Smart Data Integration (known as SDI) … etc. Among these different ways and others not mentioned here SDA turns out to be an easy one and simple to configure so we have developed some stuff inside the tool to help in the data migration task.
SDA basically consists in accessing remote tables located in the source database via virtual tables or via the linked database SAP HANA feature. SAP HANA 2.0 supports the Linked Database feature (tables can be referenced by name under the linked database so no virtual tables are required) for Oracle, Microsoft SQL Server, DB2 LUW and Teradata but not for Netezza, so for the latter virtual tables are required.
SDA requires some SAP HANA configuration that is out of the scope of SAP Advanced SQL Migration, that should be done by SAP HANA administrator and will be at Operating System level:
- UnixODBC driver manager has to be installed
- Corresponding ODBC drivers have to be installed and configured following documentation provided by the owner of the driver and SAP HANA Administration guide
To generate SDA related scripts from SAP Advanced SQL Migration:
You have to configure option number #703 ("Generate DDL/DML for data migration") to "Hana_sda"
What SAP Advanced SQL Migration generates regarding SDA:
Readme file with details and hints to configure the corresponding ODBC driver for the source database and also with details to configure some files required in the SAP HANA side like .customer.sh, .odbc.ini file, tnsnames.ora (when apply because oracle is the source) ...etc
- README_SDA_GENERATED_INFO.txt
Readme file with specific details to add to specific files like .odbc.ini, tnsnames.ora (for oracle) ...etc
- dbmtk_create_remote_sources.sqlscript
- For Oracle, SQL Server, DB2 LUW and Teradata sources this is the SAP HANA SQLscritpt to create the remote source (SAP HANA supports linked DB feature for these sources so virtual tables are not required)
- For Netezza source this this the SAP HANA SQLScript to create both the remote source and the virtual tables (linked DB feature is not supported for Netezza so the virtual tables are required in this case)
- dbmtk_data_load.sqlscript
- For Oracle, SQL Server, DB2 LUW and Teradata sources this is the SAP HANA SQLscript to be executed to perform the data load accessing data from the remote tables via linked DB
- For Netezza source this is the SAP HANA SQL script to be executed to perform the data load accessing data from the virtual tables created pointing to the remote tables (for Netezza)
- run_sql_scripts_data_load.sh
Unix shell script calling the previous script to perform the data load
- dbmtk_create_indexes_constraints.sqlscript
When option number 703 is set to "Hana_sda" SAP Advanced SQL migration generates the "create index" and "create constraint" commands in a separate script to be able to execute them after the data load has been done, this avoids foreign key constraints violations and other constraint errors making the data load fail
Other related posts:
