Archive for February, 2010
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.
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 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.
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
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.
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 “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.
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.
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
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)
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
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
RETURN ( @StringSID )
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
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.
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
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.
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