Sunday, April 26, 2015

First "real" job with SQL.

My next job was the first one I got directly due to my knowledge of SQL.  So, why not go right to the source?  Microsoft SQL Server support.  Unfortunately my stint here was pretty short due to finding it incredibly boring.  Worked there for about 9 months, last 4 or 5 months as Server Down support, including being the only person in the US supporting SQL on  Sundays.  Guess the use of our choice in databases has grown a bit since 1997.  It was a very rewarding job in many ways, just the calls where getting very repetitive and I was getting major worries about stagnating in my skill set.  One can only do so many database corruption fixes in one week before things get really boring.

One day my mentor from NDS called.  2 weeks later I was employed there as a SQL Consultant.  So after years of complaining about those Highly Paid Consultants, I was one of them.  5 years with the company I did quite a few interesting things, and a lot of not so interesting.

This being the heyday of companies starting to switch from legacy applications (AS/400, DOS, Novell) to Windows, and then to internet, there was some good times and a lot of helping companies out getting the most out of their new systems.  Many times this meant helping management understand what we could do with Windows, and a lot of it was just to teach them to do away with old ways of doing things.  Fighting bad habits became a habit!

Some highlights from this period, names of clients withheld to protect the guilty.

Wrote an application that had to load millions of records a night, and then do processing of the records.  This was my very first project as a consultant, and the one I specifically got hired to clean up the mess.  My company had had 2 developers working on the problem for 3 months, when I got there it took over 36 hours to load one day worth of records, and they had never got to the point where the records could be used.  The records contained phone call information for a calling card reward program.  The system was to be used in the clients call center to give out the rewards, or dispute bills, because of this the data needed to be summarized up front since at the time the hardware they had for this could not summarize the data on the fly.

This is the ONLY time in now 20 years of working with SQL I used a CURSOR as the solution for performance improvement, and since I was already looking at each record did an update to the summary table while I was in there working on each record.  This solution was a long shot, but the solution the 2 others had come up with looked like it should have worked.  By the 2nd day I had the query down to about 3 hours, which was good enough for the client, but I talked them into letting me stay for the week and had it all down to 30 minutes.  And while it did not make me a believer in cursors, it at least proved that the only answer in SQL is "it depends."

Another fun project was a background check solution.  This one we converted from Fox Pro (an old version of Fox Pro even in 1996).  Initially they requirement was to load the data into SQL, then do a cut to the SQL based system.  The more I learned about the system this seemed more and more strange to me.  After a while I convinced them that we could make a lot better system if we could just ignore the old data, and start from scratch, since their data was useless after 90 days I convinced them to just run the two systems in parallel for 90 days.  Since I guaranteed them the new system would be a lot faster if we did this, they let us proceed this way.

Besides that, this was an interesting client.  Small family run company with rather relaxed dress code.  One day the owner came over and asked me why I was always dressed up with a tie etc.  Explained that it was the required dress code for all out consultants.  He told me promptly that as long as I was on site at his company, I was no longer allowed to be dressed up.  Jeans and a t-shirt.  At the most.  A few weeks later my boss came by to check on the project and was mad as hell about me not being dressed up, calmly explained to her that it was the clients requirement.  My company changed our dress code to be "average level at the client +1."

One day mid 1997 I was sent to a client to check in on their performance problems on a SQL Server running on 4 Pentium Pro 200 CPUs, which at the time was big time metal.  I solved the problem within 5 minutes of walking in the door.  I felt kind of bad about this since we charged 4 hours minimum for my time whenever I had to go onsite.  I walked into the CIO and told him that and asked if it was anything else I could do for him.  "Yeah.  Give me spec for your dream SQL Server," which I proceeded to do.  He then leans over the phone and asks the person on the line if he got that (I had no idea anyone was on the phone!).  Turns out he had HP sales person on the line, got the quote ($100,000) and ordered two of them.

When I got back to the office I told my boss what happened.  She got mad that I did anything outside the scope on the contract.  Told her I thought it was a good thing and that we would get more work from it.  I ended up being at that client for 2+ years working on ETL systems for some of the major oil and food companies in the world, during most of that time we had between 1 and 4 other employees there helping me out as well.  All because I was 100% honest up front.  So much so that the CIO after a while banned their sales people and my boss from putting foot inside the company, he would send all requirements for personnel through me and take my word for it that someone was good enough for what he needed.  Off course the sales person on the account got all the commission.

While working for NDS I also got fired from a client for the first and only time in my career.  Got called on a Friday night with a performance problem at a major newspaper.  Through some intense work we managed to cobble together enough performance to get the Sunday paper out, after about 80 hours of working straight I was getting pretty tired.  In that time: we had moved it from running on a DEC Alpha based system due to a bug in the ODBC code that kept corrupting the data, this involved BCPing the data out and in, de-normalized the database since it was too normalized from SQL 6.5 too handle (hint: when street number is normalized you might just have gone too far), I had made enemy out of someone that was a professor at a University that had designed the database by calling him incompetent (ok, I just told him he needed to get out in the real world a bit more), and an assortment of other problems.

About 10 AM on Monday morning, I am sitting in their operations center with the CIO on my left, their DBA on the right (both had stayed with me the entire time!), and the CEO behind me.  I'm looking at some performance numbers on the server and the CEO comments "that is not how our DBA does it" whereupon I answered "your DBA couldn't get the paper out, I did."  Shortly thereafter I went home and went to bed.  A few hours later I was woke up by my boss who informed me I had been fired by the client, but she couldn't figure out why since she always had very positive feedback on me.  Told them the above, and figured that was it.  10 minutes later the CIO and DBA call me to tell me, says it was not their call but could not stop the CEO.  They still wanted me to personally find them two SQL devs/DBAs to help them out.  Good times!

To be continued.

Tuesday, April 21, 2015

Stupid Stuff I have done. 2/?. Sometimes even a dev server is not a good dev environment

Sometimes you can't win for loosing.

Two weeks since I took the job as the first DBA ever for a company, we upgraded from SQL 2000 to SQL 2005.  They had had a consultant in there to do the prep work, then let me loose to do the upgrade.  My personal, in production experience with SQL 2005: none.  Good times!

This, of course, sounds like the beginning of a horror story for DBAs.  One we have heard over and over.  This time it was however a fairly pain free upgrade.  Couple of DTS packages that had not been properly converted to SSIS, a few performance issues, but nothing major that stopped business from going on.  Success!




After a couple of weeks in production, I started getting curious.  Some of the problems I saw stemmed from Access based queries taking up way too much time.  Being new, I had no idea about who or what used Access, or how much it was used.  A few casual questions to the developers and some IT managers did not yield too many good answers.

This is when I think to myself "how could I track how often Access is used?"  I could do a trace, but that seems so old school.  "hmmm aren't there some new DDL triggers for 2005?  ohh what is this? Login triggers?"  Yupp.  Time to play with login triggers.

Maybe I can make these actually see what application the connection is coming from.  It's in sp_who2, which would mean it has to be part of the login process somehow....  After some digging, sure enough, I find some information about how to do this, go about my way coding a login trigger, with inserts into a table of the login information I am interested in.   Simple enough right?

As soon as I executed my code, the first line after it in the output says:
"You have been disconnected"

uhhoooo

This can't be good.  Try to connect again, nope, try with the sa account,  nope.  Did we enable DAC (Dedicated Admin Connection) on this server, surely that would get me in?  NOPE.

Oh this is NOT good.  About this time I stand up to walk over to my boss.  Developer after developer head is popping up over their cubes.

At this point I am actually laughing as I make my way over to my boss.  Tell him I managed to get everyone, including myself and the sa, locked out of the DEV server.  Also let him know I am going to try to restart it in single user, admin only mode.

Fingers crossed as I shut down the service (thankfully THAT worked).  Find the proper command line command and start up the server again.

ok, command window says it is up.  Start up management studio and try to connect.  SUCCESS!  Disable the login trigger (ok, I admit it, I deleted it completely just to be sure).  Restart the server in normal mode.  Phew.  Server works, developers can get back in,  everyone are happy.  Anders is a (somewhat) hero.

So what happened?  XML.  Or rather, a tiny bit problem with the xquery to get the data I needed out of the login data to put it in the table.  This caused the insert to fail, which rolled back the login.  Yikes.



  So what did I learn from all this?  Sometimes even a development server is too important for a DBA to develop and test on.  Sometimes it calls for your very own SQL install, what I like to call a throw away SQL install.  Since then I have always had a local to my own PC SQL install to test these kind of things on.


Monday, April 13, 2015

Stupid stuff I have done. 1/? Or, How I learned to stop GUIing and love the script



Most of my years as a DBA, I have worked on my own.  Later off course aided by great web sites such as SQLServerCentral.com and others.  But as far as day to day work, I have had to figure it out by myself.  Rarely have I had anyone to turn around and ask questions from.  Luckily in my current job I have an extremely competent co-DBA, we bonce ideas off of each other all the time.  And argue about the best way to do things.  A lot.  This definitely improves both of our quality of work.

However, learning SQL, I have made a lot of silly mistake.  Luckily not a single one has been job or career ending.  And all have been a learning experience.  Luckily my normal paranoia when it comes to making sure the data is safe and recoverable, has saved me every time.  So far.

I hope to make this into a series of smaller blog posts about stupid things I have done, and how I averted disaster.  Hopefully, some day, someone's day can be saved.

Once upon a time, well, early 2000, I was called up by an old friend about a job with a retailer, this one was the leader in the US in their particular niche book selling business with about 75 stores nationwide.  My job there would be to oversee SQL development, including setting up replication of inventory to each store, as well as getting near real time sales data back to headquarters.

After about a year in development and testing, only interrupted by celebrating receiving my US citizenship, we went to production.  Most things are working fine, a few minor glitches with replication, such as too many jobs for the level of hardware we had, so we start adding the features we had postpone until after launch.

Come a few months and we had the first major upgrade to do.  At least the first one that caused us to have to make upgrades on every single server at remote locations.  Keep in mind, this was in 2001, I was on a slow DSL from home to my desktop computer at work.  I then would remote from my work computer to all the remote servers.  Some of which where on 64K lines....

Maneuvering around the GUI to drop items, things where going fine, but it was getting late.  All of these changes had to be done during the time the stores where closed, with East coast and West coast stores, this left us about a 6 hour window, in each time zone.

About 4 (and 60ish servers done) in the morning I get another confirmation box "Do you really want to drop this table?", click on Yes, move on.  Wait?  Where did my database go?  From the lag of slow connections, and getting complacent to the point of not reading the confirmation boxes anymore, I had accidentally dropped the database.  Luckily part of my change plan was to back up the local database before making changes, so I managed to get it restored and changes applied in time for the store to open up.

After that I decided something had to change.  From now on, all changes will be in rerunable scripts. Of all the advice I have to aspiring SQL programmers and DBAs, this is #1.  Use scripts!  Even if using the GUI to make the initial change, script it out first, click cancel, then run the script.




Monday, April 6, 2015

A bit about me continued. Anders meets SQL

August 4th 1995 I did my final check flight in an aircraft, earning my multi engine, with instrument rating.  That afternoon I hooked up a U-Haul to the back of my RX-7 and headed for Dallas, TX.  August 8th I started working at American Eagle Insurance.

Within 2 weeks of starting I was in charge of a VB application running with an Access 2.0 back end. Since I was also doing data entry and under writing, it became obvious to me after about a month that this was not a viable solution for the long run.  No one believe me off course, me being a lowly co-op student, and the highly paid consultants that came up with the solution said other wise.  I finally convinced one of the network engineers to hook up network sniffer to prove that Access sent way too much data back to the client.  My estimate was that with the current user load of ~20 people, we would reach stand still at about 80,000 quotes.



Making noise off course leads to being asked "What do we do instead smart ass?" by the CIO.  My off the cuff remark was "I have heard about this thing called MS SQL Server.  It looks like what we need!"  Ahh the self confidence of being young.

This in turn lead to the same highly paid consultants being charged with converting it from Access 2.0 to SQL 6.0.  Big mistake.

When we got it in house, it took two SQL Servers to run.  1 to store the quotes and binds (term for when someone buys a policy), and the other for doing calculations.  If I remember right these two servers where Pentium 60 or 66 CPUs.  Me not knowing better I took their word for this.

When the calculation server started to blue screen every 3 to 4 hours from what appeared to be memory leaks I seriously started looking into this SQL thing a bit harder.  Up until then I just made sure backups worked.  In the calculation database I found tables with our factors we used to calculate the premiums.  I found no code I could look at.  After some research I found these xp_ things.  Yupp, extended stored procedures.  Which again was written in C.  No problem, I know C, let me take a look.  Nope, no source code anywhere.  Call to the consultants: "We had to do it this way, SQL can't do calculations."  What?  "I might not know SQL, but I have never heard about a computer language that cannot do math!  Get me the source code and I will take a look."  After much complaining, up to and including the CIO of the company threatening to pull all the contracts from them, I finally got the source code.  Printed out.  On a dot matrix printer.

On a business trip to visit with AOPA in Wichita in November 1995 I grabbed the print out and the book that came with SQL 6.0 (the actual book used on that trip is in the bottom right corner of this picture):

That was all I had to learn T-SQL from. In those days there was no SQLServerCentral.com, or the myriad other blogs and web sites to learn from, nor did I know a single other person that knew SQL.  All the hint I got from the consultant was "this is a SELECT statement, that is all SQL can do with the data in the tables."

By the time I got back from Wichita, I had all the extended stored procedures re-written to T-SQL.  Back in the office I made my very first stored procedure out of one of these queries, ran it, and WOW I HAVE A RESULT.  Pass that result into the next procedure for the next factor, and WOW I HAVE THE NEXT RESULT.  "This is COOL!!!!!!!!!!!"

Quick implementation on our dev and test server (one thing I did insist on when we went to SQL), then a lengthy round of testing to ensure values matched the AS/400 for the same criteria (we could have a max deviation of 1 cent),   When I, with <4 months of experience could prove the highly paid consultants where absolutely clue less about SQL (experience coding AIM-7s turned out to not be too relevant), that consulting company was summarily fired and we hired in NewData Strategies.



NDS provided us with some much needed expertise, as well as giving me my first mentor in Al Zwanenburg.  The next year and a half was exciting.  Learning set based programming in T-SQL, report writing using Visual Fox Pro, working out how to communicate with the AS/400 so we did not have to type the information in again to actually issue the policies.

Most of my career at American Eagle Insurance I also kept being an underwriter, as well as the Client/Server Application Specialist.  At one point we made an application that we could install in agents offices if they wanted, with this they could enter the information directly into our system, and if the application was within certain limits, it would automatically do the underwriting and quoting on the spot.  If it went above those limits it would notify the underwriters.

The efficiency we created with this system was unprecedented in the aviation insurance industry.  From what I understand from the VP of the division at the time, everyone else used rate books that they would manually go look up all the factors to give a quote, depending on the aircraft there could be up to 17 factors (in our system).  With the AS/400 system we maxed out around 250 quotes in a day, with the new system (after we got it on SQL) we broke 800 in a day easily.  At one point they even had to let half of the data entry staff go since they where no longer needed.  We could take a call, give you quote over the phone and bind the policy in <5 minutes.  The huge time save came when someone wanted options, like different levels of liability coverage: 15 seconds to generate a new quote with the new limits.

Why was this speed important when everyone else took days?  Strangely enough people will buy a new aircraft without checking on insurance first, an agent would typically type up the information about the plane and pilot and fax it to the various insurers.  Very often we would get the business purely on being the first to return a quote.  Our quote to buy ratio at one point was over 3 times the rest of the industry.

With our capabilities to electronically quote we also managed to land the contract for sole underwriter of AOPA (Aircraft Owners and Pilots of America) Insurance.  This was a huge contract that I ended up spending the last year and half with the company supporting both from a database standpoint (handling the specific database code and security for them to hook into our system), as well as being the chief underwriter on the account for a while.  Good times.  Lots of good steaks consumed in Wichita.

By the middle of 1997 it was evident I needed to move on if I was ever going to learn more, the company had stagnated, and so had my skills.  Time to move on to new SQL adventures!

My biggest regret with the first job was not taking a few extra days at a client in Florida, could see the Space Shuttle being moved to the lift off position.  Would have had a spectacular view point from the roof of their building.

To Be Continued.

Thursday, April 2, 2015

A bit about me.























Yupp.  I'm a SQL geek.  Been doing this for a long time, since version 6.0 back in 1995, but never felt I had anything useful to say.  So, why start now after 20 years?  I don't know.  Maybe because I keep getting asked about things regarding SQL from various people, from developer friends, to other DBAs.  Maybe I might just have something to say.

Mostly I want to document some of the fun things that come my way.  Maybe it will get more serious?  I don't know.

So a bit more about me before we get all busy talking about SQL.

I grew up on a farm in Norway.  It’s a bad quality picture, but it has been with me through a lot of moves since 1987.  Growing up we had dairy cows, later some beef cattle, but mostly small grains.  My parents still run the farm to this day, with my sister learning the ropes to take it over.



I was also a boy scout for many years.  A very young Anders.



After much flying around with my dad, much of it while skipping high school and flying in an A-300 to the Canary Islands in the winter months:



I decided I wanted to be an airline pilot and started studying at University of North Dakota's College of Aviation.


While there I developed an eye condition, so decided to tack on a degree in Computer Science as a fall back.  My last spring break in college I rode jump seat in a Scandinavian Airlines Boeing 767 from New York to Oslo.  Even with many hours riding cockpit with my dad, I decided that flying for an airline was a REALLY boring job.

I did finish my aviation degree, with a minor in computer science.  Last year or so at UND I worked at the Scientific Research lab operating their mainframes, Unix servers and Crays.  The geek highlight of my college years where helping out constructing a new Cray Y-MP:




This was the super computer of the early 1990s.  The power in it would today MAYBE be able to run Windows Vista.  Amazing how things have moved along in 20+ years.

One day I walked out of my "office" right into the co-op coordinator for my college.  Someone from an insurance company in Dallas, TX wanted to talk to me about a co-op job.

And this is where this starts be sort of database related!

Since I worked the night shift all by myself I was in sweat pants and a t-shirt, in this I interviewed for my first post-degree job.  With an insurance company.  With the senior vice president of their Pleasure & Business division.  He needed someone to get into the company that understood computers, and not only AS/400s like his current IT staff.  He was about to introduce the first Windows application in the company,  but had none to support it.

The kicker: he wasn't allowed to hire IT staff.  But he could hire an underwriter.  Since I knew airplanes, and that is what they insured, he figure he would get me in doing underwriting, AND support their new Client Server application.  He showed me a few print outs of the database diagram, asked me if I could understand it, I answered yes, and that was that.  April 1995, I had my first real job.  Started in August the day after my graduation.

To Be Continued