Pearl Knows

SQL 2008 Auditing Presentation in NJ by the MAK!

2010-03-15T18:28:00Z

If you are a regular to the sql server community sites, forums and blogs, then you know about the awesome stuff brought to you by SQL MVP Muthusamy Anantha Kumar(a.k.a. MAK ). Well, if you're in the NY/NJ area, be sure not to miss tomorrow's evening's presentation at the NJ User Group HQ. (Sorry for the short notice!)

Tuesday, March 16, 2010 - 6PM

This session will demonstrate the various legal compliance requirements / controls such as Sarbanes Oxley, SAS 70, etc and how to make use of SQL Server 2008 Auditing features to achieve it. This includes different types Auditing - Server level, Database Level and Audit level. This demo also includes auditing steps like auditing a domain group, Auditing job changes etc.

Details & Register: http://tinyurl.com/yzlfmnf

Bio:
Muthusamy Anantha Kumar(a.k.a. MAK - http://www.mssqlengineering.com)
MAK is a Microsoft Certified IT Professional - Database Administrator ( MCITP SQL Server 2008, MCITP SQL Server 2005) . He has more than 11 years of experience in Information Technology including Database Engineering, Database Administration, System Analysis, Design, Development and Support of MS SQL Server 2008/2005/2000/7.0/6.5/6.0/4.X for production/development/testing. He is experienced in MS SQL Server Engineering, installation, configuration, performance tuning, client/server connectivity, query optimization, back-up/recovery. He has handled VLDBs and been involved in SDLC of various data warehouse projects. Currently, MAK is Senior Database Engineer responsible for Engineering and standardizing Microsoft SQL Servers in one of the big finance companies on Wall Street. Previously, he was a Senior Database Administrator/Data Architect, responsible for development, test, DR and production servers for many finance , .com and B2B companies. He plays an active role in forums as a SQL Expert and moderator. He also teaches Database Administration at University of West Florida. He also teaches SQL Server part time in New Jersey. He published many online articles and he is a contributing columnist in DatabaseJournal.com. He is also publishing online articles in SQL-Server-Performance.com and SQLServercentral.com.

How to Consolidate & Virtualize SQL Server 2008

2010-03-15T14:31:00Z

Many companies face the decisions to 1) consolidate their SQL Servers, due to sql sprawl; and 2) whether to virtualize their SQL environment by placing muliple SQL Servers on one powerful physical box. Here is an interesting whitepaper, sponsored by Dell & AMD, that discusses how to Consolidate SQL Server 2008 on Dell 4 Socket - Multi-Core Servers - Using Hyper-V

IT pros have the difficult job of consolidating multiple SQL Server 2008 instances onto a single physical system and the problems compound in a virtual environment. This white paper discusses how to effectively virtualize SQL Server 2008 on the Hyper-V platform. Explore the concepts and procedures essential for migration of your SQL Server databases to a virtualized environment. Discover an overview of basic general virtualization and consolidation processes, and gain insight into how to set up and configure a virtual server.

Find out how to consolidate SQL Server 2008 in a virtual environment: http://tinyurl.com/ye2slrw with this great whitepaper. I hope this information is helpful!

Article in SQL Server Magazine on SQL IO Stastics.......

2010-02-26T17:46:00Z

Article on I/O tips appearing on SQLMag.com

If you've followed my blog, you know I've written on various topics, including I/O stats. I've expanded and put these tips together, and the editors at SQL Server Magazine were kind enough to carry it. If you're interested in analyzing and gathering I/O statistics, my article in SQL Server Magazine's March edition has just been published!

I'm always looking for ways to measure SQL Server's performance and present the information in a readable and useful format. Here are some useful tips I've come across for getting and analyzing I/O information:

Use sys.dm_io_virtual_file_stats.
Use SQL Server Performance Monitor to help identify I/O bottlenecks.
Understand the infamous error message.

( All credits are given to those whose tips I've referenced.)

Check out the print edition if you're a subscriber, or log on to

http://tiny.cc/9lNTY .

I hope the information is helpful. Thanks for reading and enjoy!

SQLSaturday#39 in NYC!

2010-02-18T01:31:00Z

If you can't make #33 in NC, there's always #39 in NY -

SQLSaturday#39 in NYC!

It's shaping up to be another super SQL Saturday in the Big Apple - NYC on April 24, 2010 at Microsoft's NY HQ. This is the official SQLSaturday Number #39. It's going to be great!

We currently have 200+ registrations and expect 400 - 450. So, in techno-jargon, we are about 30% full. It's still early, so register now for this FREE, AWESOME Event.

http://www.sqlsaturday.com/39/register.aspx

There will be several tracks focusing on BI, ETL and also covering SQL Server 2008 R2! Fantastic sessions and MVP speakers will be there!

Complimentary breakfast, lunch, SWAG, prizes and more! We will raffle off licenses for our own award-winning sql monitoring and alert solution - SQLCentric.

Don't forget to stop by the Sponsor Exhibits, and visit representatives from your favorite companies (including us!)

Pearl Knowledge Solutions is now an official sponsor of SQLSaturday #39 in NYC on 4.24.2010 - http://www.sqlsaturday.com/39/sponsors.aspx

From the NY/NJ User Group Coordinators: SQL Saturday #39 NYC is a free training event for professionals who work with data—particularly those who use Microsoft technologies. This event will feature high-quality technical content for SQL Server DBAs, SQL Server and ETL developers, and BI professionals. If you are a software professional working in any of these disciplines then you are a data professional and this event is for you...

See you there! - Robert Pearl

RedGate Releases SQLSearch - FREE!

2010-02-03T05:14:00Z

RedGate Releases SQLSearch - FREE!

SQL Search - Released

My friends at RedGate have released a new FREE Tool. Please read on to learn more and how to get your copy!

SQL Search, a plug-in for SSMS, makes search within SQL Server databases possible. It allows users to search their database schemas to instantly locate any term in stored procedures, functions, views and more. Best of all it's FREE to everyone right now.

Find fragments of SQL text within stored procedures, functions, views and more

Quickly navigate to objects wherever they happen to be on your servers

Find all references to an object

Integrates with SSMS

Feel free to let others know about it as well as inviting then to Download SQL Search here: http://www.red-gate.com/products/SQL_Search/index.htm

SQL Server 2008 - Top Reasons to Upgrade.....

2010-01-21T15:15:00Z

OK, you've seen many of these before, but if you haven't figured out its TIME to upgrade your SQL Servers to 2008, here are 7 more reasons, brought to you by TechTarget, Dell & Intel, as part of their SQL Server 2008 Upgrade Series. Some are obvious, others not - but if you are on SQL Server 2000, make the move as soon as possible - support is already phased out. If you're on SQL Server 2005, then, well, there are no excuses - the transition is pretty seemless.

There is much for the DBA and BI professional alike. My personal favorite reasons for upgrading are the new exciting features in SQL Server 2008, such as Data and Backup Compression (- see my earlier blog on this topic: Decompressing on Compression in SQL Server 2008....), Change Data Capture (CDC), Auditing, Policy Management, Data Collector and Management Data Warehouse, Spatial Data, and more...

For a full list of features and detailed descriptions, see SQL Server Books Online and the SQL Server 2008 Overview Web site.

So, back to the article at hand. I wanted to share and disseminate the information, which is focused on the small-medium businesses, and talks about business strategies with respect to upgrading to 2008.

Among their seven business reasons for upgrading to SQL Server 2008 are:

YOUR BUSINESS WILL BE MORE INTELLIGENT
YOUR MISSION-CRITICAL DATA CAN BE BETTER PROTECTED
YOUR ENTIRE BUSINESS WILL PERFORM MORE EFFICIENTLY
YOUR DATABASE WILL BE MANAGED CENTRALLY
IT’S GOOD TO BE CURRENT

IF YOU STILL HAVE SQL SERVER 2000, WHAT ARE YOU WAITING FOR?

IF YOU HAVE SQL SERVER 2005, WHY WAIT AT ALL?

It isn't always easy to know when to upgrade to the latest hardware or software release. This white paper explores the benefits of moving to SQL Server 2008, including improved performance and management capabilities. Gain insight into how to simplify this transition, and the top seven reasons organizations are making the shift. Learn how this upgrade can enable your organization to improve its efficiency, data security and business continuity. Learn more of the benefits of this upgrade here.

Hope you enjoy!

SQL Server Waits and Queues

2010-01-15T15:48:00Z

Recently, I was asked to do an extensive sql server performance audit and review. I will be sharing some of the information, scripts and documents that I referenced when I completed this thorough analysis. One of the most important things in performance tuning, is the knowledge that goes along with best practices on SQL Server Waits and Queues. The article is quite in-depth, but contains a treasure trove of valuable performance tuning information, especially with respect to waits and queues.

In this whitepaper, you will find examples and explanations of every kind of Wait Type, and how to correlate it to a particular performance resource issue or bottleneck. You will readily be able to identify the cause of these waits, and learn how to optimize the performance of your sql server. A user typically experiences poor performance as a series of waits. Whenever a SQL query is able to run but is waiting on another resource, it records details about the cause of the wait. These details can be accessed using the sys.dm_os_wait_stats DMV. One can examine the accumulated cause of all the waits, based on the output of the DMV, as displayed below:

So, just to take the first wait type that shows up as the highest % of waiting, on the server, CXPACKET.

This wait occurs when trying to synchronize the query processor exchange iterator. Consider lowering the degree of parallelism if contention on this wait type becomes a problem. Parallel process waits can sometimes occur when data is skewed. In such cases, one parallel thread may process a larger number of rows while another may process a smaller number of rows and so on.

In an OLTP environment, excessive CXPACKET waits can affect the throughput of other OLTP traffic. In a data warehouse environment, CXPACKET waits are expected for multiple proc environments.

Therefore, for OLTP workloads, you may want to consider limiting parallelism by setting max degree of parallelism to some number less than the total number of CPUs, and other than the default '0', meaning ALL processors. Please see the document for definitions on other wait types.

In another example, I already authored some articles on IO usage, statistics, and scripts. High I/O usage alone is not necessarily an indication of a problem, or I/O pressure. In fact, IO_stalls, is the total cumulative time, in milliseconds, that users waited for I/O to be completed on the file since the last restart of SQL Server. One DMV, sys.dm_io_virtual_file_stats provides a breakdown of SQL Server reads, writes, and io_stalls for a particular database or transaction log file.

There is a complete section dedicated to IO_Stalls, which is one type of wait that can help identify performance bottlenecks. However, this is just a tiny bit in a large pool of useful information contained in the doc.

Here's a summary of this document, which can be downloaded directly from Microsoft, by clicking on the link below.

SQL Server 2005 Performance Tuning using the Waits and Queues is a proven methodology that allows one to identify the best opportunities to improve performance, the so called “biggest bang for the buck”. These performance improvements are likely to have a significant return on the performance tuning time investment. The methodology helps identify the areas of slow performance by looking at the problem from two directions called Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant Queues (that is, Performance Monitor counters and other data) for this workload. The cross validation of the waits analysis enables us to eliminate all except the most significant performance counters, and provides a strong indication of pressure on specific resources.

Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server 2005, the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. The system or resource perspective uses Performance Monitor counters and other data sources to provide a breakdown of system resource usage according to resource queues. Taken together, the value of the application and resource perspectives used by the waits and queues methodology enables us to narrow in on the bottleneck and eliminate the irrelevant performance data.

In sum, Performance Tuning using the Waits and Queues methodology is an effective way to quickly identify and resolve application performance problems because it lets the user discover new and potentially unexpected problem areas, within applications or solutions without the typical guesswork that can accompany such work.

Performance tuning of applications and solutions has been around for many years. The performance of SQL Server 2005 database applications should be evaluated from several different perspectives. Each perspective tells a different section of the complete performance story. Together they paint a detailed performance picture of the whole and also cross validate observations in each specific perspective.

Although, the document was drafted for SQL Server 2005, it is certainly applicable to SQL Server 2008, (some new DMV's may have been introduced).

Without further adieu, to download this whitepaper entitled, SQL Server 2005 Waits and Queues - SQL Server Best Practice Article, authored by Tom Davidson, updated by Danny Tambs, and technically reviewed by Sanjay Mishra (full accreditation goes to these gentlemen), goto:

http://msdn.microsoft.com/en-us/library/cc966413.aspx --> Scroll down to the bottom to get the doc. I hope your internet connection is good and doesn't keep you WAITing ;-)

SSIS Package Credentials - Featured article on SSC.com

2009-12-10T15:48:00Z

Today, on SQLServerCentral.com, SSIS Package Credentials, is the featured article on the site.

I originally drafted this article as a blog post, and then decided it would be better published as an article. I believe many of us DBA's have come across the issue before, and therefore a wider audience could benefit from this information. The problem lies in knowing the differences between scheduling a package/job to run in 2000, as compared to the more secure SQL Server 2005/2008. In SQL 2005, a much more complex security model, makes the everyday scheduling of a package or job a bit less straightforward than a few clicks of the mouse. For the typical production DBA, this could be a major headache, as my hypothetical story shows. So, armed with the knowledge I present in my article, hopefully will make the task more streamlined.

SSC leadin: "What do you do when you run an SSIS package and it works, but it fails when scheduled? You might have a credential problem. Robert Pearl brings us a solution to a cryptic message that prevents you from scheduling a package."

Please read on here: SSIS Package Credentials

--------------------------------------------------------------------------------------------------------------------

So, as we approach the end of 2009, many IT departments often have excess budget already allocated for the year, and will be lost for the department, and so, the boss may give the green light to go "nuts" - Training, software, books, etc. Otherwise known as "burn budget"

One smart purchase and investment with great ROI, could be the acquisition of a monitoring and alert system. Why not recommend to the boss, our award-winning monitoring solution SQLCentric? (Now supports SQL Server 2008!). If your company makes the committment to buy prior Jan 1, 2010, you will receive an additional 10% discount, if you mention the code "credentials".

You can download an eval at http://www.pearlknows.com

A CIO's Voice....

2009-12-08T06:46:00Z

As you know if you follow this blog, Pearl Knows is the creator of the monitoring software SQLCentric, and offers managed Remote DBA services. I don’t often use this space to push my company or promote my products, unless I have a major announcement, or there is a newsworthy item to cover.

Hey, did you know that SQLCentric now supports SQL Server 2008, and that we will be releasing our SQLOS live performance dashboard soon? - End Plug.

Ok, so the point of this blog is, in general, when you have a great product (see above :-) that a DBA really loves and wants, or when the IT department needs to bring in additional DBA resources, that decision is made not by the DBA, but the by the all-knowing, all-seeing fearless IT leader, known as the CIO – [noun pronounced see-eye-owe] Chief Information Officer. (With all due respect and deference to all my CIO friends and colleagues).

Seriously though, if I want to sell my products and services, the person I need to get to and convince of the value, and of course the bottom line ROI, is the CIO, who controls the budget. They must decide on whether or not these services will benefit the company and not bust the budget.

Along the way one terrific fellow that I have met and corresponded with is Arun Manansingh, who is an extremely knowledgable, seasoned executive and CIO, with over eleven years of IT management experience in international finance, leasing, and banking. Arun should be considered a leading authority and expert in his field. He maintains a compelling blog called "A CIO’s Voice", which offers advice, insight, and humor for Chief Information Officers and Leaders. He has been quoted and appeared in several IT and CIO oriented publications such as CIOZone.com, and Forbes.com. In our circles, we often get the DBA view, so if you want to get a fresh perspective from the CIO's point of view, definately bookmark and follow his Blog. Feel free to reach out to Arun, via his blog or via LinkedIN.

Turns out that the subject of his latest blog, was none other than me and my company’s product and services. He also carried on his blog my recent whitepaper on Fixed Cost IT Management and the Remote DBA and the benefits of these managed services. For this I am grateful, and appreciate his kind words, as well as the the time he took to write me up, and publish his blog. You can check out the post here: A CIO’s Voice on Pearl Knowledge Solutions, Inc. /

---------------------------------------------------------------------------------------------------------

Feel free to check out PearlKnows.com – http://www.pearlknows.com

SQL Job Ownership Article on SSC.Com

2009-11-19T16:03:00Z

Today, Please see my featured article appearing on SQLServerCentral.com discussing SQL Agent Job Ownership.

Have you ever had a situation where a DBA or admin leaves the company, his login is disabled, only to discover that his years of service, and of creating sql jobs to run under his account, are now all suddenly failing? Backups, Maintenance Jobs, Packages, etc. 10, 20, 30, 40, 50 servers +.

What is the DBA left behind to do? That is the topic of this article and what you can do to automate and fix the issue accross your SQL Server infrastructure, and how to prevent it from happening in the future, using the scripts provided.

Please read on in Let's Talk Ownership (SQL Jobs)....
http://www.sqlservercentral.com/articles/SQL+Jobs/68764/

Thanks for reading! Hope this is helpful - RP

And the Winner is........

2009-11-10T07:44:00Z

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!

Thoughts on a PASSing Scene...

2009-11-06T02:06:00Z

Well it certainly has been a busy and exciting week at PASS. I couldn't go this year, but through the magic of SSC.com, the blogosphere and personal email from Seattle, I was there in spirit. You might say I was virtually there.

Speaking of virtual, one of the sessions I've followed was the Virtualization VC Breakfast Session, sponsored by VMWare, highlighting real-life experiences and challenges with SQL Server in virtualized environments. The debate to virtualize SQL or not continues.There's been a lot on this in the news and events this week, and I'll add my two-cents next week.

I even got pinged by one of my seasoned BI colleagues, Chuck Kelley manning one of the booths at PASS, hoping to hook up with Brian Knight. I left a message, but there's always a next time :-) So, let's hear some feedback on Brian's politically incorrect presentation.

My new buddies from India, Jacob Sebastian and Pinal Dave were there, as well Greg Larsen, who was given honorable mention in Andy Warren's blog as one of those to watch. And as if Steve Jones didn't have enough to think about on the way out the door to take off for Seattle, I pinged him about when my next article's going to be published. (Sorry man, I hope it wasn't my fault on the luggage mishap)

Ok, I dropped enough names, I feel like a gossip columnist. No offense to the ones I didn't. During this time, I made some new friends and connections in the SQL World. I hope to collaborate with them and bring some new and great contributions to the SQL community.

My blog has been appropriately light this week, making room for all the PASS MVPs, attendees and presenters, bringing us up-to-date coverage from Seattle. I didn't want to compete with all this, so I'll pick up the pace in the week to come.

So, in absence of technical content, and to keep those who didn't attend PASS in a good mood, I launched my first DB Audit challenge #1 (still one more day to play, before I announce the winner(s).) I will mention and profile those who participated.

Check out the challenge at http://www.sqlservercentral.com/blogs/pearlknows/archive/2009/11/01/db-audit-challenge-1.aspx
I've received very positive feedback and thank you's on this, so I will do my best to bring a new challenge soon.

Finally, maybe I'll have an MVP Profile of the week again, but this week at PASS, all our MVPs and presenters there deserve to be named MVP of the week for their extraordinary, outstanding contributions and ongoing service to the SQL Server community!

Great job ALL! On behalf of the sql community at large, we thank you!

Reference to PASS Session - T-LOG

2009-11-03T17:19:00Z

Hey, just caught wind of some timely stuff going on at PASS. Had my trusty 'Google alert' email me, set to ring on anything about the function fn_dblog.

Kalen Delaney, the sql internals extraordinaire, who brought us the book called Inside Microsoft SQL Server 2005: The Storage Engine, was in good form with her session on 'Care and Feeding of the Transaction Log' She went over the internals of the transaction log, and using the undocumented functions DBCC LogInfo, as well as the less resource intensive flexible fn_dblog function, to read and translate the transaction log.

In my own presentation on DB Forensics, I spoke at length on the Transaction Log, based on some of the materials in Ms. Delaney's book.

Here is one attendee who blogged about the t-log session, http://www.networkworld.com/community/node/47069

It was noted that one could talk about the transaction log for the whole day. Indeed!

So, those of you who attended the session at PASS, I am challenging you to my DB Audit Challenge and invite you to solve the t-log puzzle.

I posted some hints and clues on my earlier blog here: http://www.sqlservercentral.com/blogs/pearlknows/archive/2009/11/03/db-audit-challenge-1-part-ii-get-a-clue.aspx

Is there anyone out there that's up to the challenge? (Ms. Delaney and other MVP's excluded)

DB Audit Challenge #1 - Part II - Get a Clue

2009-11-03T14:48:00Z

In the spirit of my Who DID IT and RAN SQL Caper presentation.....

OK, I was hoping to get the creative juices stirring, and now I will provide some clues and direction on how to solve the T-log puzzle. It's actually very simple (easy for me to say :-). One just needs to know HOW to read the transaction log. Come on, where's all the internals geeks out there?

First, to sweeten the pot, I will INDEED provide some prizes to be announced shortly - hey, I'm still waiting to hear back from the goody providers.

If you haven't yet seen the first entry on this, I invite you to take the DB Audit Challenge here:

http://www.sqlservercentral.com/blogs/pearlknows/archive/2009/11/01/db-audit-challenge-1.aspx

Above, you will find the directions on how to play (no scratch-offs), and a schema, as well as the hexadecimal t-log content is all there for you to decipher the 'hidden' message. So here are some clues, both technical and non-technical:

To read the Transaction Log output (see blog post above), you will need to find the position of the variable-length column called ItemDescription. Since it is an nvarchar, it will be stored in Big-Endian order. Find the position of the column, and convert it to an nvarchar - hey, I just told you how to do it - now you have to find it in there. The goal is to reconstruct the entire data row (all column data based on the schem provided). Since it is the ONLY variable length column in this schema, once you find the first position, the rest of the hex data will be the entire content needed to translate the data.

Oh, one final clue: It is a DELETED record, otherwise known as a 'Ghost Record'. I completed and published this challenge on October 31, 2009. Send all answers to sqlcentric@pearlknows.com - Good Luck!

DB Audit Challenge #1

2009-11-01T18:43:00Z

Take the DB Audit Challenge - Intermediate - DB Audit Challenge #1

For those of you who have been following my recent blogs, as well as those who attended the SQLSaturday October 24 event in NJ, know that my presentation covered several topics with respect to database forensics. I demonstrated the various tools and methods, by leveraging the native out-of-the-box features in SQLServer, to audit and recover data. Topics included DDL Triggers, the Default Trace, CDC, and the Transaction Log. I gave a review of db internals on how to read the transaction log through the very useful system function: fn_dblog.

I present to the SQL Community at-large the first of perhaps a series of DB Audit Challenges below.

In order to decipher the puzzle, I am including the schema of a simple table called Item, with the column names, data types and length shown in Figure 1A. In addition, I am including the hexadecimal output of the RowContents0 field from the Transaction Log of a captured Delete Operation, otherwise known in the log as an LOP_Delete_Rows operation executed against the Item table in Figure 2A. Deletes are a logged operation and therefore can be recovered from the transaction log. These records are known as ‘Ghost Records’.

Using the information provided, I am asking you to reconstruct the data row, and give me the exact contents of the ItemDescription field. In this field is a simple message apropos for this time of year.

If you can reconstruct the contents of the entire row, I will consider it a bonus, but just want to see how many people can tell me what it is that’s in the ItemDescription column. I’ll run this challenge for the week, and will drop some clues and hints throughout to solve the puzzle.

Ok, if you need some help, you can use the book called Inside Microsoft SQL Server 2005: The Storage Engine – by Kalen Delaney. Also, if you were fortunate enough to catch my presentation, you received some handouts that can also help!

OK, so this is an internals exercise on translating the contents of the Transaction Log. If you are up to the challenge, then it’s time to get busy, and send your answers to sqlcentric@pearlknows.com.

I will post the results of how many folks got the right answer.

Figure 1A

Please note, I’ve formatted the output below into four rows for readable format. Take the four rows and concatenate it into one string, as you begin you journey to find the hidden message, and reconstruct the row. I will consider giving away some prizes, depending on how many people get the right answer. The first official hint is, remember, that all fixed columns appear first in the log, followed by variable-length columns.

I hope you find this interesting. Good Luck and stay tuned to this blog for more clues!

RowLog Contents 0

0x30003200C59201004F6374486F6C6964617953004300410

05200590031003000310030003100C5C06D01B39C000001000

0000600C00100570048006100700070007900200048006100

6C006C006F007700650065006E00 Figure 2A

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Coming soon! Our fully integrated SQL Server OS Live Performance Dashboard!

In the meanwhile for SQLCentric monitoring, and remote DBA services, check us out at

http://www.pearlknows.com