SQL RNNR

1 DBA's Professional Blog

T-SQLTuesday #42! The Long and Winding Road

by Jason Brimhall

Categories: News, Professional, SSC, TSQL Tuesday

Tags: SQLFamily, TSQL Tuesday

Comments: No Comments

Published on: May 14, 2013

The Long and Winding Road

It is time for another installment in the monthly blog party for SQL Server professionals known as TSQL Tuesday.

This month we have the pleasure of being hosted by Wendy Pastrick (blog | twitter). The topic for the month requires a bit of introspection (almost like the self-evaluation piece of an annual review). Quoting direct from her blog, here is the gist of the topic:

Here’s what I thought it would be fun to share with the community this time around – we all experience change in our work lives. Maybe you have a new job, or a new role at your company. Maybe you’re just getting started and you have a road map to success in mind. Whatever it is, please share it next week, Tuesday May 14th. Make sure you note what technologies you find are key to your interests or successes, and maybe you will inspire someone to look down a road less traveled.

For me, this is an interesting topic. It was my theme of choice last month with a major announcement (see here). And because of that, I am even using the same image – slightly changed. Only this time, I will go back a bit further into my career and the road I traveled to get to today.

I am going to go back to a decision point in my career that had a huge impact on where I am now. That decision point was shortly after having moved to Las Vegas about four years ago. After having moved to Las Vegas, I made the decision to become more active in the SQL Community. The first step was to regularly attend the user group meetings.

Prior to moving to Las Vegas, I was a member of PASS. I had been to SUMMIT. I knew of the local user group meetings in the Salt Lake City area. I just never forced the issue due to timing etc. This was something that I felt needed to change.

By making that conscientious decision, I became more involved in the online community. I soon started presenting. And before long, I was involved in the scheduling of speakers for the Las Vegas UG.

By becoming more active in the community, my skillset started to rapidly grow. I found myself blogging more and researching more about SQL Server. I really started to learn about SQL thanks to that decision. Prior, I feel I was good. Now, I feel I am much better because I invested more time and effort and I am trying to share the skills that I have learned.

I have said it before and it is worth saying again. If you really want to learn a technology, try teaching it to somebody. By taking on that extra step, you will find yourself researching a bit more and you will find that you may have to answer questions about it that you had never considered until you tried to teach it. Being active in the community has helped me to become better at my trade. I am sure it will help others as well.

May 2013 Las Vegas UG

by Jason Brimhall

Categories: News, Professional, SSC, SSSOLV

Tags: Community, SQLFamily

Comments: No Comments

Published on: May 14, 2013

Spring is in the air, I think.

With that scent in the air, we have a nice juicy topic coming up this month for any and all that are interested.

Chad Crawford will be presenting to the group on the tastiness that is Service Broker.

Service Broker in Action

SQL Server Service Broker is a messaging framework built into the SQL Server engine. It enables SQL Server to handle messaging between servers and applications with light setup and overhead. The flexibility of the framework enables Service Broker to queue event notifications, task execution requests or other messages while leveraging the strength of SQL Server transaction management, reliability and recoverability. In this session we will see how to set up Service Broker, discuss case studies where it has been implemented in industry, and step through a practical example implementing an audit log.

Session Level: Intermediate

Chad Crawford’s BIO

Chad has been working with database engines for 14 years, the last 12 focused specifically on SQL Server. He has filled a variety of roles spanning architecture, development and administration. Chad is currently the Database Architect at Henry Schein Practice Solutions in American Fork. When he isn’t optimizing a query, you will find him running, dreaming about airplanes, or looking for a new strategy board game.

Meeting Details

Attendee URL: https://www.livemeeting.com/cc/UserGroups/join?id=KWRMQ3&role=attend

Meeting ID: KWRMQ3

Lost that Single-User Connection?

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC

Tags: SQL Administration, SQL Script, SQL Server

Comments: No Comments

Published on: May 6, 2013

You have changed your database to single_user mode to do a task. As you go about your business, you lose track of which connection was the single_user connection. You start closing connections and mistakenly close the session that was your single_user connection. Now you are unable to start a new single_user session. It would seem that somebody or something has taken your connection.

Today, I am going to discuss some things you may do to get around this problem.

The first thing that may come to mind when you encounter this is “Oh crap!” Well, no need to get too terribly worried (not unless you really hosed something up and you are trying to fix it real quick before the boss notices).

The next thing you may think of trying is how to circumvent the single_user mode. And during that thought process you may be thinking that single_user does not really mean single_user so you might try something like start a DAC session. Well, let’s go through that and see what would happen in a DAC session if your single_user session is stolen.

I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists. To start a DAC session, I am going to point you to a previous article I did on the topic – here.

To ensure I am using a DAC session, I am going to issue the following query. This will ensure I am in the right session and that DAC is in use.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id 
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName 
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id] 
WHERE s.session_id = @@SPID 
ORDER BY g.NAME; 
GO

In my case, this results in an endpoint with the name “Dedicated Admin Connection” and a spid of 84. Good, I am in the correct session for the rest of this test. Next, I will issue a Use database statement. I have created a test database called ClinicDB. So I will issue the following.

Code block

USE ClinicDB;
GO

I get the following result.

Code block

Msg 924, LEVEL 14, STATE 1, Line 1
DATABASE 'ClinicDB' IS already OPEN and can ONLY have one USER AT a TIME.

So, that blows that idea right out of the water. It shouldn’t really have been a consideration in the first place because single_user really means just that – single_user.

Now What?

Well, what do you think we could do now to circumvent this little problem and get that single_user session back?

That requires a little investigative work. It is time to find out who has taken the single_user session and politely ask them to give it up. To make that task a little easier, we could modify the previous query to find out who has that single_user session (thus limiting how many people we have to ask). I have modified the following query to use sys.sysprocesses so I could limit the results to the ClinicDB. This is a limitation of SQL 2008 R2 and older versions. Getting the database reliably means using sysprocesses. Despite the database_id being available in other related DMVs, it’s just not that easy. One would think you could use sys.dm_exec_requests. But if a request is not active, an entry won’t exist for that session. This problem is fixed in SQL 2012 since the sys.dm_exec_connections DMV now has the database_id field. Enough of that birdwalk and on to the query.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id ,DB_NAME(r.dbid) AS DBName
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		INNER JOIN sys.sysprocesses r
			ON r.spid = s.session_id
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id]
	WHERE DB_NAME(r.dbid) = 'ClinicDB'
ORDER BY g.NAME; 
GO

I chose not to do an entirely new query to simply demonstrate that it was possible with a very small tweak to what has been already used.

Now that you know (in my case I can see that I have a session open with ID = 80 that is connected to that single_user database), I can walk over to the person (knowing his/her login id and computer name) and politely ask them to disconnect.

In the end, this is really an easy thing to resolve. Sure it may take some people skills – but that doesn’t make the task too terribly difficult. Next time this happens to you, just remember you can run a quick query to find who has sniped that single_user session.

MCM Road Less Traveled

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC

Tags: Certification, SQL MCM

Comments: 14 Comments

Published on: April 16, 2013

I began this post back in October of 2012 after learning that I had passed the SQL Server 2008 MCM Knowledge exam. I had set it aside in hopes of polishing it off after my first lab attempt at Summit 2012. Notice I said first attempt? I failed that first attempt. This is a bit of a story about the journey through the exams and the results.

The first attempt was a little crushing. A mix of emotions concerning the lab came over me because I felt I knew the technology. While the results were not desirable – the end effect was desirable and I am glad I did not succeed on that first attempt.

When I took the knowledge exam, I took my time going through the questions and examined the questions. I think that was a good method for that exam – I was trying to ensure I understood the question and didn’t miss anything that was stated. Though I took it slowly, that is not an indictment to difficulty for the exam. I was well prepared and did not want to make any invalid assumptions.

Fast forward a little bit to the first attempt at the lab exam. I had a plan going into the exam. I felt confident in my skills. I felt relaxed with the technology. Then the exam started and my plan went out the window. I started making assumptions about the exam that I really should not have done. I rushed a few scenarios that I should not have rushed because I could have done them better. In the end, I was my own worst enemy during the exam. This is not a characteristic of an MCM. Even when the pressure is on, doing what you know and being methodical is a wonderful asset.

Having failed the first time, I wondered if I was ready to be an MCM. In retrospect, I was not. Leading up to the exam, I did feel that I was ready for the exam. I had read several articles such as the following to try and figure out if I was ready. In the end, it’s more of a gut check and a leap of faith for some.

Gavin Payne http://gavinpayneuk.com/2012/05/01/knowing-when-youre-ready-to-attempt-to-become-an-mcm-of-sql-server/

Joe Sack http://blogs.msdn.com/b/joesack/archive/2010/11/21/how-do-you-know-if-you-re-ready-for-sql-mcm.aspx

I even perused some of the resource type of articles such as the following.

Nick Haslam http://nhaslam.com/blog/2011/11/19/mcmprep-88-970-sql-mcm-knowledge-exam-sqlmcm/

MCM Blog http://blogs.technet.com/b/themasterblog/archive/tags/sql+server+mcm/

Robert Davis Amazon MCM Reading List http://www.amazon.com/Official-Server-Certified-Master-Readiness/lm/R3RB13PQ7D8TKB

Brent Ozar MCM Articles http://www.brentozar.com/sql-mcm/

In the end, this post by Rob Farley sums it up nicely for me (http://sqlblog.com/blogs/rob_farley/archive/2012/12/23/the-mcm-lab-exam-two-days-later.aspx). Rob references an article by Tom LaRock and some of what Tom did and didn’t do. As well as some theory on how to approach the exam at certain stages. Of all those things, I think the best advice I read as well as I could recommend is to get a study buddy.

It’s not sooo much to have somebody to bounce ideas off of, as it is to have somebody to try and teach. I worked with Wayne Sheffield as we prepped for our retakes. The biggest benefit was, as I said, in that I could pick a topic and try to teach Wayne. He did the same to me. And then, we could question each other on what-if type questions about our selected topics. Another benefit is to have a heat-check so to speak. Having a study buddy can help you from straying too far off into tangents or maybe keeping you from wasting too much time on a topic they might feel you understand exceptionally well.

If you are reading this far, you probably have figured that I have taken the Lab a second time. I was debating whether to do it this soon due to life and family. My wife continued to push me to do it (much as Nic Cain experienced here - http://sirsql.net/blog/2012/3/26/achievement-unlockedmcm-sql-server-2008.html). Without Krista pushing me, I might have delayed even longer. It was also helpful to have a co-worker and friend pushing me along too (I’m a little competitive). Thanks again Wayne! You can read his experiences here.

I received my notification email while driving home from Vegas last week. Seeing that email pop up from boB Taylor gets your heart racing a little bit – especially after you have failed the lab previously. I glanced at the email and only need to go to the first word – “CONGRATULATIONS!”

Oh yeah! Vindication, satisfaction, elation, joy and general happiness set in quickly. Memories of that old “Wide World of Sports” show from a long time ago spilled in with the words “Agony of Defeat, Thrill of Victory.”

I am glad I took the time to pursue this certification. This adventure has helped me to learn and grow as a DBA. I am also grateful to those that helped or pushed in some way or another – whether they knew it or not at the time. Last but not least, I am thankful for my employer Ntirety. They understood the importance of this for me and were very supportive in this endeavor.

As one final take away, I was recently shown this link with some interesting questions to help you decide if you feel you are ready to take the exams.

Las Vegas April 2013 UG Meeting

by Jason Brimhall

Categories: Corner, News, Professional, SSC

Tags: Community, PASS, SQLFamily

Comments: No Comments

Published on: April 9, 2013

AlwaysOn – Finally, A usable ‘mirror’!

In the past, High Availability and Disaster Recovery (HADR) had many limitations. Clustering and Mirroring are great, but the mirror/secondary database is not very usable since it is not online. Finally, AlwaysOn solves this limitation by merging both multi-node Clustering and mirroring. AlwaysOn also allows the secondary database to remain ONLINE, so we can use it for reporting purposes without resorting to a Snapshot! Come see this lively session with extensive demos of setting up, configuring and testing AlwaysOn. We’ll also test automatic fail-over using a real web app to see how well this feature works.

Jim Murphy

Jim Murphy has been using Microsoft SQL Server since version 6.0 back in the 90′s. He is the President/CEO of SQLWatchmen, LLC., a managed DBA service provider for smaller companies who do not need a full-time DBA. He runs the Austin SQL Server User Group: CACTUSS Central and sits on the board of SQLPOSSE.com. He has been a professional DBA and developer for over 20 years and has run a consulting company full-time for over a decade.

LiveMeeting Information:

Attendee URL:https://www.livemeeting.com/cc/UserGroups/join?id=834ZNP&role=attend

Meeting ID:834ZNP

page 1 of 67»

SQL RNNR

T-SQLTuesday #42! The Long and Winding Road

The Long and Winding Road

May 2013 Las Vegas UG

Service Broker in Action

Chad Crawford’s BIO

Meeting Details

Lost that Single-User Connection?

Now What?

MCM Road Less Traveled

Las Vegas April 2013 UG Meeting

AlwaysOn – Finally, A usable ‘mirror’!

Jim Murphy

Now Reading

Planned books:

Current books:

Recent books:

Popular Posts

Blogroll

Categories

SQLHelp

May 2013
M	T	W	T	F	S	S
« Apr
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31