SQLLawman

I love the Olympics.  I enjoy watching the competition, and occasionally I enjoy the behind the scenes, human interest story.  But one thing that I struggle with is: Who is winning?  Depending win where you look you can see who has the most medals, who has the most gold medals, etc.  So I am going to throw my scoring into the mix.  Yes, it is probably as convoluted as the scoring in figure skating. (what is the difference between at toe loop and a lutz anyway?)  So here’s my scoring:

1. Every Gold is worth 3 points
2. Every Silver is worth 2 points
3. Every Bronze is worth 1 point

Yep, more points for the winners.  But this is where my scoring becomes interesting.  I divide the number of points by the number of athletes.  I found the number of athletes from http://www.vancouver2010.com/.  What I find cool about this is that figures out which country sent the best athletes.  That’s my idea of who wins.  Since this my blog I get to make my own rules.  So without further ado, here’s the current top 10: Final Standings Updated 2/28/10 2:55 pm EST

Yes I’m serious.  If you are going to the PASS Summit in Seattle there is an opportunity to get FREE BACON.  The SQL Virtualization Virtual PASS chapter is hosting a breakfast and game show on Tuesday November 3rd at 7:30 am and you are invited.  Do you have questions about virtualizing  SQL?  Do you have answers about virtualizing SQL?  Do you not care about virtualizing SQL and just want a good breakfast?  Come join us.  Experts from VMWare will be on hand to answer questions.  There will be fun, prizes, and food.  Now here is where it gets really cool.  We are looking for contestants.  If you are thinking about virtualization, actively engaged in virtualizations or already been there, done that, bought the T-shirt  you may qualify to be in the game show.  Very soon we will have a registration page that will give you the opportunity of a lifetime to play.  So what kind of game will this be?  Have you seen Wipe Out?  A really fun game show about people embarrassing themselves on a muddy, messy, wet obstacle course with “Big Balls”?

Well, this has absolutely nothing to do with that, but it will be fun and energetic all the same.  If you are going to the PASS Summit click here for more information and to register for the event. If you want to ensure there is enough bacon for everyone, please register before the event.  Unfortunately you will not get to opportunity to meet me this  year at the summit, but I’m really hoping for next year.  So register, get some free stuff and bacon.

We are in the process of upgrading a Microsoft product that just hasn’t gotten around to integrating with AD and uses SQL accounts.  It also has a really bad habit of locking SQL accounts if your AD policy for failed login attempts is set low.  So I got tired of searching through SSMS to unlock accounts, so I wrote a quick script to list the locked accounts and the command to unlock it.  I didn’t go as far as to auto-unlock the locked accounts but that would be a simple edit.  Here’s the script.

Declare @UserID varchar(25), @status nvarchar(5), @Command varchar(255), @command1 varchar(255)
Declare Account_cursor Cursor
for select name from sys.syslogins where name not like ‘<DOMAIN>\%’
open Account_cursor
FETCH NEXT FROM Account_cursor
INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN
select @status= convert(nvarchar,(loginproperty(@userID,N’IsLocked’)))
if @status = ‘1′
BEGIN
print ‘– ‘ + @userID + ‘ is locked’
select @command = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, NO CREDENTIAL’
select @command1 = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL’
print @command
print @command1
– exec (@command)
– exec (@command1)
END
fetch next from Account_Cursor into @userID
END
Close Account_cursor
Deallocate Account_cursor

Thank you @unclebiguns for pointing me toward the function that makes this all possible.

I am guessing that this is not the easiest or most efficient way to do this, so if anyone would like to educate me on a better way, I am very open to learning your techniques.

I was asked to find all tables in a database that had a DML trigger so a developer could ensure that all the old functionality was available in the new database.  So once I built my query to give me the information, I thought I’d save it here for future use.

select a.name as ‘Trigger’, b.name as ‘Table’
from sysobjects a
join sysobjects b
on a.parent_obj=b.id
where a.xtype = ‘TR’

This gave me a nice little result set of each trigger and what table it was associated.  Maybe I won’t have to re-invent the wheel the next time they ask.

I was installing SQL Server 2008 on a Windows 2008 cluster the other day and I received and error:  ‘The current SKU is Invalid’.  So I went looking for the resolution.  I came across  a blog post by Vittorio Pavesi that pointed me to the answer.  I did not take the course of copying the install files to the local server.  I was installing from a network share, so I simply moved the file  DefaultSetup.ini to a new location and started the ‘Add node to a SQL Server failover cluster’  again and entered the key included on the DefaultSetup.ini manually.  The rest of the install went without issue.

According to the American Red Cross here are 10 facts about the need for blood donation.

Please consider donating.  Whole blood can be donated every 56 days.

We have a database that holds information that is fairly personal, so much that the application team that supports it may only have read access to the database.  There came a need to occasionally lock all the administrators out of the system for updates.  These updates will occur every few months.  So I created a SQL user and gave that user select and update rights to the necessary table.  Then I created a couple of stored procs, one to disable the logins and one to enable the logins.

CREATE PROCEDURE [dbo].[LockOutAdministrators]
WITH EXECUTE AS ‘EXECUTIONER’
AS
BEGIN
BLAH, BLAH, BLAH
END

I then granted execute to the stored procedures to the application group.  So the application team can only update the table via the stored procedure that does only what it needs to do.  They are happy they have an easy way to do this without me becoming the bottle neck.  If this was a more sensitive system, I’d probably add some auditing, but it is not necessary at this time.

This weekend I was volunteering at the One America Mini-Marathon Expo to help raise awareness of The Leukemia and Lymphoma Society’s Team in Training and I reconnected with a team mate from Team Indiana’s cycle team for the Honolulu Century ride.  When he introduced me to his friend that came to the expo with him, the stated I was a “biking machine.”  Yep, my head swelled.  Now Scott is an athlete.  Less then a month before the century ride he competed in an Olympic length triathlon.  This summer in addition to running the mini, he is completing in an Olympic length, half, and full ironman triathlons.  He even acknowledging that I ride is made me feel so good.  So I’d like to challenge all to read this to be liberal with your compliments.  There is so much in the world that tries to beat us down, that an honestly complementing someone based on your perception of their abilities is refreshing.  Throughout the rest of the weekend, I began to watch how others greeted each other.  I was surprised how much sarcasm and insults were in the greetings.  Now I recognize that this is how we greet each other and no one takes these greeting as insulting.  But I wonder if complementing rather than insulting could change attitudes and turn back the wave of negativism that is so prevalent in our society.  So I am going to look for opportunities to complement and build up, and squelch the urge to break down, even in jest.  Since I am rather sarcastic to begin with, I find this a challenging decision.  Hopefully this will be a step in making this a better world.

I don’t know about your environment, but at mine we have two classes of systems: Prod and Test.  So as you can guess, anything that is not Prod is Test.  That’s a pretty simple definition.  Further defined anything that has a DR priority of 6 or less is PROD and anything 7 or greater (or not graded) is test.  This makes live very simple, or so it would seem.  So that brings me back to my question: Are All test environments created equal?  On the one hand an answer of “yes” lets everyone know what to expect.

• Will the Test system be backed up?
• If it is backed up, what is the retention?
• What level of disk redundancy is available?
• Can Test servers be physical hardware or VM only?
• What is the recovery priority if needed?

This sounds like a pretty straight forward way to go.  But on the other hand does one size fit all? We use many of our test systems for training.  The current production system refreshed the test system, the upgrades are done to the test system and the end users are trained on the new system using the test environment.  New employees are also trained on this system on an ongoing basis.  So here’s the scenario.  A medical system is going live in 2 days.  100 nurses need to be trained. Training takes 2 hours.  The medical facility calls in PRN staff to cover the nurses during training.  Let’s just assume all the nurses make the nation average that I googled a few minutes ago of $45,000 per year + benefits or $21.63 per hour + 39% benefit package.  (yep, made that benefit number up makes it an easier $30.00 per hour labor cost) lets also say the training room can accommodate 10 students.  Just for fun, lets also say the contract with the trainer is for two 12 hour days at $50.00 per hour including expenses.  My calculations work out to about $660.00 per hour of training or sitting if the test system is not available. 

• $30.00 per hour of the nurse in training
• $30.00 per hour of the nurse replacing the nurse in training
• $60.00 per instruction hour by the trainer ($600 per day/10 instruction hours)
• Ten nurses and replacements

I’d hate to image the cost if we were training doctors rather than nurses.  Does that system have the same requirements as the “sandbox” I use to experiment and document the SQL 2005 SP3 upgrade?

So when does this discussion happen?  Usually it involves the SAN/Backup team.  They are charged with providing disk for servers and ensuring that what is on that disk is archived and recoverable within a reasonable time.  I don’t envy their position at all.  They also have to balance backup windows, and backing up test databases uses equipment and storage that the Prod system could be using.  There are only so many data paths and hours in the day and purging old data seems to be a thing of the past.  So I’m not sure a simplistic look at Prod/Test is appropriate.  As we look at tiered storage, tiered version of SQL do we also need to look at tiered test environments or break the model completely and add a couple more tiers.  Prod, Train, Test, Sandbox.  Then place policies and expectations at each level.  How about your environment?  Do you ever struggle with getting other teams to understand the cost of data that is not “Production”?  Of course that leads into the “we want to test this software and see if it solves the problem and now we have 100 users depending on it” issue, but that’s another day.

I have been noticing a scary trend over the last few years.  Home computers have gone from a techno-toy (like a DVD player) to an important part of everyone’s life.  I have also noticed that computers are becoming 5 year assets.  People are replacing them every 5 years.  Today they are inexpensive enough to do that.  But the trend I am seeing is that the old PC is not being replaced, but a notebook is being added to the home computer.  So I have a lot of family and friends with 4 to 5 year old desktop PCs, and hard drives are beginning to fail.  Since most PCs came in as toys, disaster recovery is typically not considered by the home user.  Does the thought of loosing all you digital pictures, your password database that you are allowing your web browser of choice manage for you, or any document you have created scare you?  It should.  A couple of people have asked me my strategy so here it is.  I use a three pronged approach.  I have a wireless network in my house.  Every night at 2:00 am (wow, now that I think of that it is a really bad time to choose now that Indiana is on daylight saving time) *Note to self reschedule backups*.   I copy my documents folder to another PC that is on different power circuit in a different part of the house.  I also copy that data to a USB connected disk.  Please note, this is not a backup plan.  I cannot step back in time and get an old version of a changed file.  But in most cases I don’t need that.  Then once a month, I burn what I feel is most important (pictures, keepass password database, e-mail archive) to DVD and store it off-site.   I point all my applications to store their data in that location.  I am not saying this is the best strategy, but I would like you to at least think about your strategy.  The day after a hard drive crash is not the time to consider this.  Just remember, that e-mail and web surfing appliance is becoming a critical part of your life.  Protect it.