Voice of the DBA

Tag: T-SQL Tuesday

T-SQL Tuesday #47 – SWAG

It’s T-SQL Tuesday time, the second Tuesday of the month when we all coordinate our blog posts on a particular topic. This month Kendal Van Dyke has invited us to take a break from technical topics and have a bit more fun. This month’s topic is asking about the swag that you’ve gotten from an event. This seems appropriate given that there are so many events this time of year. In addition to the tremendous number of SQL Saturdays, we had DevConnections last week, SQL in the City this week, the PASS Summit next week, and SQL Intersection next week.

This happens every month, and if you want to participate, look for the invitation on twitter with the #tsql2sday hashtag. If you want to host, contact Adam Machanic.

The Best SWAG

I’ve been fortunate to attend many events in my career. I’ve been to TechEd, the PDC, SQL Connections, SQL Intersection, the PASS Summit, various TechFests, lots of SQL Saturdays, and, of course, Red Gate’s SQL in the City events. I don’t often get swag these days, but earlier in my career I would grab various gifts and goodies from the events and vendors. I’d often bring back items for co-workers and my kids, some of which I actually still have.

These days I don’t get much swag, though I do get speaker gifts from some places, which feel like swag to me. What are the best items? I’m not sure that I can pick just one, but I have a few things that stand out.

Multi-tools

During the WIT panel at SQL Saturday #222, SIOS sponsored a gift to take back to kids, boys or girls. It was a multi-tool and I grabbed two, one for each of my kids. I even checked my bag (which I never do) so I could bring the small knives to my son and daughter. They loved them, putting them in bags to use when they might need them. I saw my son actually break this out a week later to use the pliers.

Wine

The speaker gift from SQL Saturday #222 in Sacramento this year was a bottle of wine. I thought that was a great gift, and one that’s still sitting in our wine rack, but probably not for too much longer 🙂

Beer Mugs

A few years ago the crew from SQL Sentry gave away silicone beer mugs at the SQL Connections conference. I loved the insulated mug for cold beverages, and so did my kids. I found after a week that I never got to drink from them because my kids were filling them with smoothies, juices, and more. The mugs were always in the dishwasher. I mentioned this to Peter Shire and he graciously gave me 4 more at the next event. That hasn’t stopped my kids from using them at every opportunity, but with 5, I do get to use a few of them now and then.

The Keychain

I’ve gotten a few, and some of them are very nice, but the one that stands out for me was from the SQL Saturday #169 in Denver. The local user group made their own parachute cord keychains that I still have on a backpack. It’s similar to a survival bracelet, the kind of things that Boy Scouts carry, just in case. I love the keychain and while I hope I never have to use it, I have it if I need it.

Lots of Gifts

There are plenty of other things I’ve gotten that have survived. I have bags and shirts from many events. I have pens, USB drives, a couple keychains, and even a few bottle openers. I’m sure I’ve forgotten some of the other items, though to be fair, there were lots of forgettable items.

It’s hard to give out good swag. At SQLServerCentral, we’ve struggled to think of what things people would like. I’ve given away lots of shirts and books since I’ve felt people will keep those around and hopefully have fond memories of the events. I know I do when I use some of the items that I’ve gotten.

October 8, 2013
T-SQL Tuesday #46–The Rube Goldberg Machine

It’s T-SQL Tuesday time again. This is the monthly blog party where we all write on a topic. This month the topic is the Rube Goldberg Machine, as it might relate to SQL Server. You can read the invitation linked above from Rick Krueger and write your own post if you choose.

Just be sure you post it on Tuesday, Sept 6, 2013.

Catching the Cheaters

A long, long time ago, in a county just to the west of here, I worked for a small company as a DBA. I had to support our production operations, as well as a team of 10-12 developers that were building our main company application. Since we were an ecommerce type company, this was an important application for the company.

We were working in an high incremental way, probably similar to an Agile methodology, and deploying changes every week. However when I started the deployments weren’t smooth. Our developers were handling them, often bumbling them a bit, but able to fix things in an hour since they had done the development.

Not a good separation of duties and it was becoming more of an issue as our boss wanted minutes of downtime, preferably single digits. I tracked down the main cause as a haphazard development style where changes were made to databases, instances, and IIS without much tracking going on. The IIS changes were easy: the developers had their rights removed from production and even test machines for configuration changes. However the database required a bit more work.

As this was SQL Server 2000, we had limited ability to audit and manage rights. We also required our developers to have rights to the development machines in order to create test databases and work on importing data. We also wanted to ensure that developers moved quickly, but I needed a way to keep control of the instance.

The first contraption I used was a simple one. I loaded the output of sp_configure into a table on the instance. I had created an administrative database that I used to track backups, and I added this information in there. I then created a job that would run sp_configure every day, compare the values to my table, and then alert me to changes. It would also update the table with the new, current values.

This allowed me to catch various changes that developers were making when they tinkered with the server to “make it run faster”. I didn’t prevent changes, and if their changes worked, we’d deploy them to test (and eventually production), but this allowed us to document them and be aware.

This worked well enough that I build a few more mousetraps. Catching schema changes is hard, as there isn’t good auditing in SQL Server 2000. However there is a “version” number for each object that is incremented when it’s changed. I built a similar audit/job system for our sysobjects table, but ran this every hour, catching changes. When I was alerted, which was more days than not, I’d email the team, track down the person changing things, and make a note for our current branch of work.

This worked great in that development wasn’t slowed, but I was able to account for all development changes and slot them into the current, or future, development deployments. In a few months we reduced our deployment time from about an hour every Wed night to less than 5 minutes.

Sometimes the mousetraps actually help the mice work better.

September 10, 2013
T-SQL Tuesday #45–Follow the Yellow Brick Road

It’s T-SQL Tuesday time again, and this month Mickey Stuewe hosts the party. It’s an interesting topic, and one that I think is important, and will be more important, as we capture, store, analyze, and depend on more and more data.

The topic this month is auditing, and you can read the invitation on Mickey’s blog. I think it’s amazing no one has asked us to write on this in the past, and I expect we’ll see some interesting stories that people will share.

This is a monthly blog party and all you have to do to participate is write a post on the 2nd Tuesday of the month. The topic comes out about a week before, and you can schedule something to go out on the appropriate day. Be sure you leave a comment on the host blog or a pingback.

Auditing

I’ve never been bound by regulatory requirements for strict auditing. When I worked in industries that had bounds, they weren’t really updated for computer systems, and lots of our practices slipped by because no one thought to look. A few times that was scary.

However I’ve implemented auditing in various systems, in various ways. The most common way to do this in the past was with triggers, though I had a situation where I had to build a unique solution that would capture changes being made to systems.

I worked for a small company one time and we had a few critical systems our clients regularly accessed remotely for various functions. This was in the era of client server systems, and we delivered software to our clients that connected to our servers. We had lots of buggy software, and when we released new features, I’d cringe as I knew we would have calls and complaints for a few weeks as we patched things.

I ran a team of 3 DBAs, and we had 5 or 6 developers working for another manager, including a few remote developers. We had agreed to limit changes to known times, and document the packages being deployed so that we were aware of changes and could better troubleshoot issues. Our environment was complex enough with multiple processes communicating with each other and SQL Server without adding the issues of unknown changes. We communicated things to our development staff, and though they grumbled everyone agreed.

One day I came in to find that a process that had worked the day before had failed. We were struggling to get it working, and eventually discovered that a schema had been changed. This was in response to another enhancement, but it caused an import process to change. We hadn’t scheduled a change, which must have occurred overnight. No one admitted to making a change, and after a long day, I had to drop it.

But not completely.

I went back and set up a server side trace to run and capture information for object changes. I let this run constantly, rolling over the files periodically. It was a bit of a load on our system, but I felt it would be short term and it was important enough to understand what was happening.

A few days went back and we were having an issue with another part of the system. Clients were screaming at my boss, who was coming to the development area to complain to DBAs and developers alike. As we tried to reproduce the problem on test systems, suddenly it started working.

Relieved, but concerned, I decided to check my trace. Sure enough, I found a change to an object logged, by none other than that craft “sa”. Digging in further, we discovered that a piece of middleware had a timestamp of a few minutes before the system started working.

It can be hard to track down who is using an “sa” account, which is why it is not recommended as an account to use by individuals. Cross referencing some information from the network team, we discovered our remote developers were slinging code changes on the system whenever they felt like it, using sa and “Administration” on Windows.

They patched systems at times, but often these were patches to fix issues in poorly written software they’d deployed in the first place. They were also patching patches when thing didn’t work. Whether this was in reducing the time of problems or not was difficult to determine, but it certainly meant a lot of wasted time when we tried to troubleshoot issues with no idea what had changed. We also found lots of their changes weren’t being added to version control.

My boss agreed this was an issue, and it allowed me to change all system administrator passwords, as well as revoke all rights to production systems from developers. Eventually we achieved more stability in our systems than the company had ever found. I’d attribute this to preventing cowboy coding changes to systems, which seem to cause as many issues as they fix.

Auditing is something I’ve depended on, especially for myself. As I’ve worked with more and more systems concurrently, I find that it’s easy to forget about changes made in a hurry, or forget which change occurred on which systems. Logging and auditing allows me to retrace my own steps, as well as those of others.

August 13, 2013
T-SQL Tuesday #44–Second Chances

It’s T-SQL Tuesday time again. This is the monthly blog party, started by Adam Machanic. If you’d like to participate, write a post on the topic and publish it on the second Tuesday of the month. If you’d like to host, contact Adam.

This month the topic is hosted by Bradley Ball and his subject is Second Chances.

Second Chances

We all make mistakes. I ask the question of people in some of my talks, and note that I make mistakes all the time. I do, and while most of them are fairly small and easily recovered from, some aren’t. Some are easy to brush off, and some are very, very embarrassing.

In my career, when I think about the big mistakes I’ve made, I’ve got a few choices. There have been times I’ve deleted all the data in a table.

There have been times I’ve restarted a SQL Server without telling anyone. There have been “fixes” to the system I’ve made without running through the proper channels. However there is one item that stands out for a few reasons.

SQL Slammer

I’d let patching slip. We had a large SQL Server environment (hundreds of Standard/Enterprise versions, thousands of MSDE versions) and while we’d been trying to stay patched, it was a hassle and we let some things slip in the fall of 2002.

I’d gone to the mountains for the weekend to ski with my family. We returned late on a Sunday night to find numerous voice mail messages at home and on my cell phone. I was actually called again while I was checking messages on the way home from a friend at work that said I needed to come in. After dropping off my wife and kids, I headed to the office.

Our analysis of the worm showed that it was wrecking havoc on our network. It constantly bottlenecked the network, and we had shut everything down. For a 5,000 person company, with a central network presence and hundred of software developers, this was not ideal. We were up most of the night, waiting on someone from Microsoft to fly in and help us rebuild the patch that Microsoft had released. Since we’d installed many instances of MSDE in non-standard locations, the patches wouldn’t work.

Our network was down Mon and Tues, but we learned some valuable lessons, like email and file shares weren’t so critical to the business that we couldn’t function for a day. We also learned that allowing developers to ignore patches was a bad idea as the majority of the delays after Mon afternoon were due to MSDE instances.

My lesson? Myself and the other DBA received a stern talking to from our boss about patching. We had delayed some of the patches for a few months, but there wasn’t a good excuse for getting almost six months behind. Microsoft had been sending lots of patches, but we didn’t have a good reason for not getting them installed quarterly.

I still don’t like the every other month patches that MS releases as cumulative updates, and I don’t recommend them, but I have learned the security updates are worth getting installed ASAP.

July 9, 2013