Using In-Memory OLTP migration checklists in SQL Server 2016


By:   |   Updated: 2016-04-04   |   Comments   |   Related: More > 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.

from_SQL_Server_2014

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".

from_SQL_Server_2016

Just click on the option - "Generate In-Memory OLTP Migration Checklists" which will take you to the following screen.

first_screen_on_checklist

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".

second_screen_on_checklist

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.

second_screen_on_checklist

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.

finish_screen_on_checklist

The completion screen is as below. In our case, we had selected only three tables to generate the Migration checklist.

last_screen_on_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.

folder_location

Under "Tables" folder, sample screenshot below.

Table_folder_location

Click on the reports to view detailed HTML reports. Sample screenshot below.

HTML_Report

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.


Last Updated: 2016-04-04


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools