神刀安全网

Using In-Memory OLTP migration checklists in SQL Server 2016

By:Mohammed Moinudheen |  |   Related Tips: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.

Using In-Memory OLTP migration checklists in SQL Server 2016

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

Using In-Memory OLTP migration checklists in SQL Server 2016

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

Using In-Memory OLTP migration checklists in SQL Server 2016

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

Using In-Memory OLTP migration checklists in SQL Server 2016

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.

Using In-Memory OLTP migration checklists in SQL Server 2016

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.

Using In-Memory OLTP migration checklists in SQL Server 2016

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

Using In-Memory OLTP migration checklists in SQL Server 2016

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.

Using In-Memory OLTP migration checklists in SQL Server 2016

Under "Tables" folder, sample screenshot below.

Using In-Memory OLTP migration checklists in SQL Server 2016

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

Using In-Memory OLTP migration checklists in SQL Server 2016

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 onIn-Memory OLTP.

Last Update:

Using In-Memory OLTP migration checklists in SQL Server 2016

About the author

Using In-Memory OLTP migration checklists in SQL Server 2016 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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Using In-Memory OLTP migration checklists in SQL Server 2016

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮