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.

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

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:

1. YOUR BUSINESS WILL BE MORE INTELLIGENT
2. YOUR MISSION-CRITICAL DATA CAN BE BETTER PROTECTED
3. YOUR ENTIRE BUSINESS WILL PERFORM MORE EFFICIENTLY
4. YOUR DATABASE WILL BE MANAGED CENTRALLY
5. IT’S GOOD TO BE CURRENT
6. IF YOU STILL HAVE SQL SERVER 2000, WHAT ARE YOU WAITING FOR?
7. 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!

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 ;-)

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

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

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

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!

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!

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)

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!

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!

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

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

 Well, it's a wrap!  Saturday has come and gone, but memories will go on.  It was great to see so much effort and planning in a relatively short time to come together in one great event.   I had a fantastic time.  If you missed it, no worries, much discussion and materials surrounding the event have already been posted on Jacob Sebastian  and Michael Cole’s blog.  More photos and presentations will be posted shortly.  We had an podcast expert on hand!  It was truly an honor to present with two great MVP's Michael and Jacob!  Special thanks to the SQLDiva herself, Melissa Demsak, leader and coordinator of the NJ SQL User Group.

Jacob makes mention of our trip to NJ.  Took a little effort to find the place (Guess I should have made a right instead of left :-).  What we didn't know as Michael's presentation was underway, is that his topic "Where in the World?" on spatial data and links to google maps was about us!  We could have used this info then.

It was a pretty good crowd for a Saturday - close to 100 sql server dba's and developers.  It was great meeting this nice bunch of folks!  Excellent questions from many in the audience.  Jacob, who came after me, gave some great speaking techniques.  Like, if you don't know the answer, tell them we will take it off-line (LOL) - he should have told me that before! :-)  But, bookended by such talented and bright MVP's, we did a good job of trying to get all questions answered.  We even deferred to some smart audience folks who helped out as well.

Special thanks to our sponsors, Microsoft and Red-Gate, who provided much needed nutrition and some cool SWAG.  We gave away great prizes, including autographed books written by Jacob and Michael.  I was lucky enough to grab an extra copy of Jacob's book. (Hey Mike, you gotta get me your Pro T-SQL book) Prizes included, The Art of XSD – SQL Server XML Schema Collections, by Jacob, Michael’s book Pro T-SQL 2008 Programmer’s Guide, several copies of Windows 7, including a Signature Edition copy, T-shirts from Red-Gate and many more.  Also, we gave away two 3-licensed copies of my core award-winning SQL Server monitoring and alert product - SQLCentric.

I think it's unanomous that the surprise speaker of the day was in fact Jacob!  We all knew he was a great author, but now we know it is matched by his presentation skills!  He took a dry topic like 'Error handling' and turned it into one great interactive session that brought a teriffic conclusion to a terrific day!  Those of you who are planning to attend PASS next month, make sure you sign up for Jacob's presentation, and not to miss out!

Thanks to all in attendance who gave up their Saturday to be with us!  Feel free to ping me with questions and comments anytime!

I look forward to working with Jacob, Melissa and Michael again.  New York's Microsoft HQ is under construction (which brought us over the Hudson to NJ), and should be completed just in time for spring - where, we'll do it again with exciting topics on all things SQL.

Now that it's behind us, I hope to get back to a more regular blog schedule :-)

Signing off - RP

 UPDATE:   I just finished up my presentation slides for this upcoming NY/NJ SQL User Group meeting, on Saturday, 10/24/2009.  I may cut a few slides, but I'm certainly done adding any.  Probably a few too many, but some of them are just section dividers, and I can always skip a subtopic if I'm short on time.  Perhaps a few small modifications, but for the most part - I'm done.  I also created my script files, and have them ready for some of the Demos I plan to do.  I will be providing handouts for the audience members to follow along on some parts.  Now for the dry run....

So, my presentation, "Who Did IT and Ran" - a SQL Caper, will be a focus on Database Forensics, of how to employ native sql server views and functions, to find out who did what to your database and when. It will give you ideas on how to get your SQL Server in compliance (think SOX), without the use of 3rd party solutions.

I will be discussing the various methods to retrieve this information, such as the Default Trace and DDL Triggers.  We will explore the internals of the Transaction Log, and attempt to read it in its native HEX format.  This will be sort of a magician's secret revealed - the algorithm how the popular 3rd party vendors do it in their compliance and data recovery software.  Luckily, with the advent of SQL Server 2008's Change Data Capture, which will also be a discussion and demo topic, I will show you the far easier way to capture DML.

Finally, we will be giving away great prizes, SWAG, including FREE licenses for our SQLCentric monitoring & alert system! 

I hear we still have some seating availability left, so to register and for more info on this FREE event and for FREE Breakfast and Lunch, see the link below.  Hope to see you there!

The NJ and NYC SQL Server User Groups would like to invite you to a FREE Saturday Event featuring Jacob Sebastian, Michael Coles, and Robert Pearl

Date: Saturday, October 24th

Time: 9:00am - 3:00pm (Sign-in begins at 8:15am)

Location: Microsoft Office (Iselin, NJ)

Registration: Required

For Registration Link/Bios/Topic Summary/More Detail, please see:
http://njsql.org/blogs/user_group_news/pages/NJSQL_2F00_NYCSQL-Saturday-Event-_2D00_-Saturday_2C00_-October-24th-_2800_9am_2D00_3pm_2900_.aspx

User Group Websites:
NJSQL - http://njsql.org/Default.aspx
NJSQL Google Group: http://groups.google.com/group/nj-sql-server-user-group
NYC - http://nycsqlusergroup.com/

Sponsors: Red-Gate, Moore-Stephens Business Solutions, LLC, Pearl
Knowledge Solutions, Inc, Apress

 Hi, all;

The countdown to the NY/NJ SQL Saturday event this upcoming Saturday has begun.  And it's NOT TOO LATE to Register if you are or will be in the NY/NJ - TriState Area.  Jacob Sebastion and Michael Coles will be joining me to bring you fantastic back-to-back presentations.  Prizes, giveaways, SQL Knowledge, and FREE LUNCH, will all be given away at this FREE event.  Please see all the details and links below on where to go and how to register.

Hope to see you there!!

 - Robert Pearl

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

NY/NJ SQL Saturday Event Details - October 24, 2009

NJSQL/NYCSQL Saturday Event on October, 24th (Registration Required)

Here are the details, of my previously announced SQL Saturday event hosted by the NY/NJ SQL Server Professional Group in NJ's Microsoft HQ:

This is my first SQL Saturday presentation event, and I am honored that I will be accompanied by SQL MVP's Jacob Sebastian and Michael Coles.

Special thanks to the SQLDiva herself, Melissa Demsak, leader of the NJ SQL User Group!

Seating is limited, so if you'll be in the New York, New Jersey area, REGISTER now! Look forward to seeing you there! See details below:

The NJ and NYC SQL Server User Groups would like to invite you to a FREE Saturday Event featuring Jacob Sebastian (India).

Date: Saturday, October 24th

Time: 9:00am - 3:00pm (Sign-in begins at 8:15am)

Location: Microsoft Office (Iselin, NJ)

Registration: Required

For Registration Link/Bios/Topic Summary/More Detail, please see:
http://njsql.org/blogs/user_group_news/pages/NJSQL_2F00_NYCSQL-Saturday-Event-_2D00_-Saturday_2C00_-October-24th-_2800_9am_2D00_3pm_2900_.aspx

User Group Websites:
NJSQL - http://njsql.org/Default.aspx
NJSQL Google Group: http://groups.google.com/group/nj-sql-server-user-group
NYC - http://nycsqlusergroup.com/

Sponsors: Red-Gate, Moore-Stephens Business Solutions, LLC, Pearl
Knowledge Solutions, Inc, Apress

Swag: Books, SQLCentric License, Cool Stuff from Red-Gate

Presentation #1
Speaker: Michael Coles (Clifton, NJ)
Topic: "Where in the World?" -- Spatial Data in SQL Server 2008

Presentation #2
Speaker: Robert Pearl (New York, NY - Pearl Knowledge Solutions, Inc. - www.pearlknows.com)
Topic: Who Did it and Ran?

Presentation #3
Speaker: Jacob Sebastian ( India - http://beyondrelational.com/blogs/jacob/ )
Topic: Best practices for exception handling and defensive programming in SQL Server

As we continue to get ready for the NY\NJ SQLSaturday User Group meeting this October 24, 2009, I had the fortune to meet and plan this meeting with SQL Server MVP, developer and published author, Michael Coles.  He is also the Principal ETL Architect at Moore-Stephens Business Solutions.

Michael has over 15 years experience designing and building SQL Server databases and SQL business solutions. A prolific writer on all aspects of SQL Server, particularly on the expert use of T-SQL, he has authored dozens of published articles and several books including "Pro T-SQL 2008 Programmer's Guide", "Expert SQL Server 2008 Encryption", and "Accelerated SQL Server 2008".  These fine works are available on Amazon.com, by clicking the hyperlinks above.

Michael is currently working other book projects and many, many things, including his presentation for the SQL Saturday event.

He will be presenting on Spatial Data in SQL Server 2008, and is titled “Where in the World?”  The topic summary is as follows:  SQL Server 2008 introduces two new core data types for storage and manipulation of geospatial data. You can use SQL Server's built-in library of spatial operations to perform complex geographic calculations quickly and easily, and interface the spatial data types with front end mapping tools. The R2 release of SQL Server 2008 adds even more spatial data functionality to the Microsoft BI stack via Reporting Services improvements. In this session attendees will learn how to store, manipulate, and perform calculations with geospatial data in SQL Server to enrich their applications.

He also runs the NYC/North Jersey SQL Server Professionals on LinkedIN.

Finally, get ready to do some SQL squats. As a noncommissioned Army officer, Michael maintains his blog, on SQLBlog.com, appropriately called, Sergeant SQL.  (Get down and give me twenty!)  Here, as the headline says, you will get SQL Server development, news and information from the front lines.

You can reach out to Michael directly at admin@geocodenet.com.

I look forward to seeing him at the NJ SQL Saturday event, and appreciate his help in getting things together.  He will be giving away some of his great books, so don’t miss out.  And therefore, I am making Michael Coles, my MVP Profile of the Week!

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

Next, I will talk about my presentation topic, and some of the things I’m working on for the event.

If you are still interested in registering for this FREE one-day event, please hurry, space is limited and closing fast!  More details, including registration link at: http://njsql.org/Default.aspx