When working with SQL in a cluster, the account rights on both nodes of the cluster need to be the same
Recently I was working with a clients SQL Server cluster. The managed service provide had installed some Windows patches causing the SQL Cluster to fail over to the other node. No big deal, everything appeared to be working as normal.
After a couple of days we noticed something a little strange. There was a very strange wait type which was showing a LOT of wait type. This wait type was PREEMPTIVE_OS_GETPROCADDRESS which means that SQL Server is waiting on something outside of the database engine to respond. When I looked into the spid which was doing the waiting I saw that it was running the extended stored procedure xp_delete_file. What this file does, in case you aren’t aware is remove old SQL Server backups from the hard drive of the server based on parameters that you specified.
First thing that I did was look at the permissions of the files, they appeared to be setup correctly. the local admin group had full control, users had no rights, owner has full control. Knowing that the SQL Account should be a member of the administrators group on these servers (I didn’t set the machine up, so don’t get me started on minimum permissions). However when I looked in the admin group for this node of the cluster, the SQL Account wasn’t a member of the admin group. I jumped on to the other node and it was in that machines.
The reason that this was a problem is because of the way that NTFS handles permissions on new files when the user is an owner of the folder and has full control rights. Because the folder is owned by the local admin group, and the SQL Server was a member of the local admin group when the files were created they inherited the rights from the folder which were admins had full control, users had no rights, and owner had full control. Except that in this case ownership of the folder and the files was built in\Administrators which also carried down to the files. So when the SQL Account came through on the second machine looking to delete files it didn’t have the rights because it wasn’t in the built in\Administrators group any more.
Fortunately fixing this problem was pretty easy. I simply put the SQL Account in the local admin group on the misconfigured node and scheduled a short outage to restart SQL on that node so that it could pickup the new permissions. Then the long waits went away and the older backups were able to be deleted as they should be.
If you’d like to read more about why you don’t normally want to have the SQL Server running with admin rights and what the minimum needed rights means might I recommend you check out my security book Securing SQL Server (paperback | kindle | website) available on Amazon.com and other online retailers.
I’m very please to tell you all about my new storage blog on sqlmag.com titled “Troubleshooting SQL Server Storage Problems“. On this new blog (there’s just the one post for now, but that will change shortly) I’ll be talking all about SQL Server and Storage and how these things should be working together.
My sqlmag.com blog is all about helping you solve your storage problems, so the blog will work best with your questions, issues and problems. So please post your questions on the blog, post them here, or email them to me and I’ll get them answered (I will only include your name and/or company if you ask me to) so that not only can we get your questions answered and your problems fixed, but we can help other peoples problems solved in the process.
Last night the NETDA User Group in Redmond was night enough to ask me to present to their group while I was up here this week. It was great talking to the group, and it was great giving a presentation at the Microsoft Corporate office.
As promised here’s the slide deck and sample code which was shown. Everything is included except for the linq to SQL code that I captured from my production environment.
I had a great time talking to the user group, hopefully I’ll be able to present there again in the future.
The 2011 conference season is starting to come to a close. There are only a few large conferences left such as VM World, SQL PASS, and Dev Connections. Many people are currently working with their bosses to get sent to these conferences.
However while going through these conversations here’s something else to keep in mind. Odds are your company has just started going through the 2012 budget process. Now is the time to get some requests in to attend some conferences next year. When talking to your bosses about conferences for 2012 don’t just request one conference, request them all. The way that the budget process works is that your boss starts with a big number, and slowly hacks that number down to get to a number that his/her boss can approve. If you request a single conference with a budget of say $4000 for ticket, hotel and flight and it’s time to reduce that line item, there’s only one place to go, $0. Boom, no more conferences for the year. However if you has a team of 3 people, and you all want to go to one conference request a budget line item of $96,000 (3 people * 8 conferences * $4000 each). When asked for a list of the conferences be prepared to provide a list.
- SQL PASS
- VM World
- EMC World
- Dev Connections (There’s a couple of those)
- Build (This is similar to what PDC used to be)
- Tech Ed
- Oracle Open World
There are also other events that you can attend which may require some budget, some a little less, some a little more depending on the event.
- SQL PASS Rally
- SQL Excursions
- SQL Cruise
- Tech Cruise
- Oracle Cruise
- SQLskills Immersion Events
I’m sure that there are plenty of other events which you could find and attend. If you start with a nice high budget as you go through the process you’ll probably end up with enough budget for a couple of people to hit a conference or two throughout the year.
Good luck getting through the budget process, and hopefully I’ll see you at some of these conferences.
Have you been using SQL Server “Denali” and want to get your voice heard? Now’s your chance. Tech Target is looking for SQL Server “Denali” users (either CTP 1 or CTP 3) to interview for an article that they are working on about SQL Server “Denali”.
To get your voice heard contact Jason Sparapani (jsparapani AT techtarget DOT com) and he’ll take care of you.
That’s right SQL People, the .NET folks at the Redmond, WA NETDA user group has invited me to come and speak at their user group meeting on Monday August 8th, 2011 in the Building 41 cafeteria on the Microsoft Campus.
I’ll be giving a session which I’ve titled “Getting Back to the Basics of SQL Server” where we’ll be going back over the basics of accessing data within SQL Server. The slide deck isn’t very exciting so I’ll get it posted after the meeting with the sample code that I’ll be showing. The topics which I’ll be talking about will be useful no matter the version of SQL Server that you are working with from SQL Server 6.5 to SQL Server “Denali” (to be fare some of the stuff is SQL Server 2008+).
The doors open at 6:30pm and the free pizza starts at 6:45pm. I’ll be starting about 7:10pm. Once we are done some may go out for some beers afterwards (at least us out of town folks).
Hopefully I’ll see you there,
I’m pleased to say that I’m one of the great speakers who will be presenting at the Dallas Tech Fest on August 12th and 13th at the University of Texas at Dallas. Sadly I won’t be able to join everyone on the 12th, I’ve got commitments in Redmond until 5:30pm Pacific Time on Friday, but I’m taking a flight on Friday night so that I can see everyone on Saturday and present my two sessions.
Currently I’m presenting “Indexing Internals” at 12:45pm on Saturday and then at 2:15pm on Saturday I’ll be presenting “Optimizing SQL Server Performance in a Virtual Environment”. The schedule is subject to change, but hopefully won’t as I really like those time slots. Tickets for the two day conference are just $100 and are on sale through August 10th. Just go to the Dallas Tech Fest home page and you’ll see the Event Bright order form right there in the middle of the page. It should be an awesome event, I can’t wait to speak there for the first time.
Anyway, I can’t wait to see everyone; I’ll be there until Sunday afternoon when I have to head back to DFW so I can head home.
See you there,
Denny Cherry here, your PASS party planner. PASS is getting closer and closer, just 12 weeks away or so. You’ve probably got the basics figured out like are you going, which hotel will you be at, getting your flights setup and figuring out if you are joining the pre-PASS fun at SQL Saturday 92 in Oregon the week before.
However there is so much more to the SQL PASS experience than just going to sessions at the conference. While the official conference ends at 5 or so, that is just the beginning of the day for the seasoned conference attendee. You should be spending about 5-10 minutes in your hotel room between the time that the sessions end and when you go to sleep (which is hopefully around 2-3 in the morning); any more than this and you are doing something wrong (in my opinion). The reason that I cap this at about 10 minutes is that there are lots of people to see and talk to and plenty of parties to attend.
Every year I hear from people that they didn’t know what to do after hours, or where to meet up with people, so I’ve taken it upon myself to fix this. If you are spending time in your hotel room doing anything besides sleeping or fixing some emergency production problem (god knows that these can happen); stop it.
Some nights there are official parties like the Welcome reception on Tuesday night, the exhibitor reception on Wednesday night, and the Microsoft Customer Appreciation Party (which I would assume will be Thursday).
There will also be some unofficial things to look forward to. For those who are shutter bugs, there’s the photo walk usually on Monday headed up by Pat Wright (blog | @sqlasylum) which is a great time and a great way to meet some new people. The photo walk started a few years ago with just a couple of people and has turned into a pretty large group that goes walking around town just taking pictures and chatting. You sure don’t need to be a great photographer to go. I’ve been any most of pictures are awful (check out my Facebook photo page (soul sucking registration required) for reference material). Sadly I didn’t get to go last year as I was busy presenting a pre-con. This yeah hopefully I’ll be able to attend with my point and shoot all ready to go (this will be an upgrade for me, two years ago I just used my cell phone camera).
The big party event of the week is the most unofficial party of the week. That is the Karaoke party at Bush Garden (no, not Bush Gardens the theme park Bush Garden the Karaoke bar in the International District). SQL Karaoke will be Wednesday night starting at 9:30pm on Wednesday night until 2am, unless the fire marshal shuts us down early, which hasn’t happened, yet. We start at 9:30pm because that’s when the DJ shows up. The bar opens before that so feel free to show up and grab a table. Tables are hard to find as the place is a little small, so if you plan on sitting get there early. For those that don’t like to sing don’t worry, singing isn’t required to attend SQL Karaoke, just the desire to have a good time and watch some people make fools of themselves. There have been a few videos which have been taken and posted online over the years. If you want to get a general idea of the insanity check them out here, here, here, here, here (this is our favorite DJ singing our favorite song), and here (do you get the idea that we really like SQL Karaoke?). If you don’t come to check out the signing, at least come to check out the couch in the men’s rooms (I’m not kidding).
When all else fails there’s always a default place to go at PASS to find some SQL people to hang out with. That is Tap House Grill on the corner of 6th and Pike just across from the Sheraton hotel (about 1/4 block down 6th). The Tap House is always popular with the SQL folks thanks to its location less than 2 blocks from the Seattle Convention Center, and less than a block from the Sheraton where a good portion of the PASS attendees will be staying (including myself).
I can’t tell you how much I recommend that you check out these after events and the other ones which will be getting put together as we get closer to the PASS summit. There are also a number of private parties which happen on various nights at PASS, some hosted by Microsoft, some by venders like Quest, Red Gate, SQL Sentry, etc. which you’ll need an invite to attend. All of the public events will be published up on the passsummitevents.info website (there isn’t much up there yet for the 2011 summit, so if you are planning something be sure to get it posted) so be sure to check there as we get closer to PASS to see what parties you can fit into your schedule. Joseph Guadagno (blog | @jguadagno) who setup the passsummitevents.info site for us was nice enough to put together a blog post on his site on how to use the site and how to setup events. There’s even an iPhone app for the site. I highly recommend hitting at least one or two of the after events and meet some people. Everyone is welcome, even the locals that can’t attend the summit can join in the after parties.
I’ll see you somewhere at the PASS summit. I’ll be the guy running around with a lamp shade on his head, and shot of Jäger in his hand.
This question came up on ServerFault a while back and a wanted to expand on the solution more. The basic problem that the person was having was that the iSCSI disks were taking a long time to come online which was causing the SQL Server to crash when the server was rebooted.
The solution that I came up with was to change the SQL Server service to make it depending on the disk drivers to ensure that the disk drivers were online before the SQL Server attempted to start. Fortunately this is a pretty quick registry change which can fix the problem.
To fix this, open the registry editor (regedt32) and navigate to HKEY_Local_Machine\system\CurrentControlSet\Services\MSSQLSERVER\ and find the DependOnService key (if it isn’t there for some reason, create it just as shown with a type of REG_MULTI_SZ). Edit the DependOnService key and set the value to “Disk”. Click OK, close the registry editor and restart the server.
Once the key has been set the SQL Server will no longer start up until after the disk drivers have been started.
This will work for all version of SQL Server that are installed on a version of Windows which has the registry (so probably SQL Server 6.5 and up).
As this is a registry change, don’t be making this change on all your servers. Only tweak the registry if you know what you are doing. If you kill your system by editing the registry it’s your fault, not mine. I don’t want to hear about it if you delete a bunch of keys by accident, on purpose, because you didn’t read this correctly, etc.
This last Saturday I spoke at Dallas Tech Fest, where I had two great groups in my two sessions. As promised here are the slide decks for the two sessions which I presented.
I hope everyone had as good a time at the conference that I had.