improve.dk - RawDatabase

OrcaMDF RawDatabase - A Swiss Army Knife for MDF Files | Mark S. Rasmussen

Improve.dk Spined HTML

OrcaMDF RawDatabase - A Swiss Army Knife for MDF Files | Mark S. Rasmussen Mark S. Rasmussen improve.dkWell-nighMe Pages Categories Archive Nov 04 2013 OrcaMDF RawDatabase - A Swiss Army Knife for MDF Files .NET , SQL Server - Internals , SQL Server - OrcaMDF , SQL Server , Tools of the Trade Comments When I initially started working on OrcaMDF I had just one goal, to proceeds a deeper knowledge of MDF file internals than I could through most books available. As time progressed, so did OrcaMDF. While I had no initial plans of doing so, OrcaMDF has ended up stuff capable of parsing wiring tables, metadata and plane dynamically recreating worldwide DMVs. On top of this, I made a simple GUI, just to make OrcaMDF easier to use. While that’s great, it comes at the price of lattermost complexity. To be worldly-wise to automatically parse table metadata like schemas, partitions, typecasting units and more, not to mention abstracting yonder details like heaps and indexes, it takes a lot of lawmaking and it requires intimate knowledge of the database itself. Seeing as metadata changes between versions, OrcaMDF currently only supports SQL Server 2008 R2. While the data structures themselves are rather stable, there are minor differences in the way metadata is stored, the data exposed by DMVs and so forth. And on top of this, requiring all of the metadata to be perfect, for OrcaMDF to work, results in OrcaMDF stuff just as vulnerable to self-indulgence as SQL Server is itself. Got a untruthful marching page? Neither SQL Server nor OrcaMDF will be worldly-wise to parse the database. Say Hello to RawDatabase I tried to imagine the future of OrcaMDF and how to make it the most useful. I could march on make it support increasingly and increasingly of the same features that SQL Server does, sooner stuff worldly-wise to parse 100% of an MDF file. But what would the value be? Sure, it would be a unconfined learning opportunity, but the thing is, if you’ve got a working database, SQL Server does a pretty good job too. So what’s the alternative? RawDatabase, in unrelatedness to the Database class, doesn’t try to parse anything besides what you tell it to. There’s no will-less parsing of schemas. It doesn’t know well-nigh wiring tables. It doesn’t know well-nigh DMVs. It does however know well-nigh the SQL Server data structures and it gives you an interface for working with the MDF file directly. Letting RawDatabase parse nothing but the data structures ways it’s significantly less vulnerable to self-indulgence or bad data. Examples It’s still early in the development, but let me show some examples of what can be washed-up using RawDatabase. While I’m running the lawmaking in LINQPad, as that makes it easy to show the results, the result are just standard .NET objects. All examples are run versus the AdventureWorks 2008R2 LT (Light Weight) database. Getting a Single Page In the most vital example, we’ll parse just a single page. // Get page 197 in file 1 var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); db.GetPage(1, 197).Dump(); Parsing the Page Header Now that we’ve got a page, how well-nigh we dump the header values? // Get the header of page 197 in file 1 var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); db.GetPage(1, 197).Header.Dump(); Parsing the SlotVarietyJust as the header is available, you can moreover get the raw slot variety entries. // Get the slot variety entries of page 197 in file 1 var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); db.GetPage(1, 197).SlotArray.Dump(); Parsing Records While getting the raw slot variety entries can be useful, you’ll usually want to squint at the records themselves. Fortunately, that’s easy to do too. // Get all records on page 197 in file 1 var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); db.GetPage(1, 197).Records.Dump(); Retrieving Data from Records Once you’ve got the records, you could now wangle the FixedLengthData or the VariableLengthOffsetValues properties to get the raw stock-still length and variable length post values. However, what you’ll typically want is to get the unquestionably parsed values. To spare you the work, OrcaMDF can parse it for you, if you just provide it the schema. // Read the record contents of the first record on page 197 of file 1 var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); RawPrimaryRecord firstRecord = (RawPrimaryRecord)db.GetPage(1, 197).Records.First(); var values = RawColumnParser.Parse(firstRecord, new IRawType[] { RawType.Int("AddressID"), RawType.NVarchar("AddressLine1"), RawType.NVarchar("AddressLine2"), RawType.NVarchar("City"), RawType.NVarchar("StateProvince"), RawType.NVarchar("CountryRegion"), RawType.NVarchar("PostalCode"), RawType.UniqueIdentifier("rowguid"), RawType.DateTime("ModifiedDate") }); values.Dump(); RawColumnParser.Parse will, given a schema, automatically convert the raw bytes into a Dictionary<string, object>, the key stuff the post name from the schema and the value stuff the very type of the column, e.g. int, short, Guid, string, etc. By letting you, the user, specify the schema, OrcaMDF can get rid of a slew of dependencies on metadata, thus ignoring any possible self-indulgence in metadata. Given the availability of the Next & PreviousPageID properties of the header, it would be simple to iterate through all linked pages, parsing all records of each page - basically performing a scan on a given typecasting unit. Filtering Pages Besides retrieving a specific page, RawDatabase moreover has a Pages property that enumerates over all pages in a database. Using this you could, for example, get a list of all IAM pages in the database. // Get a list of all IAM pages in the database var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); db.Pages .Where(x => x.Header.Type == PageType.IAM) .Dump(); And since this is powered by LINQ, it’s easy to project just the properties you want. For example, you could get all alphabetize pages and their slot counts like this: // Get all alphabetize pages and their slot counts var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); db.Pages .Where(x => x.Header.Type == PageType.Index) .Select(x => new { x.PageID, x.Header.SlotCnt }).Dump(); Or let’s say you wanted to get all data pages with at least one record and increasingly than 7000 bytes of self-ruling space - with the page id, self-ruling count, record count and stereotype record size as the output: var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); db.Pages .Where(x => x.Header.FreeCnt > 7000) .Where(x => x.Header.SlotCnt >= 1) .Where(x => x.Header.Type == PageType.Data) .Select(x => new { x.PageID, x.Header.FreeCnt, RecordCount = x.Records.Count(), RecordSize = (8096 - x.Header.FreeCnt) / x.Records.Count() }).Dump(); And as a final example, imagine you’ve got just an MDF file but you seem to have forgotten what objects are stored inside of it. Fret not, we’ll just get the data from the sysschobjs wiring table! Sysschobjs is the wiring table that stores all object data, and fortunately it has a static object ID of 34. Using this, we can filter lanugo to all of the data pages for object 34, get all the records and then parse just the two first columns of the schema (you may specify a partial schema, as long as you only omit columns at the end), ending up in us dumping just the names (we could of undertow have gotten the full schema, if we wanted to). var db = new RawDatabase(@"C:\AWLT2008R2.mdf"); var records = db.Pages .Where(x => x.Header.ObjectID == 34 && x.Header.Type == PageType.Data) .SelectMany(x => x.Records); var rows = records.Select(x => RawColumnParser.Parse((RawPrimaryRecord)x, new IRawType[] { RawType.Int("id"), RawType.NVarchar("name") })); rows.Select(x => x["name"]).Dump(); Compatibility Seeing as RawDatabase doesn’t rely on metadata, it’s much easier to support multiple SQL Server versions. Thus, I’m happy to say that RawDatabase fully supports SQL Server 2005, 2008, 2008R2 and 2012. It probably supports 2014 too, I just haven’t tested that. Speaking of testing, all unit tests are automatically run versus AdventureWorksLT for both 2005, 2008, 2008R2 and 2012 during testing. Right now there are tests demonstrating that OrcaMDF RawDatabase is worldly-wise to parse the first record of each and every table in the AdventureWorks LT databases.Self-indulgenceOne of the really interesting use cases for RawDatabase is in the specimen of corrupted databases. You could filter pages on the object id you’re searching for and then brute-force parse each of them, retrieving whatever data is readable. If metadata is corrupted, you could ignore it, provide the schema manually and the just follow the linked lists of pages, or parse the IAM pages to read heaps. During the next couple of weeks I’ll be blogging increasingly on OrcaMDF RawDatabase to show various use specimen examples, including ones on corruption. Source & Feedback I’m really excited well-nigh the new RawDatabase wing to OrcaMDF and I hope I’m not the only one who can see the potential. If you try it out, have any ideas, suggestions or other kinds of feedback, I’d love to hear it. If you want to try it out, throne on over to the OrcaMDF project on GitHub. Once it’s just a bit increasingly polished, I’ll make it misogynist on NuGet as well. Just like the rest of OrcaMDF, the lawmaking is licensed under GPL v3. 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