Blog

As a consultant, I solve hard problems, and I use cool tools. These tools could help you, too– and in ways that may surprise you.

Problem 1: The DBA’s Time is Money, and It’s Limited

You know what, DBAs? You’re expensive.

DBAs are frequently involved in every task that touches a database. This includes Windows security patching, writing and running adhoc queries and reports for users, granting user access, deploying code, disaster recovery planning, high availability planning and implementation and performance troubleshooting. Additionally, DBAs must ensure that the database servers are configured in a secure way and that backup and maintenance processes are scheduled and running successfully.

Often, the tasks which are the most valuable to the business don’t get enough attention because of other demands.

The trick to managing your time— which is your company’s money— is to figure out where it’s going.

Consultant Solution: Manage Your Time with Toggl

The problem with most time tracking tools is that they’re too time consuming. You start out and immediately realize you need to start tracking the time you’re spending tracking your time. You get caught in an endless loop and emerge 10 days later looking like a Unix sysadmin.

I love the Toggl interface because it’s seriously easy to use. You can very quickly record what you’re doing and classify it using a timer. You can continue activities from previous days or start new ones.

Typically, people in IT don’t do time tracking unless they’re forced to do so by management. It’s usually dreaded, but the secret is that the data is most valuable to YOU.

Track your time use for one month. At the end of the month, look at the patterns in aggregate. Look for items that took up a lot of time collectively, but which could be done by someone who is less expensive to your company, perhaps with some automation. Identify a more valuable project you could do instead, such as designing a Disaster Recovery plan for the company’s databases. Pitch this to your manager as extra value you can add, and provide the supporting data. Maybe it won’t work immediately, but over time you’re building data to help justify a Junior DBA.

Problem 2: People Don’t Know What the DBA Does

Most of your coworkers don’t understand what you do most of the time.

Solve this like a consultant. When you do a task for someone, give them a Statement of Work.

Consultant Solution: Statements of Work

A Statement of Work, or “SOW” is essentially an agreement that explains what you’re doing, what the goals are, and how much it will cost.

When you’re taking on a new project or task, make a bit of time to write up the goals for the project, how many hours of time each week you expect to devote to it, and how many weeks it will take you to complete. State what deliverables you will provide, an overview of the tasks you will do, and anything that might keep you from delivering on your estimate. This shouldn’t be lengthy or wordy— aim for clarity and brevity. Share your statement of work with your manager and the project owner.

Your statements of work can be sent by email, but store them off in a folder for your reference and review them when you complete a project. The main value you provide by writing this type of email as a DBA is helping others understand your job and see that you’re a professional.

Tip: I know you’ve got a list of things you would love to test, investigate, or research, but don’t have enough time. Once you get good at writing SOWs, you can use these to pitch projects that YOU think are valuable to your company. In exchange, you can trade off some of those time consuming tasks you tracked in Toggl that are a little too manual.

Problem 3: People Don’t See the DBA as a Person

There’s a problem that goes even deeper: Many people don’t see the extra hours you spend after a code deployment that caused the website to grind to a halt, or the late night hours you get paged because checkdb failed. Instead, they may see an empty cube in the morning if those late nights keep happening.

What people often notice about a DBA is that they aren’t in the office all the time. In many companies where there is 24×7 on-call support, the DBA may work a different schedule, come into the office less, and participate in more meetings remotely than others.

This is a huge tacit cause of friction, but you can avoid the problem.

Consultant Solution: The Webcam

Whenever I work with clients remotely, I’ve always got my webcam on. This means that no matter how long I’ve been working, I need to brush my hair, but it does something important: it helps people relate to me.

If you sometimes need to attend meetings from home, purchase a webcam, even if it’s on your own dime. Allow people to see that you’re actively paying attention to the meeting by positioning what you are working on near where the webcam is sitting— if you’re making notes on a secondary monitor, it might look like you’re ignoring the meeting.

By seeing you at work elsewhere, people will learn that you do accomplish things when you’re not at your desk. Moreover, they’ll see you much more as a person rather than a missing entity.

Being a Consultant is Fun

The true joy in being a consultant is solving problems. You can already do this as a DBA– without quitting your job! Sometimes all it takes to feel the difference is using a few new tools to help you manage your time, shape your role, and share your work with your colleagues.

On March 23rd, I’ll be the guest of honor at the first SQLFriends Lunch in Chicago.  For just $30, you get lunch at Buca di Beppo on Rush Street plus two hours of talking about SQL, virtualization, blogging, consulting, the MCM program, you name it.

And you really do name it – on the SQLFriends registration form, there’s a spot to put in the questions you want answered.  I’ll get a copy of those, and I’ll come armed with things vaguely resembling answers.

This is a refreshing change of pace from user groups and presentations.  Here’s your chance to ask me anything in a friendly, relaxed environment and get insight from your peers.  Network with the kind of people who find me interesting – believe it or not, you’re not the only one.  (There’s also my mom.)

There’s only 30 spots available, so sign up now.

Last week Brent, Jeremiah and Kendra attended the SQL Server 2012 BareMetal workshop at Microsoft Headquarters. This was a special train-the-trainer style event where Microsoft Program Manager Dandy Weyn (blog | twitter) brought MVPs, Microsoft employees, and MCMs together. We talked about implementing SQL Server, training the community to use new features, and helping people adopt new technologies to scale out their applications.

What did we think? Here’s our highlights.

Brent Says, “You’ve Come a Long Way, Baby”

I love the command line.  I love batch files.  I love queries.  But somehow, I could just never get into the habit of installing SQL Server via unattended scripting.  The GUI works well enough, and I don’t install that many SQL Servers, so why bother scripting things out when I’ve heard so many horror stories about the difficulty of unattended install scripts?

After Dandy Weyn’s #SQLBareMetal training in Seattle, I see the attraction.  Dandy’s put a hell of a lot of work into an elaborate set of scripts that:

• Download evaluation versions of everything (Windows, SQL, Office, and more)

  

• Create Hyper-V templates for Windows
• Copy the templates into a domain controller VM and several SQL Server VMs
• Creates a domain and joins the other VMs to it
• And much more

It’s impressive.  Really impressive.  With a properly equipped host (24-32GB of memory and a single SSD or a RAID 10 of conventional hard drives), you can spin up your own SQL Server 2012 lab in an hour or two complete with a cluster and Availability Groups.

If everything works.

Unfortunately, we’ve still got a way to go.  Troubleshooting all these moving parts still isn’t easy, as evidenced by the number of stumped Microsofties, MVPs, and MCMs in the room that struggled for hours getting everything working correctly on brand-new clean virtual machines.  Troubleshooting machines in the wild is another matter altogether – scripting doesn’t make that much easier.  In fact, it’s probably worse: if you take a stranger’s scripts and run ’em, and you run into problems, you’re fighting two battles simultaneously.  You have to figure out what the scripts were doing, and figure out how to fix problems.

Sitting in the workshop, watching people struggle, it hit me: Microsoft’s not bragging about easier management of SQL Server 2012.  If you want easy management, the solution (supposedly) is SQL Azure.  SQL Server, on the other hand, gives you much more powerful features and tools, but you pay for that power by way of more difficult setup and troubleshooting.  Scripting doesn’t help that – it just makes the errors pop up faster with less repetitive manual labor.

We’ve come a long way, baby, but in terms of manageability, we’ve still got a really long way to go.

Jeremiah says, “You press the button, we do the rest.”

I love automation; the more I can remove a meat bag from a decision making process, the happier I am. It’s not because I don’t trust people; it’s because people go on vacation or play games on their cell phone, or don’t check their email for hours on end. Of course we all like to build complex Rube Goldberg style solutions to problems that should have simple solutions. To put it plainly – the more critical something is, the simpler the solution must be.

It’s pretty well known that I’m a big fan of large scale distributed databases. Part of the appeal is the massive fault tolerance that comes along as part of the deal. SQL Server 2012’s Availability Groups bring that massively robust and massively simple fault tolerance to SQL Server. Before going in to the training, I understood what Availability Groups brought to the table, but I didn’t quite see the bigger picture. As Brent mentioned, getting things up and running isn’t the easiest thing on earth. Once we were up and running with the Availability Group the administration wasn’t as simple as firing up a Riak cluster and throwing a switch, but it was really close.

During the configuration process, there were a few places where the wizards weren’t as clear as I’d like, but all of the functionality is configurable via PowerShell and T-SQL. Like Brent I love me some scripting. The ability to script Availability Groups once and then modify a base set of scripts means that I can deploy this feature fairly easily, provided that the Windows side of things is set up correctly.

George Eastman originally said “You press the button, we do the rest.” SQL Server 2012 is getting very close. The UI isn’t always as simple as pressing the button, but SQL Server will do the rest… most of the time.

Kendra says, “Give us a week, we’ll take off the weight.”

Brent told you above about how we built out a lot of installations with a great set of scripts. One thing that thrilled me about our installs was how fast and simple it was to deploy Windows and SQL Server onto Windows Server Core.

What’s different about Server Core? There’s only a very lightweight GUI: most of your work is done through a command prompt. There’s still a little bit of the old familiar, though– you can fire up Task Manager in a pinch for an easy graphic view of  what’s running on the server itself. For the most part, however, you administer Server Core locally either by command line or PowerShell. If you want to fire up the graphic Windows Event Viewer, other MMC snap-ins, or SQL Server Management Studio, you need to enable those to be run from remote machines.

Server Core is important to SQL Server database administrators for three reasons:

1. A smaller surface area is stronger and has less room for attack. (Bonus: that means less of a surface to patch, and fewer downtimes!)
2. Not having the GUI protects you from yourself. All those tools we run on servers use up precious resources in terms of CPU and memory. If the patient is having a hard time breathing, they don’t want a doctor who needs to sit on their chest to do an exam.
3. In the future, Microsoft is headed toward minimal GUI features. It’s time to start adapting now.

Do yourself a favor: make one of your goals this year to install Server Core with a SQL Server 2012 instance into your development or test environment. Spend some time with it and include it in your experiments with the new features of SQL Server 2012.

Yes, there’ll be tricky points you hit. You’ll find yourself reading a few new blogs and learning a few new PowerShell commands. But in a few years, you’ll be really glad you did.

Right after the DBA says “Uh oh,” they call in the consultants.  We get to see some really amazing acts of career suicide.

You wouldn’t believe some of the stuff we’ve seen, and some of these things are probably happening in your environment right now.  In this session, we’ll talk about some of the most common ways to shoot yourself in the foot, and we’ll show you how to put the gun back in the holster.

If you’re a production DBA of any experience level, do yourself a favor and look for these common mistakes before they call in the consultants. We’ll discuss recovery modes, bulk inserts, VLFs, replication, copy_only backups, and of course, shrinking – all so you can learn what you’re doing wrong before your boss has to hear it from the outsider.

https://www.youtube.com/watch?v=YnKW-HLuF7U

Links We Discussed

Automatically Shrink Your Transaction Logs – Jeremiah Peschka shares a diabolically dangerous idea to shrink your log files as soon as they grow.

A Busy/Accidental DBA’s Guide to Managing VLFs – Dave Levy explains the basics of Virtual Log Files, gives you a script to check how many you’ve got, and shows how to fix the problem.

Performance Impact of VLFs – Linchi Shea tested how delete/insert/update (DUI) performance is affected by 20,000 VLFs, and then follows up with an OLTP test too.

Minimally Logging Bulk Load Inserts – Greg Robidoux has a great chart explaining how to get minimally logged inserts depending on whether you’ve got clustered indexes, nonclustered indexes, existing data, etc.  Also, in the video, I don’t explicitly say that these also work in simple and full recovery modes, but they do.  (I wanted to keep things simple – no pun intended – for the 30-minute session.)

Microsoft Data Loading Performance Guide – Thomas Kejser, Peter Carlin, and Stuart Ozer discuss the improvements of trace flag 610 to get minimally logged inserts in 2008.

We all hope that we’re going to succeed beyond our wildest expectations. Startups long for multi-billion dollar IPOs or scaling to hundreds, or even thousands, of servers. Every hosting provider is touting how their new cloud offering will help us scale up to unheard of heights. I’ve built things up and torn them down a few times over my career

Build it to Break

Everything you make is going to break, plan for it.

Whenever possible, design the individual layers of an application to operate independently and redundantly. Start with two of everything – web servers, application servers, even database servers. Once you realize that everything can and will fail, you’ll be a lot happier with your environment, especially when something goes wrong. Well designed applications are built to fail. Architects accept that failure is inevitable. It’s not something that we want to consider, but it’s something that we have to consider.

Distributed architecture patterns help move workloads out across many autonomous servers. Load balancers and web farms help us manage failure at the application server level. In the database world, we can manage failure with clustering, mirroring, and read-only replicas. Everything computer doesn’t have to be duplicated, but we have to be aware of what can fail and how we respond.

Everything is a Feature

As Jeff Atwood has famously said, performance is a feature. The main thrust of Jeff’s article is that making an application fast is a decision that you make during development. Along the same lines, it’s a conscious decision to make an application fault tolerant.

Every decision that has a trade off. Viewing the entire application as a series of trade offs leads to a better understanding about how the application will function in the real world. The difference between being able to scale up and being able to scale out can often come down to understanding key decisions that were made early on.

Scale Out, Not Up

This isn’t as axiomatic as it sounds. Consider this: cloud computing like Azure and AWS is at its most flexible when we can dynamically add servers in response to demand. To effectively scale out means that we need also to be able to scale back in.

Adding additional capacity is usually in the application tier; just add more servers. What happens when we need to scale the database? The current trend is to buy a faster server with faster disks and more memory. This process keeps repeating itself. Hopefully your demand for new servers will continue at a pace that is less than or equal to the pace of innovation. There are other problems with scaling up. As performance increases, hardware gets more expensive for smaller and smaller gains. The difference in cost between the fastest CPU and second fastest CPU is much larger than the performance gained – scaling up often comes at a tremendous cost.

The flip side to scaling up is scaling out. In a scale out environment, extra commodity servers are added to handle additional capacity. One of the easiest ways to manage scaling out the database is to use read-only replica servers to provide scale out reads. Writes are handled on a master server because scaling out writes can get painful. But what if you need to scale out writes? Thankfully, there are many techniques available to horizontally scaling the database layer – features can be broken into distinct data silos, metadata is replicated between all servers while line of business data is sharded, or automated techniques like SQL Azure’s federations can be used.

The most important thing to keep in mind is that it’s just as important to be able to contract as it is to expand. As a business grows it’s easiest to keep purchasing additional servers in response to load. Purchasing more hardware is faster and usually cheaper than tuning code. Once the application reaches a maturity level, it’s important to tune the application to run on fewer resources. Less hardware equates to less maintenance. Less hardware means less cost. Nobody wants to face the other possibility, too – the business may shrink. A user base may erode. A business’s ability to respond to changing costs can be the difference between a successful medium size business and a failed large business.

Buy More Storage

In addition to scaling out your servers, scale out your storage. If you have the opportunity to buy a few huge disks or a large number of small, fast disks give serious thought to buying the small, fast disks. A large number of small, fast drives is going to be able to rapidly respond to I/O requests. More disks working in concert means that less data will need to be read off of each disk.

The trick here is that modern databases are capable of spreading a workload across multiple database files and multiple disks. If multiple files/disks/spindles/logical drives are involved in a query, then it’s possible to read data from disk even faster than if only one very large disk were involved. The principle of scaling out vs. scaling up applies even at the level of scaling your storage – more disks are typically going to be faster than large disks.

You’re Going to Do It Wrong

No matter how smart or experienced your team is, be prepared to make mistakes. There are very few hard and fast implementation guidelines about scaling the business. Be prepared to rapidly iterate through multiple ideas before finding the right mix of techniques and technologies that work well. You may get it right on the first try. It may take a number of attempts to get it right. But, in every case, be prepared to revisit ideas.

On that note, be prepared to re-write the core of your application as you scale. Twitter was originally built with Ruby on Rails. Over time they implemented different parts of the application with different tools. Twitter’s willingness to re-write core components of their infrastructure led them to their current levels of success.

Don’t be afraid to change everything.