Tags: In-Memory OLTP
This continues our video blog series about new features on the new capabilities of SQL Server Migration Assistant 7.0. SQL Server 2016 In-Memory OLTP significantly improves OLTP database application performance and allows running large queries against the same tables using clustered and non-clustered columnstore indexes. This technology was introduced in SQL Server 2014 and now SSMA 7.0 supports migrating Oracle Database In-Memory tables to In-Memory OLTP with SQL Server 2016.
Background on SQL Server In-Memory OLTP and Oracle Database In-Memory
SQL Server 2016 In-Memory OLTP is a high performance, memory-optimized engine, designed for modern hardware trends. It allows users to move data into memory resident tables while keeping other data in traditional, disk-based table structures. In-Memory OLTP introduces two fundamentally new concepts: memory-optimized tables and natively compiled stored procedures. But the most important moment about this technology is that data in memory-optimized tables resides in RAM which helps speed up data processing transactions up to 30x.
Oracle introduced Database In-Memory technology in 12c version. It provides a dual-format architecture that enables tables to be simultaneously represented in memory using traditional row format and a new in-memory column format.
If you use the databases for high performance transactions, real-time analytics, business intelligence, and reports and now plan to convert them into SQL Server In-Memory OLTP, we highly recommend you to use SSMA 7.0. The tool will also help you convert ordinary tables into SQL Server In-Memory OLTP.
In this video below we show you how to convert Oracle Database In-Memory into SQL Server In-Memory OLTP and demonstrate several conversion options for Oracle Database In-Memory tables. Additionally, we explain conversion of regular tables into SQL Server In-Memory OLTP.
Need help getting started? Check out our SSMA Jumpstart Offer!
For IBM DB2 users, if you have tables that use BLU Acceleration, you can migrate them into SQL Server In-Memory OLTP using the SSMA 7.0 for DB2.
Known issues migrating Oracle Database In-Memory
There are some limitations which you have to consider when migrating to the SQL Server In-Memory OLTP.
- The total size of all In-Memory tables in SQL Server should not exceed the half of all available memory. The user should review the memory allocation on the target SQL Server to make sure that this option will work properly.
- The memory-optimized table must contain a primary key.
- There’s a list of unsupported column data types which includes: (n)text, image, (n)varchar(max), varbinary(max), rowversion (and timestamp), sql_variant, CLR types (hierarchyid and spatial types), XML for In-Memory Oracle tables and datetimeoffset, geography, geometry, hierarchyid, rowversion, xml, sql_variant, CLR-UDT for ordinary source tables.
- SSMA doesn’t analyze materialized views that are enabled for in-memory. There’s no support in SSMA for In-memory priority and compression options, as well as the query hints.
- SSMA does not have the option to convert ordinary Oracle tables to Memory Optimized with Non-clustered columnstore and Clustered Columnstore options. This is because SSMA provides different sets of options of conversion depending on whether the source table is column or row-organized. Generally speaking, columnstore in-memory technology from DB2 and Oracle is best mapped on the SQL Server columnstore indexes while rowstore technology is best mapped on the In-Memory OLTP tables or traditional rowstore tables.
Useful references for using SSMA for Oracle to SQL Server Migration, Oracle Database In-Memory and SQL Server In-Memory OLTP
- Oracle Database In-Memory: http://www.oracle.com/technetwork/database/in-memory/overview/index.html.
- In-Memory OLTP support in SQL Server 2016.
Stay tuned to our blog for more information about the new features in the SQL Server Migration Assistant (SSMA) 7.0.
