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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
USE [DemoDatabase] go CREATE TABLE [dbo].[tblorder] ( [id] [INT] NOT NULL, [customerid] [INT] NULL, [orderdate] [DATETIME] NULL, [product_id] [VARCHAR](5) NULL, [orderqty] [INT] NULL, [orderprice] [NUMERIC](10, 2) NULL, CONSTRAINT [PK_tblProduct_ID] PRIMARY KEY NONCLUSTERED ( [id] ASC ) ) ON [PRIMARY] go ALTER TABLE [dbo].[tblorder] WITH CHECK ADD CONSTRAINT [FK_Product] FOREIGN KEY([product_id]) REFERENCES [dbo].[tblproduct]([product_id]) go ALTER TABLE [dbo].[tblorder] CHECK CONSTRAINT [FK_Product] go |
Script to create tblProduct table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE [DemoDatabase] go CREATE TABLE [dbo].[tblproduct] ( [product_id] [VARCHAR](5) NOT NULL, [productname] [VARCHAR](200) NOT NULL, [batchnumber] [VARCHAR](200) NOT NULL, [manufacturername] [VARCHAR](250) NULL, [cost] [INT] NULL, [maximumqty] [INT] NULL, CONSTRAINT [PK_tblProduct_Product_ID] PRIMARY KEY NONCLUSTERED ( [product_id] ASC ) ) ON [PRIMARY] go ALTER TABLE [dbo].[tblproduct] ADD CONSTRAINT [DF_tblProduct_Batch] DEFAULT ('NA') FOR [BatchNumber] go |
Below is the E-R diagram of the DemoDatabase.
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.
The memory optimizer advisor begins; on the pre-requisites check screen, it generates the following warnings.
The same error will occur when we try to migrate the tblProducts table.
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
- Drop foreign keys on the parent table
- Migrate the parent and child tables to memory-optimized tables
- Re-create the foreign keys
Method 2
- Create new memory-optimized tables using the definition of the existing tables
- Copy data from disk-based tables to memory-optimized tables
- 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.
|
1 2 3 4 5 6 |
USE [DemoDatabase] go ALTER TABLE [dbo].[tblorder] DROP CONSTRAINT [FK_Product] go |
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.
The memory optimization checklist is completed without errors.
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.
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.
Once changes are applied, click on Migrate to begin the migration process of the tblOrder table.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE demodatabase go SELECT object_id, NAME, type_desc, create_date, modify_date, durability_desc FROM sys.tables WHERE is_memory_optimized = 1 |
Output
Step 3: Re-create the foreign keys
Now, let us create a foreign key by executing the below query
|
1 2 3 4 5 6 7 8 |
USE [DemoDatabase] go ALTER TABLE [dbo].[tblorder] ADD CONSTRAINT fk_product FOREIGN KEY([product_id]) REFERENCES [dbo].[tblproduct] ([product_id]) go |
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.
|
1 2 3 4 5 6 |
USE demodatabase go DROP TABLE [dbo].[tblorder] DROP TABLE [dbo].[tblproduct] |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
USE [DemoDatabase] GO CREATE TABLE [dbo].[tblOrder_memory_optimized] ( [ID] [int] NOT NULL, [CustomerID] [int] NULL, [OrderDate] [datetime] NULL, [Product_ID] [varchar](5) NULL, [OrderQty] [int] NULL, [OrderPrice] [numeric](10, 2) NULL, CONSTRAINT [tblOrder_primaryKey_memory_optimized] PRIMARY KEY NONCLUSTERED ([ID] ASC) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ) GO ALTER TABLE [dbo].[tblOrder_memory_optimized] WITH CHECK ADD CONSTRAINT [fk_product_memory_optimized] FOREIGN KEY([Product_ID]) REFERENCES [dbo].[tblProduct_memory_optimized] ([Product_ID]) GO ALTER TABLE [dbo].[tblOrder_memory_optimized] CHECK CONSTRAINT [fk_product_memory_optimized] GO |
Query to create tblProduct_Memory_optimzed table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE [DemoDatabase] GO CREATE TABLE [dbo].[tblProduct_memory_optimized] ( [Product_ID] [varchar](5)NOT NULL, [ProductNAME] [varchar](200)NOT NULL, [BatchNumber] [varchar](200) NOT NULL, [ManufacturerName] [varchar](250) NULL, [Cost] [int] NULL, [MaximumQty] [int] NULL, CONSTRAINT [tblProduct_primaryKey_memory_optimized] PRIMARY KEY NONCLUSTERED ( [Product_ID] ASC) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ) GO ALTER TABLE [dbo].[tblProduct_memory_optimized] ADD CONSTRAINT [DF_tblProduct_Batch_memory_optimized] DEFAULT ('NA') FOR [BatchNumber] GO |
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:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
use DemoDatabase go /*Order Table*/ exec sp_rename 'tblOrder','tblOrder_Old' GO exec sp_rename 'tblOrder_primaryKey','tblOrder_primaryKey_Old' Go exec sp_rename 'fk_product','fk_product_Old' go /*Product Table*/ exec sp_rename 'tblProduct','tblProduct_Old' GO exec sp_rename 'tblProduct_primaryKey','tblProduct_primaryKey_Old' Go |
Once disk-based tables are renamed, run the following command to rename the memory-optimized tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/*Order Tables*/ exec sp_rename 'tblOrder_memory_optimized','tblOrder' GO exec sp_rename 'tblOrder_primaryKey_memory_optimized','tblOrder_primaryKey' Go exec sp_rename 'fk_product_memory_optimized','fk_product' go /*Prodcut Tables*/ exec sp_rename 'tblProduct_memory_optimized','tblProduct' GO exec sp_rename 'tblProduct_primaryKey_memory_optimized','tblProduct_primaryKey' Go |
Tables have been renamed without errors; run the below query to view if the tables have been migrated or not.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE demodatabase go SELECT object_id, NAME, type_desc, create_date, modify_date, durability_desc FROM sys.tables WHERE is_memory_optimized = 1 |
Output
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
- Drop the foreign keys, migrate the tables using memory optimization advisor and re-create the foreign keys
- 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
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022