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

 

obat diabetes said:

August 1, 2018 11:16 PM
 

obat bronkitis anak secara alami said:

August 7, 2018 7:48 PM
 

obat sinusitis said:

August 10, 2018 7:21 PM
 

masker wajah alami untuk menghilangkan jerawat said:

https://goo.gl/HQh67p Masker wajah alami untuk menghilangkan jerawat

https://goo.gl/4UJxUL Obat liver alami aman dan terbaik

https://goo.gl/j3PFHJ Obat herbal sinusitis kronis terbaik

https://goo.gl/W5W8F9 Obat bronkitis anak secara alami

https://goo.gl/2AsGLu Obat Kanker Payudara terbaru 2018

https://goo.gl/ssFQGs Cara meningkatkan nafsu makan secara alami

https://goo.gl/KSnnFb Obat herbal diabetes menurunkan gula darah

https://goo.gl/ngiymL Obat herbal kusta terbaik

https://goo.gl/ZsjaLf Obat benjolan di leher tanpa operasi

https://goo.gl/arrkfe Obat Herbal Kanker Otak tuntas hingga akar

https://goo.gl/VBFyi2 Obat TBC alami aman tanpa efek samping

https://goo.gl/ooXyhS Obat radang amandel sembuh tanpa operasi

https://goo.gl/94gZFX Obat asam urat alami tanpa efek sampinfg

https://goo.gl/Ld4VKF Obat tetes Sariawan secara alami

https://goo.gl/8v2oKL Cara mengobati mata bengkak dengan cepat

https://goo.gl/pnP3nA Obat tradisional kanker lambung terbaik

https://goo.gl/96UaW3 Obat kanker paru paru

https://goo.gl/bjzQzS Obat usus buntu alami tanpa operasi

https://goo.gl/PUDELN Obat asam lambung naik cara alami

August 20, 2018 8:57 PM
 

obat herbal koresterol terbaik said:

August 23, 2018 10:56 PM
 

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:19 AM
 

obat benjolan di tubuh said:

August 30, 2018 7:39 PM
 

obat wasir said:

September 4, 2018 8:26 PM
 

obat katarak said:

September 16, 2018 5:45 PM
 

obat maag said:

September 17, 2018 7:09 PM
 

obat limfoma said:

Bismillaahirrohmaanirrohiim

https://goo.gl/a79hGd

September 23, 2018 6:50 PM
 

Linda said:

I hope to see more updates from you. Thank you for sharing!

https://www.imgrumweb.com/

January 24, 2019 12:15 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement