Planning data migration to memory-optimized tables

Microsoft SQL Server Management Studio (SSMS) contains tools to help analyze and migrate tables to memory-optimized storage.

When you right-click on a database in SSMS and click on Reports | Standard Reports | Transaction Performance Analysis Overview, a four-quadrant report of all tables in the database will be made:

Figure 1.3: Choosing Transaction Performance Analysis

The report will look at each table and place it on the chart to show the ease of migration versus the expected gain by migrating the table to be memory-optimized:

Figure 1.4: Recommended Tables Based on Usage

Once you have identified tables that might benefit, you can right-click on individual tables and run the Memory Optimization Advisor:

Figure 1.5: Selecting the Memory Optimization Advisor

The Table Memory Optimization Advisor is a "wizard" style of user interface that will step you through the configurations:

Figure 1.6: The Table Memory Optimization Advisor dialogue

The wizard will take you through a checklist with any failed issues:

Figure 1.7: Memory Optimization Checklist

The warnings dialogue will flag up other important issues.

Figure 1.8: Memory Optimization Warnings

Next enter file names and check paths in the migration option dialogue.

Figure 1.9: Review Optimization options

The wizard will detect the primary keys and populates the list of columns based on the primary key metadata. To migrate to a durable memory-optimized table, a primary key needs to be created. If there is no primary key and the table is being migrated to a non-durable table, the wizard will not show this screen.

Figure 1.10: Review Primary Key Conversion

By clicking Script you can generate a Transact-SQL script in the summary screen.

Figure 1.11: Verify Migration Actions Summary Screen

The wizard will the display a report as the table migrates.

Figure 1.12: Migration progress report

Memory-optimized tables are a great feature, but you will need to plan carefully to make sure you get the performance and transactional reliability you require.

You can create a new database specifying memory-optimized, or alter an existing database to handle memory-optimized data. In either case, a filegroup for containing the memory-optimized data must be created.

In the following sample, we will create a memory-optimized database using SQL script:

-- Create Memory-Optimized Database
USE MASTER;
GO
CREATE DATABASE MemOptDB
  ON (Name = MemOptDB_Data, FileName = 'c:\sqldata\memoptdb_data.mdf', size = 10 mb, maxsize = 20 mb, filegrowth = 5 mb)
  LOG ON (Name = MemOptDB_Log, FileName = 'c:\sqldata\memoptdb_log.ldf', size = 2 mb, maxsize = 10 mb, filegrowth = 1 mb);
GO
-- Must declare a memory-optimized filegroup
ALTER DATABASE MemOptDB 
  ADD FILEGROUP MemOptDB_FG contains MEMORY_OPTIMIZED_DATA;
ALTER DATABASE MemOptDB 
  ADD FILE (Name = 'MemOptDB_MOFG', FileName = 'c:\sqldata\memoptdb_mofg')
  TO FILEGROUP MemOptDB_FG;
ALTER DATABASE MemOptDB
  SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
GO

You can also make a memory-optimized database by using SQL Server Management Studio and adding a memory-optimized filegroup:

Figure 1.13: The new database dialogue window