Allen Kinsel - SQL DBA

Whose got my DAC?

Allen Kinsel — Thu, 04 Mar 2010 16:08:00 +0000

What is the DAC?

The Dedicated Admin Connection, Commonly called the DAC is used to manage SQL Server when a regular connection wont succeed. Here's what SQL Books Online (BOL) has to say about the DAC «This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.»

DAC Errors

Occasionally, while troubleshooting SQL servers in a large environment, especially one thats managed from many different geographic locations you could come up with this error, if more than 1 person is using the DAC. It should also be noted this only happens if you have remote DAC enabled in your environment

Error 17810

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

Since I could still connect with a regular connection currently, I set out looking for a query to determine who was using the DAC connection. I whipped this up, and since I couldnt find anything in search, I thought id blog it

select conn.session_id, sess.login_name, sess.nt_domain, sess.nt_user_name, conn.connect_time, conn.last_read, conn.last_write, sess.host_name, conn.client_net_address
    from sys.dm_exec_connections conn
    join sys.endpoints edp
        on conn.endpoint_id = edp.endpoint_id
    join sys.dm_exec_sessions sess
        on sess.session_id = conn.session_id
    where edp.is_admin_endpoint = 1

This should return everything you need to know about who is using your DAC connection so you can ask them to disconnect, or KILL their connection.

Convincing your boss to pay for your training

Allen Kinsel — Mon, 01 Mar 2010 15:11:00 +0000

Things are tight

It seems like budgets are being cut everywhere, and IT training seems to be one of the first places cut. In an earlier post I outlined where you could get a lot of valuable training for relatively little cost. In this post I will explore a few methods that have worked for me over the years to get your boss to say YES to paying for you to refresh your fountain of knowledge.

The direct approach

It may seem obvious but, if you don't ask your employer, they cant agree to send you to a SQLSaturday, or to the annual SQLPASS Summit. So sometimes its just as simple as broaching the subject with your boss. But before you try that continue reading!

Explain the ROI

Many times the people who make the final decisions about training forget what a struggle it can be to not only stay current on technology but to actually excel with it. Last year PASS put together a very good list of ROI points This could be used for any SQL event, make it work for you.

Volunteer

If you volunteer for SQLPASS (at the local or national level) you may be eligible for a discount admission to the annual summit. This discount can range anywhere from 100$ all the way up to a full comp (free) admission. Armed with the discount, and your ROI documentation it can be quite a bit easier to convince your boss to send you to a weeks worth of invaluable training that they perceive to be discounted.

Offer to partner

A few years back this might have been unheard of but, I've recently decided to start trying this method to get approval for some training. Try to combine the ROI documentation with an offer to pick up 1/2 the tab for the training. For instance, split the cost, ask them to pay the airfare to a SQL Saturday, while you pick up the hotel, food and ground transportation. Another idea, offer to pay the airfare/conference registration(Free if you volunteer enough) and let them pick up the rest to send you to the SQLPASS Summit. Either way its a win-win for both parties, you get to make a small investment in your career, and it shows your employer your serious about that career.

Mix and Match

Hopefully you can take a few of these ideas and mix-n-match them to reach the ultimate goal of getting your desired education.

Accessing Top Quality Training

Allen Kinsel — Wed, 24 Feb 2010 16:00:00 +0000

Where did the Training go?

With the economy being what it is, many companies are cutting back on training budgets yet our need for knowledge as database professionals never stops. Luckily the SQL Server community is one of the liveliest around and there are solutions.

Online

I'd estimate that almost everyday of the week you can find live webcasts about various subjects in the SQL Server arena, the problem here is that many of these top quality training events aren't well publicized. I never knew about them until joining Twitter a few years back. As it turns out, many SQL user groups around the world stream their meetings online via live meeting, there are PASS VC's webcasts, Vendor sponsored webcasts, and the list goes on and on.

Local Events

Local events usually put on by your Local PASS Chapter, these are usually monthly events put on by your local PASS Chapter. The added bonus is that these events provide networking opportunities.

Regional Events

For the time being these usually are SQL Saturdays. Held at various metropolitan areas across the US these are currently one of the best bets going for very cheap, very good, education. I would highly recommend that you consider traveling to one of these if they are within a reasonable drive, or better still, a cheap flight

National Events

Generally speaking these are SQLPASS and SQL Connections. I'll just say that these are topflight, all in wonderful opportunities, and if you have the chance Id highly recommend that you attend, everything I said about the other training events, is 10 fold when dealing with a week long immersive opportunity.

PASS TPS #1

Allen Kinsel — Thu, 18 Feb 2010 20:00:54 +0000

TPS reports, love them or loathe them, they are a necessary thing for most of us working in corporate America. I firmly reside in the loathe camp, Just ask my boss.

In this case though, I've decided to start putting out regular updates about what Ive been up to for PASS. I hope the benefit is 2 fold, 1 to allow a greater transparency into what happens behind the scenes and more importantly for me to have a place to reference what we did and when!

Summit 2009 wrapup

We sent out quite a few different surveys during/after the 2009 summit, some of them are listed:

The members of the program committee

The speakers

The «suggestion box»at the PASS booth

The Session Evaluations

The purpose of each of these sets of questions were unique and helpful (some more than others but I digress)

I wrote about the 2009 session evaluations previously

The other surveys were tallied and the results reviewed to see what we could do better, and to see what we're doing well as an organization. I hope to write about some of these results later.

Summit Session Evals

I've written about this one before. We had some technical difficulties with this one this year and I hope to iron it out. One of the bigger mistakes we made this year was tossing the session attendance counts, its a long story exactly how it exactly happened but its already been remedied for 2010. If this affected you, I am sincerely sorry. PASS also uses this info to gauge many things so its a multisided loss. We've got some great ideas cooking on more effective ways to give feedback to speakers from attendees, and as soon as we have something a bit more concrete I'll write about that too.

Software selection/creation

We are currently trying to decide on a direction to go with our Summit software, Its no secret that there have been issues in our current vendors software/DVD's, and we're exploring a few options (build vs buy etc) at the moment. Once we have a decision Ill give an update. Suffice it to say that a decision as important as this one is not easily made and we've had at least 20 hours of phone conferences (demo's included) and emails likely numbering past the hundreds about this. We might be working through it a bit too much but, we have to get this right as the summit software is one of the largeer interfaces we have at PASS, between HQ-Speakers-attendees-volunteers-etc-etc.

Establishing the 2010 Program committee

We began working on updating the applications and handbooks that are required for the program committee, the application was tweaked a bit this year, and the handbook was freshened up a bit as well. Volunteer applications are being accepted through Feb 22nd. Im happy to say that so far we've had 35+ applications, while we wont need 35 people to review abstracts (the most popular job) Im 100% certain we can make use of all the volunteers, if we get a few people that are willing to make the next step and offer some help with leadership, Ive written a bit about this before

Speaker Bureau/Speaker terms

I set out a small group of volunteers with the task of redoing the speaker terms to allow for more «sharing of information» (opt out) between the Summit Speakers and the PASS Chapters. Ive written about this before and Andy Warren has the lead on the actual implementation of this project, we were trying to enable him a bit and hopefully have succeeded.

Establish 2010 Summit Critical Dates

We have a pretty firm grasp of the steps it takes to put on a Summit but, every year we have to go over the steps and make adjustments. We also need to adjust the dates for the current year in order to make the process work. Now that the dates are mostly set, We have essentially kicked the rock down the hill. Once that happens, the deadlines seem to pile up.

SQL/Windows Clustering Stupidity

Allen Kinsel — Thu, 18 Feb 2010 16:04:16 +0000

When is high availibility not?

When its any version of SQL running on a clustered Windows 2003 Server or sql 2005 running on clustered Windows 2008 Server. Seems that while you can dynamically add a disk and use it in SQL when your running a standalone server, if your running in a «highly availiable» cluster configuration you have to take SQL offline to add the disk as a dependency in order to be able to use it within SQL. Here's the proof right from SQL books online.

Im sure there was a good reason to require this at the time but clearly this wasnt thought out in the grand scheme of taking 0 downtime. Anytime a single server has a more highly availiable architecture than your system that is designed for high availibility -->youre doing it wrong, Sorry someone had to say it

SQL Active Directory User ID SID Mismapings

Allen Kinsel — Wed, 17 Feb 2010 14:10:52 +0000

Sometimes login mapping issues exist where you least expect them

This is not the traditional SQL login SID mismap issue that is frequently encountered and discussed here. This mismap was a new one on me so I thought id document it.

When trying to add a new Windows login for SQL Server 2005 or 2008 (probably earlier versions but not tested) you may wind up with this error message

Msg 15025, Level 16, State 2, Line 1

The server principal 'DXXXX\UXXX' already exists.

Contrary to the error message, If we need to proove the account doesnt exist, the following code should suffice

select name from sys.server_principals where name = 'DXXXX\UXXX'

SQL wont let you create an account because it thinks it already exists, but clearly the account doesnt exist.

How did this happen?

If a login was created previously for a windows account that has since been renamed in the active directory you cannot grant the new userid access to the SQL server because the SID already exists in SQL and you cannot duplicate it. This occurs when renaming an account in Active directory, because the SID is reused instead of recreated.

Find the pre-existing SID

The SID from Active directory can be obtained many ways, the one I use is PsGetsid which can be obtained here the syntax you'd use for this is

PsGetSid DOMAIN\USER (or DXXXX\UXXX from earlier)

This will return the SID in the SDDL Format of S-x-x-x-x

Unfortunately SQL Stores the binary representation of the SID not the SDDL format. You have several choices to do the mapping, you can hand figure out the SDDL mappings from sys.server_principals using this page Or the better way -> Todd Engen had found some need to convert this before and designed a great function to do this conversion from binary to SDDL , im only going to list it here for completeness, the original is found here


CREATE FUNCTION fn_SIDToString
(
@BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN
 
  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)
 
  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT
 
  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))
 
  SET @j = 9
  SET @i = LEN(@BinSID)
 
  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END

After creating this function use it like so

select name from sys.server_principals where dbo.fn_SIDToString(sid)='S-X-X-X-X' where the 'S-X-X-X-X' is the SID obtained earlier from PsGetSid

This should return the name of the user that has the offending «duplicate» SID

Once you know the logon ID that has been renamed you can script out the permissions of that ID (hopefully it doesn't own any objects), drop it, and recreate it with the appropriate name and grant any additional permissions

Call for Program Committee Volunteers

Allen Kinsel — Wed, 10 Feb 2010 06:14:02 +0000

The 2010 PASS Program Committee Call for volunteers is open, We're looking for the usual assortment of people to help shape the summit in 2010.

Abstract Reviews

Ever wanted to have an input into which sessions are actually chosen for the summitt? The group of volunteers who do this are chosen from these applications. Its a very challenging endeavor but also very rewarding.

Special Projects

There are literally dozens of separate projects that need to happen every year in order for the Summit to come together appropriately. Things like setting up the mgmt software, reviewing the ppts, reviewing the chosen abstracts for grammatical errors etc. These people will also be chosen primarily from the applications received

Leadership

We need a couple of people who want to be involved in the PASS Program committee at a higher level, who have the time available to commit. No prior leadership experience is necessarily required, although it would likely be helpful. The only way we can succeed on a higher level this year is if we can get a few good people who are ready and willing to commit that extra bit and can help. Also, I have to admit there is a somewhat selfish motive here, I dont see myself staying in this current position forever, and the only way I can move on within PASS (and sleep at night) is if I can start training my replacement.

Application

If your interested in any of this head on over to the survey/application. The application period this year closes on the 22nd of Feb. So please do it now while your thinking about it!!

Image thanks to kev/null

Your relationship with your professional organization

Allen Kinsel — Tue, 09 Feb 2010 11:00:40 +0000

Do you have a relationship with your professional organization?

What is your professional organization? is it in technology? Something like Ineta, PASS, ISUG, IDUG, IOUG or one of the other various technology groups that exist. Or maybe your preferred group is more in the business side of things like ASPA, LOMA or AICPA. If you dont know your desired professional organization, take a second and plug in your favorite search terms + professional organization into «binoogle» and see if the results lead you to a new beginning.

Expectations

If you already know your professional organization, what do you expect of your relationship with your organization? Is it a two way street? Do you give as well as receive? Would you like the relationship between you and that organization to be stronger or weaker? Do they do enough to help you succeed, do they provide value? Do you do what you can to enable their success? A little overly rhetorical probably, but you get the point.

Relationships

Every professional organization (that I know of) requires many dedicated volunteers to succeed. The more help they have the more work they can get done and thus the more value that can be provided to the members (thats you and me!!) The relationship between members and the professional organization is certainly a two way street and both sides need to be at their best in order for either to succeed but, normally the relationship is onesided where the members gain more from the organization than the organization gains from the members, thats why there are thousands of members and hundreds of volunteers. If your involved in an organization, do they get the help they need and use it appropriately? Do you appreciate them? Do they appreciate you? is it worth it?

How can you add value to the relationship?

No matter what you do, or what your interests are I bet there exists an organization that could use your help. Why not step up today and offer your services? No matter what level your skills are, beginner, or Grand Jedi Master, you too can help your chosen org bring more value to its members.

This post is part of the Tsql Tuesday series

Program committee task--Speaker terms revisited

Allen Kinsel — Fri, 29 Jan 2010 15:00:49 +0000

This one has been sitting on my to do list for a very very long time, actually its been on my list since nearly the day after the 2009 SQLPASS Summit closed.

Admittedly Im not a PASS «Chapter leader», nor do I attend the meetings in person more than a few times a year (its 3 hrs roundtrip to the local group during lunch) but, Im always looking for ways we can improve processes at PASS and Ive heard too many times to count over the years that we dont do a good job of helping chapters connect with speakers, or even providing a way for the chapters to contact potential speakers.

The other day I contacted Andy Warren about an idea I had for getting a simple speaker bureau off the ground reasonably quick since I know he's had that on his mind lately. I'd like to think that his post on that subject was part of the fruits of that brainstorming session but, Ill never know. In order to make some of the things we want to do for the community «easier» we need to rework (in a small way) the speaker terms and conditions to allow for it.

Speaker Terms

Every speaker who presents at the PASS Summit is required to sign a contract that protects PASS as well as the speakers, its pretty simple really, even I can read and understand it. What id like to do is integrate a few «optional» opt in type items that would give PASS the ability to manage the connection between speakers, their submitted abstracts and the chapters that need speakers. The other day, I had to tell the Appdev VC that I couldnt refer speakers to them nor could I contact a few speakers on their behalf because we had never asked if we could contact the speakers in situations like that. I suspect many speakers would be ok with occasional contact from PASS HQ when speakers are needed. This should at least give us the ability to do a better job of making that connection

Help needed

I need a couple of people to hopefully form a small group & decide how best to accomplish this goal. With any luck someone out there would also like to lead this small group in making this happen.

leave a comment here or contact me at if you're interested in helping

PASS Program Committee--Defined

Allen Kinsel — Thu, 28 Jan 2010 13:50:26 +0000

I get asked quite frequently about the different PASS committee's and how a person goes about volunteering for them. I thought I'd take a little time and explain what the program committee does and a little about how they do it.

The program committee

The current program committee could easily be renamed «The Summit Committee». This group of community volunteers are responsible for most pieces of education at the annual SQLPASS Summit.

Committee Members

Every year PASS sends out a call for volunteers for the program committee (usually in Jan-Feb). This call is actually quite formal with a few questions asked in an online survey. The answers to these questions are used to match volunteers to tasks within the committee

The Program Committee Structure 2010

PASS Board Member (2010 Jeremiah Peschka)

PASS HQ Elena Sebastiano & Craig Ellis

Program Manager (2009-2010 Allen Kinsel)

Program Leaders (2 or 3 for lg projects)

Abstract review team members
Tasked team members

PASS BOD Member

The program committee always has a BOD member in charge of overseeing everything, they are usually expected to come up with great ideas, keep everything running smoothly and handle the «problems» as they arrise. Actually the BOD members that get tricked into taking over program are very involved in so many decisions I don't even know what they are!

PASS HQ

These 2 are the backbone of «getting things done» and making sure we volunteers stay on task and on schedule. In program, as with most high profile projects, once the deadlines start they never seem to stop and if they start to slip its not good for anyone!

Program Manager

Thats my current job, I wish I could find a job description. Id surely like to see it! Essentially, I like to consider myself the glue that holds the group together and keeps us moving in the right direction. Sort of like a project manager that actually works on the work of a project

Program Management/leaders

These are high level volunteers that work to meet more difficult goals. Such as define criteria and make selections of pre/post-con Sessions and spotlight sessions. Develop speaker resources, develop better evaluation procedures and various other similar things.

Abstract review teams

This group of volunteers, usually 11 people, is split up into teams by track (DBA, BID, BIA, AD, PD) This group gets the daunting task of reading and ranking every single submitted abstract. Then they are asked to choose not only the accepted sessions but alternate sessions. This process is several months long and the bulk of the work usually happens from Mar-May

Tasked Teams

This year I intend to change the requirements of the volunteers on the committee and split up the work a little more. Every year the program committee is asked to do more work since the conference grows annually. So, I've been looking for ways to split the work up even more. This has 2 benefits, one its less work for any one person or group of people. Two, it allows more people to get involved in a great organization.

This year I hope to pull together several task based groups (with leaders) to do things such as pull the session evaluation data together for all years available (2005 onward), review the session powerpoints, revamp the speaker terms, design and test our >proposed< new summit speaker tool, group abstracts, and several other tasks. There should be plenty of work to go around the biggest issue I normally run into is finding volunteers willing to take on leadership of these tasks which leads me to my 2010 program goals.

Goals for 2010 -- I have 1 goal other than a successful summit program, that is to recruit several people into leadership positions within the program committee. It is my opinion that the only way everything PASS needs to accomplish will get done is if I can find a few good volunteers willing to lead tasks & projects.