In a recent interaction with one of my clients, they expressed their interest in implementing In Memory OLTP in their environment. They were impressed with the fact that SQL Server now offers the ability to use In Memory OLTP and wanted to take advantage of it for several applications. As they were due for a hardware refresh and were also upgrading their legacy applications from SQL Server 2005 to the latest version, it seemed like the perfect time to explore this technology.

However, the client had concerns about the readiness of their applications for InMemory technology and whether any changes needed to be made to address it. They were looking for a tool that could run across multiple servers as they had more than 15 applications to consolidate and upgrade.

When I heard the word “automation,” I immediately thought of PowerShell scripting. I remembered using the “Memory Optimization Advisor” in SQL Server Management Studio for a previous client, but this time the customer wanted a tool that could be used across multiple servers.

To test the feasibility, I used the following PowerShell script to check the readiness of 4 tables in the AdventureWorks2016 database:

$objectsList = "HumanResources.Department","Person.Address",
"Person.Person","Production.Product"

for ($loop = 0; $loop -le $objectsList.count-1; $loop++)
{
    $schema = $objectsList[$loop].Split(".")[0]
    $object = $objectsList[$loop].Split(".")[1]
    Save-SqlMigrationReport -Server 'localhost' -Database 'AdventureWorks2016' 
    -Schema $schema -Object $object -FolderPath 'C:\Data\'
}

The output of this script will be stored in a folder, and you can find the MigrationAdvisor Checklist reports for each table in that folder. This is the same script that you would get when using the UI and then scripting, but it was useful in this case because the customer preferred not to use the UI.

Opening one of the reports, you will find detailed information on what is not supported when moving to the In Memory OLTP table. Sometimes, it also provides more detailed information with references to MSDN. I found this to be a powerful way to work with migration and consolidation exercises for InMemory OLTP workloads.

If you have experience with migration exercises, I would love to hear from you. Have you used similar reports? What was your experience? Please share your thoughts in the comments below.