Brent and Kendra have been to several international SQL Server conferences, and they keep going back. Learn what they’ve loved about the annual PASS Summit – and what they wouldn’t mind missing.
I recently posed the question, “Who’s taking your backups?” This is of utmost importance, because your business’s data is the business. Protecting it and backing it up need to be priorities for DBAs.
Now, for my next question: who is responsible for restoring those backups?
The saying goes that your data is only as good as your last backup, and your backup is only as good as your ability to restore it. It’s the truth! After you back up your database, there could still be problems. You could have corruption in your database. There could have been corruption on the disk your backup was stored on. You could be missing one transaction log file in the sequence.
In the event of an actual disaster, the spotlight will be on the person who needs to restore the data. This person should have experience restoring databases, and be able to remain cool, calm, and collected. Or at least not run into the server room crying.
Excellent! You have an important job to do. Do you have a list of which databases need to be restored? As new databases are introduced to your environment, do you make sure they are added to the list? If your users depend on three applications to do their jobs, and databases for only two of those are backed up, your disaster recovery scenario is not complete.
Do you have a schedule for doing it regularly, whether that is daily, weekly, or monthly? It is not enough to test the restore once. Change is constant. The amount of data stored will change. The data stored will change. The media it is stored on will change. Make sure you are testing regularly so any changes are accounted for.
Are you running a DBCC CHECKDB against the restored database to ensure integrity? You need to know that the integrity of all objects in the database has been preserved. Finding corruption after restoring a database under pressure is not fun.
Are you checking how long it takes to do a full restore, and does that meet your RTO guidelines? If the business expects data to be available in 30 minutes, but the restore takes one hour and fifteen minutes, you’ll need to come up with a plan to meet the objective.
It’s Someone Else
I have the same request as last time – walk over to that person’s desk, or call them. Ask them these questions. This is another process that should regularly be reviewed and tested, to ensure that the process is still optimal for your environment.
Who’s Testing What?
While backups are important because your business data is your business, your disaster recovery plan is only as reliable as your recovery process. If there is corrupt data in your database or it was on bad media, and you can’t restore it, you are in as much trouble as if you had no backup. If you don’t know who is responsible for this task, or it isn’t being done, consider this an opportunity.
You should be familiar with the backup strategy for each database. Is it in Full, Bulk Logged, or Simple recovery model? Are you taking full, differential, and log backups? You’ll need to be familiar with how to restore each type of backup, and in what order to do it.
Here is what you should do: find the latest backups of your database. Test restoring them to a secondary server. Make sure the restore completes. Run a DBCC CHECKDB on the database and make sure it comes back error-free. Now, and only now, you can be sure that your disaster recovery process is effective.
Time the restore process. This way, when asked, “If there’s a disaster, how long will we be out of business?”, you are prepared to answer.
Automate this process. This is not a one-time operation. Just as you need to regularly back up the data, you need to regularly ensure those backups are usable. Have a monthly or quarterly disaster recovery drill in place.
I need to know more about restores!
This is a great topic to learn more about! I recommend Kendra’s article “How to Test Your Backup Strategy: Five Simple Questions” and Brent’s post on “The 9 Letters That Get DBAs Fired”. Another great resource is Grant Fritchey’s article SQL Server Backup and Restore for the Accidental DBA.
Nearly every day, we’re faced with a decision about quality and time. Do you write quick and dirty code or do you take the time to get it right? Your decision is going to last a lot longer than you want to remember. When we’re tuning T-SQL, I often hear things like, “Ugh, I remember writing that. I meant to fix that three years ago.” We all know that we need to write good clean code, right? Nobody is debating the merits of paying down technical debt or writing good code.
How do you know that you’re accumulating technical debt? There’s no FICO score or credit report for your code. If the application is still functioning and unit tests are still passing, how can you tell that you’ve got technical debt? There are a few symptoms that I’ve seen that are good indicators of technical debt.
The Feared Function
We’ve all run into this before. There’s one function or stored procedure that nobody wants to touch. Every time someone sees the name of the function they groan and say, “We’ve been meaning to re-write that for a while.” This function starts off as a quick fix to add a new feature. The feature grows over time until hundreds, or even thousands, of lines of business logic are embedded in the database in a scalar function.
Unfortunately, there’s no easy cure for the feared function. As painful as it sounds It’s usually the case that someone will need to sit down and map out the behavior of the code in question. It’s possible to re-write the code to either operate in a set based fashion or even push the logic up into the application tier – doing a lot of computations in the application tier is cheaper than performing them in SQL Server and, potentially, much faster as developers will have full control of the algorithms that they’re using.
Applications show users fully formatted data. Numbers are formatted beautifully for the user’s locale, time is displayed correctly, and HTML is added around key elements. It’s tempting to put presentation code in the database; if one stored procedure or view is changed to display formatted and localized time we can save hours or days of development and testing time in the application tier. Just one change fixes things for everyone, right?
While it’s convenient to put formatting code to the database, keep in mind that these tiny functions add up over time. Most monitoring tools filter out queries with short execution times – this is a good thing, otherwise the tools would spend all their time dealing with small queries. But these tiny presentation operations add up on SQL Server. Database servers are very expensive places to be performing a lot of presentation driven work. Most reporting and programming frameworks provide easy to use templating tools that make it easy to display richly formatted information to users.
The Master View
How often do you see a view that pulls back every column from nearly every table in the database? Don’t be shy, I bet you’ve seen this view, or even written this view, far more often than you’d care to admit. It’s okay, we all make mistakes. The master view seems like a compelling idea – it encapsulates core business logic and functionality into a single view that can be used to compose additional queries. And, after all, SQL Server should be smart enough to make sense of the body of the view itself and optimize our query based on the source of the view itself.
Unfortunately, the world is not perfect. Sometimes SQL Server is unable to divine our meaning from the query we write. IN these cases, instead of optimizing the view, SQL Server will execute the view code as written. What should have been a simple query that returns a few columns from two or three tables becomes a huge mess where SQL Server queries every table in the master view and returns every column you’ve listed in the
SELECT list of the query. This is clearly a bad thing.
What can you do about this? Barring finding a new job, you can start by re-writing queries that use the master view to use as few tables and columns as possible. This is going to be a time consuming process. The worst part about this is that the idea of the master view is dangerously convenient. Views should be composable, right? Unfortunately, real world experience states that this isn’t the case. The idea of a master view works great when you’re rapidly writing code but once you need performance, it’s time to dig in and optimize your queries to make sure everything is running as fast as it can and doing as little work as possible.
Want to Know More?
If you want to learn more and you’re in the Seattle area next week, you can drop by SQL in the City on Monday, November 5th and hear more about what you can do to boost code quality, test code, and find a better way to write database code. And, if you’re at the 2012 PASS Community Summit, I’ll be presenting A Developer’s Guide to Dangerous Queries. This session is a discussion about patterns and anti-patterns that we see every day in databases.
If you swing by BrentOzar.com today, you’ll notice that ZOMG EVERYTHING IS DIFFERENT!
About six months ago, we embarked on an ambitious project to redesign the entire BrentOzar.com. We quickly picked Synotac, a web design firm based in Portland, and they knocked it out of the park.
Synotac started by surveying our clients to find out why people called us for SQL Server help and what clients thought of us after the engagements. Armed with that knowledge, they built marketing stuff like customer profiles and key differentiators.
First Up: Our New Brand and Logo
The first Synotac home run – our new brand and logo:
Way back in 2011 when Jeremiah Peschka, Kendra Little, Tim Ford, and I founded the company, we wanted to use BrentOzar.com as the company’s online home base. The domain already had a lot of visibility in search engines, and we wanted to keep moving forward with what was working. However, we wanted clients to know our company was more than just one guy, and that the other founding partners were really equal partners. We came up with Brent Ozar PLF by using the first letter of each founding partner’s last name (Peschka, Little, Ford).
Nobody ever understood it. Even a year later, people still asked us in webcasts, “What does the PLF stand for? Is it like Page Life Expectancy?” It raised the wrong kinds of questions, especially since Tim had parted ways and focused on SQLCruise instead.
We changed to Brent Ozar Unlimited because it’s an amusing play on Limited, which is used in place of LLC in some places. Brent Ozar Limited was our first pick, and then Synotac suggested Unlimited instead.
The pocket is meant to evoke images of a doctor’s lab coat, only instead of scalpels and tongue depressors, we’ve got a pencil and a pair of glasses in ours. Synotac blew us away with this – during our online meetings together, they noticed that Jeremiah, Kendra, and I all had glasses, and they wanted to bring that into the logo. We loved it. (Someday, we’ll blog about the entire logo selection process because this was one heck of a tough decision. Synotac gave us several amazing choices.)
Next Up: Renovating the Home Page
Our old site’s top bar looked like this:
Who We Are and What We Do made sense to us database people, but as we worked with Synotac more, we came to understand why these headers didn’t work for the general public. Community was also a tough one – it led to our posters, video archives, and upcoming events, but those aren’t really communities.
The home page of BrentOzar.com was the blog. This is the way WordPress has always been by default – the home page is the latest blog posts – but it doesn’t make sense now that we’re a consulting company. (Frankly, I’m amazed anybody ever contacted us for consulting because we just looked like a blog.)
The first thing you’ll notice on the new home page is the “hero shot” of the four of us (Jes included) done by the wonderfully talented Eric Larsen. We’d loved our old illustrations by Kendra, who is much more talented than she will admit, but she got tired of us saying, “Can you make me look a little more intelligent?” We loved Eric’s analog style and sense of humor, and we added his funny illustrations on each person’s bio page too.
Above the hero shot, we’ve now got prominent links for problems we solve, services we provide, and first aid. First aid is where our free public stuff goes, and of course we’re going to continue to amp up what we give away.
Below the hero shot, we’ve got links for upcoming events and recent posts. We heard feedback that readers were most interested in these two things, so we wanted to make ‘em really easy to access from the home page.
Everything Else is Better, Too
Of course we’re biased, but here’s some of our favorite stuff about the new site:
- Better Blog Post Headlines – before, people kept leaving comments saying, “Hey, Brent, great post” on things I didn’t write. That sucked. Now, right at the top of each post, we’ve got very clear pictures showing who authored the post.
- Better Free Video Archive – we now include all of our past videos broken up into easier-to-understand categories.
- Better Descriptions of Our Work – Synotac’s copy-writing gurus blew us away with the way they translated our personalities into the written page. We’re so happy with the writing on the problems & services pages.
- Better Newsletter Signup – it’s at the bottom of every page, and the newsletter is now skinned with our web site template, too.
- Better Poster and Whitepaper Downloads – you’ve loved our posters, and now we bet managers will like our short solutions briefs too.
We could go on and on about how thrilled we are with the new site, and we hope you’ll like it too. When (not if) you run into bugs, drop us a comment here and we’ll check it out.
RICON. RICON was a distributed systems conference hosted by Basho Technologies and the event sponsors. The event was, hands down, a smashing success.
Yes, the conference was put on by a software company, but this was different than most conferences put on by a vendor. RICON was clearly a conference put on by people who love building distributed systems. All of the talks were given by people building distributed systems in the wild. There were no marketing talks, but there were talks about new features, new ideas, and new products all viewed through the lens of distributed systems.
Both Adron Hall (@adron) and Dan Ostrowski put together good write ups about the talks that they enjoyed. If you want to know more about the talks themselves, check out their posts or start reading the presentations while you wait for the videos. The quality of the presentations was incredibly high and I was not disappointed.
What made RICON great was the focus on the attendees and the community. It was clear that the organizers went out of their way to make sure that attendees had a great time. From the custom hoodies and beautiful signage to vegan meal options and amazing vendor-sponsored party I felt like I was at something bigger and better than a conference. This felt like an inclusive social event where I could learn with and from my technical peers, make new friends, and feel like the dumbest person in the room in the best way possible. It’s not every day that you get to go to dinner with the keynote speaker, a distributed systems researcher, and a team of engineers building a distributed monitoring platform for distributed systems.
RICON drastically changed my reading list. While it’s never been a short list, I’ve re-organized it to help fill up the gaps in my knowledge that this event pointed out. The reading isn’t just about distributed systems – I’ve added reading about databases, programming, networkings, and even from the humanities. In short – RICON did more than get me thinking about distributed systems can solve the problems I see regularly. RICON got me thinking.
Was the conference worth it? Heck yeah. I got to meet up with old friends, make new friends, and learn a lot about a subject I’m passionate about. I’m happy that Mark Phillips (one of the organizers) reminded me that I should buy a ticket before they sold out (the conference did sell out, by the way). I’m happy that I got a chance to go. And I can promise you that if there’s a RICON 2013, I’m going to try to be the first person to buy a ticket.
Holy cow, have we got a lot of upcoming free training – both in person and online. Check these out:
November 15 – SQL Server Memory & Virtualization
Brent Ozar with Quest Software
In this session, leading SQL Server gurus Brent Ozar and Jason Hall will explore why memory is more complex in a virtual environment than a physical one. Then they’ll discuss the questions you should ask to ensure you’re efficiently allocating memory in your environment. Brent and Jason will also show you how to:
- Determine when you have enough memory
- Change memory on the fly when it turns out you don’t have enough
- Use special Windows memory
- And much more
November 20 – “Don’t Touch That Button!” Four Dangerous Settings in SQL Server
Kendra Little, Tech Triage Tuesdays
Every software product has its gotchas. SQL Server has some settings which sound like a great idea but can cause major problems for performance and availability when used improperly. In this free 30 minute webcast, Microsoft Certified Master Kendra Little will give you a tour of SQL Server’s most dangerous settings, from priority boost to lightweight pooling. She’ll explain why you need to be cautious and how you can check if your SQL Servers are configured safely. Register now.
November 27 – SQL Server Management Studio: More Than Meets The Eye
Jes Schultz Borland, Tech Triage Tuesdays
SSMS is the standard tool for working with SQL Server databases. It does the job well. But if you’re using the default settings, you’re missing out! You can enable word wrap and number the lines. Change the settings when you script objects out. Filter through objects. Let Jes guide you through the world of hidden SSMS settings! Register now.
December 13 – SQL Server CPU & Virtualization
Brent Ozar with Quest Software
In this session, leading SQL Server gurus Brent Ozar and Jason Hall will help you figure out how much CPU each of your VMs needs. They’ll also show you to implement effective capacity planning. Plus, they’ll present tricks and tips for working with highly transactional systems, BI systems, and parallelism. Don’t miss this chance to learn techniques for achieving optimal performance on virtualized SQL Servers from two of the world’s top experts. Register now.
If you work with SQL Server databases, you work with filegroups. Learn what filegroups are, cover best practices for using them, and see them in action! Bring your hard hat – we’re diving into the database structure!
Did you enjoy this session? There’s more where that came from! I’ll be presenting “The What, Why, and How of Filegroups” at PASS Summit on Wednesday, November 7. This session is 75% demos – come see filegroups in action!
Every DBA needs to know how to create and maintain filegroups because they are part of every SQL Server database. Filegroups affect the performance, maintenance, and security of your data. What are filegroups, and how do you use them?
In this session, we’ll see how to create filegroups, create objects in them, move objects between them, and perform maintenance on them. We’ll also walk through piecemeal restores. The information in this session will help you create databases that perform better and are easier to maintain.
When you run into problems at work, how do you tell your manager what you did? Typically you would write up the problem, the solution, and a quick summary of the outcome. If you’re like me, this isn’t a lot of fun. And, if your manager is anything like most managers, they don’t want to read a summary of the problem, your actions, and try to figure out how big or small of an impact you had.
TL;DR – use a picture
TL;DR is shorthand for “too long; didn’t read” and it’s used across the web to summarize a tedious article. Since you can’t summarize your actions with “TL;DR – stuff was broke, fixed it, things got fast” we’ll have to come up with another approach. The easiest thing I can think of is using pictures. Kendra and Jes can draw. Brent takes pictures of tater tots. I can use Visio to arrange squares to make a grid. None of us are professional artists, but we make use of a lot of pictures to make things clearer with our clients.
A picture is worth a thousand words, right? While true, a picture can also demonstrate your own worth. I recently worked with a client who were having massive blocking issues. A change had just been rolled out and they were furiously trying to figure out if they needed to roll back the entire change or fix a specific but currently unidentified problem in production. We looked into the situation and found that there were two missing indexes. After a quick chat I created the indexes and the blocking disappeared in a matter of minutes. After an incident, I like to write things up and send it off to my manager or everyone at a client. How do you think I wrote up this incident?
If you haven’t figured out that the answer is “a picture” or perhaps “a screen shot”, go get more coffee. I’ll wait.
That’s right, I wrote this up using a screen shot! We waited for several minutes after the problem cleared up so I could grab this:
I couldn’t have written this up more effectively if I had tried. Pictures convey more information in a few pixels than you can convey in the same size block of text. Best of all, pictures are immediately easy for anyone to understand and require almost no effort to produce. Grab a screen shot of Performance Monitor, Task Manager, or your favorite SQL Server reporting tool. That screen shot is gold for quickly demonstrating that there was a problem and the problem was fixed at a certain time. You don’t even really have to understand what’s going on in order to see the effects of what happen. That’s the best part! Pictures make it easy to understand what’s going on without requiring domain specific knowledge – your manager doesn’t have to be a DBA to know that you just saved the day.
Free Screen Shot Tools
Windows users: Windows 7 comes with a free tool that’s called Snipping Tool. Although it sounds like a terrible scissor infested doom, Snipping Tool is a nice utility that lets you take screenshots of a window, a region, or the entire screen.
On the Mac, I use Skitch. It does the same thing as Snipping Tool but it also lets me do some minor image editing.
Hi Brent -
I would imagine you get many of these notes of appreciation for what you do but I just couldn’t leave the office for the day without say “Thank you so much”. I would have never have thought that my personal investment would have paid such dividends.
Today, armed with the awesome information, knowledge and suggestions you provided in your SQL Server for VMware training, I headed into what I thought would be a rather contentious meeting as we have been experiencing some serious performance issues for almost a year and the discussions just never went anywhere.
To make a long story short – it was amazing, I was able to bring them on board, got my reservations, got access to the ESX Hosts for checking performance and an admission that maybe the SAN could have been better configured for SQL Server.
I have been a fan for years – thanks again and I look forward to reading and hearing more from you, as always.
Wanna know what Kelly was raving about?
My 3-hour training session is half off until October 25. SOLD OUT!
Wait! You only get one chance to do this right. Before you run setup.exe, there are some basic configuration steps that will make all the difference in performance and troubleshooting. Brent Ozar will explain his SQL Server Setup Checklist covering the page file, service accounts, memory settings, and hardware stress testing. We won’t cover scripted installations or slipstreaming — this half-hour session is for folks who click through the setup GUI because they only install a few servers per year.
For the full details, check out our SQL Server Setup Checklist.