How to check tables and stored procedures for in-memory migration

SQL Server introduced In-memory optimized tables with 2014 and it has been enhanced with SQL Server 2016. This improves the performance significantly and it is not that difficult to implement too. If we create a new database for new set of requirements, then it is easy to determine whether tables and stored procedures can be created using in-memory OTLP but it is bit difficult to check and see whether tables and stored procedures in existing databases are compatible for it.

We have been given a user-friendly wizard by SQL Server 2016 for determining the compatibility. This wizard helps us to go through tables and stored procedures we have in our database and check whether they can be converted. Not only that, if it is not possible, what are the possible reasons and solutions for converting them.

Let's try with one database. I have restored ContosoRetailDW database in my 2016 instance. I can find the wizard called Generate In-Memory OLTP migration checklist as below;

When click, Welcome Screen appears and the next page is for selecting objects to be checked. I can

get checked all or I can select individual items too. Remember the path set to Save checklist to. That is the place to be checked after completion of the wizard.

Last page allows to get the Powershell script generated too. If you need, you can get the code. At the end of the wizard, you see whether it has gone through all objects or not.

Now we can go through the checklist. Open the folder used, you should see three folders for; tables, stored procedures, and user defined functions.

Let's take one checklist and see. Lets open the checklist generated for DimDate table: MigrationAdvisorChecklistReport_DimDate_20160509.html file.

As you see, it clears says whether table is supported and what are the possible reasons if unsupported including possible solutions.