In one of my recent interaction with my client, they were so much impressed with the fact that SQL Server has now introduced the ability to use In Memory OLTP. They said they were interested in implementing the same in their environment for a number of applications. The hardware refresh was due and they were also upgrading a number of their legacy applications which were hosted on SQL Server 2005 to the latest version as that was also due. In this blog post we will learn about InMemory OLTP Migration Assistant.
I felt them in a perfectly right direction to go ahead and implement the same. The only trouble here was, they wanted to know the readiness in using the InMemory technology and are there any changes they need to be made on their application to address the same. I remember using the “Memory Optimization Advisor” earlier in one of the client’s location using SQL Server Management Studio. But here the customer wanted something like a tool to run across multiple servers as there were more than 15+ applications they wanted to consolidate and upgrade.
Whenever I hear about the work “automation”, I am pleasantly intrigued with the fact that there must be some scripting that can be done. My instant thought in the back of my mind was to use PowerShell scripting here.
For a quick testing, I used the following PowerShell script to check the feasibility for 4 of the tables in my 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 will be stored in a folder and I saw the MigrationAdvisor Checklist reports were there for each of the tables in the following folder.
This is the same script one would get when working with the UI and then scripting. But this was useful here because the customer didn’t want to go via the UI. Now if we open up one of the reports, it would span out like below:
It has detailed information on what is not supported when moved to the In Memory OLTP table and sometimes it also give more detailed information pointing to the MSDN. I personally felt this was a really powerful way to work with migration and consolidation exercises for InMemory OLTP workloads. Have you ever used such reports in your migration exercises, what has been your experience? Do let me know via comments below to what did you do differently for the same.
Reference: Pinal Dave (https://blog.sqlauthority.com)
