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:
