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.




No comments:

Post a Comment