C H A P T E R  1

The Database Administration Profession

Why is this damn application so slow?
I accidentally deleted my customer’s order a few days ago. Can you retrieve it?
The auditor is coming in tomorrow to review our security access list. Please have something ready.
Here is your pager; enjoy the weekend!

The life of a database administrator (DBA) can be quite interesting. To some nontechnical folks within a company, a DBA is a necessary evil. They can look at a developer and say, “Oh, this is what you created.” When they look at a DBA, what can they see? Usually, they don’t think much about the DBA until they have a problem. When this occurs, you are more important than anyone else, including most executives. In these situations, you, as a DBA, can make or break your career.

Why Do We Need a DBA? Access Runs Great!

We are in the heart of the Information Age. The global economy has shifted from the manufacturing of physical goods to the storing, managing, and mining of information. It is amazing to think about how much information is being recorded every second.

Some grocery store chains in the United States offer “savings cards,” which, when presented to the cashier upon checkout, may give buyers some additional savings on the various products they purchased. To the customer, this simple chore of carrying around a little plastic card and remembering to give it to the cashier is worth the potential dollars in savings. To the store, this savings card program is worth far more than the few dollars it will cost the store per transaction. First, it encourages customer loyalty, since customers want to get the discount and so shop at that store. The second, and arguably the most important, benefit to the store is it allows the store to perform data mining.

Take a look at the kind of data that can easily be obtained. For every customer checkout (transaction), the store gets the list of items purchased as well as who bought them. With some analysis and mining through products such as SQL Server Analysis Services, the grocery store can answer questions like “what is the most popular product sold each day?” and “how often are hot dogs and hot dog buns sold together?” If you, as the customer, provided some more information on your savings card application, the store could also answer questions like “what is the most popular product sold to a married person with children?” and “how much do single females spend per visit compared to single males?” Having the answers to these questions allows the store to target advertising campaigns to various demographics as well as give customers occasional coupons that align with their buying behavior.

All of this data gathered from transactions is stored in a database. You can imagine the potentially large amount of information that could be obtained over time in this example. Had the grocery store owners simply asked their business analysts to put the data into a database in something like Microsoft Access or, more commonly, Microsoft Excel, they wouldn’t be able to easily derive the important answers they need for the business. Without a centrally managed relational database, there would be no single version of the truth. The company would have data scattered throughout the organization, and that data would be difficult to secure. Competitors and high-tech criminals would love to get hold of a customer list or a store’s sales data. For these reasons and many others, companies hire smart DBAs like you to help store, manage, and protect their data.

What Exactly Does a DBA Do Anyway?

Much like other technical jobs, defining exactly what DBAs do is difficult because each company may use them in different ways. In the previous example, information from the grocery store’s cash register could be fed into a database. A business intelligence tool like SQL Server Analysis Services could be used to create multidimensional models of shopping data. Other tools such as SQL Server Reporting Services could be used to create reports that business analysts would use to make business decisions from. As a DBA, you might be responsible for backing up, restoring, and securing the data within the data warehouse, or you might even be responsible for maintaining the reporting server. Your exact role really depends on the organization you work for. Some DBAs spend more time on production servers and others on development servers. In an effort to make a generalization about the role, the following sections describe some of things people working in the database administration field might expect to be responsible for.

Database Maintenance

Maintenance of the database encompasses a variety of different tasks. It is perhaps one of the most important skills to develop as a DBA. Proper database maintenance starts with doing regular database backups to ensure that valuable company data can be easily retrieved should something happen to the production database or server. There is nothing like the feeling when a server goes down and you realize that you don’t have a current backup handy. If this ever happens to you, there are some great web sites that can help you get a new job.

Periodically, the database itself will need some tuning and optimization. Indexes are a vital part of the database, and you may occasionally need to evaluate them for their effectiveness. New ones may need to be added, and others may be irrelevant. Microsoft SQL Server provides some tools to make index maintenance and management easier for the database administrator. Indexes will be discussed more in Chapter 3.

It is impossible to make a claim that software is 100 percent bug free. For this reason, we have service packs. Service packs contain cumulative updates of a product since the original version was released. Most organizations have a process for deploying service packs because, although they are not supposed to change the behavior of SQL Server, in some rare cases, they have been known to do so. Even something as innocent as a performance enhancement to an application may cause problems. As a DBA, you may be asked to set up a test environment to test a new service pack with your organization’s existing applications. If your organization does not have a test environment, you should lead an effort to make one. Also, as soon as you find an issue in your test environment that ultimately saves downtime in production, you may get an instant promotion.

Security and Regulatory Compliance

To some, working on security is about as glamorous and fun as cleaning up after a party when you are hung over. However, to a company, security is as important as the alcohol is to the party itself. Without these beverages, no one will come, and without security, no customers will feel safe giving their information to you.

So, what does security mean to a database administrator? Security means managing the user access to SQL Server and the corresponding databases it contains. This user management may be as specific as configuring permissions on objects within the database, or it can be as general as specifying surface area settings such as disabling the use of xp_cmdshell within SQL Server.

With the advent of the corporate scandals in the early part of this century came an increased awareness and requirement for companies to be in compliance with various government and industry regulatory standards. Some of these regulatory standards may be familiar and include Sarbanes-Oxley (SOX), the Health Insurance Portability and Accountability Act (HIPAA), and Payment Card Industry Data Security Standards (PCI DSS). SQL Server contains a lot of features to help mitigate the requirements imposed by these regulations. Some companies need to comply, and some do not. Auditing your company’s compliance with relevant regulation is one area where you, as a DBA, may need to help out.

If you study security, you will realize that there are many kinds of attacks against SQL that intruders can perform. These include SQL injection, repudiation, and even janitor attacks. These attacks can come not only from the outside world but from internal employees and contractors as well. A complete discussion on security and auditing is available in Chapter 11.

Disaster Recovery

Disasters can happen at any time and can take the form of many different types of events. In the case of a natural disaster or terrorist attack, more things may be damaged or destroyed than your database server. Thankfully, the more common scenario is a catastrophic hardware failure. As a DBA, you will either create or be responsible for reading and understanding the disaster recovery plan. To help mitigate a disaster, companies usually back up their databases and copy or transport these backups to a different physical site. In addition, companies depending more on real-time data may configure database replication between two physically separate locations. Other technologies within SQL Server that help in disaster recover include AlwaysOn Availability Groups, which is the ability to have a live or near-live copy of the database ready in case of a problem on the primary server. This copy can be used for active read-only connections.

Design and Performance Improvement

As you gain more experience as a DBA, you may be asked to help architects and developers design schemas. A proper schema design is critical to the efficiency of the applications that will utilize the database, and a lot of tools and techniques are available to help model data.

Outside of designing the schema for the database, more often, a DBA will be asked to troubleshoot poorly performing queries. SQL Server 2012 comes with tools to help find and troubleshoot these problem queries. First, the Database Tuning Advisor will take a look at a workload and suggest improvements that can be made such as creating indexes on certain columns used by a query. The data collector can collect historical performance information, which can be used to easily discover trend information. Trends can show you, the DBA, when a particular query started to slow down and at what rate the degradation occurred. You’ll find a thorough discussion on the Database Tuning Advisor, data collector, and many more performance-tuning and optimization tools in Chapter 13.

Documentation

As a DBA, you may have to document the configuration of your database servers, disaster recovery plans, schemas, and best practices. You also may be asked to establish a standard configuration for SQL Server. This configuration and instructions on how to use it are usually written by—guess who?—you. If you are not familiar with the idea or need for a standard SQL Server configuration, consider the case where you, as the DBA, own and are responsible for the database server. An outside group wants to use SQL Server, and if you didn’t require them to use your “standard SQL Server configuration,” you could potentially end up with custom maintenance and security scripts. Not defining and using standard SQL Server configurations may increase your workloads by a larger amount than you expect, so do not be afraid to start defining standards and documenting them.

Salary Information

The U.S. Department of Labor and Statistics has an occupational handbook that describes different professions and their perspective job growth, average earnings, and other interesting bits of information. You can find the DBA profession at www.bls.gov/oco/ocos042.htm. The annual earnings for a DBA, according to the handbook, range between $50,000 and $85,000. Still dislike writing documentation? This salary, though, is subject to the local cost of living, to supply and demand, and to other factors. With that aside, DBAs make a nice income.

Words from Real-World DBAs

Rather than give you only our opinions about DBAs and what they do, we’ll share with you different perspectives of this profession from other people who are performing this role today. The following sections contain interviews with practicing and successful database administrators.

Grant Fritchey, Principal DBA

Grant Fritchey has been a DBA for 10 years and has been working in the IT field for about 20 years. Grant works for Red Gate Software as their product evangelist.

Robert Walters: How did you end up as a DBA? Were you always interested in this career?

Grant Fritchey: My first job as a DBA came about by accident. I had been working for years as a developer. During that time, I had worked quite a lot with databases in smaller shops that sometimes didn’t have a DBA. I picked up a lot of the basics. I was working a startup that was undergoing a lot of turmoil. Our DBA had a quit a couple of months earlier. Because he quit, we had started running into all kinds of problems with our databases. One day, I got really upset and went into my boss, Vipul Minocha, great guy, and started hollering about all the issues. Vipul wrote them all down and talked to me a minute about the priorities, which ones were more important, and so on. After a bit he said, “OK, so now you’ve got your priorities; let me know if you run into any issues working them out.” After I picked my jaw up off the floor, I was a DBA.

Robert Walters: What do you like about the job?

Grant Fritchey: My principal job now is to help other DBAs to do their jobs better. That’s great fun. I get to find out what kinds of problems that DBAs and data professionals run into during the day and come up with methods to help them do their jobs better. I spend a hefty chunk of every day documenting best practices and approaches for all sorts of things—backups, deployments, disaster recovery, all that. It’s constantly challenging and extremely hard work and I absolutely love it.

The thing I like the most about being a database professional is that things constantly change. We can’t sit back on our previous accomplishments and declare ourselves finished. There are always new challenges and new problems to solve. Best of all, the technology is constantly changing so that we have new solutions to both the new and old problems. There’s no sitting still or even slowing down. You have to constantly learn. That makes this a thrilling position to be in.

Robert Walters: What is the most challenging part of the job?

Grant Fritchey: There are two really big challenges to this job, and they can be summed up in two different words: “technology” and “people.” You have to keep your technology running. This means learning everything you can about it and creating appropriate monitors and checks and best practices—all to help keep it running. Further, you have to know what to do when the technology fails. All that taken together can be very hard. Add on to that, you have to work with people. This job straddles the normal IT hierarchy. You have to work closely with development teams and the infrastructure teams. That means talking to people. People can be very hard to work with. You may have to tell developers that the query they spent two weeks writing just won’t scale and has to be completely rewritten. That’s like calling their baby ugly. You may have to request disk space from SAN managers who just don’t understand why you need more space when they gave you space last week. All these negotiations require soft skills that aren’t taught in college or at the local IT school. You have to develop them through other means. That’s hard too.

Robert Walters: How many server/databases or organizations do you support?

Grant Fritchey: None. Or, you could say, thousands. I don’t have to support any databases for the company, but I’m involved with tuning queries on our products that go out to thousands of databases all over the world, so you could say I support all of those. I do have a couple of servers running locally that I break and rebuild on a regular basis so that I know how to do disaster recovery and database restores and backups better than I ever did before.

Robert Walters: What is a typical day for you at your company like?

Grant Fritchey: Fortunately, there is no typical day. I may be performance tuning a database compression algorithm in the morning and then documenting a better way to do object-level recovery in the afternoon. I do spend a certain amount of time most days answering questions on forums like Ask SQL Server Central, SQL Server Central, and the Red Gate forums. I also travel quite a bit and present at user groups, SQL Saturdays, and various conferences. The presentations are mostly on exactly the types of things we talk about in this book, setting up servers, performance tuning, backups and restores, consistency checking, and all the rest.

But my focus remains on standard DBA and data professionals’ issues. I want to come up with solutions to common problems. I want to identify common problems. So I spend my time interacting with other DBAs and doing research and testing.

Robert Walters: Do you have any advice for new DBAs?

Grant Fritchey: First, and most important, make sure you know how to do a backup, a log backup, a differential backup, and any other kind of backup that’s available. Once that’s done, learn how to do a restore, a log restore, a restore to a point in time, system database restores . . . you get the picture. Don’t just learn this stuff; practice this stuff. If you learn nothing else as a DBA, know how to back up and restore your systems.

After that, keep learning. Pick up books on the various topics of T-SQL, SQL Server, data warehousing, whatever, and learn them. Take part in your local user group. You can learn stuff there. Take part in online communities like SQL Server Central. Keep learning all the time. There’s a lot to learn for any given version of SQL Server, and just about when you’ve got it reasonably well in hand, an upgrade will come along, and you’ll have to start all over again. But that’s the fun part.

Roman Rehak, Principal Database Architect

Roman Rehak has been working with databases for the past ten years. He currently works for MyWebGrocer, a provider of online grocery services.

Robert Walters: How did you end up as a DBA? Were you always interested in this career?

Roman Rehak: For some reason, I always liked databases. I remember when, as a student at the University of Vermont, I was working at the school lab and launched Microsoft Access just to see what it did. I started as a C++ developer. My company developed a product similar to Visual Basic, and once the product was done, aside from selling it, we started doing custom development for companies using that same RAD environment. I was not happy about being downgraded from a C++ guy to a RAD type of guy, but luckily, our applications were using SQL Server as the back end, and I quickly fell in love with it.

At my next job, I was offered a position of a development DBA, where the work was split between SQL Server and coding in Visual Basic 6, ASP, and later .NET. As a developer, I prefer coding the background stuff—things like components, middle layer, web services, and of course, T-SQL. My current position is the first DBA-only type of job, but I still sneak in some C# coding to develop my own tools and utilities or to code complex XML imports. Right now, aside from supporting our development team, I am responsible for several 24/7 production servers, the development and QA environments, and everything else that goes along with that.

Robert Walters: What do you like about the job?

Roman Rehak: There is never a dull moment in the database world. I like the feeling I get when I resolve a production problem or avert a potentially dangerous situation. I love query tuning more than anything else, especially if I can make a huge difference in execution time and resource usage. Being a good DBA involves a lot of different skills, so you need a solid background in coding, relational theory, networking, and hardware; plus, you need solid people and communication skills. On top of that, you need to develop pretty good detective skills to resolve what I call “evasively obvious issues” like “This code behaves differently when I run it on its own and when I execute it in a stored procedure.” (Answer: You have different ANSI null settings.) I love the variety I get as a DBA. Every day is different from another. I also enjoy the mentoring part of the job—helping developers with their database tasks and teaching them best practices. I got bored doing just regular web development from a spec, and this is much different.

Robert Walters: What is the most challenging part of the job?

Roman Rehak: Balancing and prioritizing what needs to be done. I usually have a long list of items I need to work on, both my own stuff and requests from developers or operations. I use a whiteboard and constantly modify my daily, weekly, and long-term to-do lists. But when there is a problem in production, it takes precedence over anything else, so these interruptions add to the challenge.

Robert Walters: How many server/databases or organizations do you support?

Roman Rehak: We have about 60 employees. We have ten production database servers, six standby servers, and six development/QA servers.

Robert Walters: What is a typical day for you at your company like?

Roman Rehak: I check my e-mail about 20 times a day, including in the evening. We have pretty extensive monitoring in place, so we get notifications about free disk space, long-running transactions, job failures, replication problems, application timeouts, and many other things. I often have to respond to those issues right away or the same day, and that takes precedence over anything else. I attend a few meetings a week with the development or operations teams. When things are quiet, I can work on regular stuff, like coding stored procedures, creating new tables, creating SSIS packages, or putting together deployment scripts. Once in a while, I work from home half a day so that nobody bugs me, and I can think about the big-picture stuff, like architecture. Things are slower on Fridays, so I often work on some proof-of-concept stuff or spend an hour reading SQL Server blogs.

Robert Walters: Do you have any advice for new DBAs?

Roman Rehak: This is a good field to be in, if you are cut out for the job. I survived a total of six layoffs at my three previous jobs mainly because of my database skills, since those are harder to replace, so I can say that my choice to make SQL Server my main skill has served me well. My most important advice would be to have a good disaster recovery plan in place. Make sure backups are done and stored in multiple places. If you are shipping to a standby server, remember that a database is not enough to get your application running again. Make sure all SQL Agent jobs and all dependent objects in system databases are there and ready to go if needed. The other advice I have is to stay current. The technology changes quickly, and SQL Server is becoming more and more complex, so you need to read magazines, blogs, and books and attend conferences and local user group meetings.

Charlie Pyne, Senior DBA

Charlie has been a DBA for the past ten years at Partners Healthcare in Boston. His team supports the database servers for many Boston hospitals.

Robert Walters: How did you end up as a DBA? Were you always interested in this career?

Charlie Pyne: After working in the construction management field for a few years, I went to Worcester Poly Tech for a certificate program in client server application development. My first SQL related job was in 1999 for Partners Healthcare supporting a terrible MS Access–based work order system that happened to have a SQL back end. In the process of working with this system, I was exposed to T-SQL programming and eventually transferred to the DBA team at Partners. I’ve worked here ever since.

Robert Walters: What do you like about the job?

Charlie Pyne: I like how DBA work is a mix of programming, hardware, and systems administration. All three are areas I have always been interested in. DBA work is the perfect mix for someone who can’t pick one computer field.

Robert Walters: What is the most challenging part of the job?

Charlie Pyne: The most challenging part is finding the right balance between the perfect system and “good enough.” Customers, server owners, and vendors often want to do things “their way,” which almost never fits nicely into a DBA’s plan for how a server should be configured. We spend a lot of time coming up with configurations that are solid and secure but are also as flexible enough to accommodate everyone.

Robert Walters: How many server/databases or organizations do you support?

Charlie Pyne: Approximately 500 servers and 5,000 databases in 15 hospitals.

Robert Walters: What is a typical day for you at your company like?

Charlie Pyne: Every day is different, but here’s a typical day.

• Morning: Check backups and fix any issues that may have appeared in our monitoring system
• Afternoon: Meetings with programmers, vendors, and business owners to plan upgrades and new systems.
• Night (from home): Upgrades and work that can’t be done during business hours.

Robert Walters: Do you have any advice for new DBAs?

Charlie Pyne: Be sure you learn to write SQL and write it well. This will not only help you be a better SQL Server DBA but also directly relates to programming and use of other database systems. I’m always amazed by how many DBAs can barely write a basic SQL statement. You’ll be at a distinct disadvantage if you can’t write SQL well.

Ed Clapper, Database Product Architect Manager

Ed has been in the IT field for 35 years, with 31 years at CIGNA Corporation. He spent the first seven years of his career with mainframe–based application programming. Following this, he moved into IMS DBDC support then DB2. For the past 20 years, he has worked with SQL Server as a DBA and in product support within CIGNA.

Robert Walters: How did you end up as a DBA? Were you always interested in this career?

Ed Clapper: Starting out as an application programmer, I learned early that I was much more interested in the technical aspects of IT than the business components. This led me to system design and consulting for applications utilizing relational database systems. Early on, I transitioned to providing SQL Server DBA support, as there was a sizable push to move off the mainframe platform, as there was a perception that Wintel deployment was less expensive then mainframe. Providing DBA consulting for a variety of internal businesses and then subsequent DBMS product support positioned me to stay in the forefront of the technology directions.

Robert Walters: What do you like about the job?

Ed Clapper: I am constantly confronted with new and changing situations requiring a continued passion for learning new and challenging technologies. It provides opportunities to provide major impacts to my customers’ bottom lines.

Robert Walters: What is the most challenging part of the job?

Ed Clapper: Enforcing standards across the distributed enterprise including security and object management.

Robert Walters: How many server/databases or organizations do you support?

Ed Clapper: My team supports approximately 1,100 production databases and 3,500 additional databases for various levels of testing and certification. We provide DBMS product support for an additional 1,500 databases.

Robert Walters: What is a typical day for you at your company like?

Ed Clapper: A typical day involves mix of activities:

• Responding to application DBMS–related issues that arise on a daily basis
• Designing architecture for new applications and cost effective solutions
• Providing knowledge transfer to DBAs and developers
• Managing the flow of product enhancements and fixes for both the DBMS and all supporting products
• Designing new solutions to improve availability for critical business applications
• Researching technology trends and their viability for my corporation

Robert Walters: Do you have any advice for new DBAs?

Ed Clapper:

• Automate, automate, automate.
• Stay clear of dependence on GUI tools.
• Develop solid relationships with your partner software and hardware support teams.
• Expect the problem to be perceived to always be the database until it is proven otherwise.
• Enforce standards above all else.

Michael Esposito, Senior Technical Consultant and DBA

Michael has been in IT for about 30 years. He has been a DBA off and on for about 25 years.

Robert Walters: How did you end up as a DBA? Were you always interested in this career?

Michael Esposito: I was not always interested in being a DBA. In college, I do not think they even mentioned the position. When I got out into the professional world, I was an application developer working with a DBMS system called Integrated Database Management System (IDMS). One of my applications was having locking issues, so I started working closely with our DBA team. I had done a lot of research about the issue, and I was telling the DBAs how the locking system in the DBMS worked. My relationship with DBA team developed to the point where, when a job posting came up for a DBA, they asked me to post. One of the big reasons they wanted me was that most of them had come from OS system support, so having someone on the team who could talk to application developers easily was a big plus.

Robert Walters: What do you like about the job?

Michael Esposito: I like being able to a have a foot in the OS/systems world and in the application world. One day, I might be tuning a query, and the next, I am designing the layout of instances and databases for a new release of the DBMS. The variety of tasks keeps things interesting.

Robert Walters: What is the most challenging part of the job?

Michael Esposito: The most challenging task for me is dealing with costs. Cost issues have become very tough because consumers of IT resources want everything for free or at least for less, but IT management wants to charge back the consumer directly for everything possible. It is challenging to come up with fair chargeback schemes that do not cost the company more as a whole. Also, as certain technologies decline, the consumers who still use those technologies can see charges go up for the same service. It is difficult to have that conversation with a customer and still keep them satisfied.

Robert Walters: How many server/databases or organizations do you support?

Michael Esposito: We about 100 servers and 2,000 databases. We have databases from 10MBs to 300GBs.

Robert Walters: What is a typical day for you at your company like?

Michael Esposito: There really is no typical day, which is good because I like the variety, but there are tasks that must be done like checking your backups, checking filesystem space, being sure there are no production problems, etc. It is like doing your homework before you can go out and play.

Robert Walters: Do you have any advice for new DBAs?

Michael Esposito: If you can, do some code development along the way. It always helps to walk a mile in another person’s shoes. Also, be flexible and open to opportunities. At one point in my career, databases on the Unix environment were rare, but my company wanted to be ready for them so they were looking for “volunteers” to join the Unix admin group. A few people refused, but I said OK. I did not do anything database-related for a few years, but I learned so much about Unix that when the Unix database environment started to take off, I was an instant expert.

Summary

Throughout the years, we have found the best thing to do to learn about a new job is to do an informational interview with someone who is currently in that role, like the ones in the final section of this chapter. Make sure you ask them the tough questions about the job. Depending on where you currently live, another great resource is the local SQL Server users group. These groups usually meet once a month to cover a topic. They are great for meeting other IT professionals as well as getting job leads! Although there is no official Microsoft web site that lists all of the SQL Server users groups, there is one that lists quite a few worldwide: www.mssqltips.com/tip.asp?tip=949. SQL Saturdays are also a great way to meet people and learn about SQL Server. More information can be found at www.sqlsaturday.com.