Archive for the ‘Uncategorized’ Category

the-taste-of-alpharetta-ga

Well it’s that time again. Join us at SQL Saturday Atlanta a place to learn and meet new friends while networking. SQL Saturday Atlanta has set the bar when it comes to SQL Saturdays. If you haven’t been to one this is a great first time event!  If you have been to one, but not Atlanta, you will be amazed at the sessions as well as the sheer number of people that attend.

The SQL Saturday team is providing me the opportunity to share with you this weekend and it’s an honor to present at an event like this. I will be presenting my session More than Just a basic Database Backup and Recovery in the afternoon right before the raffle. I promise to get you out the door in time so that you can attend. We all love free stuff and Atlanta has lined up some great sponsors.

Provided is a link to the full schedule.

Caution
What I am showing impacts your registry. Do all the precautionary steps to backup and ensure that if something bad happens when making this change you can get back to a stable state.

About 6 or 7 years ago, I had an issue where my SQL Server performance counters were not available when looking within performance monitor. I went thru the steps of unloading and reloading the counters according to Microsoft documentation and by looking at many of the blog posts out there. The performance counters still would not show.

My next step was to dig into the registry and look at the performance counters entry for SQL Server.

We can drill into the provided registry path by running regedit.exe:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance
*If you have a named instance you will need to choose it in place of MSSQLSERVER*

I found a curious registry value; one I had not seen in the past, “Disable Performance Counters”. Just recently I ran across this little gem again. However, I forgot about it until I started digging further. So this time I decided to blog about it in order to help you, the audience, and me to not forgot about this in the future and save time trouble shooting the issue.

In the provided screenshot I highlighted the registry setting, and you can see the value is set to 1. Having the value set to 1 indicates that our SQL Server performance counters are disabled, and we will not be able to choose them within performance monitor.

Image_registry_prechange

To show this I have provided the following screenshot. Within the screenshot we can clearly see that the SQL Server performance counters are not available to us.

Perfmon_prechange

In order for us to see the expected performance counters we need to modify the registry value to 0. You can do this by double clicking on the “Disable Performance Counters” registry value. Enter 0 into the window and it will convert it to the Hexadecimal equivalent. After you make the change, your registry setting should appear as it does in the provided screenshot below.

Image_registry_post_change

Open up performance monitor, scroll down, and you should see your SQL Server performance counters. Happy monitoring!

Perfmon_post_change

I would like to note when I did my demos, this change did not require a restart of my SQL Server instance or the server I was working on. I have read instances where a recycle of either was required. So please keep that in mind when making this change.
As always thank you for reading and hanging out with me. If you have any questions please reach out.
@DBABulldog
Dan

First off I would like to thank all of the sponsors, volunteers, and coordinators of SQL Saturday Jacksonville. It was very well run and overall a great experience. I also want to give huge thanks to all of the attendees, without you coming on a beautiful Saturday, we would have had an event of just speakers. That would have proved to be interesting. To all the new folks I me,t you all are awesome and I look forward to meeting up again in the future.

I was excited to share my presentation More than Just a basic Database Backup and Recovery with the group. Overall the presentation went well. I thought my cadence was good and you the attendees were attentive when I was speaking. But, yes there is a but for this one.  As I moved forward with the presentation I realized my timings were off. I moved into my demos and the first couple went well. However 50% of the way into my demos I noticed that I had not reset my environment. This did throw some kinks into my presentation, as well as, my demos. I was able to cover some really cool stuff; however, I did not get to the real nuts and bolts of the recoveries. Part of my goal was to show you how to do partial and piecemeal recoveries. Unfortunately time got away from us on Saturday morning. I had done this presentation prior to this but I did not tweak and account for the 60 minutes we had on Saturday.

I received good reviews on the topic and the material that was covered. I also received excellent feedback that provided insight into how the session could have gone better. I have taken this feedback to heart and will be tweaking this presentation for future events.

Provided is a link to a Webinar in which I cover all the topics of the slide deck.

http://pragmaticworks.com/Training/Details/More-than-Just-a-Basic-Database-Backup-and-Recovery

Take a ways from this session:

  • Add to my checklist to reset my demo environment before each session (I have already put these measures into place. Not sure what it wasn’t there from the beginning. )
  • I need to watch my timings. I like to talk and sometimes I can’t get out of my own way…

Follow-up

  • There was a great question with regard to differential backups and how the differential change map behaves when SQL Server decides it would be more beneficial to do a full backup. I plan on doing a blog post on this to show what happens.
  • Prep this session for SQL Saturday Atlanta

As always thank you for reading and hanging out with me and if you have any questions please reach out to me.

Your @DBABulldog

Dan

This is going to be one busy, crazy, and exciting week for the Bulldog. I took a break from SQL Saturdays and the community for a couple of years as I moved between jobs. As of late I have become more active in the community that has given so much to me. I knew Pragmatic Works would provide me with opportunities as well as invigorate me. I didn’t realize how much.

This week I will be doing a pre-con with my good friend Bradley Ball. A Day of DBA Fundamentals: Install, Maintenance Plans, and Security is near and dear to me. We will be providing information that both of us wish that co-workers or friends had shared when we were starting out in the industry. This information is geared toward the individuals interested in become a SQL Server DBA or have just been placed into the role of a DBA. There is still time to join us May 6th!

Then on May 7th I will be giving one of my favorite presentations – More Than Just a Basic Database Backup and Recovery. Some people love fast cars; I really enjoy playing with database backups and recoveries. Every time I do a backup and recovery I think of or find something new. If you are attending the SQL Saturday Jacksonville stop on by my morning session. If you are unable to attend my session over the week I will upload my slides and demos to the site for you to exam.

If you can’t attend either of the sessions, we would love to chat with you at the Pragmatic Works booth…….

Back in October of 2005 (in the long long ago) Microsoft released to market SQL Server 2005. Since this release Microsoft provided DBA’s detailed insight into the server state via dynamic management views (DMV).

In the beginning I did not fully embrace the DMV’s and changes made by Microsoft. In all honesty it was not until the past 3 or 4 years that I started embracing them. Maybe it was part of my natural progression of learning as well as part of my path to becoming a more knowledgeable DBA. I think it is safe to say that each and everyday a good portion of us are learning new uses or even new DMV’s to help resolve issues on our database servers.

One of my all time favorite system tables was sysprocesses. With the release of SQL Server 2005 Microsoft created 3 new DMV’s which have been maintained in all versions of SQL Server since 2005. Dynamic management views sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests where mapped to the sys.sysprocesses system view. I kicked, I screamed and I resisted these DMV’s convinced that sysprocesses provided me with everything I needed to understand about the connections  within my database instance. Yikes! I was so wrong.

A little over 4 weeks ago I was at a shop that had over 2052 + instances of SQL Server 2000. Approximately 75 percent of the environment was SQL Server 2000. We DBA’s at the shop were more likely required to trouble shoot an instance that was SQL Server 2000 requiring the use of sysprocesses since DMV’s were not yet available. I needed to push myself to grab hold of and utilize the server-scope views since my daily routine did not require them. My advice to any of you hold outs is move forward you are being left behind and as difficult as it may seem these server-scope views are our future.

Here are a couple of recommendations and explanations for the views which map back to old faithful sysprocesses. I think at this point it is important to point out that sysprocesses is no longer a system table it has been changed to a system view.

The 1st DMV I always utilize when examining sessions connected to a database instance is sys.dm_exec_sessions. This server-scope view provides us one row per authenticated session and shows information about all active user connections and internal tasks. When returning result sets from sys.dm_exec_sessions I like to look at session_id, Login_time, host_name, program_name, status, transaction_isolation_level and last_request_end_time. There are many other columns that can be returned and are fully documented by Microsoft. You can find detailed documentation about sys.dm_exec_sessions following the provided Microsoft link.

http://msdn.microsoft.com/en-us/library/ms176013.aspx

The second DMV I utilize when examining sessions connected to a database instance is sys.dm_exec_requests . This sever-scope view provides us one row for each request executing within a SQL Server Instance. When returning result sets from sys.dm_exec_requests I typically look at session_id, start_time, status, command, sql_handle, plan_handle, blocking_session_id, wait_type and wait_time. There are many other helpful columns that can be included in your result set such as percent_complete (very cool for backups/restores) and reads & writes. You can find detailed documentation about sys.dm_exec_requests following the provided Microsoft link.

http://msdn.microsoft.com/en-us/library/ms177648.aspx

The third DMV I utilize when examining sessions connected to a database instance is sys.dm_exec_connections. DMV sys.dm_exec_connections provides us with server-level information about the connections in SQL Server. When returning result sets from sys.dm_exec_connections I typically pull back session_id, connect_time, auth_scheme, client_net_address and client_tcp_port. When we were having Kerberos issues with SSRS we utilized sys.dm_exec_connections in order to identify the SQL Server Authentication scheme being used by our reporting system. You can find detailed documentation about sys.dm_exec_connections following the provided Microsoft link.

http://msdn.microsoft.com/en-US/library/ms181509(v=sql.110).aspx

I am convinced that once you make the leap into utilizing these DMV’s you will not look back. You can find all the information sysprocesses provided you with some added bonuses. Once you become familiar with sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests individually have fun writing queries joining them together. Also as of April 2013 we should all see a dramatic drop in the number of SQL Server 2000 database instances we support since it is officially no longer supported by Microsoft. SQL Server 2000 instances will still exist, but the ratio should change dramatically in our favor to SQL Server instances that have DMV’s providing us more detailed information into our server state.

Thank You Flogging Molly for providing blogging inspiration.

 

 

Day 1

October is one of my favorite times of year for many different reasons. One is the changing of the seasons; the second is our family trip to Cataloochee Valley North Carolina. Having lived many years of my young life in Londonderry N.H., visiting this part of the Mountain chain floods my memory of many positive events.

To this point this trip has not disappointed.

Friday evening we left the Taylor house at 11:15 PM, right on schedule. The drive up was a peaceful one. About 4 hours into the trip we started noticing the outdoor temperature change. The outside temp was in the low 60’s, mission 1 accomplished. Mission 1 was to find cooler temperatures. At 9:30 AM we pulled into Waynesville N.C. Our hope was to attend the Apple festival. Man it was packed, so in our own best interest we decided to skip the festivities. We moved on and decided to hit the Blue Ridge Parkway. We hit one of our favorites, Water Rock Knob. Or as our 5-year-old says “Water Knock Rob.”  About 2 years ago our Poodle Callie of 13 years needed to go onto a better place. This was one of her favorite spots to get wind in her ears. This day would not have disappointed. It was a cool 55 degrees with about a 20 knot wind. Very refreshing. We had a very cold hard Salami and Butterkasse cheese sandwich. It was time to move on.

Best Poodle Ever

 

 

 

 

Upon getting in the truck I noticed a light on the dashboard. Missy (Wife of 16 Years) pulled out the manual. Thank you, Honda. Well that is interesting, the low pressure light is on. We had experienced this one time before when we had the truck in the snow, so we thought nothing of it. I went as far as checking the tire pressure. Nothing really out of the normal, and I did not hear the leaking of air. We decided to come down off of the Blue Ridge and head down into Maggie Valley, a city made for our Motorcycle friends. By the way, the concept of free air has not left this town. I filled the low tire with air and the light went off. Issue resolved at least that is what we thought.

We have been looking for property of our own up here, so we decided to head back to Max Patch and get a lay of the land. About 30 minutes into the trip I noticed that the low tire pressure light was on again. I pulled over into a local parking lot and check out the tire. This time my worse fear was realized, we had a slow leak in the tire evidenced bythe all too familiar sound pssssssssssssssssssss. O.K. this is serious… time to get into town and resolve this issue. Living in the Tampa area one cannot throw a stone without finding a tire retailer. Not so here in the mountains.

The search for a tire shop should have caused panic. However, I am quite proud of the family. Missy was doing Google searches attempting to find a tire shop. Pretty much everything was either closed or very far away. We remembered seeing a small gas station back in Jonathan Creek which appeared to be open and had a sign that said brake and tire repairs. To the gas station we headed.

Once at the gas station we noticed quite a bit of activity. A couple of the technicians were working on a big ole’ RV. They were fixing a tire; yeah we are in the right place. I pulled up and spoke with Nate. Upon talking with him I felt like we were in good hands. Nate was able to jack up the truck with all the gear in place. He quickly pulled off the tire and found the gash in the middle of it. Dang you highway debris. Michelin makes a great tire, but something to keep in mind is that they are difficult to patch. On his third try the patch took for Nate. We were all very happy including Nate. I went in to pay for the Patch and we were done with that part of the adventure. Before leaving I went to give Nate something for his troubles. He would not accept any gratuity from me and was very gracious about it. He kept saying it was too much. Even at the small dollar amount Nate kept saying “It was too much”. It soon became evident that Nate enjoyed what he did. He would not accept our gratuity, and I was close to insulting him. I shook Nate’s hand and we were on our way to the cabin.

Jen & Gary’s Cabin ( Our friends Cabin )

http://www.cataloocheemountaincabin.com/

 

 

 

 

Sitting in the Cabin in front of the fire watching my wife read and my son play, the day’s events gave me hope. It also made me think.

Often as DBAs or Developers we are not often accessible to each other. Especially after late nights or early morning production calls. We are those little gas stations in the middle of nowhere. However, we are the people who often make it happen. Well a little gas station called Sorrels in a place called Jonathan Creek made it happen. This goes back to the old Cliché, “Don’t judge a book by its cover”. We passed the Sorrel gas station much earlier in the day. Based on how Sorrels looked I passed without stopping. This was a huge mistake on my part. Often times we pass on things because they are not the most appealing or most glamorous. This latest life lesson with the tire gave me glimpses into the past. Some of my most challenging projects and or tasks have been those that were either not glamorous or appealing. Many of these have brought me to today and have provided me with this incredible time with my family in the mountains.

This event also gave me pause and hope for the state of the United States. I found a group of individuals in the small town of Jonathan Creek working at a gas station by the name of Sorrel. These are hard working Americans helping a town survive. Swing on by and talk with these folks and experience what I did that day. This town could have easily given up, but everyone here is surviving and trying to make it to the next set of glory days.