By: Mohammed Moinudheen | Comments | Related: > In Memory OLTP
Problem
Is there a tool available in SQL Server 2016 which would help to identify all the tables and stored procedures that are incompatible with In-Memory OLTP prior to migrating to the technology? Is it possible to generate a report to capture the results?
Solution
In SQL Server 2016, there is a very simple tool that can be used to generate detailed reports regarding memory optimization incompatibilities for all tables and stored procedures. If required, the tool could be run selectively for any specific table or stored procedure.
Refer this screenshot from SQL Server 2014 by right clicking on any database.
Compare the same interface in SQL Server 2016 by right clicking on any database. You would see the new option "Generate In-Memory OLTP Migration Checklists".
Just click on the option - "Generate In-Memory OLTP Migration Checklists" which will take you to the following screen.
Click "Next", which will take you to this screen. Update the "Save checklists to" section and provide the correct folder path. Either select the option to "Generate a checklist for each table and stored procedure in the database" or the option to "Generate checklists for specific tables and stored procedures".
If you select the option to "Generate checklists for specific tables and stored procedures", the required tables and stored procedures needs to be selected as shown.
Click Next which will take you to this screen. Take note of the option to "Script PowerShell Commands" as we will try this tool using PowerShell in the next section. Click "Finish" to complete.
The completion screen is as below. In our case, we had selected only three tables to generate the Migration checklist.
After clicking "OK", go to the folder where you had opted to save the checklists. You will find separate folders for "Tables" and "Stored Procedures." Refer to the screenshot below.
Under "Tables" folder, sample screenshot below.
Click on the reports to view detailed HTML reports. Sample screenshot below.
With this report you can easily verify if there are any compatibility issues with the tables you are planning to migrate to In-Memory OLTP.
From one of the above screenshots, you would have seen an option to "Script PowerShell Commands." This is also an easier option to generate the same reports. Just click on that option to "Script PowerShell Commands" and run the command. In our case, as we selected three tables, the PowerShell script is as below.
$objectsList = "dbo.AWBuildVersion","dbo.DatabaseLog","dbo.ErrorLog"
for ($i = 0; $i -le $objectsList.count-1; $i++)
{
$schema = $objectsList[$i].Split(".")[0]
$object = $objectsList[$i].Split(".")[1]
Save-SqlMigrationReport -Server 'SQL2016' -Database 'AdventureWorks' -Schema $schema -Object $object -FolderPath 'C:\Users\mohammed\Desktop\AdventureWorks'
}
After the PowerShell scripts finish running, just go to the folder where you had opted to save the reports and you will find the reports there.
Next Steps
- Try running this tool on a SQL Server 2016 database using GUI.
- Try running this tool on a SQL Server 2016 database using PowerShell.
- Verify the reports that are generated.
- Check out other tips on In-Memory OLTP.
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
