New AMR Tool: Simplifying the Migration to In-Memory OLTP
As we have addressed in previous blog articles, the transition into SQL Server In-Memory OLTP is not as simple as flipping a switch. One of the design principles of In-Memory OLTP is that you would only need to make surgically precise migrations for your app to reap the most benefit from In-Memory OLTP for the least amount of work. With that in mind, we recognize that it is sometimes difficult to identify the appropriate targets for you to take advantage of In-Memory OLTP. To solve this problem, we have devised and integrated the AMR (Analyze, Migrate, Report) Tool into the SQL Server 2014 CTP 1 Management Studio to assist you in your transition to In-Memory OLTP.
The AMR Tool works well if you are:
- Uncertain which tables and stored procedures you should migrate into In-Memory OLTP
The AMR Tool’s analysis process is built with this goal in mind. With its analysis reports, you can easily identify the tables and stored procedures that can reap the most benefit when moved into In-Memory OLTP.
- Seeking validation for your migration plans
You may understand your workload extremely well. By reading about In-Memory OLTP’s characteristics and playing with it a bit, you know which objects you want to move into this new, efficient engine. The AMR Tool can help you validate your knowledge and support your decision with data straight from your workload instances.
- Evaluating work needed for the tables and stored procedures you plan to migrate
You know from experience and data what objects you need to migrate. You need a list of work items so that you can carefully plan your migration effort, estimate the amount of time needed for each object, and give a completion date of the migration. The AMR Tool can help you by identifying the incompatibilities in these objects and even do some of the work for you.
As released in SQL Server 2014 CTP1, the AMR Tool consists of the following components:
- A set of data collectors, leveraging the existing Data Collection framework in SQL Server
This set of data collectors are ultra-lightweight and can easily attach to your SQL Server instances (in production or otherwise) as long as they are running SQL Server 2008 or later. They collect performance data points from DMVs in your SQL Server instance and deposit them to a designated database called a Management Data Warehouse for the analysis reports.
- A set of Management Data Warehouse analysis reports
This set of reports ingest data from the Management Data Warehouse into which the AMR data collectors have uploaded data. They use these data points to analyze your workload patterns and provide recommendations for migration based on:
- your workload’s access characteristics,
- your workload’s contention statistics, and
- your workload’s stored procedure CPU usage
The overview reports are designed so that it is easy for you to identify the tables and stored procedures with the best return on investment as illustrated below:
The reports will provide a set of recommended tables or stored procedures for each basis. For example, if your workload has a contention issue, you may choose to follow the recommendations based on contention statistics and disregard the others.
Since natively-compiled stored procedures cannot access disk-based tables at this time, we highly recommend that you make your table migrations first. When you are already taking advantage of the multitudes of benefits memory-optimized tables provide, you can run the AMR tool again on a second pass and select stored procedures you want to compile with In-Memory OLTP.
The setup and teardown of the AMR Tool is extremely simple:
- First, you need to connect to a SQL Server 2008 or later instance using SQL Server 2014 Management Studio. You do not need a SQL Server 2014 instance to proceed.
- Next, you need to establish a Management Data Warehouse (MDW) somewhere. This MDW can reside on any instance on the same network as the workload instance. It could even be your desktop computer.
To create a MDW, go to “Object Explorer”, expand “Management”, right-click “Data Collection”, mouse over “Tasks”, and choose “Configure Management Data Warehouse”. The wizard will guide you through the task of creating a MDW for use.
- After you have a MDW, you will need to establish data collectors on your workload instance. The AMR Tool requires the set of data collectors to situate on your workload instance in order to obtain and store the performance data it needs.
To install data collectors on an instance, go to “Object Explorer”, expand “Management”, right-click “Data Collection”, mouse over “Tasks”, and choose “Configure Data Collection”. In the wizard, check “Transaction Performance Collection Set” in the list of collection sets.
- Once you have finished your migration work and do not want to use the collectors any more, you can remove them easily as well. We provide a graphic user interface for tearing down both the system data collectors the AMR data collectors in SQL Server 2014 Management Studio.
To tear down the AMR data collectors if you don’t want them running any more, go to “Object Explorer”, expand “Management”, right-click “Data Collection”, mouse over “Tasks”, and choose “Clean up Data Collection”. In the wizard, check “Transaction Performance Collection Set” in the list of collection sets.
If you are thinking about In-Memory OLTP for your app, why not give the new AMR Tool a try today? We hope that you’ll like it!
SQL Server 2014 CTP1 is available for download here, or for more information please see the introduction and index to this complete series.
