Wednesday, July 10, 2013

FileStream and Windows 2012

I promised you an update when the fix arrives… Here it is: The Microsoft fix for the AlwaysOn FileStream problem with Windows 2012 is published under KB 2835620

Project Hekaton and BLOBs

I have kept silent for quite a while, mostly because I was so busy test-driving Hekaton that I didn’t have much time for other things. And as Hekaton was under NDA for a long while I just couldn’t tell you anything about it. Well, that’s over now… And here is my first post on the topic:

As you might have heard by now Hekaton does not support large objects. (The limitation states that a row is not allowed to exceed 8000 bytes.) Microsofts comment on that is that normally large objects and in memory technology don’t go well together anyway, and you should maybe leave the BLOB in a relational table while putting the rest of the data in memory.

Well, … I disagree… There are some areas where having a BLOB in a latch-free, highspeed environment just makes a lot of sense. Dilema? No… Because there is an easy way to work around the limitation in the Hekaton engine. (Which by the way is the reason I didn’t oppose this limitation a lot harder…) The key idea is to split the BLOB on storing and reunite it on retrieval. Sounds a little stupid, but actually works like a charm, and even hell fast…So… Now that you know the concept you can for sure implement it yourself.

For those of you that are too lazy for that, or just don’t know what the hell I am talking about, here is the POC. It supports Store, Update and Retrieve of a BLOB in Hekaton, based on an nvarchar key. The way I implement this in my projects is to replace the original BLOB field with a reference key field (e.g. a GUID) and then use the sprocs in my script to actually handle the BLOB. It’s not as elegant as I would have liked it to be, but it works for all applications I came across so far.

Please bear in mind that this is a POC script only, it lakes error handling in most cases and for sure doesn’t cover everything. But it should get you a good idea of how it’s done. Therefore I take no warranty whatsoever for the script and what happens to your system by using it.

If you have questions about it, or about anything else in the Hekaton space for that matter please feel free to ping me.

Tuesday, February 12, 2013

FileTable and Windows 2012

I had hoped for a quick fix by Microsoft, otherwise I would have posted this earlier…

A while back I published a white paper on how to run a backup solution using FileTables. Please be aware that this solution currently only works if you use SQL Server 2012 on Windows Server 2008 R2. Reason for that is a bug in Windows Server 2012 that causes FileStream not to work in conjunction with AlwaysOn Availabililty Groups. (The solution will look fine in the management tools, but the FileStream will not be available via the AGs listener.)

I’ll put a post up once a hotfix is available.