EarthLink

Made2Mentor’s MacGyver Moment Meme

David Stein (@Made2Mentor) started a neat new post My McGuyver Moment and tagged Brent Ozar (@BrentO) who tagged me.  In David and Brent’s post they talked about building something from nothing that the company ended up needing for several years.

I to have had my share of shoestring budget projects that I’ve had to deal with putting together.  The one that I’m probably the most proud of was when I worked at EarthLink.  We had a rather large call center, at the time it was a single office, but over time it grew to 7 offices and 3 outsourced call centers, but I’m skipping ahead here.

On the things which our call center supervisors were supposed to do was grab the call center queue stats every 4 hours and email them to management’s pagers.  Needless to say this seemed like a rather big waste of time to actually have a person sum up these numbers in Excel and then send out an email.

So I built a quick VB app which would log into the PBX, gather the required numbers and send out the email.  This was cool and all, and took about a day to put together.  After this I decided to put the data out on a web server so that everyone else that didn’t have access to the rather expensive Lucent PBX software could see the data.  So once everyone thought that was awesome, we look it to the next logical step and started exporting all the call center data and created web pages which would allow anyone to see the data.

Now these weren’t fancy dynamic pages.  They were just static HTML files that refreshed themselves every few seconds and were rewritten by the app I wrote every few seconds.  Now on the server side of things the PBX server that I had to connect to was a closed system meaning that my only option to connect to it was via the management tool.  At the time there was no scripting support in the app, so I had to write an app which would send keystrokes to the client app and export the data to flat files, then process the files into the needed html files.

This lasted for a couple of weeks until we started adding more groups into this mix.  It would end up taking several minutes to process everything.  I then started loading the data into an Access database and using classic ASP to allow the users to select the data that they wanted to see.  After a couple of weeks of loading data into Access every few seconds Access blew up so we upgraded to SQL Server.

After some time we added more and more call centers to the company we added more and more computers doing processing.  Now when all this started we were running on old P2 400Mhz workstations all running like 128 Megs of RAM for each machine.  By the time we were done with this we had a view computers all these crappy workstation class machines except for one rack mount server (see the picture of my desk below).

By the time I left EarthLink (more specifically I was tossed out kicking and screaming as all our jobs were off-shored) we had equipment in 5 offices in additional to all the machines running under my desk.  There were somewhere around 4000 internal users accessing the web app and we were actually feeding hold time information to the customer facing website so that customers could see what the wait time would be in real time.  By the time everything was said and done we had saved hundreds of thousands of dollars in Lucent/Avaya licensing fees as each new Lucent PBX we brought only the server and only needed the default 5-10 licenses that came with the PBX system.

Needless to say this was probably the biggest spit and chewing gum project that I’ve ever worked on.  The total cost to the company was only a few thousand dollars (the rack mount server).  Our SQL License was covered under our Enterprise Agreement, and all the workstations were older machines which weren’t in use any more by Tech Support (they liked it when we stole the old machines, it gave them an excuse to order new ones).

Now, as for the people that I’m going to tag I’m going to go with some people that don’t always get pulled into these.

Jessica Moss (@jessicammoss)

Geoff Hiten (@SQLCraftsman)

Tom LaRock (@SQLRockStar)

Jessica and Goeff haven’t been going a lot of blogging recently.  Hopefully this will help them start writing more often.  Tom blogs like crazy, and one more just isn’t going to hurt him.  I picked these three because I’m curious to see what these folks have had to band-aid together with duct-tape over their careers.

Denny

Making SSMS Beep

Today I read a post from my good friend Colin Stasiuk (aka @BenchmarkIT) where he talked about making SSMS beep when a query has completed.

I didn’t know this was such a well guarded secret.  Which leads me to a story to share.  I had a supervisor (Ed) back at EarthLink who made it a point to have story time during his pre-shift meetings, those were the best days to be working.  I remember that Ed was on vacation once and our assistant supervisor Dennis was running the team.  Well instead of telling us a story, he brought in a kids picture book to read to us.  It was awesome, there were about 80-90 tech support reps all sitting in the break room hanging on every word from a book for 2 year olds.  Funniest sight ever.

But that’s a whole different story than the one I wanted to share.

One one particularly long night at EarthLink, back in 2001 or so (I was using SQL 2000 at the time), I had a lot of database data changes to make (I have no idea why at this point).  Probably business rules had changes so data needed to be changed, and indexes needed to be changed, or something.  Needless to say SQL was going to be running queries all night long.

Well, having already worked 12-16 hours that day, I didn’t really want to stay up all night watching SQL run.  So I cranked up the speakers on the PC as load as they would go (I had some nice speakers so they got loud), and set Windows to as load as it would go (I’m a heavy sleeper) and started the first of the T/SQL Scripts.  I promptly curled up on the floor with my pillow and blanket that I had brought in and went to sleep.  About three hours later I was woken up by the loudest “Windows ping” ever.  It really scared the crap out of me, but it did the job.  I checked what ever it was that needed checked and started the next script.

This happened probably three or four times through out the night.  The next morning I took a shower (we had showers on site for just such nights), and changed my clothes (my wife was kind enough to bring me a change of clothes before she went to bed) and went on with my new work day, after technically pulling an all nighter that I slept through about 99% of.

If only all, all nighters were that easy.

Denny