The Essential Guide to SQL Server 2014 Series: The Analysis Migrate Report (AMR) Tool

In this new series I’ve been covering some of the most important new features in SQL Server 2014. In the last column I covered the new In-Memory OLTP engine. In this column I’ll show you how you can use the SQL Server 2014 Analysis Migrate Report (AMR) tool to migrate your databases to use the new In-Memory OLTP. First, it’s important to know that AMR is not required in order to use the new In-Memory OLTP. You can build your In-Memory database schema from the ground up using T-SQL or SSMS. AMR is used when you’re looking to convert an existing OLTP database to use the new In-Memory OLTP feature. Like its name suggests, AMR is able to analyze a database and determine what tables and stored procedures can be memory optimized. It also reports on any issues or conflicts that that your database might have in the conversion process. AMR can work with SQL Server 2008 and higher.

To get started using AMR you need to capture a running SQL Server workload. Microsoft recommends that you run the AMR tool for at least one hour to capture your performance baselines. However, the longer you run the tool, the more representative your captured statistics are likely to be. When the capture portion of the AMR tool is running it collects performance data every 15 minutes. It stores the data in two collections named Table Usage Analysis and Stored Procedure Analysis.

Running your Initial Data Collection

To start using the AMR tool, you must first create a Master Data Warehouse (MDW). First, open Object Explorer then expand the Management node and right-click Data Collection. Select Tasks, and choose Configure Management Data Warehouse.

That will create the MDW database. To start collecting data with the AMR tool use Object Explorer to expand the Management node then right-click Data Collection. Select Tasks, and choose Configure Data Collection. This will start the Configure Data Collection Wizard that you can see in Figure 1.

Figure 1 - Running data collection with AMR

The wizard displays a dialog that lets you select the current SQL Server instance and the MDW database that you created earlier. After those selections are made, select the Transaction Performance Collection Sets check box. Then click Next and Finish to kick off the data collection process.

Generating the AMR reports

You can generate an AMR Transaction Performance Analysis Overview report by right-clicking the MDW database, selecting Reports, choosing Management Data Warehouse, and selecting Transaction Performance Analysis Overview. The AMR report provides information about all user databases on the server. You can drill into each database and display the AMR recommendations that you can see in Figure 2.

Figure 2 - The Transaction Performance Analysis report

The portion of the report that you see in Figure 2 displays the table usage. Another portion of the report recommends which stored procedures should be memory optimized. After running the reports you can also optionally use the Memory Optimization Advisor to migrate the selected tables and stored procedures. Open Object Explorer and navigate to the object you want to migrate then right-click the object and choose Memory Optimization Advisor.

You can find out more information about SQL Server 2014’s AMR tool on the SQL Server Pro site at SQL Server 2014's Analysis, Migrate, and Report Tool and on the SQL Server Blog at New AMR Tool: Simplifying the Migration to In-Memory OLTP.