Orlando PASS Meeting

Tonight is the OPASS bi-monthly meeting.  Todd Holmes will be giving a mini presentation on SQL Server Backups and out main speaker will be Jorge Segarra (@SQLChicken) speaking on Policy Based Management.  Jorge is very involved in the community as a blogger, twitterer, and speaking at local user groups and SQLSaturday’s.  This will be my first time hearing Jorge speak, but I am sure it will go well as Jorge is knowledgeable and engaging.

Come on out.  Meeting starts at 6 and there is free pizza and always some type of SWAG.  We meet at End to End Training’s office in Altamonte Springs, FL, sorry that would be SQLShare’s offices (Map).

Thanks Space Coast SQL User Group

I want to thank the Space Coast User Group for having me over to speak on the Default Trace last night.  I had a great time meeting everyone and hopefully I presented some information that they all can take back to the office and use.

Space Coast is a relatively new user group but they have good core and are an enthusiastic group.  They asked good questions and 7 out of 9 (I think there were 9 I didn’t take attendance) attendees (not counting me) went to the after meeting get together at Holiday Inn.

The meeting started with some announcements and then I got to jump in and start my presentation.  I started by doing some marketing for SQLSaturday #21 – Orlando and the great seminar series scheduled the week before.  I then asked, “Before tonight, how many people knew that there is a trace running in SQL Server 2005/2008?”  Once again the majority were not even aware it existed.  We discussed what the Default Trace is, what it traces, where it is used, how to query it, and how to archive the data.  I went a little longer than an hour so I’ll have to trim it a little for SQLSaturday.  I’d probably grade myself a B-/B as I stumbled around as I changed applications to show code and do demos and had a couple of brain cramps.  I need to practice this one a few more times.  My slide deck and demo scripts are available here on SkyDrive and I have sent them to Bonnie Allard to post on the Space Coast SQL User Group web site so watch there as well.

We had some great discussions after the event about Powershell, hurricanes, software vendors, and the differences in diets around the world.

What happened to that email?

This question:

Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.
Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

asked in this thread on SQLServerCentral prompted me to do some research into Database Mail.  The result of the research is that there is no way to get this information from SQL Server.

Basically the way Database Mail/sp_send_dbmail works is that the message is placed in a Service Broker queue (sp_send_dbmail returns success), the external Database Mail executable reads the queue and sends the message to the designated SMTP mail server.  If the mail server accepts the message then Database Mail is done and the status is set to sent.  So, if you have an incorrect email address or the receiving server refuses it, SQL Server has no way to know.  In order to find this out you would need to use a valid Reply To or From email address and monitor that mailbox.

Here’s the query I use for checking Database Mail:

SELECT
    SEL.event_type,
    SEL.log_date,
    SEL.description,
    SF.mailitem_id,
    SF.recipients,
    SF.copy_recipients,
    SF.blind_copy_recipients,
    SF.subject,
    SF.body,
    SF.sent_status,
    SF.sent_date
FROM
    msdb.dbo.sysmail_faileditems AS SF JOIN
    msdb.dbo.sysmail_event_log AS SEL
        ON SF.mailitem_id = SEL.mailitem_id

Let me know if you have any better ways to find errors for Database Mail.

Speaking at Space Coast User Group

I have the privilege of presenting, Dive into the Default Trace, at the Space Coast User Group, tomorrow evening (Sept. 10). 

We’ll be discussing what the default trace is, what it collects, where' it is used, how to find it, and how to query it.  I have what I think are some interesting demos and hopefully information that will help developers and DBA’s better manage and audit their SQL Servers.

I’m really looking forward to meeting Bonnie Allard and the rest of the group.

Windows 7, UAC, and SQL Server

This is just a quick note, almost a continuation of my Access Denied, Not Possible post.  I have been working on some queries for a Default Trace presentation that I am preparing for the Space Coast User Group and SQLSaturday #21 – Orlando, and one of the queries has to do with trying to find logins that have gained access through a Windows Group.  Since I am working on my laptop (no domain), I decided to add the Builtin\Administrators group, delete my explicit login, and get access via the group.  Interestingly enough, in order to get access to SQL Server via Builtin\Administrators you need to run SSMS as Administrator.  Here’s the error I get when not running SSMS as administrator:

When I did run SSMS as administrator, I was able to successfully login to my local SQL Server.

No, I do not leave Builtin\Adminstrators as sysadmin on my servers and with SQL Server 2008, I do not have it at all.

Networking Successes

Over the last few weeks I’ve had several instances where I’ve had to learn new things and, in my struggles, have had the opportunity to get help from people I have met recently (both in person and on-line).  Notice I said “opportunity”.  One thing I’ve learned recently is that people like to help other people!   As part of my professional development I’ve been attempting to work on my networking skills, and, in my opinion, networking is more than meeting people, it is interacting with them to help and to be helped.

What the heck is jQuery?

The current project I am working on is using ASP.NET MVC and AJAX for the web site and my HTML and javascript skills are not strong so I was reading and workring with Professional ASP.NET MVC 1.0.  As I went through the examples I encountered a jQuery script that was not working.  I posted a question on Twitter which was answered by Jeremiah Peschka (Blog|Twitter).  He sent me his email address and offered to look at the script for me.  He also forwarded on the problem to a jQuery guru he knows.  All that effort and we’ve never met!  See people DO like to help!

How does this work in Powershell?

A few months ago I began interacting with Chad Miller (Blog|Twitter) on Twitter and was able to set him up to speak at my local user group (OPASS).  Chad is a Powershell guru and presented on T-SQL vs. Powershell back in July.  I’m working on a presentation about the Default Trace and I wanted to provide some examples of how to archive the Default Trace files/data.  This seemed like a good opportunity to learn some Powershell, so I sent Chad an email asking him to point me in the right direction, which he did.  I completed a “working” Powershell script and sent it to him for review.  He responded with explanations of what I had done wrong and a corrected script.

Why can’t I get this file processed?

Again as part of the Default Trace presentation I wanted to present a solution using SSIS.  Now I have some experience with SSIS and consider myself to be at an intermediate level so I figured I could get it done without trouble.  Well, I was wrong.  I had what I thought was a working solution, until I got Log_10.trc at the same time as Log_9.trc.  The ForEach File Enumerator orders files by name so the active Log_10.trc file was the first file the File System Task attempted to move and it is locked, thus the task failed.  So once I again I used Twitter to ask an SSIS guru, Andy Leonard (Blog|Twitter), if there was a way to change the sort order on the ForEach File Enumerator.  He said that you needed to script it, unfortunately.  He also emailed me an example script.

Those are just 3 instances where I’ve had the opportunity to truly practice networking (I blogged about another here).  Interacting with people and using those interactions to learn new skills and share your skills.  In my mind this is real networking.  Sure these are examples where I got something from my network, but there have been times where I’ve been on the other side, and you’d better believe if I can help out any of these guys I’ll do it!

24 Hours of PASS

From 7:45 pm (Eastern DST) on Tuesday, September 1st until 8:00 pm on Wednesday, September 2 PASS provided free online seminars each hour.  It was a veritable who’s who in SQL Server and a great preview of what’s to come at the PASS Summit in November.  Unlike Tom LaRock (aka SQLRockstar)  and Jonathan Kehayias I did not try to stay up and attend every session, I chose to cherry pick the sessions I would attend, none of which were in the middle of the night.  The sessions I did attend went really well with only 1 minor technical glitch during a session, which is very impressive when you think that every session I was in had at least 250 attendees.  There were some issues with errors in the links to the sessions on the 24 hours of PASS website, but Twitter definitely helped there.  Here are the sessions I attended with a few notes on what I picked up:

Session 1 – 10 Big Ideas in Database Design - Louis Davidson and Paul Nielsen

A big one for me here was that Classes <> Tables.  While ORM tools want to create a class for each table, this does not really work with a good relational design there really is not a one to one relationship there.  With a truly normalized database you will probably need to have a class that spans multiple tables. 

Session 3 - Team Management Fundamentals – Kevin Kline 

This was probably my favorite session.  I am not a manager and I really don’t want to be a manager, but I do want to understand how to manage and especially how to run meetings.  Kevin offered lots of great advice, but my one takeaway was that every meeting should end with an ACTION PLAN.  You should know what is going to happen because of this meeting and what tasks you are responsible for.  I think I heard this phrase at least 4 times in the hour.

Session 11 – Effective Indexing – Gail Shaw

This was at 6:00 am my time, and I’m not a morning person, but as a DBA/Developer I don’t think you can ever know enough about Indexing so I made a point of being up for this session.  Gail is also a friend on SQLServerCentral that I have learned a ton from there and from her blog so I knew it would be a good session.  Gail did a great job explaining how indexes work with equality and inequality operators, and how they work from left to right so you want your most selective column used in an equality operation first in your key list.  I used to make the mistake of putting bit columns, like an active flag, first because they are typically used in every query.  This is a bad choice because they are typically not very selective. 

Session 13 – Query Performance Tuning 101 – Grant Fritchey

Wow! If this was a 101 session I’d hate to be in 401 session with Grant!  Tons of good information about creating a baseline so you KNOW if you are having performance problems, what to look for, where to look, and the tools to use to look (PerfMon, Profiler, oops, sorry Grant, SQLTrace, DMV’s).  One thing that Grant mentioned as did Paul and Louis, “normalization is not evil”.  Meaning that a properly normalized database (~3rd normal form) usually does not need to be denormalized for performance reasons, if you have proper indexes.

Session 17 – Building a Better Blog – Steve Jones

Another very popular session, I guess because so many of us have blogs now.  Steve had some great tips about keeping your blog technical/professional and if you want to blog about personal things start another blog.  He did hit one hot button issue when he recommended hotlinking images instead of downloading and embedding in your blog.  He believes you should hotlink because that can protect you better from copyright violations, while others considering hotlinking to be bandwidth stealing from the hosting site.  I don’t use many images, although it is recommended so maybe I’ll start. 

A main point he made was to “Praise Publically, Criticize Privately”.  Basically don’t call someone out in your blog.  If you have an issue with someone keep it private.  Remember that your blog is public so current and prospective employers may see it.  This is really just a good piece of advice for every situation.  I did disagree a little when he said he does not comment on blog posts where he thinks there is an error, but rather contacts the author privately. I do tend to comment on blog posts where I think there is an error, but I try to do it constructively and provide solid reasons and examples for my opinion.

Session 21 – What’s Simple about Simple Recovery Model – Kalen Delaney

I can’t say that I’ve read all of Kalen’s books, but I have read a couple so I knew there’d be good information in this session and there was.  She really covered much more than the title implies.  She discussed how the transaction log works and how the different recovery models affect the transaction log.  Between sessions like this and Paul Randal’s blog I think I may eventually understand the transaction log.  The main point is that you need to carefully choose your recovery model and understand that the Simple Recovery model does NOT mean that the transaction log won’t grow, but it does mean that you do not (cannot) back up the transaction and CANNOT restore to a point time.

Overall, it was a great event (series of events?).  As I mentioned in my post, No Training Budget Still No Excuse, with events like these there really is no excuse for not taking time for professional development.  It’s YOUR career and YOU need to manage it.  Even if you had to choose 1 or 2 sessions that’s better than doing nothing.  It was also a great preview of the PASS Summit as all the speakers will be speaking there as well.