Category: Uncategorized

The segment should go out in this week’s show, but I spent half a day down in Colorado Springs with the CSTechCast crew, doing an interview for their weekly podcast.

They hosts of the show, Eric, Eric, and Josh, are friends I’ve known for a few years in the local tech community. I did an interview a few years back for their audio only podcast, but this was my first video interview with them. I talked about free training and the SQL Saturday franchise, so check out the show this week.

I’d also recommend this as a decent summary of the tech world in general from the perspective of a few different technology areas. They cover Windows, Exchange, Development, along with SQL Server, and have a good rapport with each other. It’s a long show, 40 minutes, but running in the background it should provide you with a nice summary of the past week’s tech world, and perhaps broaden your view of technology.

I missed T-SQL Tuesday #1, but participated in T-SQL Tuesday #2. Now it’s time for #3, and I’m making my entry. I like this style of getting a series of posts from the community on a topic, and thanks to Rob Farley, we have an interesting and open-ended topic for T-SQL Tuesday #3: Relationships.

Making Friends
Some year ago I started a new job as a DBA with a large company. I was a production guy, working in the Operations group that was responsible for keeping our live systems running. That’s the kind of work I enjoy.

In the company there was also an Engineering group, that was a counter-part to my group. They helped architect, design, sometimes build solutions that we would then deploy. As you might expect, there were usually bugs or issues in systems, and since my group was on-call for issues, we had to support those systems at night, often without a lot of help from Engineering after hours. The relationship between the groups was somewhat contentious, and even outright hostile at times.

Enter Steve. New DBA, and taking the place of a senior DBA that had moved on. Without the baggage of previous relationships, I wasn’t aware of issues. However when I got a call one day asking if the DBA could come help Engineering on an issue, I headed over. More than a few of my co-workers told me that I should stand my ground and not let the Engineering group tell me what to do.

Walking over to the other side of the building, I couldn’t help but wonder what I was in for. All the talk from my group had put me in a slightly defensive mood, but when I got there, all I found was another IT guy asking for help discovering why something wasn’t working. A little SQL Server debugging and tracing, and I’d fixed his issue, gotten thanks, chatted for a few minutes, and then went back to work.

Over the next few months, I had a few more encounters with Engineering and Security people, usually being called to solve a problem. I made some friendships by helping people get their jobs done. I left my ego at the door, and just solved problems, even when they were self-inflicted by people that ought to know better.

Those relationships were handy later in my career at that company. There were times I needed to shortcut a rule to get something done, and would get the help I needed. My responsibility to comply with paperwork or regulations had to be completed, but having a good working relationship allowed me to get the work done first, and the documentation second. It also helped me to stop an Engineering deployment a few times and let them know that they had a system with issues and their group was responsible. Hearing that from someone with whom you have a good relationship is completely different than hearing that from someone you dislike. It allowed me to legitimately push-back and have someone listen to my reasons without being overly defensive from the start.

In short, the relationships helped me to get work done and problems solved in an efficient matter. Sometimes being a DBA means working on relationships outside of SQL Server in order to have those inside SQL Server work better.

I was watching Michelle Ufford’s (blog, Twitter) presentation from the 2009 PASS Summit recently on DVD. I had purchased them, and this was one session I had attended, but had to leave early because of another commitment.

However I thought the session was interesting, I like Michelle’s writing, and this was the first one I popped in when I got my Summit DVDs. It talks about the overload on their database servers from the first GoDaddy Super Bowl commercial, which referred people to their website. The week after the Super Bowl, they started redesigning their database and infrastructure to handle the extreme loads they experienced.

One of the things that Michelle mentioned is that data types matter. I know lots of designers don’t think about this, and perhaps it isn’t important for most databases. After all, worrying about squeezing things down takes time, and you could easily “guess” wrong when you’re building a database. If you have a small system, and you use a tinyint only to find out that you needed a smallint, it’s a lot of work to change things sometimes. And it isn’t necessarily worth it.

However in a large database, things might be different. This is an image that Michelle used in her presentation.

Two tables, partially represented here. If you calculate the differences here, you’ll see that if you use the smallest data type possible, as in Table B, you end up with

(449-323) / 323 * 100 = about 40% more rows. That could translate into significant IO savings for this table in an index scan, and lots of reports use Index scans.

Is it worth limiting yourself in all systems? No, but you ought to take a few seconds when you’re building a database and if there are obvious places where you don’t need a larger data type, pick a smaller one.

I saw a post recently from my fellow Red Gate-er, Brad McGeHee, on backups. Brad’s got a ton of experience and he puts out some great posts that help many DBAs do a better job each and every day. I highly recommend you follow Brad’s blog if you’re new to the field.

I think that this is great advice, but I wanted to expand on it a bit since I’ve had slightly different experiences than Brad, and I’ve had a system that worked well for me.

My experience has primarily been with smaller databases. They’ve ranged from MBs to a few hundred GBs, nothing TB sized in my production experience, which for the most part ended in 2003. Since then I’ve worked only for some small companies as a consultant and for SQLServerCentral.

If time and hardware allowed it, I have always run full backups every night. That provides me protection for data that typically changed substantially every day, and was critical for the business. A full backup gave me a relatively quick way to recover the database in the event of an issue.

I have always run log backups as well. Only in very rare circumstances, < 1% of the time, have I managed databases in simple mode. The time between log backups has varied depending on the recovery interval I’ve needed, but typically log backups are run as rarely as every hour and as often as every couple minutes.

That’s Brad’s advice, and it’s worked well. The place where we disagree is with differential backups. I have found them to be a handy tool that speeds recovery for me. If you’ve ever had to recover a lot of logs, like the number from midnight until 6pm the next day, you realize it’s a lot of work. Even if you have scripted the recovery, it’s nerve wracking to try and restore all those logs in order and ensure that they get your system back. And it’s time consuming to try and debug any issues that may crop up.

After one particularly harrowing RAID controller failure, I decided that I’d use differentials as a way to speed recovery. With a differential every two to four hours, you can cut the number of logs you need to restore substantially. You also reduce your risk of a possible problem with some of your files by having fewer files to depend on at one point in time.

Saving Dollars

I found another reason to use differentials at my last job before SQLServerCentral. I worked for a large company with hundreds of SQL Servers. We had a large TSM backup device that handled file servers, mail servers, and SQL Servers. At that time, the TSM was configured to only back up changed data (incremental in backup terms) and we were backing up over a TB of data every day. In 2002, that was a lot of data and it cost us literally hundreds of dollars a day in tape costs.

At the time I was working with DBAssociates, the inventors of Litespeed, trying to build a case to use the product on our SQL Servers. It seemed like it would be a good use investment, and I had to write an ROI paper to present to my boss. As I was working on it, it occurred to me that if I moved away from nightly full backups to a weekly full with differentials I might save even more space. I tested the backups, extrapolated out the savings in backup space, and included the data in my report.

At the time we bought nearly $60,000 of Litespeed licenses and paid for them in a little over a year with tape savings. The savings were partially from the compression of data, but also from the move to differential backups instead of full backups each night.

Have a Good Process

Part of the reason that I’ve been able to have success with differentials is that I’ve always ensured we had a good process for not only keeping a full backup on disk, but also ensuring that a good copy was moved offsite each day. This protection from an issue in the office, which I have dealt with, helps to make a differential backup a tool worth using.

Evolving your backup process from a simple “copy to disk or tape” into something that include offsite recovery, differential backups, and good procedures that non-DBAs can follow will save your company money, and at the same time reduce your risk of data loss.

You can even further evolve things with other SQL Server technologies like log shipping, replication, and database mirroring.