improve.dk - OrcaMDF

Introducing OrcaMDF | Mark S. Rasmussen

Improve.dk Spined HTML

Introducing OrcaMDF | Mark S. Rasmussen Mark S. Rasmussen improve.dkWell-nighMe Pages Categories Archive May 03 2011 Introducing OrcaMDF SQL Server - Internals , SQL Server - OrcaMDF Comments I’ve been spamming Twitter the last couple of days with progress on my pet project, OrcaMDF. But what is OrcaMDF really? Miracle Open World 2011 I was invited to speak at MOW2011 for the SQL Server track. Last year I got good reviews for my presentation on Dissecting PDF Documents, a deep swoop into the file format of PDF files. Wanting to stay in the same grove, I decided to take a squint at the MDF format as it’s somewhat closer to SQL Server DBA’s than PDF files. Having scrutinizingly worn my SQL Server 2008 Internals typesetting lanugo from reading, I’ve unchangingly been interested in the internals, though I still felt like I was lacking a lot of knowledge. A parser is born For my demos at MOW I wanted to at least read the records from a data page, just like the output from DBCC Page. The vital page format is well documented, and it’s not the first time I’ve taken a deeper squint at pages. Surprisingly quickly, I had record parsing from data pages functioning using a hardcoded schema. Parsing a single page is fun, but really, I’d like to get all the data from a table. Restricting myself to just consider clustered tables made it simpler as it’d just be a matter of pursuit the linked list of pages from start to end. However, that meant I’d have to parse the header as well. There’s some good information out there on the torso of pages, but everything I could find had a unshared lack of information on the very header structure and field types. While resorting to #sqlhelp didn’t directly help me, Kimberly Tripp was kind unbearable to point out that I’d probably not have any luck in finding deeper documentation out there. Fast forward a bit of patience, some help from #sqlhelp and @PaulRandal in particular, I managed to reverse engineer the header format, as well as a bit increasingly than I initially set out to do for MOW. Feature outtakes With a lot of preconditions (2008 R2 format especially), these are some of the skills OrcaMDF currently possesses: Parsing data, GAM, SGAM, IAM, PFS, TextMix, clustered alphabetize and and the marching page (preliminary). Scanning linked pages. Scanning clustered indexes, either by depth-first into linked-page scan or by forced use of the b-tree structure. Scanning heaps using IAM chains. Scanning tables (clustered or heaps) using just the table name as input – root IAM/index page is found through metadata lookup.Worldly-wiseto parse the pursuit post types: bigint, binary, bit, char, datetime, int, nchar, nvarchar, smallint, tinyint, varbinary & varchar. Adding remaining types is straightforward. Parsing of four crucial system tables: sysallocunits, sysschobjs, sysrowsets, sysrowsetcolumns. Parsing of key metadata like table names, types and columns. There’s probably some errors here and there, and I’ve liberally ignored some complexity here and there thus far, so don’t expect this to work on everything yet. I’m standing minutiae of OrcaMDF. My hope is to have it support 95+% of typically used features, permitting most MDF files to be parsed. If you have a specific use specimen or scenario you’d like covered, please get in touch and let me know. Why oh why are you doing this? I thought I understood most of what I read well-nigh internals. I can now tell you, I did not.Unquestionablyparsing this stuff has taught me so much more, as well as given me a really good hands-on understanding of the internals. I’ve still never touched SSIS, SSAS, SSRS and all of that other fancy BI stuff, but I believe having a solid understanding of the internals will make the later stuff so much easier to comprehend. Furthermore, I think there’s a big opportunity for a number of polity supported SQL Server tools to arise. Some possibilities that come to mind (just thinking aloud here, don’t take it too concretely): Easily distributable read-only wangle to MDF files to desktops, mobile & embedded clients. Disaster recovery – forgot to replacement and can’t restore your untruthful DB? You might just be worldly-wise to pericope important shit using OrcaMDF. Need to probe the contents of a DB without attaching it to an instance? Reading .BAK files – should be possible, will indulge single-table restores and object level probing of replacement files. DBCC CHECKDB of non-attached MDF files – this is probably not going to happen, but theoretically possible. Learning opportunities. By opening up the lawmaking to everybody, this should provide some solid teaching and learning opportunities by looking at samples of how to unquestionably parse the contents and not just read & talk well-nigh it. Alright, alright, show me the codez! All source lawmaking is misogynist on GitHub under the GPLv3 license. Feel self-ruling to fork, watch or comment. The only thing I ask for is that you respect the license. If you end up trying out the lawmaking or unquestionably using it, please let me know – I’d love to hear well-nigh it. Want to follow the latest developments – why don’t you come over and say hi? Mark S. Rasmussen I'm the CTO at iPaper where I petting with databases, mold lawmaking and maintain the overall technical & team responsibility. I'm an voracious speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or squint me up on LinkedIn. CATEGORIES .NETAS/Flex/FlashAmazon Web ServicesComputer ScienceConferences and PresentingIISLifeMiscMiscellaneousPerformancePokerSQL ServerSQL Server - CommunitySQL Server - Data TypesSQL Server - InternalsSQL Server - OptimizationSQL Server - OrcaMDFSQL Server - TricksTestingTools of the TradeUmbracoVisual StudioWebWindbgWindows ARCHIVE 201420132012201120102009200820072006 Copyright © 2014 Mark S. Rasmussen