Nisarg Upadhyay
ER diagram of SQL Database

Migrate tables with a foreign key to memory-optimized tables of SQL database

February 23, 2022 by

In this article, we are going to learn how we can migrate the tables with a foreign key to memory-optimized tables of the SQL database.

In my previous article, Migrating disk-based tables to memory-optimized tables of a SQL database, we learned how to migrate the disk-based tables to memory-optimized tables using the memory optimization advisor of the SQL database. The Memory optimization advisor migrates the table that meets all pre-requisites to create memory-optimized tables. If the table does not meet any requirements, it will throw an error. It will show you the list of issues that needs to be fixed. In our previous article, we migrated the tables, which we simple schema and meets with all pre-requisites. In this article, we will migrate the tables that have foreign keys relationship between the tables.

Environment setup

I have installed SQL Server 2019 and created a database named DemoDatabase on it. I have created two tables named tblOrder and tblProducts in the DemoDatabase. The script to create both tables is the following.

Script to create tblOrder table

Script to create tblProduct table

Below is the E-R diagram of the DemoDatabase.

ER diagram of SQL Database

Now, let us try to migrate the tblOrder using a memory optimizer advisor. To do that, expand DemoDatabase 🡪 expand tables 🡪 Right-click on tblOrder and select memory optimizer advisor.

Open memory optimization advisor of SQL Database

The memory optimizer advisor begins; on the pre-requisites check screen, it generates the following warnings.

Memory optimization checklist for tblOrder

The same error will occur when we try to migrate the tblProducts table.

Memory optimization checklist for tblProducts

The above errors prove that we cannot migrate the tables that have foreign keys using a memory optimizer advisor. Now, let us see how we can fix it. We can migrate the tables by using any of the following methods:

Method 1

  1. Drop foreign keys on the parent table
  2. Migrate the parent and child tables to memory-optimized tables
  3. Re-create the foreign keys

Method 2

  1. Create new memory-optimized tables using the definition of the existing tables
  2. Copy data from disk-based tables to memory-optimized tables
  3. Rename the tables, constraints, and associate indexes

Let us understand both methods.

Method 1: Drop and re-create the foreign key.

Step 1: drop the foreign keys

Let’s drop the foreign keys named FK_Product from the tblOrder tables.

Step 2: Migrate the tables to memory-optimized tables.

Once keys are dropped, let us run the memory optimization advisor on the tblOrder and tblProducts table. Right-click on tblOrder and select Memory optimization advisor.

Launch Memory optimization advisor of SQL Database

The memory optimization checklist is completed without errors.

Memory optimization checklist for tblOrder

Specify the memory-optimized filegroup name and location. Once tables are created, we want to keep the data of the tblOrder table, so enable the “Allow copy table data to the new memory optimized tables” option.

Optimization Options

The tblOrder table has one primary key so that the memory-optimized table will use it and the primary key is the NONCLUSTERED indexes, and the sort order is ascending.

Primary Key conversion

Once changes are applied, click on Migrate to begin the migration process of the tblOrder table.

Migration Process

Once migration is completed successfully, now, Migrate the tblProducts table using the above method.

Once tables are migrated successfully, run the following query to verify that the tables are migrated properly.

Output

Tables have been migrated

Step 3: Re-create the foreign keys

Now, let us create a foreign key by executing the below query

The foreign key has been created, and the tables are migrated from disk-based tables to memory-optimized tables in SQL Database.

Now, let us understand the second method.

Before we understand the second method, drop both memory-optimized tables.

Method 2: Create new memory-optimized tables and export data from old tables.

In the second method, we must create new in-memory tables with a different name. I am creating tblOrder_Memory_optimzed and tblProduct_Memory_optimzed executing the below queries.

Query to create tblOrder_Memory_optimzed table

Query to create tblProduct_Memory_optimzed table

Now, let us export the disk-based tables to in-memory tables using the SQL Server import-export tool. You can read the article to learn more about the import-export tool of SQL Server. Configure the source and destination tables as shown below image:

Source and destination to export data to memory-optimized table

The import-export method copies the data between disk-based tables and memory-optimized tables.

Step 3: Rename the tables and indexes

First, let us rename the disk-based tables and indexes. To do that, we are using sp_rename stored procedure. Run the following query to rename the tables.

Once disk-based tables are renamed, run the following command to rename the memory-optimized tables.

Tables have been renamed without errors; run the below query to view if the tables have been migrated or not.

Output

Tables have been migrated.

As you can see, the tables have been migrated successfully.

Summary

In this article, we learned how to migrate the tables with a foreign key to memory-optimized tables of the SQL database. I have explained two methods to migrate the tables

  1. Drop the foreign keys, migrate the tables using memory optimization advisor and re-create the foreign keys
  2. Create new memory-optimized tables using the definition of disk-based tables, export the data from disk-based tables to memory-optimized tables and rename the tables, indexes, and constraints
Nisarg Upadhyay
Database design, ETL, Importing, exporting

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

1,477 Views