AWS RDS SQL Server – Using Memory Optimized Objects in your DB

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
GO
-- 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]
GO
-- Set the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to On
ALTER DATABASE [sampleDB] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO

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)
GO

These Memory-Optimized Objects do offer a significant speed improvement but don’t go crazy w/ them.

AWS RDS SQL Server – Using Memory Optimized Objects in your DB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s