TJay Belt's Thoughts

SQL Saturday - Colorado Springs #104

2012-01-15T20:32:00.000-08:00

I had a great opportunity to go to a SQL Saturday event in Colorado Springs recently. It was my second time attending this event.

As a Regional Mentor for PASS, I have felt responsibility to contact and assist user group leaders in my area of stewardship. As I have contacted these individuals and fostered relationships with them I have become friends with many of them. Chris Shaw (blog | twitter) is one of these individuals that I have become friends with. Last year, Randy Knight (twitter|blog) and myself flew out from Utah to attend. The event was so much fun and we made so many great contacts that the next Utah SQL Saturday we had a large contingent from Colorado to attend our event. In response to this, I again felt the need to attend their event.

This time, instead of presenting during the regular SQL Saturday event, I had decided to try my hand at a Pre-Con session. I had discussed with Chris Shaw the possibility of combining our skills in presenting and dbaness to share a pre-con. It seemed like a good idea and I hoped that we would work well together. We had not really done anything together, so I was leery that we'd fit. I also didn't realize that when I asked him, he was the primary leader for the SQL Saturday event. With all that said, he agreed, and over the course of the months between SQL PASS Summit and the SQL Saturday event, we planed, schemed, and created presentations to present.

In an effort to sit down and do some final work on our session I asked if I could hang with Chris the night before the pre-con, staying at his house. This would simply afford us a lot more time to spend together as we prepared for the next day. Chris was a trooper and allowed me to hang with him, even picking me up at the airport in COS. I arrived a little after 3pm, and planned on spending the evening preparing for our presentation. What I had no idea that was going to occur was the quantity of stuff that needed to be done still for the event.

You see, Chris's wife, Gia Shaw(twitter), had been working like crazy to prepare for this event. Imagine your dining room in your house. A normal sized dining room, in a normal sized house. Now on the dining room table, picture boxes full of stuff. Now picture boxes full of stuff on the floor. In fact, picture boxes almost in every nook and cranny of your dining room. This is what Gia and Chris had produced in the past few weeks and days prior to the event. An incredible amount of stuff had been collected and needed to be transported to the event. AS soon as we got to Casa Shaw we started loading stuff up for a trip to Mr. Biggs event location to deposit items. We filled a vehicle up with gear. All in all, I believe 4 vehicles of stuff was transported to the event.

After dumping things off, we went to dinner nearby. My brother and his wife met us for dinner, along with Christina Leo (twitter|blog). We had a great dinner, actually being served dinner by a Tom LaRock (twitter|blog) look alike. After dinner and antics, Chris and I returned to his house and prepared for our pre-con. We ended up staying up later than we should have, but got enough sleep to get up early for the pre-con day of Friday.

The morning was without issue as we took loaded vehicles to the even location and got ready to present. We had to setup the projectors and get things situated to present, as attendees started showing up. And before we knew it, the event was underway and we were presenting on ourUtility Belt session. I felt that the session went well, lots of discussions occurred with the attendees. Happily, after the session was completed, I believe 5 different attendees handed us jump drives so that we could copy our slides, demos and templates for them. This always makes me happy to provide information as well as something tangible to assist folks in their day to day jobs. This is why those of us that present, present. To share. I love it.

I had no idea if I could make an entire day of presenting. Granted it was not a single pre-con where I talked the entire time myself, but it was not as hard as I thought it might be, and the time flew by. I was happy that I had enough information to share and had a great time doing it. I even heard from someone that heard from someone that it had gone well and they had enjoyed it. I'll take it. I love hearing that it went well.

After the pre-cons, we were able to make our way over to the speaker dinner just a bit north of the event location. Here we were able to see all the speakers, some sponsors, and many volunteers of the event. It is always a great time to spend with #SQLFamily prior to and after an event. This networking is always a great time and very profitable. Not always in the means of dollars, but profitable to your career. I had the unique experience of meeting one of my heroes, and was pleasantly surprised to have him introduce himself to me, eager to meet me as well. This always makes my day.

After the speaker dinner, my brother picked me up and took me to his house for the night. You can see that I try to spend as little as possible on these events. I try to scrimp where I can, so that I can attend more and more of these events. Bundling these events with seeing friends and family is a bonus. Seeing my brother and his wife and kids was a joy. We spent most of the time I was at his house wrestling and playing with cork pop guns. They were a blast to see and play with, and exhausting. We stayed up too late, as is normal for these events. We played. We talked. We enjoyed each other.

The next morning, Saturday, I got up early and took off, without seeing anyone from my brother's family. He lent me his truck to drive myself to the event. I made it a little later than I expected, but early enough to get some breakfast before the rocket of SQL Lurn took off. I love to volunteer for these events and learn as well as serve where I can. I was grabbed by Kieth Tate (twitter) grabbed me and asked for assistance as he presented. This was his first time presenting, and i was more than humbled to be asked to help. I sat in on his presentation and learned some things I took to my team to implement.

One of the favorite things with this event is the networking that occurs. Between certain sessions, games are played that thrust us attendees into situations where we must interact with each other. Bowling, pool, lasertag. You would think that these are simply fun activities, but they afford those that play a chance to interact with each other and build friendships, outside the normal SQL LURN. Its unique. Its a blast. It adds to the overall experience. I love it.

I volunteered to help run the SQL PASS desk, along with Sarah Strate (blog | twitter) and Jason Strate (blog | twitter), I helped out with lasertag. I helped organize the vendors end of day give-aways. I ran various errands. I learned from experts. I networked with friends and new friends. I discussed stuff with vendors. I made a great connection that may be monetarily profitable. I had a blast.

After the event we all retired to dinner at a nearby hotel. Some more final shmoozing and networking. Some great food. Great friends. Good times. I ended up leaving a bit early and heading over to another location with Red-Gaters Annabel Bradford (twitter), Max and Grant Fritchey (blog | twitter). Along with Tim Ford (blog | twitter). After some great food and talk, I retired early to visit with my brother and family again. I only had 1 more night in Colorado and wanted to spend as much time with them as I had with my SQL People.

The next day I flew home to Utah, after having spent the morning playing cork pop gun wars, shooting BB guns, tag with the kids and the dog, building a fire in the family room, taking pictures with the kids, making scrambled eggs, eating an incredible coffee cake, and a great talk with my bro about growing up and being a dad and all that that entails.

As usual, throwing myself into these events, and using as much time as possible to network, learn, and enjoy made for an incredible weekend. Its so full, so much fun, so quick, yet so full.

SQL Saturday. When are you attending your first? or your next? I can't wait for my next. I hope to see you there!

How long will this backup take?

2011-12-13T06:00:00.000-08:00

It's a Friday afternoon. You just did an oops and need to fix it. You think you need a backup. You know you need a backup, before you do anything. You quickly kick off a backup job and let it start performing the backup. Now, you know that this job has executed in the past, daily, probably. You can check the history of the job and get an idea of how long it will take.

The last execution was 1:10. A previous execution completed in :45. So you can safely guess that you are in for at least an hour of wait time before completion. This displeases you as you realize that it is already 3:30pm, on a Friday. The backup is already going, so it's a waiting game at this point. Wait, wait, wait.

Your boss suggested that you take off and get some away time, knowing that once the backup completes that there will be a hectic time performing the needed tasks to fix the oops. You consider leaving, after all, the backup will be running for an hour or so... Maybe sooner, maybe longer.

Since this isn't a backup from the UI, you do not have the handy dandy percentage showing up in the corner of the screen, giving you an idea for how long it may take. All you have is a small window saying that the job started, and the spiny guy showing you it's still going. And will complete. At some point.

So, how long will it really take? Only a time machine will truly tell you. But we can make logical guesses. A quick few clicks in Google/Bing would show you several scripts that could help. Here is the one that I found.

SELECT

    CONVERT(NVARCHAR(60),DB_NAME(database_id)) AS [database],
    CASE command
      WHEN 'BACKUP DATABASE' THEN 'DB'
      WHEN 'RESTORE DATABASE' THEN 'RESTORE'
      ELSE 'LOG BACKUP'
    END AS [type],
    start_time AS [started],
    DATEADD(mi,estimated_completion_time/60000,GETDATE()) AS [finishing],
    DATEDIFF(mi, start_time, (DATEADD(mi,estimated_completion_time/60000,GETDATE()))) - wait_time/60000 AS [mins left],
    DATEDIFF(mi, start_time, (DATEADD(mi,estimated_completion_time/60000,GETDATE()))) AS [total wait mins (est)],
    CONVERT(VARCHAR(5),CAST((percent_complete) AS DECIMAL (4,1))) AS [% complete],
    GETDATE() AS [current time]
  FROM sys.dm_exec_requests
  WHERE command IN ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE')

It simply queries the dm_exec_requests and looks for items that indicate a backup. The other fields will massage the resultant data into something that you can use to see how long it may take.

Once I executed this, I was sad, because it showed me over 2 hours to complete. The sad was the duration was longer than I had hoped. The good was that I now had a goal. A time when other tasks would start, after the backup completed. This allowed me a better chance to plan and prepare for those tasks.

Habit and Perception

2011-12-09T10:36:00.000-08:00

The other day I took a shower. I do this when I intend on meeting other humans during the day, and this was to be a day of meeting other humans, so a shower was necessary. When the shower was complete I grabbed my handy dandy bath towel and proceeded to dry off. However, this time, the towel had a tang to it, an oldness, an odor... This has happened to me before in the past, and I am not properly equipped to deal with this situation. You see the bath towel is a bit of a mystery to me. Its simply there for my use. And for a while it will remain there, a blue towel, hanging where I expect it to hang, standing at the ready. Then one day it will change to another colored towel. This towel is also a bath towel and serves the same purpose, and by all accounts in my mind, is the same towel, simply different. I often wonder where the blue one went. But I end up seeing it again, some other day. They come and go from my use with a similar mystery as other items in my life that vanish, only to appear later where I originally found them. It sure makes life exciting to not understand it all, to live and witness such magical behavior.

So, there I stand with my smelly bath towel, wondering what to do. The magic task of switching towels is so magical, I was not sure where to start. Where to look. Where to go. I secretly hoped that if I closed my eyes the magic towel switched to the new one, solving my problem. I was alone. No one I could ask. No help to be received. I was alone.

As I wondered around the room and adjoining rooms, searching for a towel, I walked past 2 towels that were guarding the corner of the bedroom. 1 towel was from a work we had this past summer. It was a nice blue hue with a company logo on the corner. It was fairly new. The other was from a racquetball tournament. The first was given at a pool party event, while the latter was given during a tourney. Both seemed to have a specific purpose. And neither of those purposed seemed to be for post shower use. I dismissed them. They were not the intended targets. I needed a bath towel. I never thought of using them.

As I wondered around the adjoining rooms and storage areas, I encountered a fair share of similar items, but no true bath towel. I could not seem to decipher the runes indicating the direction and storage of these magical devices. Their purpose understood, yet their hiding place eluded me. I returned to the bedroom disappointed in my efforts and more so with their result. It was then that my eye was drawn to the 2 other items, similar to the elusive magical bath towel. Could I use either one of these objects? One was intended to live in a gym bag and dry my body after a shower from a work out. That was fairly similar to my morning bath. The other was used to dry me after a pool adventure from a work party. Also intended to be a drying device.

By golly, I think I had a bold, new, and exciting idea. I could use one of these items to complete the task I had at hand. Maybe they were not intended for this exact purpose, but they could do in a pinch. They would suffice. They would server the purpose and actually accomplish the goal. My morning wasn't actually in ruins anymore. Life could proceed, tasks could be completed, and so on. Yeah.

(Yes, I have fun writing up these little dramas, these little glimpses into the brains attempt at making entertainment of the simple, the mundane, the commonplace. I enjoy the dichotomy of a few moments of thought elongated into a tale that seemingly takes much longer to complete.)

What's the point of this little tale? Its about my perception. My habits. I had fallen into a habit of waiting for someone else to take care of my bath towel situation. I had the perception that the other towels in this tale were not intended as bath towels, as I needed. How often do we do this in our work? How often do we forgo a solution that is compatible, only because it is not the solution we expect to be the correct one?

Do we only use sp_who to diagnose an issue? Why didn't we use the DMVs? Did we use the DMVs when an sp_who or sp_who2 would have sufficed?

Do we open up a profiler session and monitor our production boxes instead of crafting a trace that will do this more nimbly, with less impact? And should we use the DMVs to dig into what we are looking for and skip the profiler and the trace all together?

Do you excuse an error that you see because you have seen it over and over and over, and believe that you have a grasp on what it means, why it occurs, and simply do not deem it worth your while?

Does the report run quick enough that no one is complaining, and why should you deal with it now? It works fine, doesn't it?

There are probably more questions I could ask. I hope that you are actually thinking of your own situations now. Those items that you have taken as habit, or have carefully crafted a perception that you can accept, be it correct or erroneous.

Take a step back. Look at the situation. Is this a habit, a bad habit? Should you do this differently? Look at how you perceive the situation. Is your perception jaded, twisted, erroneous?

If so, please have the courage to take the time to remediate the situation. Fix it. Make it better. Make it faster. Do it the right way. Investigate what the right way is.

Putting on my first Pre-Con for #SQLSat104

2011-12-02T12:30:00.001-08:00

While at PASS 2011 I had the inspiration to discuss the possibility of combining my speaking efforts with those of Chris Shaw (blog | twitter) on utility databases. I have been speaking for a while in various venues and most of the topics I have covered seem to float around this idea of utilities that help me as a DBA perform my day to day job, with an emphasis on monitoring, collecting baseline information and reporting on said information. You can check out some of the past presentations I have done in this blog post.

I have seen Chris present on Utility Databases and was impressed at his approach and solution to the problems he has faced. I approached him and discussed us speaking together, combining our efforts (read:climbing on his coat tails and enjoying the ride). He was positive in the receipt of the idea. Various discussions have ensued since that day.

We have created, what we think, will be an enjoyable day of sharing of our experiences, actual code, actual data, actual demos and actual templates that you can use in your organization to solve some of the problems we all face as DBAs. There is no right or wrong way to perform these steps, and every implementation will vary based on your knowledge, skill, time and need. But these ideas will be things we have done, are doing, and continue to do to support our own organizations. We hope that they will help you out in yours as well.

Expanding on the idea of a Utility Database, we want to help you create a Utility Belt with tools you can use. Think of it like the Utility Belt that Batman wears. I would imagine there are some great gadgets in that belt that help in various situations. We hope to populate your Utility Belt with some of the tools we have learned and love to use ourselves.

We will start the day with some list of tasks to perform daily/weekly/monthly. Hoping that these ideas will get your brain juices flowing and thinking of how they may be implemented in your shop. We will discuss Chris's Utility Database, my Data File Size collection and reporting system, new techniques with SQL Server 2012 features, and even delve into documentation (a much needed, yet much ignored topic).

It's my hope that you will come away from this day long discussion with some tools that you can take home with you and use in your shop. Take them, tweak them, mash them into your square peg hole and make them work for you, just like Chris and I have done to gain this knowledge ourselves. We all end up learning from each other, sharing with each other and helping each other. Together we can make our jobs and our own effectiveness that much better.

Come see us. Come learn from us. Come share your experiences with us and teach us as well.

Homepage for the SQL Saturday Colorado Springs event is found here.

http://www.sqlsaturday.com/104/eventhome.aspx

Homepage for the pre-con we are putting on can be found here.

http://sqlsat104utilitybelt.eventbrite.com/

Indexes and Powershell

2011-11-29T12:47:00.000-08:00

I ran across a blog before the Thanksgiving Holiday that caught my eye. Its something Ive been intending to get into recently, but just didn't find the time. So on the slow day of Wednesday before the Thanksgiving holiday I took the time to dig into it and play with it in my system. It was a blog by SQLFool (Michelle Ufford | blog | twitter ) on how she deals with indexes. She basically has a proc that shows her Missing Indexes. She also uses Kimberly Tripp's (blog | twitter) sp_HelpIndex2 proc to dig into the actual table that says its missing indexes. The results of these 2 sets of information are manually processed and decisions are made about indexes.

Simple enough. You need the knowledge of how your systems is being accessed, coupled with the information these two procs provide and a little time, and you too can make informed decisions about your indexing needs.

I then coupled this information with a script from Pinal Dave (blog | twitter) that looks at unused indexes. I tweeked both these scripts from Pinal and Michelle to match the results I was looking for. Small tweeks, mind you. The result were 2 queries I could use to look into a particular database, see in a glance all missing and unused indexes. Using Kimberly's proc to review the table for existing indexes and combining this with the knowledge I had of our systems, I made choices and either added or removed indexes at will. I felt powerful. The power of the DBA coursing thru my veins...

< side note >

I want to take this chance to thank these folks for their work, their sharing and their knowledge. It's nice to stand on their shoulders, grab solutions that work, implement them, tweek them to my needs and system and topology, and devise a workable solution that fits my needs.

Thank You!!

< /side note >

After digging into several databases in several servers, I ended up on a particular database last night, just before I went home. While I was looking at this db one of our database developers called me with a complaint about a slow running report he was working on. After looking into it, locating the spid and digging around it to see what I could determine, we determined that it was indeed processing, it was indeed working, just very slowly. It had been running for an hour and a half so far, when he called me. My first thought was that I had caused the slowness with the index work I was performing. After disclosing this to him, and determining that it was not the case, we kept looking. At one point I decided to give up, remembering that I was just about to dig into this database in particular and look at its missing and unused indexes. I informed him we should do that now, instead of looking more.

He killed the report and we watched it complete. He could tell that it had processes somewhat, but was not finished yet. I then looked for missing indexes, and with his help, identified likely candidates that would affect his query in particular. We added a few indexes to these tables along with 2 other tables. I think in total we added 6 indexes only to this database. We then removed several indexes that were unused. After this work I suggested he kick off the report. Almost immediately we received an alert from SQLMonitor indicating that we had high processor utilization. This lasted for 3 minutes and levels returned to normal. The report was finished in 4 minutes. This report, remember, had been approaching 2 hours previously, and now completed in 4 minutes. We executed it again, and monitored again. Same results. Yeah!! Database Developer is happy. I am happy. I go home.

The next day I realize that this is valuable information and could help me historically, if I could collect it. I set out to do so, and if you are still reading, do not be disappointed. I succeeded, or I wouldn't be writing this blog about it, I'd still be working on it...

I converted the 2 queries I was using into powershell variables, and then I execute this query against each database I want too, against each database server I choose too. I collect this data, and store it in a staging table in my DatabaseMonitoring database. After it has all been processed I call a proc that sticks this collected data from the staging table into a historical table, adding an Identity ID and a datetime stamp. Now I know when the execution occurred, along with the information about the missing and unused index, per server, per database, per table, etc.

I did not get fancy with the powershell and do any looping or anything like that. The DBs that I look into are hard coded in the script. Someday, maybe, I can get back to the script and make it purty. But now, I'm done. The code is put into a job that will fire off periodically and collect this information historically. Now I just have to look at it periodically as well.

I want to take some time soon to pull this accumulated data into a report and add it to my 'Monitor Everything' report that sends me daily email statuses for all these processes I have added to our monitoring topology. Soon. After a few projects are complete, I will return.

a little powershell will do me good

2011-11-15T12:50:00.000-08:00

I had an issue today and needed to report versions of OS and SQL Server for a few machines to support. I was unsure of the OS versions of these machines. After asking my team for any documentation on said machines or what versions the OS was currently, I thought... Powershell can probably do this for me. Whip out my google-fu and a few minutes later I have a small working script I can execute against a box to determine the pieces of information I desired.

Simple.

get-wmiobject Win32_OperatingSystem -comp Server1 '

| select __Server,Caption,ServicePackMajorVersion

get-wmiobject Win32_OperatingSystem -comp Server2 '

| select __Server,Caption,ServicePackMajorVersion

Here is a sample of the Powershell script I used. It returns 3 columns. The Server Name, the OS version and the Service Pack. Simple little table of information.

Obviously you could add to this script to perform a loop from a list of servers. You could augment this to do many things. But for me, it was a simple solution to determining what the OS version was so that I could report this to the support staff I was dealing with. And whats better, I didn't have to bother any other humans for the needed information.

What have I done?

2011-11-14T21:16:00.000-08:00

I thought this was interesting when I compiled it recently for a consulting company that I am working with (SQLSolutionsGroup). Its basically a list of the speaking engagements I have performed as well as articles written. I enjoyed putting it together, as it represents rows of data that indicate an activity I performed on a certain date. Its a table of data. And since I had never really sat down to write it down, to form the data, I thought it was interesting. I plan on keeping track of this and adding to it.

So, look, if you care too. This is mainly for me. I enjoy it.

Speaking Engagements

User Groups

Utah User Groups June 2005 Reporting Services

Utah User Groups August 2005 Profiler / Trace

Utah User Groups February 2006 Sarbanes-Oxley

Utah User Groups January 2008 Query Processing

Salt Lake City User Group 2004-2008 various topics

Utah County User Group 2006-2008 various topics

Code Camps

Salt Lake City Code Camp October 2006 Profiler / Trace

Salt Lake City Code Camp April 2008 Query Processing

Salt Lake City Code Camp November 2008 Query Processing

Miscellaneous

Microsoft 2006 Analysis Services

ACS 2006 Sarbanes-Oxley

PASS SIG 2007 Some topic

SQL Server World Wide User Group Virtual Conference

SSWUG vConference April 2009 Auditing your users

SSWUG vConference April 2009 Best Practices

SSWUG vConference April 2009 Job Execution System

SSWUG vConference April 2009 Monitor Index Fragmentation

SSWUG vConference April 2010 Configuring and Managing GoldenGate

SSWUG vConference April 2010 Database Switch

SSWUG vConference April 2010 SQL Server Profiler and Tracing

SSWUG vConference October 2010 Database Change Process

SSWUG vConference October 2010 On Call Duties

SSWUG vConference October 2010 Professional Development

SSWUG vConference October 2010 SQL Server Logical Query Processing

SSWUG vConference April 2011 Data File Sizes

SSWUG vConference April 2011 Documentation – you know you love it

SSWUG vConference April 2011 Server Profiler and Tracing, a “How To”

SSWUG vConference April 2011 Stand-alone DBA dailyweeklymonthly

SQL Saturday

SQL Saturday 54, SLC October 2010 Database Switch

SQL Saturday 54, SLC October 2010 SQL Server Profiler and Tracing

SQL Saturday 66, Colorado February 2011 Documentation – you know you love it

SQL Saturday 94, SLC October 2011 Data File Sizes

SQL Saturday 94, SLC October 2011 Documentation – you know you love it

SQL Saturday 97, Austin October 2011 Data File Sizes

SQL Saturday 104, Colorado Springs January 2012 Utility Belt Pre-Con

Articles

SQLServerCentral.com

From the Real World : On Call Again – 04 Apr 2009

On-Call Duties – 27 Oct 2008

Audit Database Changes in the Real World – 11 June 2008

A Simple DR Solution – 31 July 2008

Job Execution System – 27 Mar 2008

A Failed Jobs Monitoring System – 4 Sept 2009

Simple-Talk.com

Can Twitter Help your Professional Development? - 16 Dec 2008

SQLServerPerformance.com

Monitoring Index Fragmentation - 14 Nov 2008

So, you find yourself On-Call - 22 Aug 2008

Automate Audit Requests - 26 May 2008

Audit Data Modifications - 15 Apr 2008

Meme Monday: SQL Family

2011-11-07T07:58:00.000-08:00

Tom LaRock’s (blog|twitter) question this month: What does #sqlfamily mean to me?

When I think of Family, I think of my parents, my brothers and sister. I think back to the beginning when I was younger and we all hung out together. I remember the fun things we did together. I recall the times that they helped me or that I helped them. I know that there were bad times. But i also know and remember the good times. If I haven't seen my brother for years, when I do, it always begins with an embrace, a recounting of stories, a telling of my current history. We recall the good things. And push out the bad things, because after all, we are all Family. That trumps all. Family.

Now as I think about my #SQLFamily, I think the same things. Some of the members of this family have been around for a long time and there is a long history of good memories to draw from. Let's recount those. Let's relish in those. Let's repeat those by helping each other out when we need it most. When I wonder around the virtual house we all inhabit late at night, worried about something, I know that one of my brothers or sisters will be there, they will see my hurt, and they will ask what they can do to help out. Just like my family would do. When I meet a member of my #SQLFamily virtually for months or even years in the intertubes we occupy, our relationship can still be formed and solidified even though no physical presence between us exists. But on that fateful day when we actually meet, you can bet there will be a joyous embracing and retelling of stories that made us a better person for having interacted. I can recall almost each and every instance when I finally met a member of my extended #SQLFamily for the first time, and felt that tug of kinship, that knowingness of shared experiences, that pull of another family member you have forged a relationship with. I can feel that urge to help and be helped and better ourselves through our interactions.

Just like a family that loves each other, regardless of the goings on, regardless of the silliness we all experience from time to time, our Family and #SQLFamily will always be there for us. We will be there for them. And they for us. Always.

This is what Family means to me. This is what #SQLFamily means to me.

Yeah for SQL Monitor, Boo for SQL Monitor

2011-10-24T12:22:00.000-07:00

Over the weekend, starting on Wednesday evening, I was on vacation. In Utah there is a school break in the Spring and Fall, sometimes with the kids having an entire week off. I took Thursday and Friday off, and we headed south to visit Zion National Park. We had a glorious time. Internet connectivity was via WiFi while we were in the campground only. Our cell phones stopped receiving cell signal about 40 minutes from the National Park, and remained eerily quiet for the duration of the weekend. This is a blessing and a curse. Knowing that things may go awry and I may need to pop into my databases always leaves me a bit uneasy. So, as often as I could, I connected to the WiFi and checked my work mail. A few times a day, usually in the morning and evenings.

On Sunday morning I was able to connect and to my surprise, SQL Monitor had captured a slew of error messages. 10 or 12 of them in a row, all occurring since midnight. My heart skipped a beat. What was going on... some digging ensued.

I feel so luck to have SQL monitor watching my systems, and emailing myself, along with 2 others in the IT department. If something awry occurred, it would let us know, and if I didn't jump on it, at least the other 2 humans would know via email something was up. So, something was up. I found it, and was investigating. Thanks SQL Monitor for watching my DBs while I climbed to Angels Landing in Zion National Park.

What was the error that SQL Monitor was complaining to me about? A nice generic one. 'SQL Server error log entry'. This means that something happened, was logged into the error log, and, well, that's it. Just that that think happened. If you are like me, your heart starts skipping a few more beats. As I looked at each alert down the chain, they were all the same. What I didnt notice at this juncture was that each alert was coming to me via email roughly and an hour interval. This is valuable to know, as it wasn't 12 alerts about 12 error log entries. It was an alert about an error log entry, and it was being repeated every hour. Much different story. So i dig deeper. To do this I need to drill into the alert itself and look at more details. Now I learn the following

The Database ID 6, Page (1:118112), slot 0 for LOB data type node does not exist.

The suggestion was to run dbcc CheckTable on the offending object. I need to know what database that is. Database ID 6. Is it one of my important databases? or a lesser important one? or a supplemental one. Which one is it... aaarrrggghhhh, I NEED TO KNOW!!! Since I had been out in the mountains, it seemed like my TSQL-fu was lacking and it did take me a minute to remember I could query sys.databases to see which one was Database ID 6. This was probably a few seconds, but in the panic of digging, it always seems longer. So, Database ID 6 turns out to be the database RedGateMonitor. Whew. Its only that db that is having an issue. A couple of quick DBCC CheckDB commands later I realize that all other DBs are in proper order. At least at the instance that I ran the CheckDB command. (hehe). So, I run on the RedGateMonitor and it encountered an object that has some issues. It is the object 'data.Cluster_SqlServer_SqlProcess_UnstableSamples'. I have no idea what this is, or if its needed, or what I should do with it. But, since its RedGateMonitor itself that has an issue, I figure I have some time.

Yeah for SQL Monitor for finding an issue on my DBs. Boo for SQLMonitor for its own object becoming funked up.

So, as you picture this, picture me on the top bunk of our camper, in my jammies (the desired PG rating refused to allow me to let you picture me in my undies), with a laptop on my lap, hooked into the RV campground WiFi, hunting down this issue. I am much more calm now that I know what db and what object is causing the issue. I quickly pen an email to my crew letting them know that I have discovered the issue, and a fix will come at some point, but until such time, we will continue to receive emails about every hour reminding us. Luckily its the weekend, and they can be ignored. My next step was to tweet my question about this object to #sqlmonitor and #kickasssupport. Knowing that someone will see it, and we'll get going on a solution soon. Maybe on Monday, maybe before.

Sure enough, I come in to work on Monday and see a tweet from @JowleyMonster with suggestions on how to remediate my issue. After a few attempts at recovering the data in the table via backups, I resorted to simply running a DBCC CheckDB with repair_allow_data_loss. I had to put the DB into SINGLE_USER mode first. Then run the DBCC CheckDB command. Then continue to verify via DBCC CheckTable on the particular object in question, followed by a larger DBCC CheckDB on the entire DB. When it all seemed OK, I returned the DB to MULTI_USER.

Now, you may be screaming at the screen now that I simply horked the connections to SQL Monitor, and did so in a graceful manner. I thought the same thing, and honestly wanted to try it out. I have been being so careful with it with other operations, I wanted to see what happened. In other instances I will actually stop monitoring everything, then have someone actually shut down the service, and then verify that all connections from SQLMonitor had terminated. But this time, I was curious.

It handled itself perfectly. Obviously the webpage that I had up throughout this ordeal was in a funked state. With a refresh, a few moments of uncertainty and stress on my part, it refreshed just fine. All was back to normal.

I am now free to pursue other tasks, as I leave the little gremlins that are SQLMonitor to do their job watching my DBs and letting me know when something is awry. It will happen. They will let me know. And I will dig in and fix them. I love having them around watching, especially when I am not.

FirstTimers Cheat Sheet 3 : More Humans to Hunt

2011-10-05T12:23:00.001-07:00

In an effort to not leave anyone out from one of my previous blogs where I went into considerable detail on some 20 individuals, I have a larger list of humans to hunt while at #SQLPASS next week. Now, in case some of you complain, this list is not comprehensive either. But is an attempt to list out those humans that have interacted with me to a degree that they stood out and i listed them. This is in no way a complete list of the people you should hunt.

So, onto the list. Add these fine folks to your network. Learn from them. Each one has a certain set of skills that help them in their daily work and personal lives. I bet if you try, you will find something that you can learn from them as I have. The best part is that they too are willing to share this information with you. So we can all become better. All of us.

kevin kline

adam mechanic

jonathan kehayias

joe webb

aaron bertrand

jessica moss

randy knight

geoff hitten

jes borland

lori edwards

jeramiah peshka

alan hirt

lous davidson

neil hambly

karla landrum

rodney landrum

meredith ryan-smith

ted krueger

ben miller

midnightdbas

rob farley

paul white

mladen prajdic

tim mitchell

jack corbett

phillip beazley

argenis fernandez

erin stellato

kendal van dyke

kathi kellenberger

scott stauffer

brian knight

simon sabin

tara kizer

robert davis

merrill aldrich

jorge segarra

bill fellows

doug lane

joseph richberg

peter shire

tim ford

Since the goal of the Summit, in my opinion is to Learn and Network, this is the Network portion, which I have relabeled Hunt. Now we have LURN / HUNT as the two goals of the summit.

As with the previous list, I challenge you to find these folks. Meet them. Add them to your network. If someone can meet all these, as with the previous list, and bring me proof, you will win a prize. Some of these fine folks I have yet to meet IRL and hope to do so this summit myself.

When you are not LURNing, happy HUNTing.

FirstTimers Cheat Sheet 2 : Adventurous Version : Cards

2011-10-04T23:15:00.000-07:00

I was challenged to create cards to match my words. Similar to those used to track down various individuals in Iraq. Allowing you at home to print up said cards and hunt down said individuals.

http://dl.dropbox.com/u/11735707/2001%20Summit%20Pack%20of%20Cards.pdf

Go get them. Print them. Hunt them. The first one to meet them all, who didn't know them all, gets a prize from me.

FirstTimers Cheat Sheet 2 : Adventurous Version

2011-10-04T12:31:00.000-07:00

I recently gave you some ideas of some good things to do while at the PASS Summit next week.

Now let's get a little more adventurous. I want you to hunt down some specific people. Keep in mind that I love each and every one of these individuals as a brother or sister. And though I may be asking you to do something that would appear to not be in your best interest, I promise that you will have a good time doing it, and the individual you meet should be nice to you in return, i think.

Ok, here we go. Things to do when you find these people while at the summit.

[EDIT : if you do not appear in this list, do not fret. I can only fit so many per blog. SQLFamily is huge. More blogs to come]

Try to get a run in with Steve Jones from SQL Server Central. This man is an inspiration to so many for his daily running routine. Even if you see him in the elevator while going to run will be an inspiration. Ask him how much he likes shoveling hay. If you dont have guts enough to do this, simply thank him for being the godfather of this SQL community we now enjoy. If you have the time, listen and learn. He can teach you so much about so much. As those of us that have learned from him for many many years can attest.

Challenge Grant Fritchey to an arm wrestle. Or better, tell him you can chop wood faster than he can. Actually, anything you can challenge him to in his presence will impress me, as he has a stern persona at first glance, but is one of the nicest people you will meet at the summit. Ask him about Azure, about performance, about anything, and be prepared to learn.

Purchase a cup of coffee and deliver it to Buck Woody. Do this and you may make a friend for life. I dare you to ask him if he likes tea. Any kind of tea. If you cannot find him while at the summit, keep your eyes peeled for a blur in the hallways, that's probably him simply moving faster than the naked eye can detect as he is amped up on caffeine. 10 minutes learning from him will keep you busy for months, if not years.

When you run into Paul Randal, challenge him to a game of Blokus, hinting that you could probably kick his butt. This will prove to be a fun conversation, if you survive the first accusation of superiority. My first time meeting him is so ingrained in my memory, I will always cherish it. This man will impress you from the get go, and you will be surprised when he speaks that his accent doesn't quite match the one you had in your head all these years as you read his blog. Remember that he wrote most of what you use today in SQL Server. Be prepared to learn from him. Listen raptly.

If you are lucky you will meet Kimberly Tripp soon after you meet Paul Randal. She will smile at you and make you feel welcome. As if you are actually of worth. Realizing that you are in the midst of SQL Server royalty, open yourself up to what she can share with you. Amazed you will be.

If you want to meet the nicest guy at the Summit, then you need to hunt down my brother from another mother, Andy Leonard, and become his brother thru conversation. He is so knowledgeable and so kind, you will simply be blown away at how much you learn from him while listening to him speak. If you don't feel like giving a bear hug at the end of this encounter, you may have done it wrong. He is the best.

If you like lists like I do, then you mush try to find Brad McGehee and learn from him how to keep track of and take care of your database servers. Ask him about Hawaii. He lived there. This alone is an amazing treat, in my mind. One of the nicest people you will meet is Brad. Learn from him. Let him teach you. You will be amazed.

Once you run into Tom LaRock, ensure that you ask him about his favorite sausage. Go on and on about how much you love sausage. Or is it bacon. I cant remember... find out for yourself. After giving him a hard time, sit back and let him teach you about the community. About how we need to help each other. About how we can do a better job. Soon you will find yourself volunteering to help him on whichever of the myriad endeavors he is embarking upon to make us all better people, in spite of ourselves. He will make you a better person simply by being around him.

I'm a sucker for accents, and have always loved to listen to Gail Shaw. You will too. Be prepared to ask a simple performance question of her, if you can find her, and then record the answer. Type it up and read it over and over and be amazed at how much knowledge you will be innovated with in a short period from her. If you could extract a small ounce of her knowledge, it would probably more than double what you already have.

If you are looking for a quite rock of knowledge that will thoroughly impress you and impress upon you that you need to be a better person, seek out Andy Warren. His quiet way will shock you into the reality of continuous improvement. Listen to him when he talks, and how he talks, and watch others learn from him. He is a giant of a man and you should be humbled to simply learn as much as you can from him.

When you meet Denny Cherry, be ready to be cussed at and maybe even yelled at, but come out of the event knowing so much more about storage than you ever thought your little brain could handle. He is not for the faint of heart, but has such a store of knowledge that you would be amiss if you skipped this opportunity to learn from him.

If you see a giant (literal) of a man wondering around with a camera, stop and say HI to Pat Wright. He too has a quiet way of pushing you out of your comfort zone and into the community and then letting you do what you will within it. But you are a member of it because of him. How many of us can say that? I know I am a member of the community solely because of his influence. Tell him HI from me when you do see him. I owe him sooo much.

If you get a chance to hunt down the elusive Brent Ozar, do so. At first I thought he was a fictitious bot generated from Quest on twitter, but he turned out to be real. A real boy with a vast collection of knowledge. And given the chance, he will share it all with you. You will not be able to take it all in at once, so drink from this fountain only as much as you can take it. He will teach you so much.

If you run into Aaron Nelson, ask him if he's ever heard of this new language called PowerShell. Tell him you think its a fad. Then if he is still talking to you, ask him to help you with a script. He probably has it on him, and can give you a copy. And you will be opened up into the world of Powershell that you never knew existed. Anything you can think of, he's probably already done it and will share it with you.

When you see Allen Kinsel, which you will. because he is everywhere, simply run up and give him a hug. He may resist. But while hugging him tell him how thankful you are that he made the PASS Summit work. None of us will ever know how much he puts into this event. We all ose him so much. After thanking him, tell him how sorry you are that he still has copies of SQL 6.5 in production. Offer him your condolences.

If you happen to see a DBA that looks like a criminal and causes you to pause, possibly switching to the other side of the hallway, this may be Chris Shaw. Don't avoid him. His shaven head and stern face is covering a plethora of Administrative knowledge that you cannot afford to pass up. He will give you his shirt off his back, if he could. Another one of the nicest people you will meet with a head for Data that will amaze you. Again, learn from him. Learn. Soak it in.

I could go on with more, and hope to continue this list as the Summit grows closer. This is a list of people I think you should meet. I have met them. I love them. I learn from them. I am amazed to be in the same community as they. They are SQLFamily. You should take the time to meet and add them to your network. Become part of the SQLFamily.

A Post just for me

2011-10-04T12:29:00.000-07:00

These photos are from the Austin SQL Saturday event. I just wanted to grab them so I oculd show my family.

http://www.flickr.com/photos/sqlserverio/6211465364/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210954761/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210955225/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210958065/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210959395/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211472002/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210960329/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210995959/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211522598/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211531668/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211542400/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211031085/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211031599/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211543762/in/set-72157627692791739/

FirstTimers Cheat Sheet

2011-10-03T08:58:00.000-07:00

FirstTimers Cheat Sheet

Let me start by saying that I have been there. Standing in a room or strangers, wanting to reach out to them, add them to my network, but unable to do so. Why? Why couldn’t I? Well, that would take way too long to blog about. So let’s just skip to the how.

Let’s start over. Why do you go to PASS Summit? To Learn and To Network. Learning is easy. Attend at least 1 session while at the summit and I guarantee that you will learn something. It’s the second part that causes some of us to stumble.

Let’s break down networking. My own definition is to add individuals to my network of individuals that can help me in my day job, my tasks. Notice that I said add individual(s). This entails more than one. Multiples. Many. You get the picture. People. Humans like you.

How can we do this quickly and easily? I’ll show you 3 easy steps with several ground rules associated with these steps. First, back to the definition. Adding individuals to your network. These will be bonds that you create with another human, adding them to your toolbox of skills to accomplish your tasks. This is a selfish goal. But to accomplish this, you must be unselfish. You must reach out to them and be interested in them. Some people you may have very little in common, while you will find others that you have many points of interest connected. This is ok. They all go into your networking bucket. All. Stick them all in there.

Ground rules

For the next 3 steps, I want the following rules to apply. Do not talk about yourself, unless asked. Do not dismiss the individual during any of the steps. In between steps you may have to break the contact. This is OK. Contact can be reestablished later. Learn how to be genuinely interested in their responses to your 3 steps. I didn’t say pretend. Actually become interested. This is part of the unselfishness on your part. If all that happens is that you learn about them, then the goal has been accomplished. You may say to yourself that they know nothing about you. That’s ok. The goal here is for you to add them to ‘your’ network. Selfish goal, but accomplished by unselfish means. Become interested in each response. Become enthralled in what makes them human and a database professional and everything in between.

Steps

What I want you to do here is to memorize these steps. If you cannot do that, then write them down on a cheat sheet. I want you to ask the other humans the following questions.

· Who
· Where
· What

When do you ask the other humans these questions? Whenever you find yourself near an individual for more than 10 seconds, turn to them, look them in the eye and ask them these questions. Realize that there are 3 and that a break in contact may occur in between any of these questions, and that is ok. Let it happen organically. But these are the questions to ask. Remember to be interested in their responses, and do not simply wait for a break to talk about yourself. In fact, do not talk about yourself, unless asked.

Who

Look them in the eye and ask them ‘Who are you’? Do not look at their name badge. This only tell them their name. You may forget this anyway. Try not to. But this is not what you are asking. You want to know who this person is. Let them tell you, in their own words. This is a nice simple ice breaker. It’s like asking a date out for a drink instead of dinner. Thus allowing an out if it’s not working out and they are trapped in a longer event than planned for (dinner). This is short and sweet. ‘Who are you’? It may sound brusk and you may alter it as needed to fit your personality. Point is that you are engaging them, and asking about them. DO NOT TALK ABOUT YOURSELF, unless asked. Remember that you are adding them to your network. They may not be adding you back. That’s ok. It may happen, it may not. It’s OK. Remember to be interested in them. ‘Who’ they are. Learn from them. Let them go on and on about themselves. I bet you will find that they are experts in talking about themselves. It’s one of my favorite topics.

Where

This digs a little deeper and can be several questions, depending on how it’s going. Where do you live? Where are you from? Where do you work? Each of these questions opens up a plethora of responses and follow up questions. As organically as possible, let these happen on their own. This section of steps can become rather large. Be ready to take the time and be interested. Remember that you are still forbidden from adding your own information at this point, unless asked. (I bet you will be asked and this will allow the other individual a chance to find out the same things as you are finding out, and thus add you to their network as you are adding them to your own). As you find out another piece of info, try to dig a little deeper, or make a connection.

Before I go on to the last step, realize that we have said nothing really about databases, our skills, our goals, our talents, our favorite or least favorite piece of the database world. We are getting to know the human. Remember that they are 1^st a human like you, second an IT professional, and third a database professional. There are many things you can have in common before you actually reach the database professional stage of information gathering. Hopefully before this stage even occurs you have forged enough bonds with this person, built up a relationship of trust and added them to your network, regardless of whether they are in BI, DBA, Development, Contracting, etc.

What

This question gets more into the nitty gritty and you start seeing where you overlap in skills and talents. Some sample questions for this stage are ‘What do you do?’, ‘What brings you to the Summit?’, ‘What do you hope to gain out of this Summit?’, ‘What is your favorite part of SQL Server?’, ‘What are you good at?’, ‘What are you not so good at?’, ‘What do you hope to be better at?’, and so on. Add here as you see fit. But the point of this section is to learn more about them. As I have said before, you are here to learn about them. Where possible, refrain from telling your own tales and wowing them. Let them ask you. It will happen. It’s human nature. But by you showing a genuine interest in their skills, in them, you forge that bond that will elevate this person into your upper echelon of helpful networked individuals.

Once you have exhausted these initial questions, realize that that may be the end of this interaction. You may or may not have made a friend for life. Time will tell. But it is important that you break away before it becomes awkward. Let this happen organically, naturally. Remember to not monopolize their time. Selfishly, you are here to form bonds with as many people as possible; adding each one to your network. Sticking it out beyond usefulness does neither of you any good.

Let’s Pretend

You find yourself standing next to me in a hall. You turn to me, without looking at my badge, and ask me ‘Who are you?’ I will probably grab my badge to double check the name, and then tell you my name. I will then probably do the same to you, asking your name, while still looking you in the eye. My eye will then wonder to your name badge to see it visually, as I attempt to burn your name and face into my memory. Sometimes this burning succeeds, other times it does not. But I will always remember this interaction with you.

Next you ask me ‘Where do you come from?’ For me, this answer requires a bit of time. I come from California. That’s where I was born and raised. It’s part of who I am so I will answer with this bit of information. But I will also tell you that I am from Utah, as this is there I live now. With this information you have a plethora of follow up questions. You can ask me about where in California I grew up, why I left, if I know Fresno, where your uncle lives and you visited once. I will tell you that I do know Fresno, and have visited friends of our family many times. In fact, they live in Clovis, nearby to Fresno. Since I opened up more information about Fresno, feel free to dig into this. At this point you realize that you know someone that lives in Clovis, and ask me if I knew them. Why not? I may know them. You’d be surprised. Next you may ask me about my living in Utah. Here you can be adventurous and ask me if I am ‘Mormon’, or even more adventurous and ask me how many wives I have. This will lead to some interesting discussions. Maybe you only want to ask me if I have visited Lake Powell or Zion National Park, or if I snow ski? Realize that you have not said you do any of these things. You are asking me about me. You may want to ask ‘Where do you work?’ This too will open up a plethora of follow up questions. See the pattern here? You are not talking about you. You are asking about me. Letting me go on and on about me. It’s one of my favorite topics.

You get the idea. Start small. Grow organically. Let the networking happen easily and efficiently. Stick to these simple questions and branch out when you feel comfortable. Learn about them. Add them to your network. Remember this interaction. Make as many of these as you can.

When you find yourself having 10 seconds near another human, lean over, look them in the eye, and then ask them Who, Where and What. You may have just found a friend for life. At the least, you may forge a bond with another human that can be added to your network. Goal accomplished.

My experience with the RedGate SQL Azure Backup tool

2011-09-22T11:13:00.000-07:00

Success!!

I should end this blog post here. You should simply be happy that it worked. I got it to work. Woohoo!!

But, alas, I write here to help me. And you. So, on with the explanation.

Last night, I had some issues with the command line tool seemingly doing nothing as I submitted to it various commands that I knew worked elsewhere. Manually I could get it to work just fine. Thru powershell or SSM via xp_cmdshell, I could not.

After various discussions with Grant Fritchey (t|b) and others at RedGate, Grant asked the dumb question that needed to be asked. "What version are you on? I'm on version X". While I thought this was silly, as did he, that was the solution. The version that I had simply wasn't working like we both expected. Upgrade, and viola! success.

So here is what I did. On my local machine, I created 1 SQL Agent Job was created with 3 steps. This job will reach out to the cloud, grab the database I want to backup, and bring it down to my box for safe keeping.

Step 1 checks if the database exists, and drops it if it does. Here is the code:

IF EXISTS ( SELECT * FROM master.sys.databases WHERE name = 'MyLocalDatabase')
DROP DATABASE [MyLocalDatabase]

Step 2 calls the Command Line utility for SQL Azure Backup. Here is the code:

EXEC sys.xp_cmdshell 'C:\redgate.sqlazurebackupcommandline.exe
/as:MyDatabase.Database.Windows.Net
/ad:MyDatabase
/au:MyUser
/ap:MyPassword
/ls:MyLocalMachine
/ld:MyLocalDatabase'

Step 3 calls the Command Line utility for SQL Azure Backup. Here is the code:

IF EXISTS ( SELECT * FROM master.sys.databases WHERE name = 'MyLocalDatabase')
SELECT 'Database exists'
ELSE
BEGIN
SELECT 'Database does not exist'
RAISERROR( 'Database does not exist', 16, 1)
end

Nice and simple. This job will backup my Azure database to a local copy.

You may be asking yourself 'Why?'. Since Azure has redundant copies of my database already, why do I need to make a backup at all? The scenario in which this task becomes important to me is within the scope of Release Management. As we are uploading new features to our Azure hosted solution, we inevitably need to make database changes. In order to ensure continuity of service and function, I ALWAYS enforce a process of backup/snapshot/something of the existing database, prior to and after changes. This extra step will save me time if we ever need to revert back to another version of the database, even an Azure database.

My basic release process is like this.

Become involved in the Release as soon as possible to assist with architecture
Review proposed DB changes
Produce Release Plan of all steps necessary to perform release
Review Release Plan with all necessary folks
Ratify Release Plan as a group
Test Release Plan in environment that mimics target environment
Prior to actual release, take a Snapshot and/or Backup of target database
Perform Release according to Release Plan
After actual release, take a Snapshot and/or Backup of target database
Compare Pre and Post Snapshots and/or Backups
Resume normal life

In the normal DB world (not with my head in the Clouds) I have various tools and processes that have helped me along this path to a very successful release process. Granted the process may not fit in every situation and needs to be flexible, for the most part I follow it to a T. Once a process has been established, and a set of tasks can occur to fulfill that process, automation is my next step. Remove the human factor from the steps to ensure success. Remove me from remembering what steps i need to take to accomplish the task. Automate!!

Introduce the Cloud, and the inability to perform some of these tasks and I get freaked out a bit. Luckily, designing solutions is something I enjoy doing, and this was a fun one to work on. I now have an automated task that I can click once, and perform the backup I need, allowing me to continue with my Release Process.

My thanks to those that helped me. I hope that this may help you as well. It has helped me. I now have a repeatable task that I can kick off and not worry that Ive forgotten any piece of the puzzle. Next release to the Cloud (next friday) we'll use this little piece of the repeatable puzzle. Soon, a monkey could perform the steps for us. And yes, I may be that monkey.

My experience with the RedGate SQL Azure Backup tool

2011-09-21T16:33:00.000-07:00

We have a few databases that are in SQL Azure. We need to back them up from time to time. Since there isn't an easy way to do this from SQL Azure itself, we have started using RedGate's SQL Azure Backup tool. The UI works great, even though the little slidey effect gets old after the first few executions. I was able to quickly and easily connect and backup my databases from the cloud to my local machine. Backup!!

The next time I went to backup, it errored, because the database already existed. I figured I could easily fix this with a quick rename of the existing database. But that was not the case. Since the backup was actually creating files based on the internal names and that DB already existed. I had to drop the database. I could have done something else, like backup the local, and restore it to keep a historical copy. But I simply dropped the last backed up db. Then the UI was able to backup without any issues.

Next? Well, why not try the command line. If you are like me, it will take you several (read many) iterations to get a command line anything to work properly. After quite a few iterations of stupid mistakes on my part, I finally got a simple command line script that would work and backup my db. Yeah!!

So, now I have a process. I drop the database. Then I run the command line and I have my database backed up. Yeah. Except for the parts where I have to do all those steps, over and over... that part sucks.

So I start thinking. I see these as 2 steps. 1 step to drop the db could be put into a SQL Agent job. The second step of running the command line, I could put into a batch file and execute via SQL via another step in the SQL Agent job. Yeah.

Boo.

The drop step works just fine. But the second step does not work. I have a batch file that I can execute and watch it process the backup successfully. But when this is wrapped in a SQL Agent job, it does nothing. So I grab the xp_cmdshell (I know, I know) statement and execute this in SSMS, it seems to take some time, then returns nothing. And apparently nothing has transpired either. No database was created. Nothing. No error. Hum....

I tried Powershell to no avail also.

So at this point, I have a job that kicks off both steps. Both succeed, but only the database is dropped. I will then manually kick off the batch file and backup the database via the RedGate tool command line.

It works. Its 2 steps. Its not automateable yet. But I am closer. Always getting closer.

Soon, I suspect, I will figure out what I did horribly wrong and remediate it, and I will have an automateable solution that will backup my Azure database to my local machine. At least I have that backup. And this will make me happier when we release code to the Azure database.

Backups, snapshots and a budding release process to the Cloud. We're getting closer.

Now I want to be more efficient.

I don't know anyone, I don't sing karaoke, I don't drink. Can I still have fun at SQLPASS? addendum

2011-09-08T23:01:00.000-07:00

I wrote this blog post the other day about SQL PASS and how to experience it and ensure that you experience it all. Break out of your shell and soak it all in.

But I forgot to mention a few things, and I hope to clear these up in this post.

A few of my favorite things that always happen at the Summit are

SQL Server Central opening party

Welcome Reception & Quiz Bowl

Exhibitor Reception

Women in Technology Luncheon

Friends of RedGate Dinner

Keep in mind that these events are on different dates and times. And they often come with food. And people. People that you should be networking with. In a situation that allows you to network and get to know each other, ifn you'll simply open your mouth and talk to the other humans.

The SQL Server Central party is one of my favorites. Its always a relaxing event, with easy social interactions. Steve Jones and Red Gate put on a great party that allow us humans to interact easily with games, giveaways and fun. I would suggest you find your way to this event and join in. Its a hoot. Maybe you'll loose at whatever gambling game you participate in. Maybe you'll win a prize as Steve randomly circles the event giving out tons of swag. Maybe you'll make a fool of yourself and have the entire table yelling and screeching as you attempt to reach 21 and explode when you actually reach 21. In any event. you get my drift. You should attend, meet some great folks, play some games, and you might even get a descent shirt out of it.

The Welcome Reception and Quiz Bowl are a huge opening event, prior to the SQL Server Central party. It has food. It often has music. I've seen video games in the middle of the room. It may even have food. You wont really need to buy dinner. And all of a sudden there will be a few folks up on stage answering questions and amazing you with their knowledge and humor. The Quiz Bowl is always an entertaining event to participate in. You could even win a book or other swag as the quiz is going on by merely being close enough to the stage to join in.

On Wednesday night, after all the sessions of the day, you'll find yourself wondering into the Exhibitor Reception, which is located in the Exhibitor Hall. Besides all the people you can network with and wave at and join in with conversations, there's food. Again, dinner doesnt need to be purchased as there will be descent food here to keep your motor running. Take an opportunity to wonder around and visit with the vendors. Get some swag. Get some food. But don't forget to talk to the humans. They are all around. They have knowledge that can assist you in your day to day work. Or maybe you can help them. Maybe you need to make a list of things to talk about and refer to it, to assist you. But open your mouth. Talk to the others. Remember we are all there for the same purpose. To learn. To Share. To Network.

As a father with female offspring that I often do not understand, I find that attending the Women in Technology Luncheon is an eye opening event. Learning from the expert panel is always enlightening. I tend to sit there and think about my girls and how I can share this information with them. Help them enter the workforce ready and armed with what they may need to make it easier on them. This event, though it contains the value 'Women' is not solely intended for the women of the Summit. Its for us all. It helps us all. Oh, did I mention that there is food? Yeah, there is. Lunch. Good lunch. As you attend this event, put your head on straight and participate. Soak in the atmosphere and the topic.

Another event that I love to attend is the Friends of RedGate Dinner. This is such an honor to be a member of this fine club. Being able to rub shoulders in a social environment with the employees of this company is always engaging and fun. There is food, but that's actually not the top priority. Being able to interact with this crew of folks by far outweighs the food. This allows mere humans like me to be in the presence of many fine folks that have influence in the community as a whole. Open up your ears and you can learn tons from these folks. By volunteering in the community, gaining a position of influence and being able to share with folks is what allows you to become a member of this group of 'Friends'. Sidle up to these folks and learn. Learn all you can.

So, as you can tell, there are a lot of scheduled events that occur. Check out the schedule yourself. Try to hit as many of these events as possible. Especially these on my list of favorites. Maybe some of these can become your favorites.

Other things that you can do that I have loved to do.

Photo Walk

Breakfast at Lowells

Grabbing fresh fruit and other snacks at Pike Place

Wandering the streets towards Pike Place

Visiting a French Bakery with your new southern brother

Make yourself some goals. Write up some talking points and use them in a pinch to open discussions with other people. I guarantee that if you will make an effort and talk to the humans, you will find several fine folks that will become fast friends and cohorts in this journey we are all on to better ourselves thru professional development and education. You'll learn or share what you have learned. You'll make friends. You'll experience the best of SQLPASS.

I don't know anyone, I don't sing karaoke, I don't drink. Can I still have fun at SQLPASS?

2011-09-06T11:08:00.000-07:00

The Summit for the Professional Association for SQL Server is the premiere event of the year for the group commonly referred to as PASS. Its coming up soon. In October to be more precise. If you are not planning on attending, stop what you are doing and make plans.

I attended my first PASS summit in 2004 in Florida after the hurricane had hit the Florida area. Since Disney World was close by, my family came along with me. We rented a condo and stayed there for the duration. I had a rental car, and I would get dropped off at the Summit while they went to Disney World. I joined them twice, leaving the summit once early, and again on Friday. We went as an entire family on Saturday and then flew home. Each day I would arrive at the summit after breakfast and usually after the keynote. I would attend session and after session, learning tons of valuable information, and then meet my family somehow afterwards. That was the extent of my participation in the summit.

Looking back and realizing that some amazing memories were created with my family at Disney World, I think I would do things differently now. Knowing what I now know.

I would attend the summit earlier each day, earlier than breakfast. People are there already, milling about. I could get to know them. We could network. I could learn from them. No just the sessions. I would talk to more people while at breakfast. I would ask what they did, what they struggled with, and I would share those same things with them from my day to day experiences.

I would attend the keynotes and instead of making sure I had that saftey chair between me and anyone else in my row, I would sit next to someone. In fact, if I saw a group of folks talking and chatting, i might siddle up to them, if nothing else than to listen in and hope to join the conversation when I could. Inbetween sessions I might look again for a group of folks that seem to be discussing interesting topics and join in. If I was late to a session, I wouldn't stress about it. I'd attend when I could.

But what about drinking? What about all the parties? What about SQLKaraoke? What about wearing a Kilt? None of these things are things I would do in my day to day life. I never go to a bar. I have never drank alcohol in my life, nor do I smoke. Why would I go to something like this? Should I go to a gathering like this? Should I wear a kilt?

Well, let me tell you that after the first summit, I went to my next summit and implemented some of the things I mentioned above that I said I should have done from the beginning. I met people. I talked to people. I ate breakfast with people. We discussed issues. Soon I learned that there were even more networking opportunities out there for me. After the sessions had closed, people kept doing things. Kept hanging out. Yes, lots of them drink and even tend to sing crudely in front of an audience. Some will even wear a kilt. And no, these are not things I usually do, so I would usually return to my hotel. I should avoid them? WRONG!!

At one of my early volunteer outings with other PASS volunteers I realized that even if I didn't drink, I could still have fun with people that did. I talked. I networked. I commiserated. I discussed issues. I partied like I party. When someone tried to make fun of me for not drinking, another PASS volunteer stood up and defended me. He didnt drink either. But he sure likes pie. Instead of leaving or avoiding the gathering, I met more and more people. More and more opened up to me. I met even more people. It's insane the number of people you can meet if you open yourself up to meeting them.

Remember, they are all like you. There is not a cool kids club. We are all part of the club. Its up to us to determine how much we participate. Or not. And if we don't, we are simply hurting ourselves. Because others are meeting, talking, planning, sharing and learning from each other. Why aren't you out there with them doing the same?

Sleep when you return home from PASS. Soak up every moment you can with the SQLFamily.

Get out there and meet them. Open up. Experience PASS.

Powershell + Reporting Services?

2011-05-19T14:30:00.000-07:00

I have a small need to produce some data in a much easier way than I have heretofore. The process that I currently do it this. I run a query that produces the table counts of various tables in a specific database. These results I paste into Excel. I then repeat that process on another database (the destination of replication) and copy those results also into the same Excel document. I have within this Excel doc a column of fields that are functions, indicating if the totals from the left are the same as on the right. This lets me easily check if the replication has caught up, and if not, which tables are missing data. Its crude and rudimentary, but a descent check on replication.

Now I have to do this for multiple databases. Repeat those steps multiple times. I dont like repeating things, unless they are fun things. This is not a fun thing. I kinda want a portal of sorts that i can click, and get the answer I want. This is not that solution.

I think about powershell. Hum... I could have a PS script that can pull the results of the table counts, and deposit it into a table. If I do this for each side of replication, for each database, I will have a simple table of results, and a simple SQL query will then let me show off only those records that are not equal, meaning replication is off on those tables. Hum... this is promising...

I easily create the script as the following.

$DESTINATION="DBSERVER"
$query = "SELECT
@@ServerName as [ServerName],
DB_NAME() as [DatabaseName],
o.name AS [Name],
i.rowcnt AS [Count]
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id AND indid IN(0,1)
WHERE xtype = 'u'
and o.name in ( 'Table1', 'Table2')
order by i.rowcnt desc"
$Data = invoke-sqlcmd -query "Delete from ReplicationReviewResults where ServerName = 'DBSERVER' and DatabaseName = 'DBName'" -ServerInstance "DBSERVER" -database "DATABASEMONITORING"
$Data = invoke-sqlcmd -query $query -ServerInstance "DBSERVER" -database DBNAME
Write-DataTable -ServerInstance $DESTINATION -database "DATABASEMONITORING" -TableName ReplicationReviewResults -Data $Data
$Data = invoke-sqlcmd -query "Delete from ReplicationReviewResults where ServerName = 'DBSERVER2' and DatabaseName = 'DBNAME'" -ServerInstance "DBSERVER" -database "DATABASEMONITORING"
$Data = invoke-sqlcmd -query $query -ServerInstance "DBSERVER2" -database DBNAME
Write-DataTable -ServerInstance $DESTINATION -database "DATABASEMONITORING" -TableName ReplicationReviewResults -Data $Data

This I repeat as many times as necessary. In my case, 3 times, with 3 different DBNames.

All this I shove into a PS script. I test it out and it works fine. It removes the data from the last execution, for a particular DBNAME and DBSERVER and then collects it again and deposits it into a central database on some db server. Simple. As long as connectivity exists, PS can execute, users exist, and all that type of config, I am able to execute this, and populate data. Woohoo.. Closer.

So now as I think about the portal idea... I think of Reporting Services. A report could easily display this data. Summed data or detail data. Something easily retrieved from the table ReplicationReviewResults. Easy. However, that data won't be accurate. It will be latent, since the last time that I ran the PS script mentioned. So... hum... What if I put that PS Script into a SQL Server Job? Could that work? I do have other PS scripts executing with jobs... that should work.

After testing the job and seeing success, I know have a way for my data to be populated. All I need to do is incorporate this into a Report with a step to call the job execution, then maybe wait a bit for it to finish, then display the data. Yeah!! However the user that can run reports doesnt have a lot of rights, and running jobs isn't one of them. He is a simple datareader...

So, after playing with granting rights to sp_start_job and even select on sysjobs, I'm still stuck. Duh...

I realize that this user needs to have Agent rights and recall that there are roles in the MSDB with something to do with Jobs. I add the role [SQLAgentOperatorRole] to this user, and they can in fact now execute a job. Woohoo. So I am now armed with a PS Script that is embedded in a job that can be executed by the user that reports run under. I create a proc, embedding the job start code along with the query to produce results, and then I create a report that calls this proc. Viola! I now have a portal that I can refresh, it retrieves new data, and displays that data to me.

Replication status updates as simple as hitting F5. No more repetitive tasks taking me minutes of my precious day...

Companies you love to love

2011-05-18T10:06:00.001-07:00

We all know that there are companies we simply put up with. Others we hate. Some we love, but learn to put up with and ultimately degrade into a hatred. It happens. Time marches on and customer service goes down, priorities shift, things simply change and it goes from good to bad to worse, on occasion.

Back in college, I had a trusted adult in my church that ran a mechanics shop. I could call him and tell him my car was making a noise, describe the noise, and he would diagnose the issue and suggest solutions, over the phone, with no visit to his shop. As time marched on, we continued to take our vehicles to him for help and repairs. As his company grew, so did the dissatisfied service provided, until the day that we had to leave and find another mechanic. Sad. But it happens.

On the flip side, how often do you fall in love with a company, get treated well initially, but as time marches on, they treat you better and better? Think to yourself, list out the times that this has happened to you. I'm betting that it is not often that this happens. I can think of few examples myself. However, as the title of this post suggests, I have one such example.

Lets talk about "this Company"

Years ago, as a young Database Professional, I heard of a company that had a simple tool that compared databases. This wasn't rocket science, but it did prove handy from time to time. I used the tool, more and more. As the company made progress, they made the tool better, and soon branched out into other tools. I do recall thinking that this was a mistake, that doing too many apps or tools would cause them to loose focus, and the tools I had grown to love might suffer, and the company, like so many others, would go down in my eyes. I have been involved in a few different levels as an outsider with this company. I even made a little money for doing usability testing on various products. I had used the product, and they wanted to know some information from me, and I freely gave that information. The payment was a bonus, I thought. I woulda given the info to help out. But was glad to get something back from them. In fact, the motorcycle gear bag I won now, and have used for many years, was purchased using part of that money. It made it easier for me to put in the rest of the money on the bag myself. I think about them every time that I ride and enjoy using that gear bag. Ironically, its red. Not because of anything other than it was the best looking bag at the store at the time. But I find it funny that it's red.

So the days, weeks, months and years march on. I continue to try out products, continue to use the old standard apps and tools, continue to simply use what they have produced and love it. So many times one of their tools has come to my rescue and helped me out. 9 months ago I finally took the time to dig into the API and write an app (a feat which i had almost forgotten from my developer days) that generates snapshots from all the databases on a server. Simple task. However using their tool saves me tons of time from manually doing it. And using the API saves me even more time. Minutes compared to hours. Recently I purchased a monitoring tool that has given me the peace of mind that something is always watching my database, and will let me know when or if something goes awry. Being a single DBA with no NOC to watch my systems, this has greatly improved my life and as mentioned, peace of mind.

All this is praise for a job well done, and should be expected of a company providing tools and apps and simply trying to sell their wares to us. However, this company does a bit more. They interact with the community and you as an individual. I have many stories that I could tell you. Some of them involving some funny situations. Find me someday, and I will relate them to you. The best involves a bio break + phone call. But the one I wish to relate today is the personal touch that they perform that affects me and my life. They reach out and in a way no other company ever has, touches you. They make sure that you know you are important to them.

The actual Story

The story is simple and short and happened in the last couple days. A few months ago I received a package from RedGate that contained some swag. One of the items was a lanyard. My son saw this and wanted to have it. He transitioned it into a school badge holder, which he wears to school every day. Monday of this week he participated in a school play, and was proudly wearing his Red lanyard along with his play costume, which comprised of a few pieces of gold material around his wrists and head. I took a picture of him in this getup, with the proud smile. Since he was wearing the lanyard, I figured I should share this with the twitterverse, letting RedGate know via hashtag. Her is the picture.

This photo is me just showing my support for my son and by extension for RedGate.

A day later, I received a picture from RedGate that showed that at least 10 people were thinking of me and my son and wanted to touch us from across the pond. Here is that photo.

How awesome is that!?!?!? Have you ever had a company go out of their way to show their support for you in such a fun and silly manner? Simply having a good time, but at the same time, making that connection that we have both forged over time that much stronger. I owe each of these individuals a hug or a heartfelt hand shake, which ever they feel most comfortable with. I bet though, by looking at the that way they responded, they'd opt for the hug.

Way to go guys. You made quite a few of us happy today with this response, and I can't wait to show my son later today what you've done for him also.

Thanks.

You already have my loyalty and appreciation and even a bit of my and my companies money. This is simply over and above. And we love you for it.

running, working out

2011-05-04T13:22:00.000-07:00

If you are like me, you may have noticed that you are not getting any younger. In an effort to further my existence here on planet earth, and ensure that I have time to attempt to understand all that Paul Randal says on a daily basis, I need a lot more time ahead of me. A lot of time. In order to ensure that I stick around, one thing I can do is watch my health. Inclusive of many other things I can do, working out is high on my list. It used to not be, and in the past few years has increased in its priority. Due in no small part to the constant reminder of a great influence on my life that is embodied in the person we all know as Steve Jones. Seeing his constant reminder of what he has done on a daily basis for his health has propelled me forward when I really didnt feel like it to do my own exercise. Not to keep up. Not to compete. Simply his shining example has fortified my resolve, allowing me the extra added boost on more than one occasion to go that extra mile and get off my butt and do something. It is so appreciated and I find it a daily reminder to get up and do something active. Thanks Steve.

It is at this point that if you live in Colorado, near some horses, and answer to the name Steve Jones, you should stop reading this blog post.

Thanks for reading thus far, but seriously, this is for everyone else now.

Ok, I think he's gone. If you fall outside of the above criteria, and have found yourself influenced by Steve to go a little more, dig a little deeper, I'm interested in hearing your tale. Email me at tjaybelt@yahoo.com with your story or words of thanks or whatever. This is an idea in the inception only and I simply want to do something to say thanks back to him for his influence. Especially as he approaches the landmark goal of 1000 sequential days of running.

Meme Monday

2011-04-04T07:28:00.000-07:00

Tom LaRock started a Meme Monday challenge.

Write an 11 word or less blog post.

Today’s meme is “Write a SQL blog post in 11 words or less”

I wrote Multiple...

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

It's Monday! Again!

Fine!?!

Let's get through this quickly and efficiently!!

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

Hello Monday. I wonder what jobs failed over this last weekend?

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

Good morning SQL Monitor. What did you see happen this weekend??

I have a backpack

2011-03-18T12:05:00.000-07:00

I won it at a Microsoft Event many years ago. It says Microsoft Insider. Its an OGIO branded backpack. I have used it to daily transport my goodies to and from, between work and home. Almost daily I will grab it, with a laptop inside, some power cables, various other emergency cables of all varieties, spare mouse with batteries, an MP3 player, and so on. It has become the haven for holding all those precious things that I may need and use for electronic uses. When I go on a trip, I can typically grab this bag, throw in some cameras and camera chargers, and maybe a book, and be ready for the weekend. Whatever happens, I've got a cable or tool in that bag for what I may need. As you can tell, I practice the motto 'Be Prepared'.

Ive done this as far back as this backpack has resided in my possession. Although the actual items have shifted during said time, the existence of these items, among any others that seem necessary has been constant. This bag has a place to rest at home. A thrown if you will, where only it lives. Its near the door, so I can grab it easily on the way out the door. At work, it always goes in a similar place of glory. Always where I can get to it, see it, be comforted by its existence and all it stands for.

Whats the point of all this? We'll get there...

So, I changed jobs recently. I no longer have a laptop to bring home at night, nor to bring back to the office in the morning. I no longer have that single power cable for the laptop. My bag is a lot more light these days. But I continue to bring it back and forth. Now, the only purpose to do this is habit. The extra cables and power supplies and what nots are simply that, extra. Usually used in conjunction with a laptop. Either at the office, at home, or away. So, without a laptop in the bag, whats the point? It becomes a bag only. Not a super special device to hold all my special things. It ceases to have the purpose it once had. And I believe that it is silly for me to bring it back and forth under the old habit, since my circumstances have changed.

Get where I'm going yet? This applies to us as Database Professionals...

Sit back now, look at what you do, day in, day out. Analyze these actions and detail to yourself which of those actions serve a purpose, accomplish a goal, and which are simply formed from habit. Those that are only habitual, really dig deep on them, and tell yourself if they still serve a purpose or not. I bet you will find some that you simply do because you have become accustomed to perform said action, and they may no longer be needed. If you do not have any, congratulations. Or maybe you didnt take as honest a look at your tasks.

As for me, my bag is at home now. Still holding its collection of extra tools and what nots. But its not at the office. This entire week, i forced myself to not grab it and bring it. Forced myself out of my comfort zone to see if this tool, this crutch of a habit is really necessary. The jury is still out on that. I may need more time to fully realize the results of this small action. But I took the step to analyze myself, my actions and the usefulness of that tool. I hope you will do the same.

I'm Addicted, I'll admit it

2011-03-14T08:35:00.000-07:00

Before I started this new job of mine, (nearing the 3 month mark as we speak) I had heard of this little tool that i envisioned would make my life easier. In this role, I am a solitary DBA, in a shop that has very little visibility into the database systems. DBs were setup, configured, and if worked, were left well enough alone. Until something bad happened, no one really looked at them.

With my new task to be the Administrator of all things Database, I was concerned that something would occur when I wasn't sitting in my chair. How could I know what was going on on the systems in the early morning hours? What about later at night, while I was unwinding from my day? And lets not even talk about the weekends... those were the worst of all. What if something happened, while I was out playing... how would I know? How could I react quick enough to solve issues, how could I be informed?

Well, all this was a concern and had been addressed when I installed, configured a special piece of software called SQL Monitor, by RedGate. Once configured to my likes, it would capture events, send me alerts, even keep track of what had happened. I was freed up to do other tasks, as I had a monkey in the background collecting and monitoring for me. I love this monkey. I have grown accustomed to having him around and letting me know whats up.

Then came this last weekend, when everything went to, well, it wasn't what I was expecting. Last week, after an extension or two of my trial period expired ( I have yet to get the money makers to get me the money to purchase the product), I was left without a window into my database. It was amazing at how quickly I was able to become addicted to this software solution and rely upon it. I had found myself checking my phone (personal cell phone) periodically when the little blip went off that let me know I had received an email from work. Sometimes it was an email from my favorite monkey (SQL Monitor) that I had left back at the office to tirelessly watch my db. Sometimes I had to deal with the alert. Sometimes not. But it was always there, swinging in its cage, keeping an ever persistent watchful eye on my systems.

However, this last weekend, my monkey stopped working, because my trial expired. Addiction at its worst. I went into withdrawal. I didn't know what was going on. I had to check it manually, periodically, to ensure things were ok.

So, what have I learned from this experience? I warn all those of you thinking about using SQL Monitor. Don't do it. I mean, don't do it, unless you are willing to allow yourself to become addicted to its features and information and alerting. Don't do it unless you want to find yourself falling in love with an imaginary monkey that watches your systems without fail when you are not in the office. Don't do it unless you like offloading the automated digging into your systems, the automated alerting of issues, the freedom that this product grants you to do other tasks while it consistently does its prescribed tasks.More importantly, don't install it, become addicted to it, rely upon it, unless you can get it purchased quickly and not become disconnected from its addictive services. This I experienced this weekend. It wasn't nearly as fun as previous weekends when it simply worked, simply monitored, simply kept my sanity in check...

I have now received an extension on the trial again. I am plugged in. I got my fix. I can last a few more days. During this time, I MUST get the monies to purchase the product, so that I can continue having my fix. Continue to get the steady drip of love from #Red-Gate and #SQLMonitor. Allow SQL Monitor reach out its virtual hands and together we can grasp the greased rope holding on to my sanity. (who's quote is this? "even the most well- adjusted person is holding on to his or her sanity by a greased rope.")

I do enjoy the product, and have become used to its usefulness and features. I have become addicted to it. I need it.

The log for database 'Borked' is not available. Check the event log for related error messages. Resolve any errors and restart the database

2011-03-02T13:15:00.000-08:00

I found that a small database across the country that no one really thinks about often was giving this message. I was made aware of this thru a series of events that led me down quit the rathole. From one thing to the next and the next, and finally finding this to be a core issue in the event logs.

Its a little database. It does a small specific task.Let's call it 'Borked'. It was part of a maintenance plan that performed backups, and since the log file was unavailable, it was unable to backup. Luckily this was part of a maintenance plan, so when it failed at the beginning of the process, it also prevented any other databases from backing up. Yes, that was facetious. This was one of the items along the rathole that I found out. The issue came to my attention as folks noticed that some data was out of sync. Borked DB that lives on this db server is copied elsewhere and restored, and reports are pulled from it. This data on the report was out of whack. Because there were no new backups of DB X, because DB Y had an issue... part of maint plan... yeah...

So, I do some research online, and see people suggesting DBCC CheckDB on this DB. However, when I attempted to run this command, it would tell me the same error. Odd. Looking into the errorlogs shows me that its been happening for quite some time, about 20 days, and the first instance of it showed me nothing of note in the Error Logs that could point to any other issue. There are 4 other dbs on this DB Server, and they are all ok. Before touching this Borked DB, I ran DBCC CheckDB on all the others, and performed manual backups. I also removed the maint plan and instituted individual backup jobs. I tested each of these, and the next day confirmed that they executed properly. So, all was well with the other DBs. But nothing seemed to work on Borked.

Being worried that there is some corruption, that I may loose data, coupled with the fact that 20 days have transpired without a descent backup, I was obviously worried about the data. I created a new database, and exported all the data from Borked to BorkedData db, to keep track of it. I blew it on this step, since I had not initially created the objects as they were in the original, they were stripped down copies of the tables (missing Identities, missing constraints, etc). So now I have a copy of the data, but the schema is a bit goofed. I take some more time to actually use #RedGate tools to make a copy of the database, then export the data, then compare again to ensure that me BorkedNew database has all the proper objects and data. Whew.

Now I am free to futz with the Borked. Obviously some tasks have been attempted already, but they all resulted in an error message about a missing log file. Not much more help than that. So my obvious thoughts went to detaching the DB, and bringing it back online thru attaching it again. This seemed to make sense in my head, and was the direction I had headed since the inception of messing with Borked. I had to get my ducks in a row to ensure that I could restore it, if the need arose. Thus all the moving of data, copying of schema, etc. Just to make sure I had a copy. So, I get ready to detach Borked and reattach it. I create all the scripts, double check the paths, and simply make sure all was in order. I envisioned that this would be a quick 1-2 punch. Detach. Attach. Viola, no one the wiser. Keep in mind that there is a web app using the DB, but its not highly used, so a small interruption would not be noticed, or so I was told by the 'owners' of this system. I use the term 'owners' in the loosest sense of the term. I have now spent more time vested with Borked than anyone else in the company, so technically, I am its new 'owner'. Anyway, I digress. We are about to detach and attach the db, which in my mind will release it, and then upon attaching it, will reinit the link to the log, and somehow make life happy again for little Borked. I pause, looking over the script one last time, ensuring I have selected the appropriate T-SQL... pause for effect, then execute the fateful code.

I know, you are expecting that this is the end of this story... But no. It is not. What happened? same thing as all other attempts... Error about the missing tlog. Crap. Crap Crap. Ive prepped, Ive backedup. Ive copied data. Ive ensured I can restore. I have the entire DB copied elsewhere. and that backedup. Yeah. What to do now.

So, I right click it, and goto tasks in SSMS, and notice the small menu item I have rarely used. 'Take Offline'. Hum, not quite a Detach. Not quite a Delete. Something I've honestly, rarely performed on a DB. So I click it. And sure enough, DB goes offline. There is a nice little icon next to it that shows me its offline. Well, at this point, it seems that there is only one choice. That is to 'Bring Online' again. So I now click this. And viola, Borked is now online.

I go to the script that I had saved that had all other attempted commands I had previously executed against Borked. I ran a DBCC CheckDB against it, and it succeeded. I then realized I had fodder for a Blog post, and started writing this. About the time that I wrote the word 'Take Offline' in the paragraph above, I actually went back to the DB to ensure that was the proper term, and realized I had yet to backup this database, after bringing it back online. Doh! What a dork. So i obviously kicked off the backup, and it succeeded.

Viola! my little Borked db is now online, and all seems well. Had I known this little caveat, I might have performed those simple menu items yesterday, instead of worrying about getting backups, moving data, digging into the processed, and all that. But, the silver lining is that this little db Borked now has a new owner. One that knows more about it than others, and will love it for years to come. Even if it cant figure out where its tlog went and why it can no longer access it. Fun Times !!

swwug 2011

2011-02-28T09:14:00.000-08:00

I have been invited back to present for the SWWUG vConference in April. I will be flying down to Tucson on March 8th and recording my 4 sessions. These are not the exact titles, but from the top of my head.

Documentation. You know you love it.

Data file sizes.

Profiler / Traces

DBA Tasks

At this point, Documentation has been completed, and actually practiced as a presentation already. The others are nearing completion. I had set a goal to have them completed by this last weekend, and failed to accomplish that goal. I have a bit more work to do on some of them. But most are already well planned and created. Just need to be fluffed up at this point.

One of the tools that I used to help me was a mindmap software where I simple was able to link ideas together and list them all out. I used a couple different versions of the software, and a few different computers, including my Galaxy Tab, to simple brainstorm with myself and come up with the skeleton for each presentation. I believe that I will actually use this document in the the Documentation presentation, as an example of how to simply start documenting. Its been a work in progress, and the mindmap software allows me to simply keep adding ideas to the map and utilize those when the time comes to flesh out the presentation.

I'm nervous and excited, as always, as the date approaches. I love the creative process, I love actually presenting, I love teaching and talking and always hope that i can do a descent job and help folks. However, its still a nerve wracking process. Once its all done, I always appreciate the effort put into it, the experience as much as I do that its over. Its a funny process to look forward to something that scares you, kinda like a rollercoaster. I keep getting back in line for another ride, but am scared while in line and on the ride, yet I always return to the line and ride.

SQL Saturday 66

2011-02-15T08:40:00.001-08:00

This started out as a simple review of the SQL Saturday 66 in Colorado Springs. Then it needed some back story. Then it simply became a novel. So, enjoy, or jump out now. Your choice.

Sometime last year, I decided that I would try to get to some regional SQL Saturday events. It didn't know exactly how I would accomplish this, for there were a couple obstacles standing in my way. The first was time.

The second was money. Those were the big ones.

Time was hard to come by, as it seemed that I didn't really have a lot of time to do professional development since it consistently seemed to be impacted by work. Ironic that I was unable to learn about skills that would help me in work, because I really needed to be at work instead...

Money was a difficult resource to acquire and use for this type of venture. Its benefit is obvious. Its a great education and networking opportunity to attend any SQL Saturday event. Yet it was always difficult to come up with the slush money to fly myself and get a hotel, car and anything else needed in order to go to somewhere else for a couple days.

Let's keep it simple, and keep it to those two obstacles. Time and money.

If I was one of the lucky ones, those that work at a company that has a vested interest in attending an event such as this, I'd be lucky to be sent, with expenses covered. This is an obvious win win for company and employee. I, however, am not one of those few lucky ones.

There are also those that are in the consulting gig that see their attendance at these events as a business expense to drum up potential business for their own company and skills. This is a noble gamble to bet on yourself, all the while giving back to the community as you do. This is a win win as well. I am not one of these either.

I'm just a working stiff, like many of the other DBAs and Database Developers out there that attend these events. However, something changed in my life a couple months ago. A job change changed everything. I now have more discretionary time that allows me to attend an event like our beloved SQL Saturdays. So, the time element was relaxed and I decided to simply throw the money part out the window. After discussing it with my CFO, she and I decided that we'd spend the money to fly me to a location and spend a couple nights. I can keep it cheap, and doing so would allow me to learn, grow and experience professional development on a budget.

So, I looked at the events coming up, and thru my hat into the Colorado Springs SQL Saturday event. It's close by, and I know some of the folks from the area. I am also the Regional Mentor, so it would be good for me to meet more of the local SQL people and get some face time with them as well as their leaders in the community. From my house to their home is about a 10 hour drive, and with the gas prices as they are, it would be 6's to fly or drive as far as cost went. However, there is a significant cost to spend 10 hours driving, instead of 2 hours flying. So a choice was made to fly to Colorado Springs. A couple nights in a hotel would be $50 or so, maybe $100 each. This isn't too bad of a cost. But its still a cost. I had a few opportunities to cheapen my hotel cost with a few options that presented themselves to me. One was to rent a cabin with Chris Shaw (twitter|blog) and split the cost. Another was to split a room with some local community people. Another was to stay with a good friend that lives in the area. Since this last option was going to give me some good time to catch up with a good friend, i chose it and made arrangements to spend the night at his place. In order to get there with enough time to hang out, I decided that I needed to fly out a bit earlier (1:50pm) instead of the later time (8:00pm).

This decision meant that I needed to ask for a bit of time off on Friday to leave early. This means I need to talk to my boss. In the past, when events of this nature occurred, this didn't always work out well. When work seems more important than anything else, a SQL event falls way short. I was worried to ask, but needed to, and did. As I informed my boss, he started asking me questions. The conversation went something like this.

Me: I need to take off a bit early on Friday to fly to a SQL Saturday event.

Boss: What is this event?

Me: Its put on by individuals for individuals, part of the SQL Community, all over the nation.

Boss: This sounds like a Professional Development event

Me: Yeah... (waiting for the hammer to fall, some excuse why it would be difficult to attend)

Boss: This sounds like something tht we should be paying for.

Me: (shocked) Yeah.

This is the simple version, but they decided to not only allow me to take off a bit early on Friday, but they were intending on paying for the flight and hotel. I was shocked and amazed. Time and money. Solved. Woohoo!!

Off we go to SQL Saturday in Colorado Springs.

Luckily, as the dates approached, my friend Randy Knight (twitter|blog) also decided to attend. This meant that we could travel together and split some of the costs. We headed up to the airport Friday afternoon, and after a moment of slowness as we passed an unfortunate accident, we were again on our way, albeit a bit late.

Once at the airport we cruised thru security, leaving only a single bottle of Strawberry Jam behind that apparently is insidious enough in nature as to not be allowed into the airport proper for fear of use as a weapon of some sort. This bottle was an intended gift for the wife of my buddy, as a thank you for allowing me to spend the night at her house. It was also a bottle canned by my wife at the LDS Cannery, which made it something special from us to them. It was not meant to be though...

The plane ride was uneventful, dumping us into a different version of winter than we had at home, but similar enough to not be terribly different. As I left the airport, and watched as Randy grabbed his rental car and departed, I waited for my ride to appear. Even though it was clear and sunny, I was soon cold, having forgotten it was winter here too. I stand outside for a bit, until i start communicating with my buddy. The text messages and voice conversation soon took an odd turn as questions were posed but unable to be answered to anyones satisfaction. It turned out that my ride was in Denver at the airport as I was standing outside the airport in Colorado Springs. Funny. Even funny to us at the time. A bummer. But funny nonetheless. What a mix up. With all the communication tools that we have and at our disposal, neither one of us seemed able to clearly indicate which airport I was arriving at for this event. Funny. Sad kinda funny, but funny nonetheless.

I end up calling Randy back to retrieve me, as he had a head start to the speaker dinner from the airport already. Luckily he was able to return to get me, leaving the nearly empty freeway to return to the airport with plenty of time to allow the rest of Colorado Springs residents to embrace the Friday afternoon rush hour traffic, allowing us to gain a more full experience of the clogged freeway. This irony was repeatedly brought to my attention by my new chauffeur as we were stuck driving behind residents on their way home from work. We took off, driving north to the speaker dinner. We initially went to Randy's hotel to check in, and I even checked on a price for a room there that night, in case I wasn't able to hook up with my friend for the evening. I then checked on the hotel that I was going to stay at the next night, to see what the price would be for the first night, again, in case. After this, I went to Mr. Biggs to assist with the preparations for the event in any way that I could. It was great to meet some of the speakers and organizers. I already knew Marc Beacom (twitter|blog) from past SQL Community events, and it was great to catch up with him. He was the only person that I already knew IRL though. The others, some friends, others acquaintances, were mostly known to me via twitter already. I got to give Chris Shaw (twitter|blog) a big bear hug to great him as we had become friends over twitter, facebook, Im and email for the past couple years. I have always felt like he was a brother. Funny how our community becomes family. SQL Community -> #SQLFamily.

Soon it was the speaker dinner and time to shmooze, cavort, meet, discuss, talk, enjoy and share. It was also the first meal of the day for me, as I had barely eaten anything for breakfast and completely missed lunch because of the time suck that flying can be. Luckily I survived on some snacks from my bag while on the plane, as well as those incredible Biscotti cookies. Dinner was great and we had a great time talking to new people. One of which, Jeremy Lowell (twitter|blog), was nice enough to allow me, a stranger, to get into his vehicle and deliver to the house of my friend for the evening. Jeremy was headed home anyway, which turns out was about 5 minutes from my end location. So it turned out that it was a win win for us. We talked about SQL, but soon turned the conversation to other random topics that we found out we had in common. Its always amazing to meet people in these events that are just as human as you with the same concerns, ambitions, interests and whatnots. We had a great time talking for an hour. A friendship began.

Once at my buddies house, I was honored to meet the kids. Gifts were given, minus the Strawberry jam which now lives in a trash in SLC airport. Soon we were simply talking, telling stories, and having a great time. We both do woodworking and it was great to see his work space. Later in the evening I was even able to help feed the horses, which made me feel like I was channeling Mike Rowe as I attempted to perform a task I was unaware of how to perform. Standing in the cold Colorado night, helping feed the animals in the snow was invigorating. Fun to do once. Once back in the warmth of the home, it was time to get to bed, so that we could get up early the next morning and make the 1 hour commute down south to the SQL event. Off to bed we went. Me, grateful for the time spent, the stories told, the experiences had and the roof over my head and bed offered within their home. Thanks.

The next morning had us up early to make the commute. After stopping at a gas station along the way, and after purchasing the first cup of joe ever, I was able to make a small effort at paying back my gracious host for the previous nights lodging. We had a great time talking about many many things and I truly enjoyed the time we had to spend together as the sun chased us. Soon we arrived at our destination and got on our game faces. It may seem otherwise on occasion, but we are all geeks at heart and want to simply sit in front of a computer, shunning human contact in all its forms. Attending an event such as a SQL Saturday seems against our nature, but it is such an important part of our Professional Development that we suck it up and go and put on our game faces, getting ready to talk and interact with each other.

Luckily Chris Shaw and the other organizers thought of this and planned to have many networking events that forced and allowed us to interact with each other, getting to know each other. The first event gathered us together in disciplines, into two circles. One inner circle faced an outer circle, one to one. We were to take 2 minutes with each person in the circle and perform some SQL Speed Dating, answering 2 questions. It was a simple exercise and allowed me to introduce myself to 13 people as well as get to know them. It was fun. Soon the sessions were underway. SQL Lurn was ON! Between each session, a networking event occurred. The first I chose to participate in was Lazer Tag. What a rush. This is always such a fun activity and we all seemed to have a blast shooting each other. There was lunch, which allowed us a chance to eat and talk with each other, performing our own networking discussions while cramming pizza into our gullets. More sessions and more networking occured. Lurnin and Networkin. Over and over. It was great, well organized, and well participated in by all.

I ended the day with my session in the last slot. I had participated in Lazer Tag just prior to my session in one of the networking events, which got my blood pumping and ready to stand in front of a group of SQL peeps and discuss Documentation. I believe that the session went well, and we had good discussions. Many speakers attending my session, which was always a bit nerve wracking initially, but enables me as a speaker to engage the audience even better than typically, as these are now friends and family that can be pulled into the discussions easily.

After the sessions, we had one more networking event. We all had to make a trek across the family fun center to the sand box, and retrieve an easter egg. Inside each egg, for each member of the event, a prize awaited them. The organizers had gotten many items to give away and each person received something. It was well received and I believe everyone went away feeling a bit more special at having received a thoughtful gift for simply attending. It was a nice touch.

And just like that, as quickly as it started, it was over. It was such a full day, with so many interesting topics, great knowledge, great networking, that it blew by so quickly. I for one barely noticed that I had been on my feet for many hours, had done a great many different things as well as learned and gotten excited about SQL stuff. It was exactly what was needed. Many thanks go out to the organizers. Great job!!

After the event folks retired to the bar to continue networking. I don't drink, so tend to not spend a ton of time at the bar, but did take the chance to relax a bit, talk a bit, and continue to network and build those relationships started previously. Discussions about the event ensued, Regional Mentor duties were performed with various members of the community, help given to those folks looking to setup new chapters and needing some ideas to get them going, and friendships were continued. It was a great time.

Randy and myself departed, after many handshakes, fist bumps and a few hugs. We took off to go get some dinner and relax before our 4:30 am start time the next day for the 6:00 am flight. Dinner was quick, once we finally found a place, got a seat and got our food. Having forgotten that this was a Saturday night and that many other people also wanted to eat dinner, we were slightly delayed in our own goals. Dinner did occur, bellies were filled and more discussions were had about SQL and other topics. Finally, bed awaited, its siren song calling me to slumber at 8pm, finally falling asleep sometime around 9pm. Rare for me to retire so early, yet needed to make the flight the next morning.

The next morning came quite a bit earlier than I could have expected. Its always funny to drive around in the dark, knowing its morning, yet being deceived into still believing its night. An odd juxtaposition. We arrived at the airport without a problem, albeit at the ungodly hour of nearly 5am. We got thru security easily, except for the TSA groping that occurred. Ironically I tend to travel in a tshirt and sweats, releaving myself of a belt, buttons or anything that would pose a threat to the metal detectors. However, this was the reason the TSA groper choose to use to get close and personal with my persona, the baggy sweats. Funny.

After arriving home a couple hours earlier than I would normally wake up on a Sunday, it was all over. I was home. Early and tired and spent. I laid down and rested for most of the day, getting ready to start my normal work week. Good times, good friends, good knowledge. Forging existing relationships and building new ones. Making the SQL Community into my SQL Family. Thats what I see occurs in these events.

Long live SQL Saturday!!!

SQL Monitor - giving it a whirl

2011-01-13T22:26:00.000-08:00

I recently started a new gig, and there is little monitoring occurring on the database systems currently. Happily enough, I am aware of a fairly new product called SQL Monitor from RedGate that says it can fill the bill nicely. What better time to try it out than now?!? I get myself a copy of it and install it to my local hard drive, web presence and db together ( i know its not the best installation scenario, but its worth giving it a try). I initially only set it up to look at my machine, which has little or nothing going on on it anyway. This is done for a day or so, to see what sprouts up, what it finds, and what it suggests on a quiet system. It still finds plenty to complain about, like missing backups, no tlog backups, no maintenance (DBCC CheckDB, or as they are called in the tool, Integrity Checks). Plenty of entries appear each day. I was particularly fond of the Long Running Queries that it came across, especially when these were actions I was taking to remediate other issues that it had found. I found some of the Long Running Queries funny to me, since they were judging this execution against past executions. Lets say I had a process that took 5 seconds to run once. The next time it took 10 seconds to run. Well, this is twice the previous time, and warranted an alert as a Long Running query. Don't get me wrong, I can see the irony that a job that takes 10 seconds once and 5 seconds the next time is labeled a 'Long Running Query'. But the alternative is to have this process run, take twice as long, and no one be the wiser. I would rather know, and deal with it, remediate it, or ignore it than not know.

After watching my boring database, I turned it onto a couple other systems. It found a lot of the same things, which prompted me to create jobs to take those actions into account, and perform them on a much more regular basis than before. I had already launched into fixing the Backups from being a single Maint Plan for everything, to individualized jobs that would execute in proper sequence, and order, with proper notifications going to the newly minted email list 'DBAlerts@mycompany.com". A couple more processes in place, and I had all but eliminated those alerts that SQL Monitor was complaining about. The next morning, though, I was surprised to find myself inundated with over 300 alerts from one of the databases. It's a good thing that I hadnt hooked up email alerting to this yet, or it would have been a miserable night of sleep. As I waded thru the alerts, there were some obvious items that were of valor, and needed to be fixed. More processes needed to be added to the systems. Or the alerts needed to be configured to be a little more forgiving, or disabled, whichever be the case for the particular database and its alert. This went on for a few days, until the majority of the alerts were related to Long Running Queries again. From these, I have compiled a list of distinct queries that need to be investigated. When others within the company are asked if they recognize these particular items, they all sigh, groan, say how nice it is to have someone seeing these things, and more importantly, grin when they realize that they may in fact be 'dealt' with at some time, instead of simply ignored, as has been the process heretofore sans a DBA.

So, after dealing with a few days of many alerts appearing, two days ago, i simply wiped them all out. Keep in mind that they are still stored in the SQL Monitor system, simply with a cleared setting, so that they do not obfuscate the main portal shoeing 'active' alerts. I eliminated them to give me some breathing room, and start a new baseline of activity after having configured many alerts. The one that seems to appear the most is 'Long Running Queries', and I have not configured this one in any way, as it doesnt get specific with SQL Statements that can be ignored. Whereas Unusual Job Execution times are not only specific for SQL Server instance, they are for job name as well. So a similar job on 2 systems can have distinct handling of the alert, based on its specific configurations. Allowing for more leniency on one side and more strictness on the other, for example.

Its not all rosy and shiny though. I have had some alerts raised that when I clicked on them to dig in, the app simply coughed up an error. Support let me know that this is a known bug and will be fixed. When you click into an alert to handle it, there are a lot of options, but when you are done, you have to go 'back' to the alerts page, often losing your place. I wish that there was next or previous button within the actual alert, such that once the alerts is dealt with, you can traverse the list to the next one more easily. When you add a comment to an alert, there are 4 controls, 2 of which are buttons (cancel and the other, maybe its ok, i cant remember). Point being, when you add a comment, and then tab from the comment control, its actually highlighting on the cancel button, though you cant really tell, but if you hit space or enter at this point, it cancels the comment. I cant tell you how many times i have written the most beautiful comment, only to have it wiped out by my quick fingers on the apparently wrongly focused next key. I know that there is a lot left to learn about the product, and look forward to learning it, either on my own, or via training from RedGate (free webcast on the product is coming up in a week or so).

I'm almost out of time on my evaluation period, and have not been given the money to purchase the product yet. I may have to request a bit more time to test it out. Since I installed it locally, I do not have access to see it via any webpage, except my own host, and I am unable to send emails. Both these functions, i believe, are important to a true review of the product. I will attempt to get it onto a true web box soon, maybe tomorrow, and get it visible to me at least, via web page.

This is a rather quick run thru of the product from my memory, while at home, before heading to bed. I am sure that there are items I have forgotten to mention that warrant mentioning. I will sit down with the product and try to recall all these, and get a list of them for a future post. Since I didnt win a copy of the product, nor a fancy iPad recently, I am excited about getting pics of my eval version on my Galaxy Tab monitoring my systems from some place other than my office to show off to folks. If nothing else, the tool has given me a means to brag about DBAs abilities to become informed of goings on in the systems. I have reported some of the items found in our daily stand up meeting, and a Product Manager mentioned 'after hearing these things about our database, I am glad, today, that he (meaning me) is part of our company as the DBA'.

So far so good. This tool has done what it claims to be able to do. Its interactions with me have not left me frustrated or angry. It has provided me with information that is useful in my day to day duties. Configuring it properly will allow me to monitor my systems and keep an ear to the ground, even if I am not in the office to be present when things occur in my DB.

See more reviews of this product at the RedGate website itself http://www.red-gate.com/products/dba/sql-monitor/reviews. And look at more information about the product here : www.red-gate.com/sqlmonitor.

#TSQL2sDay: Resolutions

2011-01-11T11:42:00.000-08:00

For this month’s T-SQL Tuesday, Jen McCown asks:

So tell us: what techie resolutions have you been pondering, and why? Are you heading for a certification? An award? Are you looking to pick up CLR because that guy at the Summit said it’s “bitchin’”? Go crazy…

What is T-SQL Tuesday? Let's read from the master's blog ( Adam Machanic (Blog| Twitter) ) the following :

Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic. The event is called "T-SQL Tuesday", but any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you're working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it.

I thought that the idea of doing these T-SQL Tuesday blog events was a grand idea, and couldn't wait for my first opportunity to contribute. You may notice that its been going on without my contributions for quite some time. I am finally contributing.

Last year, around this same time, there was a rash of resolution blog posts making the rounds. I thought long and hard on what I wanted to accomplish, and posted these here in a blog entry. My goals then, as in previous years, surrounded 10 distinct items. I would like to continue with these this year, albeit with modifications.

1. Local User Group
2. Produce presentations. 4-6 presentations for the year
3. Speak
4. Perform Regional Mentor Duties
5. Blog
6. Articles
7. MVP
8. Read
9. Topics
10. #InboxZero

1. Contributing the the local User Groups is paramount. Be it thru UG meetings, simply being part of peoples lives that are members, SQL Saturday events, Code Camps, and so on. There are many opportunities to contribute, and this last year I did so in some of these ways listed above. I strive to continue to do so this year. I will be branching out a bit, as money permits, to attend regionally local SQL Saturday events as well.

2. Presentations are a good way of documenting what you know, and sharing it with others. Its a good way to learn a subject more fully than you currently know it. I have had good success with SSWUG presentations, an will continue to pursue this avenue, as well as others that come my way. I love to learn, and love to share that knowledge with others. Presentations are an obvious outlet for this activity.

3. I had this goal more specific last year, indicating speaking at SSWUG. I have removed that specificness this year, and simply want to speak more, including SSWUG, SQL Saturday events, and the like. I would simply like to stretch those speaking and presenting muscles more and more.

4. As a volunteer with PASS, its important to define an avenue for your work, then contribute as much as you can to this. I am a Regional Mentor for the Southwest region, and I, along with my colleague Andrew Karcher need to support these Chapters and ensure that whatever they need, they can be provided with. This is a fun job, although I often do not busy myself as much as I feel I should with these duties. There are some side projects in the works as well that are fairly exciting.

5. Writing in a blog can be an interesting endeavor. I will often go days, weeks, dare I say, even months without posting something here. This is not to say that I do not find value in blogging, but simply that I often find value in only posting what I believe is necessary to share, or im simply to busy to do so. So, as in previous years, I vow to post more here, to glean more information from my day to day tasks that are worthy of this forum. Not to simply post to post, or get quantity. I'm going for quality. And in come cases, I need to prod myself to generate that quality, more often.

6. This last year, I did not write as many articles as I was happy with for myself. I vow to set this as a goal again, and produce, as mentioned, more content that is valuable not only to myself, but to others.

7. Nothing will change for this goal, as its a passive goal. All the other tasks will occur on their own, and this is simply there as a carrot.

8. Again, this goal fell short last year, and will remain on my list for the year coming up. I have extended this from simply reading books, to include other mediums as well, and have imported books to my android phone and other devices, with the intent of having them more readily available, and actually carving out time away from the TV to read more.

9. This year, with this new job, I am trying to be more open to topics that were previously shut off to me for various reasons. One of these is PowerShell, and I have actually started using this more already, and hope to do so more this year. Another topic that has come my way is simply general documentation. I have spent a lot of time in my last job documenting many many things for DB systems. I will implement this skill in my current gig to document everything that is missing documentation, which right now is everything.

10. I love that this goal has made it to my list, and will forever remain. This is the ever present goal of cleaning up my email, managing my email, and imply organizing myself, accomplishing my tasks at the same time. With a new job comes new vigor to keep the inbox down to zero. Home has suffered for far too long, and will be high on my list to sort thru these emails and get them processed as well.

Well, there you have it. 10 items that I have found that are important to me, that I have continued to promote as my resolutions through a few years, with modifications as time marched on.

I hope to accomplish these in the coming years. Or at least, to stretch myself to become closer to attaining whatever task or goal each one indicates. Its good to have goals, ans we in our family just recently discussed this, coming up with spiritual as well as personal goals for each member of the family.

I challenge you to read thru my list, see if any of these things ring true for you, or impress you to set your own similar goals. Come back often and reference this, and ensure that you are accomplishing them as the year marches on.

Trackback to original blog : http://www.midnightdba.com/Jen/2011/01/tsql-tuesday-014/

Day 7

2011-01-05T08:19:00.000-08:00

So, its been a week of work days at this new gig so far. In that time I have experienced 7 daily stand up meetings (read scrum) in which the members of the Server teams have said what they did yesterday and what they are planning on today, and any issues they have. Its a great group of folks and they are genuinely interested in helping each other out and accomplishing alls goals.

Ive experienced 2 group meetings on Mondays that share with us what the Executive Leadership Council discussed in their Monday meetings as well. The details and quantity of transparent information from top down is refreshing and engaging. The company is doing well, and they want all to know it. I've also had a company Christmas party, a launch party, a lunch out with the team, and even a meeting that blew thru the lunch hour, leaving us all hungry, but satisfied that we had discussed the important issues of the meeting.

The first few days were incredibly overwhelming with information, as the next few weeks are sure to be as well. A lot of new faces to learn, new systems, new product, new processes and policies. There are no cubes here, but each of us has a desk (a few varieties to choose from) that is mobile. The area in which we work and live is a large open space with our desks spread out into different areas and orientations to best suit our needs and the group we participate in needs.

Its an odd configuration, and requires some adjustment, but, its ok. Like everything else so far at this place, its all ok. Kinda even keeled, and nice. The people, the product, the stress, the mood... its relaxing, yet not to the detriment of work.

As far as the databases go, I was able to get access to them on day 2, and started looking into things. I have quite the list that I would like to accomplish on these systems, starting initially with a review, documentation of said review, and backups. Backups seem to be the first thing to delve into, and I immediately noticed that there was a failing job associated with backups. It turns out that the backup file repository can only hold 1 day of a certain databases backups, while 3 days for all others. Thus, the big db fails for 2 days, and the others succeed each day. On the third day, the big one succeeds, as its previous backup is removed. Simple issue. Instead of doing fulls daily on the big one, lets do Diffs. Or we could get more storage. Or... or... well, you get the idea, there are options, and I simply need to design a solution to fix the problem, test the solution, and implement it, then monitor it for success.

I created an email group to be used by all SQL Server jobs to send notifications of failure. I am investigating a couple monitoring tools, including #RedGate Sql Monitor and SQL Sentry's PA. There is development needs, perf tuning of slow running reports, security review, data file size monitoring and baselining, determining the release process, upgrading to SQL 2008 (r2), ensuring CPU speeds are properly being used, monitoring and reporting, and on and on...

It looks to be a great time ahead of me, with plenty to do. Today, I've started to investigate Change Data Tracking for a possible solution to a need. Color me excited and tag me as a happy DBA.

New gig, new changes and social networking

2010-12-08T15:32:00.001-08:00

I've been at my current employer for 2.5 years. In a nutshell, I am a DBA for a very highly transactional system, a 24x7 shop that takes and processes payment for many different clients. If we have downtime of mere seconds, we loose a lot of transactions. Providing 'Service Delivery' to this system is paramount. Keeping the systems up and running requires a lot of planning, a lot of knowledge of the topology and a lot of time sensitive reactions.

During the past couple of years, I have definitely learned a lot. Skills that will help me forever in this line of work. I have also worked some random hours, long hours and extra hours. Many times I have been known to work the full day, only to find myself working the night as well. This is not uncommon in our line of work. It is understood that if you are supporting a database system, you become the goto guy/gal at any hours of the day or night to help keep your system up and running. You may often find yourself in a situation where your tasks require you to do specific things, and be unable to do other things. Meaning, our job requirements may be detailed for us, and disallowing us from performing certain activities. Roles and Responsibilities, division of duties and all that. These rules allow us to ensure that the right people are performing the right tasks at the right time.

Well, as I wax poetic about our jobs and responsibilities, I realize I got off topic a bit. So, back on track we go. A month ago, at the beginning of November, I received a random email from a Systems Engineer at a company I had never heard of. I have never heard of the individual that sent me the email either. It turns out that I am a secondary link thru the social networking wite LinkedIn. This individual seemed to have the need of a DBA's services, and knew that relying on his connections, may be able to uncover someone that was interested.

Whenever I receive requests of this nature, either thru a person I know, or a headhunter, or from whomever, I always entertain the information. It may be of interest to me, or maybe better suited for someone else. But I may as well listen to the option. If it is interesting to me, I'll follow up. Usually it is not interesting, and I can share this with others that may be looking, or members of the User Group or the local community. So, I entertain any job prospect. In fact, I looked back at my email and saw that about 12 different options had come across my desk in the last year. This is by no means a 'brag'. Most of these options were simply not interesting to me, or even worse, I was not qualified to be interested in them.

So, back to this job prospect. Random email or pointed search, I entertained it, and shot my resume over to this person. Once they received it, they were interested, and asked to see me. I love this part of the process. It makes me feel loved, wanted, and validates my skills and work ethic. This first foot in any door makes you feel alive. You must remember that this feeling also was felt with your current employer, once upon a time. Its fun to feel again and again. But it was once felt, and there were reasons you chose what you chose at that time. Keep these in mind when you weigh the possibilities.

After a couple back and forths, me learning about them, and them learning about me, it seemed we were both interested in each other, and offers were made. This is when those feelings of excitement take an odd turn. All the rest leading up to this is simply flirting and is very exciting. Once an offer comes in, it's game time. The feelings get all mixed up. Remember, you once felt excited about your current employer, and made a decision to begin working there. What to do, what to do....

I spent a couple days agonizing about the pros and cons of each option. In the end, I decided to jump to the next opportunity. The pros of this shop are as follows.

The product that is being sold is to school systems around the country and internationaly. The goal of this product is to teach children to speak and read english. The product uses games and video and interaction to teach. There are databases that produce the learning experience, databases gathering data on what is going on, what users are doing, and so on, databases that are used to report on many aspects of the product. I plan being able to help with performance tuning, data warehousing, data migration, and building out the systems and databases to be more robust systems. I'll get to take the skills I currently have and utilize them in a variety of ways, as well as learn new and exciting things. The systems are all SQL Server 2008 databases, which is exciting to me, as I seem to have been stuck just behind that curve with critical systems being on 2005 and even 2000. I have often wanted to do more work with BI and DW, and this will give me that chance. The company is close by, which will not negatively impact my commute. I have heard nothing but good things from employees as well as families of employees of this new company. The money and other options are similar to what I currently enjoy, and are neither a huge increase or decrease. The company is not tiny, but is not huge either. I have had conversations, as per the interview process, with almost a dozen people and have been really impressed with what I have learned thus far. I will get to attend SQLPASS this next year, as this was one of those options I requested in the beginning.

So, new chapter, new gig. Life will change. Day to day work will be different. Different people will become my coworkers. Lots of differences to come. Its exciting and scary at the same time.

My SQLSaturday #54 Recap

2010-10-25T21:37:00.000-07:00

A few years ago, Pat Wright (Twitter | Blog) was hard at work on some Community events, as a member of the Board of Directors of SQLPASS, that would be similar to SQLSaturday events we have seen popping up all over the country. We had been having discussions about having a larger than a User Group event, but didn't move forward at that time. After a few SQL Saturday's started popping up, we started talking to the organizers about doing one in Utah. I believe we were initially #5 or #6. But it didn't come to fruition at that time either.

Fast forward to May of 2010, and luckily, Pat Wright got us all involved again, and got an event in Utah going. I must say that without him, none of the large events that happen in Utah around SQL Server would probably happen at all. Our hats are off to him and the amazing work that he puts in behind the scenes for Code Camps, User Groups, Photo Walks, IT Professional User Groups, and now, SQL Saturday events. So, enough kissing his butt. Back to my recap.

With the event in full swing now, it was a matter of simply ticking off the tasks, completing them, and letting time march us closer and closer to the day of the event. We had various emails going around, discussions, phone calls, and so on, as the tasks were completed. None of these tasks are super time consuming or difficult. It does take help from a few or even many volunteers to ensure that they all get done. And the fine folks behind the SQL Saturday website and SQLPASS HQ really help you stay on track and be busy completing tasks. We had a few conference calls with HQ. A few calls amongst ourselves. In the end, we learned us a great many things.

One of the things that I was in charge of was advertising and trying to get speakers from around the country to come and speak to us. I hit up some of my close SQL friends, and some thought about attending, but in the end, were unable to for various reasons. Others did express interest over twitter to other organizers. Even local folks (these are really those that should be speaking at these events anyway) came out of the woodwork to volunteer their times and talents to present. In the end, we had 4 speakers from the global #SQLFamily traveled to Utah to give of their time and talents. A bunch of local speakers did the same. We thank each of you for this.

Fast forward again to a few days before the event... Sponsors had come out of the woodwork, willing to give us money, in exchange for visibility. The SQL Saturday website and folks behind the scenes of the website are super important to this. Sponsors know that this is good visibility. Some even want to come out from all over the country to show off their stuff, and are willing to pay. This is so incredible to have a central location, and allow us to be able to have this access to sponsors. With money in hand, we were able to concentrate on the last few tasks. Speaker Shirts. Lunch. Speaker Dinner. Attendee Shirts. Gathering swag. Stuffing Swag in bags. Getting prizes. And so on.

With Twitter allowing us to constantly talk about the event, and other speakers and attendees talk about the event as well, we just hammed it up for days and weeks before the event. As it drew closer, and travel took some of us closer to Utah, twitter was the way to get the word out. Planning meet ups, speaker dinner and others things were all discussed as we got closer and closer.

Friday before the event, I left work, ran home and grabbed my wife, printed off speaker names onto papers, and headed to the Speaker Dinner. Along the way, I stopped at Best Buy, and purchased a large giveaway product. Earlier in the day, I had asked the SQL Family over twitter for some ideas. I ended up picking a Flip Video camera. Thanks to Ronald Demeron (Twitter) for the suggestion. He actually won one of his own at another SQL Saturday event.

At the Speaker dinner, we got to meet and remeet a lot of the speakers. Denny Cherry (Twitter | Blog), Nic Cain (Twitter | Blog), Pat Wright (Twitter | Blog), and Randy Knight (Twitter), Ben Miller (Twitter), Scott Heffron and Craig Berntson (Twitter | Blog).

The next day I got to meet the other speakers. Bill Pearson (Twitter) and Jason Brimhall (Twitter | Blog). We even met some other folks like Meredith Ryan-Smith (Twitter), Stefan Nelson (Twitter), Dale Cunningham (Twitter), Lars Rasmussen (Twitter). Even more have joined twitter since the event. My favorite phrase that was used to answer most questions ceased being the typical ‘It Depends’ and transitioned to ‘Get on Twitter and use #SQLHELP’. A few attendees actually got on twitter for this purpose.

During the event, Pat Wright had hooked up a netbook to a large screen TV near the registration desk, and was running tweets with #sqlsat54 hashtag. Folks were able to walk around the event, and see all the conversations happening in the subtext of the Twitterverse directly related to the very event that they were attending. This was a great idea. It was a great addition to the event.

What should you do??

Getting to the event early as an organizer is imperative. This lends to some great networking opportunities, most likely some hard work being accomplished, as well as a fair share of OH tweetables. But don’t fret, if you pay attention, you will find yourself surrounded with both Physical and Logical OH fodder you can tweet about all day long. It’s also imperative that you add to the event during the event, as you did before it, and will inevitably do afterwards. Take pictures. Post tweets. Talk to people. Help people. Clean up empty boxes around the registration desk. Talk to the SQL Stalker while allowing your colleague a chance to escape. They will repay you later in the day by doing the same. Talk to the vendors, make sure they feel wanted and loved, and have what they need to be successful. Put yourself in the hallways, near the registrations desk, remain visible all day long, as often as possible. If you are an organizer or a speaker you will have the shirt to prove it, and should be visible to assist where needed. The only time I went into the speaker rooms was to actually prepare for my sessions.

Back to my report

I arrived a few minutes later than I should have. I was bringing some of the items for the registration table, and hefted them up to the registration table. I took up my post at the table, and simply greeted people as they arrived, explaining the name tags, the raffle tickets, and answering any questions. As I usually do, I mess with people, tell jokes, and tease. I ended up meeting quite a few of the attendees and ended up talking with them throughout the day because of these initial talks. I hung out at the registration desk for a couple hours in the beginning, as my first session wasn’t until just before lunchtime. As I dew nearer to the time, I migrated to the speaker room to run through my slides one last time. I went into the room I was assigned and setup about 15 minutes early. This is such an unnerving time for me. Standing there in front of various folks in a room, setting up my laptop, and realizing that this is it. I am running the show. I’m in charge of the room. It’s up to me to set the tone and get it going. No one else is there typically to do these tasks for me. It’s just me. Each time this happens, it’s as if it’s the first time, for a moment. As I setup, I randomly start talking about whatever pops into my head. I talked about my background on my laptop. I talked about the logo we added to the power point template. I just talked about whatever. It’s important to be social at this time, even before you start. It will help get the audience to participate, I believe. Tell some dumb jokes or whatever you need to do.

My first session had 20+ people in it, and we had a lively time discussing Database Switch preparation tasks. My next session was after lunch, and had less people, but the same amount of discussions about Profiler and Trace. Once both sessions were completed, I was able to unlax and simply hang out. I spoke with attendees, speakers, and even one poor woman who was trapped for a moment by the SQL Stalker.

Once the event was over, we all gathered in the big room, the room that Denny got to speak in, and gave out prizes to those that stayed. Tshirts were flung into the audience. And the big prizes were given out to a happy crowd. We ended up giving out some great prizes thanks to the sponsors. Folks were happy, and have even tweeted about playing with their newly acquired toys.

And that’s it. The event that seemed to take forever to get here, arrived a lot quicker than I would have thought it could have. Thanks to all those that had a part in helping fulfill it. Thanks for the lists and suggestions from SQLSaturday creators and PASS HQ. Many many thanks to Pat for doing almost everything for this event. It will happen again. We will repeat, and make it better. We hope to get more speakers, more attendees, and most importantly, more SQL Lurning for the #SQLFamily.

SSWUG presentations October 2010 - LIVE!!

2010-10-21T09:24:00.001-07:00

I actaully am sitting in the Bits on the Wire studios at this very moment, gearing up to present 3 sessinos. I will be speaking about GoldenGate SQL Server Replication Configuration, Database Switch preparedness, Profile and Trace.

I am always honored to be selected to present in this virtual conference and love to do it. It kicks me in the butt to get some topics prepared, and then to get them polished to the point that they may make sense to others. I simcerely hope that my knowledge can be shared with others thru these presentations and help others out there that are struggling with similar items that I work with daily.

I sit here writing notes on paper, to ensure that I actually cover everything I want too. Its tough to get all the information distilled, then ensure that its actually covered.

Just wanted to say some of these things to the blog and document it. So, when i am at home sometime, preparing a presentation, i can recall these excited moments just prior to going live.

Replication : Love to Hate it!

2010-10-02T07:45:00.000-07:00

We use native replication very infrequently at work, but still have a couple boxes using it. During one of our last releases (schema changing) we had an issue with replication distribution agent restarting. It seemed to have mixed up the order of new fields and data being sent to the subscriber, and we would get errors about extra fields or missing fields in the sp_MS proc thats autogenerated.
We edited the proc to add and remove fields as needed. Once we worked our way past all the data flowing and errors that kept coming, all seemed to be working well.

Yesterday, we encountered the same error again, with the same published article and the same proc. It was odd. We went back to the same troubleshooting we had previously tried by editing the proc depending on the errors we saw. However, it simply was not working. Without thinking out of the box, we tried to do the same things as before, but without success. One of the other DBA's suggested we add all the fields but with null defaults. When you think about it, it makes sense. For some reason the data being replicated kept switching from 15 fields to 16 fields. Each time it had 16, and only 15 were being sent to the proc, it errored. By setting the 15th and 16th columns in the proc to nullable, and letting them be used or not, seemed to fix the issue this particular article was having.

One thing I have learned about replication, whichever solution that is used, is that its all a pain in the butt, but it gives you a solution that is super handy, yet difficult.

Regional Mentor and SQL Saturday

2010-09-26T08:24:00.001-07:00

I have been a Regional Mentor for SQLPASS for a while now, and have been over a few different areas. With the last reorg of the areas of User Groups, aligning them more with the Microsoft regions, I am now over my area in the west again. In the southwest, to be more precise.

I have friends and acquaintances in a few of these User Groups, and hope to have more in the coming months.

For my own record keeping, and as full disclosure to any of you that may read this, these are the groups I am going to be assisting. http://www.sqlpass.org/PASSChapters/USSouthwest.aspx

Arizona - Phoenix	Arizona SQL Server Users Group
Arizona - Tucson
California - Los Angeles	L.A. SQL Server Professionals Group
California - Mountain View	Silicon Valley SQL Server User Group
California - Orange County	Orange County SQL User Group
California - San Diego	San Diego SQL User Group
California - San Francisco	San Francisco SQL Server User Group
California - San Francisco & Mountain View Area	Bay Area Microsoft BI User Group
Colorado - Boulder	Boulder SQL Server User Group
Colorado - Colorado Springs	Colorado Springs SQL Server Users Group
Colorado - Denver	Denver SQL Server User Group
Hawaii - Honolulu	Hawaii.Net Users Group
Nevada - Las Vegas	SQL Server Society of Las Vegas
Utah - Salt Lake City	SLC SQL Users Group
Utah - Utah County	Utah County SQL Server User Group

It will be easy for me to deal with the two Utah UGs, as I am intimate with them already.

Luckily I have made some great friends in the Colorado area as well, due to presence on Twitter and Facebook, and simply getting to know these folks thru SQLPASS.

I'de love to make it back to my home state of California more, and get to know those folks. There is even a UG in Mountain View, which is one of the other offices of my current employer. I would think a trip to CA wouldnt be too hard for me to muster up, and drag along my family whom is enamoured with CA.

Arizona is an area I am familiar with, since I worked out of Phoenix offices of one of my past employers quite often a few years back, and I have traveled to Tucson a few times to participate in SQL Server World Wide User Group (SSWUG).

Nevada (Las Vegas) is closer than one would think, and in my early days of being a newlywed, before being a parent, we used to visit there quite often. Before getting married, i would travel thru Vegas often between my boyhood home in California and school in Utah. I would love to visit Vegas more often for UG needs. And an excuse to get away. Its only 6 hours away. It almost begs for a weekend trip.

The last area that I am assisting is the one I have little contact with. I suggested that all RMs get monies from PASS to visit all the UGs they can, and obviously Hawaii would be first on my list of visits. I do know a great guy that lives there.

All in all, this is kinda my attempt at divulging my stewardship and some of the desires of my heart to assist with these UGs.

Now, speaking of SQL Saturday. I had a great opportunity to visit Denver SQL Saturday this actual weekend, but declined. My excuse is that it is my daughter's birthday today, and I didnt want to drag her to a 'old computer nerd convention' (her words). I do think that she would have enjoyed parts of it as a weekend away with dad, but for sure parts wouldnt have been enjoyed. In any event, i skipped Denver SQL Saturday.

Coming up is the Utah SQL Saturday that I am helping out with currently. This one will be easy to help with and attend.

I intend on making plans to visit other SQL Saturdays, as many as I can muster, that are in my area of stewardship. There is one in Phoenix in February. I need to start making some plans to figure out how to get there, and attend and participate.

As other SQL Saturdays pop up, I want to attend.

For those of you that are interested, here are all the SQL Saturdays that are on the docket currently. http://www.sqlsaturday.com/

Event Date	Event Name	iCal
Oct 2, 2010	SQLSaturday #53 - Kansas City 2010
Oct 2, 2010	SQLSaturday #48 - Columbia 2010
Oct 16, 2010	SQLSaturday #49 - Orlando 2010
Oct 23, 2010	SQLSaturday #56 - Dallas (BI Edition) 2010
Oct 23, 2010	SQLSaturday #54 - Salt Lake City 2010
Oct 29, 2010	SQLSaturday #58 - Minnesota 2010
Nov 20, 2010	SQLSaturday #59 - New York City 2010
Dec 4, 2010	SQLSaturday #61 - DC 2010
Jan 22, 2011	SQLSaturday #45 - Louisville 2011
Jan 29, 2011	SQLSaturday #57 - Houston 2011
Feb 5, 2011	SQLSaturday #60 - Cleveland 2011
Feb 19, 2011	SQLSaturday #47 - Phoenix 2011

Kalen Delaney Speaking In Denver on June 9, 2010

2010-05-26T10:37:00.000-07:00

A good friend of mine, whom i have yet to meet, Chris Shaw (blog|twitter) was asking me recently how to promote a a great event that they are having in Denver. There are many many ideas out there for promoting events. This is my attempt at helping out and getting the word spread.

Below is a blurb about the event.

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

Well-known SQL Server MVP and author Kalen Delaney (blog) will be speaking about SQL Server Plan Cache and Recompilation on Wednesday, June 9, 2010 at the Denver Marriot Tech Center on 4900 South Syracuse Street, Denver, CO 80237. The event is free, but you need to register in advance here.

Kalen has written a number of excellent books over many years about SQL Server. She is very well regarded in the SQL Server community, especially by her fellow SQL Server MVPs. If you are in the Denver area, you should take advantage of this opportunity to hear her speak.

What do Statistics have to do with Scripting Jobs?

2010-03-25T08:08:00.000-07:00

We have consistently scripted out our jobs from our Production systems and stored them in source control. We have close to 100 jobs on our main system, which has 4 total copies in an HA environment. We also have support systems that are likewise duplicated with much less jobs. Making quite a few copies of jobs. Lets say 15 systems, with 4 of those systems having 100 jobs. Doing this manually from SSMS will take me some time to accomplish, and only introduces my human element, which much to my chagrin, is not perfect. I cant count the times that I have generated all the jobs, stored them in source control, then as a failsafe simply counted the jobs, and compared against the files I generated only to find i was missing a few. Which ones? And the search is on. Needless to say, this can be a tiresome chore, a time-suck, and an overall frustration in the rear. Why not automate this, you say? Great question. The first few times, we needed these stored in source control in a time sensitive way, and simply did it the awful painful manual way. When time alloted us, we attempted to find some solutions to perform this task in a more easy fashion. We scoured the internet for other solutions and ideas, and settled on a VBscript that uses DMO to produce script files for each job. We plugged this code into a DTS, and away we went, somewhat blindly.

The next time someone on the team needed a job, they could simply refer to source control. There they were all, awaiting some DBA to use them. Said DBA opens the file, and attempts to load the job on a system, only to encounter an error. Odd. Face goes into that half scowl, half frown as they attempt to dig into the job code and understand why it failed. After a few stabs at it, its realized that this code will not work as is. How many other jobs will not work? how long have we been backing up jobs via script and it not working? Well, a smart man would say, since we introduced the automation to save us time. Back to the drawing board to find out what is wrong. In a quick overview, a couple things were wrong with these scripts. There is an sp_Add_Category proc call that was missing a named parameter. @name was not referenced, and the command failed if the category was not present. The servername was included sometimes, and not others. But the worse was that the job steps were not in the proper sequence. The script would attempt to create step 2 before step 1, and, well, SQL Server didnt seem to like that. So, any job that had more than 1 step, was potentially scripted incorrectly, and useless. The other issues were issues, but this step thing was a show stopper. Yeah, back to manual job script generation we go. I can actually script out 100 jobs in about 8 minutes, if i am not interrupted. Click the job, hit F2, copy name to clipboard, hit escape, right click job with mouse, hit S, then C, then F, which will select menu options to Script to file, paste name into File dialog, hit enter, move to next job, repeat. Nice and automated, huh? Not!

A coworker started digging at the VBScript code, as well as other options. After a bit of time, made the discovery that the code was ultimately hitting a table (sysjobs) that had its statistics out of date. Another index was being used, step name, and this was determining the order for the output of the DMO code. After updating statistics on the sysjobs tables, viola! Steps were now in order. Wow. Odd. Wow.

So, we set to talking about this. We had auto statistics configured on this database. However, statistics dont get kicked off until a percentage of the table changes. There are 100 jobs on the system, and they have been there for a long time. We may add 1 or 2, maybe 5 in a year. Not a lot of change. So, I could see how they may have gotten out of whack, and we not even really known, since its such a lowly piece of the system. But with a weird side-effect.

With statistics updated on the systems, and some slight modifications to the VBScript code, we once again have an automated approach to generating Job Scripts for each job, so that they can be saved in Source Control. And this time, we are testing the scripts generated, to ensure that they actually create jobs, and can execute successfully. No more assuming they are good, only to find all the steps out of order.

sqlPerspectives guest blogger

2010-02-25T08:33:00.000-08:00

Last week, I got a unique opportunity handed to me by Chris Shaw (blog|twitter). He allowed me to review a section of the Deep Dives MVP book that most of you have heard so much press for recently. This was a great chance for me to read from the experts and comment on their writings and insights. As we all know, we are the community. We are the lifeblood of this great SQL Family of folks trying to help others as well as ourselves. We are writing, we are mentoring, we are training, we are working with SQL Server. My first thought was who am I to actually comment on something like this, but that is the Evil TJay that is always trying to beat me down, and bruise my ego. Luckily the Good TJay won out that day, and I agreed to read and comment on a section.

I took this as seriously as my first book report way back in the day. I read it all, and reread it, and as I wrote my comments, read it again. I really wanted to be accurate, through in my perspective, even a story or two from my own experience. In an effort to become a better writer myself, this is exactly the type of thing that I relish. So, thanks go to Chris for allowing me to comment. Thanks to Jeremey Lowell as well for assisting with this small endeavor. People I have never met, giving me the opportunity to grow, what a great community we exist in.

Anyway, without any more flowery language and metaphors, I present the tweet I saw this morning, with the link to the guest blog I produced. I hope that someone enjoys it as much as I did preparing for it, and executing it.

DataRealized Blog: @tjaybelt is the sqlPerspectives guest blogger this week! http://bit.ly/9v0Qyf #SQLServer #SQL #SQLBlog #microsoft

I hope to do more, grow more, learn more. You are all part of this experience of mine, and hopefully I am part of yours on occasion.

Name that Caption Contest

2010-02-12T18:04:00.000-08:00

Another contest is afoot

Thomas LaRock is running a contest to caption the header you see above.

I submit my entries for the contest here.

"I can see my house from here"

"They all look like ants from up here"

"Wish I could open this window"

"This is one of these rare moments of peace between Database requests"

"Its Monday, this is gonna be a great week!"

"Did any jobs fail last night? I should check soon"

"I wonder if there are any databases i could shrink..."

"I can do this!, I'm a DBA!"

My daughter suggested the following

"A dream becomes reality"

"Looking out to the life ahead"

"I wonder what's for dinner"

my wife suggested :

"Looking toward my future"

SQL in my every day life? really?

2010-01-28T10:06:00.000-08:00

In my goals post, I mentioned my love for data. This doesn't mean that I am amazing or extraordinary at gathering and reporting on said data. Just that I like it. I wanted to tell the tale of an instance of data use in my personal life.

Before we get to the SQL, I need to deviate to Churchy stuff for a moment.

In my church, the members of the congregation are allowed to participate in the inner functions of the local church. We get the opportunity of accepting a 'calling', and performing some tasks. This opportunity lets you interact with other members of the congregation, volunteer your time and talents, and assist in the church. Depending on the 'calling' you may have, you may spend a little time or more time. In the past, I have worked with the Cub Scouts, have taught Sunday School lessons, have been on the party planning committee, and so on. My current calling is different from all these, and has me more closely working with the week to week duties of keeping the church working. We meet each Sunday for worship services. At one of the meetings, there is an opening prayer and a closing prayer. I am in charge of seeking out the members of the congregation and getting them to pray in these two prayers each Sunday. Not a huge task. But over time, you find some interesting things. People remember the last time they prayed, and if they have any qualms about doing it again (nervous, shy, etc) they will typically recall the last time they prayed as having been more recent than reality, regardless of when this prayer actually occurred. Other people will never volunteer to say a prayer, and I need to remember to not ask them. Other people will always say yes, and would end up praying each and every week asked. I need to refrain from asking them too often.

So, now you understand a little about what I am doing. To keep track of who has prayed, and try to not repeat too frequently, as well as avoid asking some that can't/won't, I started keeping a simple list. We'll call this DB 1.0. It was on my palm pilot, and each week I would write down the name of the person that performed each prayer, next to the date. I kept doing this for sometime, and as time marched on, I created quite a list. I could refer to this list fairly easily in the first few months, and see who had prayed recently or not. I was even able to tell some folks that recalled praying recently, that in fact it was a bit longer than they had remembered, and could show them the actual date on my list. My little list grew in fame with some folks.

This all started in July of 2007. Fast forward to today. I have a record of every prayer (both opening and closing) since that time. All in a note on my phone now (having retired the palmpilot from daily use).

A couple weeks ago, I finally imported this data into a database. I have a table of prayers. I also have a table of members. After some scrubbing, I was able to link the names in my note to the IDs in the members table. I added another table to indicate if they could pray or not. Also added a field to the members table indicating if they are enabled or not. Since people have moved in and out of the congregation, I need to keep them as a member, but make them disabled if no longer present. From here, I was able to create some simple views, and procs. I got tired of looking up the ID to assign a prayer, so made a proc to AddPrayer. This will look for the name in the members table, and use the ID it finds. If it cannot find it, it'll add it to the members table. Now, I can quickly add new prayers each week. (keep in mind that I still keep the list on my phone, and add to it.)

Some fascinating data was able to be pulled form this database. I could generate a list of the people that had prayed most recently. By getting the max date, and sorting by that, along with the count of prayers they had performed, I could have a list of those I had picked on most recently. I could reverse this list, and show those folks that have not prayed recently, or at all. This allows me to more easily spread out the load to those folks I do not normally contact. Since so much of the church and membership is a social animal, I find myself in a certain few circles, spinning around, and not including the entire membership. This way, the DB can suggest some names of folks I would not normally think of.

Stepping away from SQL for a momment... So, those of you that are religious may be asking yourself if applying stored procedures and data mining to choosing a prayer is the proper way. To get religious on you, I do rely on being guided by the Holy Ghost as to whom I select, hoping that my selection will fit into some great plan that I am not privy too. This has happened on more than one occasion, and the individual I selected was 'supposed' to pray that day, for some reason, and I was merely the instrument in that action here on Earth. This has been an incredible experience, when it happens. But it doesn't happen each week. But at the same time, how can I say that by my making a database to keep track of all this, and using the tools it provides is not also some aid in the 'Grand Plan'? Besides, its fun to have found a chunk of workable data, with an end goal in mind, and be able to assist my weekly task with this data. Its been great fun in thinking of different ways to view the data, and make a story out of it. It turns out that my wife is not the most asked person to pray. I tend to lean on her if someone else was unable to pray, doesn't show up, and so on. I would have thought she would have had the most prayers. But no, she doesn't. Who does? Well, I know, and can prove it with data.

Back to SQL. This has been an interesting journey to gather this data, 2 records at a time each week. The story it tells will not fascinate most people, but those in my church are interested. Not all, mind you, but some think its a great thing to have captured all this, and be able to report on it.

I challenge you to think of a place in your life where gathering data and storing it would tell an interesting tale to you and yours. Maybe not right away, but as some point in the future, you can look back, apply some of your SQL knowledge to your personal life, and enjoy the results.