If you’re using Amazon Web Services RDS for SQL Server (btw, it works great) and want to use Memory-Optimized Objects, it’s easy to alter your DB to allow this. Below is a little T-SQL. The key to this is the directory “D:\rdsdbdata\“, the default where RDS is storing your data.
-- Add new file group
ALTER DATABASE [sampleDB] ADD FILEGROUP [MOD_FG] CONTAINS MEMORY_OPTIMIZED_DATA
-- Add the new file
ALTER DATABASE [sampleDB] ADD FILE ( NAME = N'Mem_Opt_Data', FILENAME = N'D:\rdsdbdata\DATA\Mem_Opt_Data.ndf') TO FILEGROUP [MOD_FG]
-- Set the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to On
ALTER DATABASE [sampleDB] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
Things that you’ll need to swap out w/ your own info is “sampleDB” and “MOD_FG”.
Now your DB is all ready for some Memory-Optimized Objects.
Want to create a table that’s memory optimized, just use this:
-- Sample table create
CREATE TABLE testMOD (
SomeId INT IDENTITY(1,1)
,FirstName VARCHAR(30) NOT NULL
,LastName VARCHAR(30) NOT NULL
CONSTRAINT [PK_SomeID] PRIMARY KEY NONCLUSTERED HASH (SomeId) WITH (BUCKET_COUNT = 131072))
WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
These Memory-Optimized Objects do offer a significant speed improvement but don’t go crazy w/ them.