DB Audit Challenge #1
And the Winner is….
First, I want to thank everyone that participated in the DB Audit Challenge, and appreciate your positive comments and feedback. One reader thanked me for running this challenge. Another was happy to stay engaged b/c he could not attend PASS. Many of you were preoccupied with or attending PASS, so I hope to provide more challenging challenges soon.
I’ve received some interesting replies, and some very subtle questions like, ‘how do I figure this out?’, and ‘it’s impossible to read the transaction log without one of those products’. Some more creative replies just dumped the whole thing into an online HEX editor, and just sort of did one of those word searches to find the answer.
But, one of the best, and winning answers, was definitely well-thought out and wholly complete. While I asked for the contents of at least in one of the field columns presented, I asked for the whole data row to be constructed from the hexadecimal output provided. This fine SQL DBA, not only gave us the correct answer, he broke it down exactly how it should be and explained how he figured it out. (See above reply, now that fellow will know how to figure it out 🙂
Thanks for playing and hope to have the next challenge coming your way soon.
So, without further adieu, the Winner of the first DB Audit Challenge is: drum roll, please!
Michael Lato. Congratulations! Michael is a SQL Server Specialist at DataTamer Inc., hailing from Canada. He has more than 10 years of experience consulting on various database systems with a special focus in the CRM (Customer Relationship Management) field. Michael's past roles have been as diverse as system designer, project manager and head trainer. Michael is currently a member of the Quest SQL Server Expert Panel and he speaks regularly at SQL conferences such as PASS and SQLTeach. He is also open to new sql server opportunities, so if you can use his services, feel free to reach out to him at michael.lato@datatamer.ca
Michael ‘s comments, along with his answer on this contest were:
‘ Well, that was an interesting challenge. This was a deeper dive into the transaction log than I’ve ever done before and the hexadecimal conversions threw me for a little bit until I found the endian references. Here are my results and the codes I extracted to get them:
ItemNumber: 103109
ItemName: OctHoliday
CatalogSkew: SCARY10101
ItemDescription: Happy Halloween
EntryDate: 2009-10-31 22:11:39.963
NoAvailable: 1
Source: RowLog Contents 0
0x30003200C59201004F6374486F6C6964617953004300410
05200590031003000310030003100C5C06D01B39C000001000
0000600C00100570048006100700070007900200048006100
6C006C006F007700650065006E00
Source Breakdown:
0x - header
30 - status A
00 - status B
3200 - position of number of columns in data row (50) required byte swap
C5920100 - ItemNumber,int4 (103109) required byte swap
4F6374486F6C69646179 - ItemName,char10 (OctHoliday)
5300430041005200590031003000310030003100 - CatalogSkew,nchar20 (SCARY10101)
C5C06D01B39C0000 - EntryDate,datetime8 (2009-10-31 22:11:39.963) required byte swap
01000000 - NoAvailable, int4 (16) required byte swap
0600 - number of columns in data row (6) required byte swap
C0 - null bitmap ()
0100 - number of variable-length columns (1) required byte swap
5700 - offset, end of first variable-length column (87) required byte swap
480061007000700079002000480061006C006C006F007700650065006E00 - ItemDescription,nvarchar300 (Happy Halloween)’
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
For SQLCentric Monitoring and Remote DBA Services goto http://www.pearlknows.com
SQLCentric now supports SQL Server 2008!