THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Point in Time Recovery for Operations on Memory—Optimized Tables

In my class in Budapest last week, someone asked if point-in-time recovery was supported for transactions on memory-optimized tables.  I couldn’t think of a reason why not, but I had never tried it. And just because there is not a technical inhibitor to allow certain functionality, it is often the case with a new technology that certain features aren’t included just because it just hasn’t been tested sufficiently.

But as I thought about it a bit more, I realized there was no way that point-in-time recovery could work for disk-based tables unless it also worked for memory-optimized tables. After all, the transactions are all stored in the same log.

But, I decided to verify anyway.

So I created an in-memory database (IMDB) and a memory-optimized table. (If you want to run the code, you’ll need a folder on your C: drive called HKData and one called HKBackups.  Or you can edit the code to change the paths.) I then did an initial database backup of the database with an empty table.

USE master
GO
SET NOCOUNT ON
GO

IF db_id('IMDB') IS NOT NULL DROP DATABASE IMDB;
GO

CREATE DATABASE IMDB; 
GO
----- Enable database for memory optimized tables
-- add memory_optimized_data filegroup
ALTER DATABASE IMDB
    ADD FILEGROUP IMDB_mod_FG CONTAINS MEMORY_OPTIMIZED_DATA
GO

-- add container to the filegroup
ALTER DATABASE IMDB
    ADD FILE (NAME='IMDB_mod', FILENAME='c:\HKData\IMDB_mod')
    TO FILEGROUP IMDB_mod_FG
GO

USE IMDB;
GO
-- create a memory-optimized table
CREATE TABLE dbo.t_memopt (
       c1 int NOT NULL,
       c2 char(40) NOT NULL,
       c3 varchar(100) NOT NULL,
 
       CONSTRAINT [pk_t_memopt_c1] PRIMARY KEY NONCLUSTERED HASH (c1)
        WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

BACKUP DATABASE IMDB TO  DISK = N'C:\HKBackups\IMDB-FULL.bak'
    WITH NOFORMAT, INIT,  NAME = N'IMDB-empty-table', SKIP, NOREWIND,
       NOUNLOAD,  STATS = 10;
GO

I then inserted a row into the table, waited a minute, grabbed the time, waited another minute, and inserted another row. I found that if I didn’t put the WAITFORs in, it would get a bit confusing about exactly where I wanted to stop restoring.

INSERT t_memopt VALUES (1, 'a', REPLICATE ('b', 100));
GO
WAITFOR DELAY '0:1:0';
SELECT getdate();
WAITFOR DELAY '0:1:0';
INSERT t_memopt VALUES (2, 'c', REPLICATE ('d', 100));
GO
SELECT * FROM t_memopt;
GO

I then noted the time captured with the getdate(), saw the two rows returned,  and then made a log backup.

BACKUP LOG IMDB TO  DISK = N'C:\HKBackups\IMDB-log.bak'
    WITH NOFORMAT, INIT,  NAME = N'IMDB-2-inserts-in-log'
GO

Now I could test the restore.

USE master;
GO
RESTORE DATABASE IMDB
    FROM DISK = 'C:\HKBackups\IMDB-FULL.bak'
    WITH REPLACE, NORECOVERY;
GO
RESTORE LOG IMDB
    FROM DISK = 'C:\HKBackups\IMDB-log.bak'
    WITH RECOVERY, STOPAT = '<insert getdate() value noted above>';
GO

USE IMDB;
GO
SELECT * FROM t_memopt;
GO

When I saw just the one row row returned, I knew the point-in-time recovery had worked!

So now I can start working on a real juicy bits and bytes type blog post. Hopefully I’ll have it up next week.

 

~Kalen

Published Wednesday, April 22, 2015 2:05 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement