It’s always fun to talk to Richard Campbell about what’s going on, and this time around, it’s SQL Server 2016:
Listen to me chat with Richard about what’s coming in vNext.
We’re now in the second quarter of the year, and we still don’t have pricing, licensing, or edition/feature lists. Normally, when you release software, you need to educate users on how to choose the right box, but to do that, you need to train the trainers first. There simply hasn’t been any community guidance available yet on the questions end users – and managers – ask the most: how is this thing gonna be licensed?
That means one of two things: either it’s gonna be exactly the same (which doesn’t seem likely, given the crazy number of new features that are going into the boxed product this time around), or it’s gonna be wildly different, and passionate discussions might still be going on.
I think this is the best release since 2005. Granted, there’s still a couple of massive problems – for example, CHECKDB simply skips In-Memory OLTP and stretch tables, so I think you’d have to be an outright idiot to deploy either of those features on data you care about. Don’t think you care about the data? Remember, if there’s any corruption in your Hekaton tables, your entire database doesn’t start up. None of your other tables are accessible, and you have to restore from your last full backup plus your transaction logs. (You were taking those, right?)
But aside from that, SQL Server 2016 is fantastic, and in the podcast with Richard, we talk about Query Store, AlwaysEncrypted, the run to the cloud, and more. Enjoy!
7 Comments. Leave new
Checkdb skips stretch tables ? Oh dear, this did sound like a really useful feature 🙁
Brent,
The author of this post:
https://sqlactions.com/2014/07/31/how-to-recovery-database-from-in-memory-table-corruption/
states that if you set a memory-optimized FG OFFLINE, disk-based tables can still be accessed.
I have not verified this on 2014 or 2016.
Ned – ooo, thanks for the heads up! I haven’t tried that easier, would be interested to hear what others experience. I’d also be curious to see how that works with AGs.
Brent — I drilled down on this topic with Jos de Bruijn, and his reply was:
##################
“Computing the checksum is done at the block level whenever there is a write to an open checkpoint file. Closing the checkpoint file does not result in additional computation of checksums – the checksums of blocks that have previously been written to the file remain as is.
Then, during database backup, as well as any other operation that reads the files (e.g., recovery or checkpoint file merge), for each block, the checksum is computed again and compared with the checksum that is stored with the block.”
##################
So if the case of storage corruption that affects checkpoint file pairs – detected during a backup – your data in memory is ok, but the file on disk is corrupt. Perhaps I’m missing something, but in this regard, it seems no different than disk-based corruption — you could copy your memory-optimized data to disk-based tables, bcp out to files, etc.
Or you could simply choose to recover the database to before the corruption was detected, allowing some data loss.
Ned – there’s a very important difference on database startup.
If you have disk-based corruption in your conventional tables, your database still starts, and all of the data – except the corrupted pages – is still accessible. You can even use non-clustered indexes to help repair and recover from data loss. (To learn more about that, check out Steve Stedman’s database corruption challenge series of posts.)
If you have disk-based corruption in your Hekaton tables, your database WILL NOT START. You cannot query ANY of your Hekaton tables, let alone non-corrupted pages in the same table. Even worse, you have no control over when this restart happens – if someone just happens to restart your SQL Server, or if it restarts due to patching or a bug, whammo, your data is completely inaccessible.
That’s pretty far different. 😀
Brent – Yes, I’m aware of those issues, and not discounting them in the least.
Only attempted to narrowly define my reply to “corruption detected during backup”.
No getting around the fact that In-Memory OLTP is in its infancy.
Well, infancy’s probably the wrong word. It was in its infancy in SQL 2014. This time around, it’s in the Terrible Twos, I think. 😉
But yeah, corruption detected during backup is different because with conventional on-disk pages, you’re not in a rush. You can do page-level restores and keep right on trucking without a failover or a business outage. Not so with Hekaton – there’s no concept of a page-level restore there.