Category: Uncategorized

It’s a little less than two weeks until SQL Saturday #52 in Baton Rouge. I attended last year, and thoroughly enjoyed my time in Baton Rouge, and I decided early on this was one event I wanted to return to.

I was asked by Patrick LeBlanc to give the keynote, which was exciting, and a little nerve-wracking. I have been on stage lots of times, and jointly given some keynotes, but this was the first time I’ve written a talk from scratch to give as a keynote. I guess it’s not much different than an editorial, but it feels different.

I’ve tried to make it fit the event, a free day of SQL training, along with the location, Louisiana. A number of books I’ve read have talked about making sure you tailor things to your audience, and I hope that I succeed.

I’ve been working on a short speech, and I’ve written, and rewritten it a few times over the last few months. I will run through a rehearsal this week, working on timing, and see how it goes. Then I’ll likely rewrite some pieces.

The event will be at LSU, which is a large university, and a beautiful place. The weather is hot, and humid, and hard on people, but I enjoyed running around University Lake last year and am looking forward to another lap or two this year.

If you’re in the area, or can make it, this is a fun event, and please stop and say hi.

This is the final lecture this week at SQL University. We are covering the topic of capacity planning this week for SQL Servers. In the first lecture we looked at an overview of capacity planning and some general considerations for DBAs. Our second lecture dove deeper into disk capacity planning, examining both space and throughput requirements.

Today we talk about a few other considerations for keeping control of your environment and planning for the future. By

Disaster Recovery Planning

One of the places where I find people skimping on capacity is with Disaster Recovery (DR) systems. Typically the hardware size for DR servers is some fraction of the regular production servers. This is often because DR servers aren’t often used, or maybe never used, by many companies. I know that I have not used them 99% of the time in my career, and as a result have typically not sized them to handle the full load of my primary systems. The logic being that in a disaster, likely some of our customers will also be affected, and most of them are likely to put up with slower performance if disaster strikes.

That may not be true in your environment, and I know a number of companies that size their DR environment exactly as the same size as their primary systems. For your situation, please ensure that you handle this appropriately and size your systems, and set expectations for business people, based on what you do.

However even if you size your DR system at the same level as your production server, you are not done. In three companies that I have worked with, they sized their DR server appropriately, but then forgot about them as they upgraded and expanded their live production servers. In all cases we were fortunate to discover this fact without a disaster.  A test in one case and audits in the others exposed the fact that a disaster would have been a true disaster with us not able to even restore because of a lack of disk space.

Quite often management doesn’t want to spend more money on DR than absolutely necessary, and that often means less resources for those systems. If you are stuck in this situation, be sure that you quantify the issues for management and document differences. You should try to interpolate the possible performance issues, or test them, and come up with specific numbers where possible. Document differences like “The sales report will run in 40 minutes instead of 4”, using verbiage that your managers will understand. They might agree to spend more money on DR, or you might need to use this as justification for poor performance when you do fail over.

Test and Development Servers

For each database that you have in production, you ought to have a test instance with a copy of his database. Even for third party databases, you should still have some suite of tests run against patches or service packs.

If you write code, then you also need to have a development instance, and if you have lots of clients, you might even have a User Acceptance Test (UAT) environment as well. I have also seen Customer Service instances in places that needed to replicate an issue quickly to work with a customer.

In terms of capacity planning, there are two issues to consider with all these additional instances: disk space and performance. You also face the cost and resource issues with additional instances, as well as a more difficult argument to get appropriate funding for these environments.

Disk space is always an issue as data sizes grow. If you imagine a 1TB database in your production environment, that usually means that you need a 1TB for each of your test and development instances as well as backup space. Since you often restore databases from a local backup file, this means that 1TB of data needs at least 2TB on each server, and across 3 environments (production, test, dev) that’s 6TB of space.

Each time to manage space in your production environment and proactively grow databases, you should be aware of the impact on other environments. If you buy more disk space for your main database, be prepared to buy more disk space in other environments as well.

Virtual Machines and cheap disks can ease the burden of setting up other environments and sharing hardware among some environments, but they might not be enough for some critical databases. No one may be concerned if that company birthday tracker runs a little slow, but a drop in performance on the financial analysis database could actually cost the company money.

There are always some databases that truly need a duplicate environment in order to test changes and gauge their impact on performance. In these situations you must ensure that as your production environment requires more resources, you are also adding those same resources to the test or development environments.

This seems obvious, but it can be easy to forget. Make sure that you have clear documentation and notes that remind you to order additional hardware resources for your other environments when you are adding production resources.

Human Resources

I have never really liked the term “human resources” as I think humans aren’t easily replaceable, and certainly aren’t interchangeable. Each person is unique and brings something to your environment

One of the capacity issues that seems to constantly be an issue for many companies is the employee staffing to handle the workload. As environments grow, companies are often slow to hire additional resources, even when it would allow them to substantially grow their business and increase revenue. I actually think that this is a good philosophy, but it can become a problem over time.

Each database server adds something to the workload of the administrators involved. If there is code to be written, even more load is added. While a smart DBA can be efficient, write code that helps him/her manage the servers as groups, and automate many of the tasks that need to be accomplished, there is still a limit.

I have managed hundreds of instances myself, but only when everything is automated and most of them aren’t critical and overloaded. Usually I find there are a few instances that require most of my time, and I spend every week or every day working with them. If you have two or three instances with issues, you can easily be overwhelmed.

As you grow your environment, you ought to have an idea of where you spend your time, and how much time you spend working on those instances that need lots of hand holding. If you find yourself regularly falling behind, or your hours constantly exceeding 50 in a week, you need help. You need to plan for additional people to help ensure you environment continues to run smoothly and you continue to function at a high level.

Don’t burn yourself out trying to handle too large a workload. Examine the human capacity of your environment periodically and manage that just like you manage disk space or CPU cycles.

Conclusion

This lecture concludes the Capacity Planning week for SQL University. We’ve looked at capacity planning at a high level, dived deeper into disk issues, and talked about a few things that people forget when trying to manage their environments.

If you have additional comments or questions, or would like to add your won notes to this week, please feel free to leave a comment or link to a blog/article below.

Thanks for attending this week of SQL University and enjoy the rest of your semester.

Last year Brent Ozar interviewed all the candidates for the PASS Board of Directors. I think it went over pretty well, and this year Andy Leonard (blog | Twitter) has taken up the cause and is interviewing each candidate.

I sent in my answers, albeit a little slower than others, and today I’m featured on Andy’s blog. Check it out and let me know what you think.

It seems that Thursdays are becoming SQL Saturday day here at my blog. I wrote a few posts about SQL Saturday recently after attending SQL Saturday #22, and have been scheduling them out. Since the first two were on Thursday, I decided to keep this moving forward. The previous posts in this mini-series are:

• Why Work on a SQL Saturday?
• Building a Foundation
• Working with Sponsors

People Don’t Read

I was talking with one of the leaders of an upcoming SQL Saturday about one of his emails. There was nothing wrong with the email, but it didn’t include some information about a speaker change and a few other things. When I asked this person why he’d left it out, he said it was “to build some excitement.”
Huh?
While SQL Saturday is exciting to many of us, it’s not the all-consuming passion of many people. There are plenty of people that see a SQL Saturday as no more exciting than this:

Not that the Denver SQL Server User group isn’t exciting, but it’s not necessarily something that drives hundreds of DBAs in the area to attend each month.
As a result, most people that get notifications like this don’t really read them. If you registered for SQL Saturday, and you get an email, I bet half of you do what I do:

• skim lightly over the content
• double check the date/time
• think if I still want to attend or if there’s something better
• move on with life.

Of all the SQL Saturday’s I’ve attended, and of all the times I’ve gotten some reminder, I’ve typically ignored half of them. I might check my calendar or think about if I’ve done all the stuff I need to do, but I don’t often re-read each email.
I do read a couple, which is what most people do. So don’t hold things back, send a good list of things every time. Move things around, make it interesting, but don’t assume anyone other than you is reading every email.

Touching people often

Hopefully none of you read that title in a bad way. If you do, shame on you, and probably me, for using a catchy sub-heading.
There’s a reason that car companies and beer companies, and other companies use the same commercial over and over, often during the same program. It’s because people will remember what they see often, and because some percentage of the audience will miss one or more showings of the commercial.
I think that Karla Landrum did a fantastic job of sending emails regularly to remind people, present information, repeat things, and just build some excitement around SQL Saturday #22. I think that more SQL Saturday organizers should do the same thing, and that we need to get a list of which emails to send, and when. There are some different ones, like for volunteers and speakers, so it helps to have a schedule set.
But include relevant information about the event to each group, and try to touch them once a week or so. Change your subject, change the first line or paragraph around, ask for referrals, but touch people often.