Allen Kinsel - SQL DBA

Community Choice Sessions at the PASS Summit 2010

Allen Kinsel — Wed, 21 Jul 2010 13:15:58 +0000

Its Still Alive

Yes this blog is still alive! I recently had some hard decisions to make with regards to what I needed to drop in order to get at least 3 hours of sleep in a night. And unfortunately for the 3 of you that read my blog, it was the blog that lost that battle. I should be getting back in the habit of writing now that the biggest crunch time for the program committee at the PASS Summit is over.

What happened?

Short answer : The PASS Summit speaker selection process. (Program Committee Manager role)

Longer yet short answer: The PASS Summit program committee leadership position is a very involved volunteer position, taking more hours of my time than I can count (Just ask my wife!!). For the last month the 4 amigo's (Jeremiah Peschka (Blog|Twitter) , Lori Edwards(Blog|Twitter), Elena Sebastiano (PASSHQ), and myself) have been working tirelessly towards the first goal of announcing the community sessions. With that out of the way the PASS work doesn't stop, we get to do other «fun» stuff. Innovate with PASS if you like, because that's what we're going to try to do!!

Community Choice Sessions

Ive talked about this before and I'm happy to say, after quite a bit of work in ironing out the details, it is going to be a reality. We're in the process of building the pages, but I can already say that 20 Speakers are going to get a second chance at being selected to present one of their sessions at the Summit. In order to make this happen, we went over the summit track selections, looking at alternates first, then other sessions that were ranked highly by the review teams. We collected 20 sessions that we thought could fill in gaps in the educational offering, or that we thought deserved another look. Essentially, we took the work the selection teams did and distilled it down to give the community 5 session choices in each of 4 different groups

We split up the sessions into 4 groups, 1 each for the tracks AD/BI/DBA and then added a fourth as a sort of menagerie containing sessions across all 4 tracks. We plan on using the «PASS voting booth» to facilitate the voting in each category, so each «registered PASS Member» will have a vote for a session in each of the categories. At the end of the voting period, the session in each category that has the most votes will be confirmed & put in the lineup to be presented at the 2010 PASS Summit in Nov.

Next week we should have all of the details finalized and Ill write more about it then but, for now be on the lookout for an email from PASS with more details about how you can help directly shape the sessions at the 2010 Summit.

Sql Server and SSPI handshake failed error hell

Allen Kinsel — Thu, 17 Jun 2010 15:18:00 +0000

The infamous SSPI Failed error strikes again!

One of our SQL servers was generating these errors for «some» Windows logins but not all.

Error: 17806, Severity: 20, State: 2.

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.1]

Error: 18452, Severity: 14, State: 1.

After exhausting all of the normal troubleshooting for this error (accounts locked, disabled, Sql Service accts, bad connection strings, SPN's, etc.) I spent the next few hours learning more about the way SQL handles authentication requests than I had ever wanted to know.

The Scenario –

A couple of separate individual Windows ID's started generating these errors while attempting connections, all other windows logins were working properly. The connections were initially happening through applications, but also occurred through sqlcmd. When logged in to the server locally with the offending ID's the connections to SQL would succeed.

The Troubleshooting process –

Check all the regular SSPI issues, I wont bore you with the details as they are easily searchable

A relatively easy way of checking the «easy» authentication issues If possible/appropriate is to log into the SQL Server locally with the offending ID and fire up sqlcmd and connect to the server via sqlcmd –Sservername,port –E (by specifying the port you force TCP/IP instead of LPC, thereby forcing the network into the equation)

Verify whether the login is trying to use NTLM or Kerberos (many ways to do this but simplest is to see if there are any other KERBEROS connections on the machine)

SELECT DISTINCT auth_scheme FROM sys.dm_exec_connections
If Kerberos is in use, there are a few additional things to verify related to SPN's, since only NTLM was in use on this server I skipped that

Determine if the accounts were excluded from connecting to the machine through the network through a group policy or some other AD setting

After all of these checked out OK, I began to try and figure out what the error code 0x8009030c meant, turns out, its fairly obvious what the description is : sec_e_logon_denied. This description was so helpful I thought about making this server into a boat anchor but, luckily for my employer the server room is located many miles away and has armed guards.

Since I knew we could logon locally to the SQL Server with the ID that SQL was rejecting with logon denied something else was trying to make my life miserable.

We didn't have logon failure security auditing turned on so, I had no way of getting a better error description, As luck would have it though this would prove instrumental in finding the root cause. To get a better error message, I found this handy KB article detailing steps needed to put net logon into debug mode.

Say hello to my new best friend! -- nltest.exe

After downloading nltest & using it to enable netlogon debugging on the SQL Server, I got this slightly better message in the netlogon.log file

06/15 14:15:39 [LOGON] SamLogon: Network logon of DOMAIN\USER from Laptop Entered

06/15 14:15:39 [CRITICAL] NlPrintRpcDebug: Couldn't get EEInfo for I_NetLogonSamLogonEx: 1761 (may be legitimate for 0xc0000064)

06/15 14:15:39 [LOGON] SamLogon: Network logon of DOMAIN\USER from Laptop Returns 0xC0000064

The error code 0XC0000064 maps to «NO_SUCH_USER»

Since I was currently logged in to the server with the ID that was returning no such user, something else was obviously wrong, and luckily at this point I knew it wasn't SQL.

Running «set log» on the server revealed that a local DC (call it DC1) was servicing the local logon request.

After asking our AD guys about DC1 and its synchronization status, as well as whether the user actually existed there, everything still looked OK.

After looking around a bit more I discovered this gem of a command for nltest to determine which DC will handle a logon request

C:\>nltest /whowill:Domain Account

[16:32:45] Mail message 0 sent successfully (\MAILSLOT\NET\GETDC579)
[16:32:45] Response 0: DC2 D:Domain A:Account (Act found)
The command completed successfully

Even though this command returned «act found» it was returning from DC2. (I dont exactly understand why the same account would authenticate against 2 different DC's based on a local desktop login or a SQL login but it apparently can)

After asking the AD guys about DC2 the light bulbs apparently went off for them as that server actually exists behind a different set of firewalls, in a totally different location. While DC2 would return a ping, the console wouldn't allow logons for some reason. After a quick reboot of DC2, and some magic AD pixie dust (I am not an AD admin, if it wasn't totally obvious from my newfound friend nltest) the windows Id's that were having trouble started authenticating against DC3 and our SSPI errors went away.

Interesting tidbit -- During troubleshooting, I found that this particular SQL Server was authenticating accounts against at least 5 different DC's. Some of this might be expected since there are different domains at play but, I haven't heard a final answer from the AD guys about whether it should work that way.

The solution

Reboot the misbehaving DC, of course there may be other ways to fix this by redirecting requests to a different DC without a reboot but, since it was misbehaving anyway, and the AD experts wanted to reboot so we went with that. A reboot of SQL would have likely solved this problem too but, I hate reboot fixes of issues, they always seem to come back!

SQL Saturday 35 Recap

Allen Kinsel — Wed, 26 May 2010 16:08:00 +0000

I made my way to Dallas to take part in the SQL Saturday #35 that the NTSSUG was sponsoring. After reading the excellent reviews from Jonathan Gardner, AJ Mendo, and Bill Fellows. I thought Id keep this very short and say «I concur!» --Profound aint it?

Ok, Ill dig a little deeper--

I saw some great Sessions, met some great people, and caught up with some old friends. All In all a great way to spend a Saturday!

Having had more than a little experience with putting on large SQL events, I think the volunteers and organizers of the event really need to pat themselves on the back, they pulled off a wonderful event and did it with style. In my opinion, one of the best measures of success when trying to tell how well an event «worked» is to gauge what the event looked like to a normal attendee. By that measure Id say the even the toughest critic would say it was a resounding success. Everything looked buttery smooth, and this «normal attendee» didnt see any obvious distractions. It looks like the SQL Saturday model appears to be maturing at a very rapid pace, and this is excellent news! In talking with the organizers at the after party, they were relaying some of their more interesting challenges, I'm not sure I said it at the time (maybe too many beers??) so Ill repeat it here for posterity: I hope the organizers aren't too hard on themselves because no matter what level of prep, thought, or organization you put into an event, something is ALWAYS going to slip through the cracks and/or not work quite as expected. How these «challenges» opportunities are handled is what separates the avg from the great, and in this case everything was great.

1 general observation I do have –> The after party of the event was, as usual, one of the great benefits of this event and I made plenty of contact with others at Humperdinks and while it was obvious that a few of the speakers, and a most of the volunteers showed up, I suspect I was one of less than 10 «regular» attendees that attended. This is a tragic situation and something that we in the SQL Community need to figure out how to increase participation. The information&contact info gained from the social pieces of community events is an aspect that most attendees are obviously overlooking. I've often wondered if it was apathy or just lack of communication about these events and this time it was obviously not communication, It was pretty tough for anyone to overlook the after party event at #sqlsat35, I saw the after party mentioned in at least 3 different places in the guide book, not to mention verbally at the closing reception. I'm not sure how we can encourage people to attend these events but, they are a core part of the «connect» piece of the equation.

I'll be attending SQL Saturday 35

Allen Kinsel — Tue, 18 May 2010 13:44:00 +0000

I'm happy that I finally figured out how to clear my schedule enough to get to Dallas and soak up some free SQL Server Training

If you happen to be at the SQL Saturday #35 in Dallas this weekend, please introduce yourself. Ill be there and as always wearing my (Ugly)green SQLPASS 2006 laptop bag.

I really wish PASS still gave out real laptop bags, you'd be surprised how many people will stop and ask you about it if they're SQL Server related. Its too bad they decided to do away with this ice breaking networking tool in favor of a 1 time use, throw it in the trash poly bag.

I'm excited that I'll finally be able to experience a SQL Saturday firsthand. Sure its about a 6ish hour drive but I'm sure the experience will be worth it. Not to mention being able to catch up with a few friends who I've spied on the attendance list

Expect a post after the event with my first SQL Saturday impressions

Special Houston Area SQL Server group meeting

Allen Kinsel — Mon, 17 May 2010 13:01:00 +0000

Want to learn SQL from a master (or even better a pair of masters?) Have a free evening? Live within a reasonable drive of Houston? You wont want to miss this presentation. Over the years I've had the opportunity to listen to hundreds of different SQL speakers and 2 people who would make my short list of «don't miss presenters» happen to be presenting at a HASSUG meeting this month.

If your not in Houston, I'd recommend using the LiveMeeting link!!

The following Info is from the http://houston.sqlpass.org site

Special Evening Meeting in THE WOODLANDS!

When: Tuesday, May 18, 2010 – 6:30pm-8:30pm

Where: Woodforest National Bank

25231 Grogans Mill, Suite 550
The Woodlands, TX 77380

Topic: Essential Database Maintenance

Presenters: Kimberly Tripp & Paul Randal, SQLSkills

LiveMeeting Link for May 18 presentation –

https://www.livemeeting.com/cc/usergroups/join?id=HASSUG_WOODLANDS&role=attend

Online portion of meeting to begin at 7pm

Conference Call for audio – 1-888-320-3585 (passcode 76027128)

PASS Summit Call for Speakers Now Open or “enter now for your chance to win!”

Allen Kinsel — Fri, 14 May 2010 13:45:00 +0000

Hey, I bet you've heard by now that the PASS Call for speakers is now open.

No matter what your skill level as a public speaker, I would encourage you to submit an abstract. Even if you think you're not good enough or don't have enough speaking experience, go ahead and submit an abstract. It may be your lucky day and your abstract will be selected, at worst it will be practice in the art of composing a good abstract.

Why will this be good experience? After looking through thousands of abstract submissions for years I've decided that composing a good abstract is an art. It requires equal parts black magic and pixie dust to make a good abstract, and even when you have that it takes a bit of luck to get it accepted at a national conference.

In order to help potential speakers understand why their abstracts weren't selected, last year I asked the review teams to tag each unaccepted abstract with a reason that it couldn't be accepted. As you might expect this created quite a bit of extra work for the teams but, for new speakers there should be good value in knowing whether the reason for no selection was the competition, a poor abstract, lack of interest in the topic, or some other reason noted. The system isn't perfect by any means because we don't have the means to provide a detailed reason for no selection but, hopefully its useful.

One final reason I think everyone reading this should submit an abstract—> if during the submission process you select the «speaker bureau» checkbox, your info will be used by other PASS events needing speakers so you will have more potential exposure that will help you get the experience needed to speak at the Summit.

Important pages I would use if I were submitting an abstract to ANY SQL related event

I love examples, and learning from examples is what works for me so the currently submitted abstract list is a wonderful resource for learning how to write an abstract, not to mention scoping out the competition http://sqlpass.eventpoint.com/submittedsessions

The definition page – Obviously you cant submit a session if you don't know the rules http://sqlpass.eventpoint.com/Definitions

Pre/Post Conference prerequisites - Already an expert? ready to present a full day and get the recognition you deserve? this one is for you http://www.sqlpass.org/Community/SpeakerResource/PrePostConferencePrerequisites.aspx

Understanding the selection process – Want to know how your abstract will be evaluated? This is the place! http://www.sqlpass.org/Community/SpeakerResource/AbstractSelectionProcess.aspx

The speaker resource page – lots of good info here, don't be afraid to dig around http://www.sqlpass.org/Community/SpeakerResource.aspx

Allowing effective developer access to SQL Server

Allen Kinsel — Thu, 29 Apr 2010 14:56:00 +0000

When creating a new application, after going through the entire business analysis & requirements gathering process, normally you wind up with a datamodel that includes many tables and relationships. By this time, depending on the size of the datamodel/system there has been considerable amounts of time invested on all sides. We need a way of preserving this investment of time while still allowing developers to do their thing!

Deploy

Most shops have policies in place for what level of access developers can have in each environment. In many places I've seen, developers are allowed DBO access in development, and some lesser access in the higher environments (read only usually).

After you've deployed the datamodel to the physical database in a development environment, before you grant the developer group dbo access consider all of the time/effort that has been spent making the datamodel what it is. In order to allow the developers to do their jobs but not allow them to modify the actual table/schema layout you can grant a combinations of privileges.

Grant Alter Schema on the schemas where the developers will need to modify database objects (for instance stored procedures and functions)

Grant db_datareader --to allow read access

Grant db_datawriter --to allow write access

Grant Create Procedure, Function, Default, Etc -- Allow developers to do whatever you are comfortable with

Deny Create Table in the database --This restricts all Table based DDL

Optional** Deny Create View, Function, Default, in the database -- Restrict any create/alter permissions as needed.

Important** Alter Schema permissions will allow Alter of ANY object type in the schema that you havent explicitly used a Deny on

Principle of least privilege

This method has proven effective to allow developers to write Stored procs, Functions & Views while still keeping the actual datamodel (tables and relationships usually) in pristine shape. You could also mix and match your own grants/denys on certain object types to allow for unlimited configuration without granting the almighty DBO. Yes, you might say that I'm a paranoid DBA who restricts permissions even in DEV! Of course my great developers would never change a modeled database thereby forcing my hand into figuring out this lockdown of privileges

PASS Program Update #3

Allen Kinsel — Thu, 22 Apr 2010 15:09:00 +0000

Its been a while since I wrote an update about whats been happening in the PASS Program Committee. I just havent had time to write about it with all of the work thats going on in addition to my regular day job. Hopefully Ill have time now to do a better job at this!

The annual content survey was sent out and the results are in, I'd like to go on record now and say, Im not a BI user/admin/developer. We took the BI questions from last year's survey (which were obviously from 2008). Unfortunately, while going through them and updating the questions I didnt reach out to a BI person and get a gut check for the BI questions. So we wound up with some out of date info in that section. I swear we like BI @ PASS, I just goofed, there's not some secret conspiracy, and YES to the 1 of you who asked, I do read all of the comments . The good news, for those that asked, the survey results will be released as soon as we can get them collated and readable (any day now) **UPDATE** The survey responses are here there are definitely some very interesting tidbits to be mined from this.

We are making progress in working on several projects, from redoing the speaker resources, to developing a new system to house the speaker evaluation data. As with all things volunteer driven, these tasks are taking time but thats not unexpected.

The biggest project Ive been spending my time on is the call for speakers. The call for speakers (and resulting abstract review site) is always a huge undertaking. This year it seems to be even more magnified since we're undertaking a new vendor (the same 1 that does tech-ed). There have been quite a few bumps in the road along the way (I wont bore you to tears with all the details) A steady diet of 1-2 conference calls a week and about 50-100 emails a week and we're closing in on a useable product in the call for speakers site. The abstract review site, well that will be the subject of a whole other blog post in the future! Ill just say that right now Im hoping to find some spare pixie dust or at least a few extra rolls of duct tape and bailing wire prior to the close of the call for abstracts

There have been many discussions about changing some of the SOP in the program committee, I have blogged about some of those previously so I wont rehash those here again. Ill just add a few more ideas Ive been kicking around.

1 of the largest things that will effect the average attendee at the Summit is that we're exploring ways to allow 2 new session types this year.

1) Community selection – The current thought is to allow the community to choose from (pre filtered) submitted abstracts to choose a session per track (or some similar method/amt)

2) Best of the Summit– The current thought is to take the top session(s) from the first 2 days of the summit and repeat them on day #3

Both of these ideas have execution issues to overcome but, I think they should be doable for the 2010 summit.

Runaway System Cache Increase Kills SQL

Allen Kinsel — Wed, 21 Apr 2010 13:47:00 +0000

Ran into this a while back, and we finally found a root cause so, I thought Id put it out here in hopes that it saves at least 1 person the amount of head bashing I had with it

Environment

Windows 2003 Enterprise R2 SP2 w/32GB RAM

SQL Server 2005 standard ed SP3 64bit active/passive cluster

We started seeing this glorious message in the SQL Server Error log.

A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration XX seconds. Working set (KB) XXX, committed (KB) XXX, memory utilization 0%

The message varied slightly but the essence was always the same.

This error message can be too common on systems where SQL memory is misconfigured or where something is unduly pressuring SQL for memory. In this case a quick verification of the settings showed that everything was in order. The first 2 times this happened it was the middle of the night during backups(in the SLA window), so no one really noticed a performance degradation. We didn't think much of it at the time but in hindsight, we should have.

The Failure

Monday morning 8 AM, developer makes a bad update to the database, No problem I say, Litespeed can rollback the transaction, So I start to copy the full db backup+tran logs off the server (~25gb) this is the way we process litespeed recoveries through the log reader. About 3 minutes later the server became totally unresponsive, and the error about paging the SQL process memory was logged. At the time I didn't put 2 and 2 together as this particular server runs a varied workload of about 1500 batches/sec and has anywhere from 1200-2500 connections open at a time, so It could have been anything! After some further digging I figured out that the file copies were causing the sql memory to get paged out. At the time I had never heard of a file copy causing an issue in SQL Server!

The experts weigh in

While looking at the issue 2 perfmon counters stuck out--> Memory\Cached Bytes and Memory\Avail MBytes. While file copies were happening the cache bytes counter would increase very quickly while the avail bytes counter would drop, once the available mbytes dropped to 0 sql server started to page memory out. After a bit of paging, the errors were logged that SQL had its memory paged out and SQL became unresponsive. Since this was a high priority system, I did what any good SQL Server DBA would do, I contacted a few people in my network who may have seen this before. Interestingly enough I got the exact same response from every one of them, «use lock pages in memory» and don't use windows explorer to do huge file copies as this is a known «problem».

Even though I trusted my sources of info, I had a hard time believing that file copies of sizes all the way down to 1GB would cause this sort of havoc without this being something Bing+Google would know about (different file sizes mattered, some sizes worked fine, some would cause the problem).

Workarounds not welcome

I had a valid workaround with lock pages in memory and not using explorer for file copies but, I don't normally like workarounds such as this on systems as important as this one is to us. After a few server rebuilds we finally figured out that we could reproduce this issue on any win2k3 R2 ent ed 64bit server, this would be the clue we finally needed to make a breakthrough. After rebuilding the systems from scratch and loading no drivers except SAN we noticed that we couldn't cause the error! So, after painstakingly adding each and every piece of our standard server build we realized that Symantec AV (10.1.9.9) was the cause. Yes, another file system filter driver was misbehaving.

In looking back through the change communication, we ID'd where a new version of AV was pushed out and we just didn't hit the error soon enough after the installation to put 2 & 2 together. Since disabling AV wasn't an option we started trying to find a setting that specifically caused the problem and happened across a change that could be made and allow AV to run and SQL to not get paged out. By unchecking the network scanning options, the windows cache no longer increases during a (network) file copy. problem solved!!

Some time in the future vendors are going to figure out how to write good file system filter drivers, or they are going to stop trying to use them! After fighting this issue for a few weeks (or was it months) I can only hope this happens sooner rather than later

April Houston SQL Server Users group meeting

Allen Kinsel — Tue, 13 Apr 2010 14:16:00 +0000

In Houston? Hungry for lunch? Want to learn SQL Server from an expert? Head over to the Microsoft office and learn the top SQL mistakes and how to avoid them from Kevin Kline

Not in Houston? or hungry? there's always the live meeting option!

Here are the details from the HASSUG Site:

Houston Area SQL Server User Group Monthly Meeting Reminder

When: Tuesday, April 13, 2010 – 11:30am-1:00pm

Where: Microsoft Houston Office

2000 W. Sam Houston Pkwy. S. #350

Houston, Texas 77042-3615

Pizza and drinks provided by Idera.

LiveMeeting Link:

https://www.livemeeting.com/cc/usergroups/join?id=HASSUG_APR2010&role=attend

Conference Call for audio – 1-888-320-3585 (passcode 76027128)

Topic: Top 10 Mistakes on SQL Server

Speaker: Kevin Kline, Technical Strategy Manager, Quest; Immediate Past President of PASS (blog | twitter)