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. It is in these situations where you as a DBA can make or break your career.
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 the buyer 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 beyond 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. 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 per 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 vs. single males?" Having the answers to these questions allows the store to target advertising campaigns to various demographics as well as give the customer an occasional coupon that aligns with that customer's buying behavior.
All of this data gathered from transactions is stored in a database. You can imagine over time the potentially large amount of information that could be obtained in this example. Had the grocery store simply had their business analysts put the data into some databases 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 one version of the truth. The company would have data scattered throughout the organization, and it would be difficult to secure the data. Competitors and potentially 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. .
Much like other technical jobs, it is difficult to define exactly what DBAs do 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. The exact role really depends upon the organization you work for. 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.
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 websites that can help you get a new job.
Periodically there will need to be some tuning and optimization of the database itself. 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.
To some, working on security is about as glamorous and fun as cleaning up after a party when you are hung over. However, to the company, security is as important as the beer and 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 13.
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, there may be more things 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 database mirroring, 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.
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. There are a lot of tools and techniques available that 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 2008 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 be used to 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 11.
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. For those 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 a SQL Server, and if you didn't require them to use the "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.
Note
As a fellow technical person, it took a long time for me to realize the importance of documentation. As a software test engineer back in the mid-1990s, my philosophy was something to the effect of "Can't you just look at my test cases? Why do I have to spend the time writing a Word document?" It wasn't until I started working as a program manager on the SQL Server Management Tools team that I realized the importance of writing things down. The specifications I wrote would be read by many other folks. By having a well-written specification, I found it was easier to answer people's questions with, "It's in the spec! Read it!" —Robert Walters
The U.S. Department of Labor and Statistics has an occupational handbook that describes different professions, their perspective job growth, average earnings, and other interesting bits of information. You can find the DBA profession at http://www.bls.gov/oco/ocos042.htm. The expected earnings for a DBA according to the handbook are between $70,000 and $100,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.
Rather than just give you our own 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 are two interviews with practicing and successful database administrators.
Grant Fritchey has been a DBA for 10 years and has been working in the IT field for about 20 years. He currently works for FM Global, which is a global commercial insurance company with about 4,000 employees.
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: I work mainly as a development DBA, so my work entails understanding business processes and definitions and turning those into structured storage. It requires me to cover a lot more territory and understand a lot more about the business, software development, database development, systems, and so on, than other IT jobs. We straddle the fence between pure systems work and pure development. It really keeps things interesting. Plus, there is so much to learn about how the SQL Engine stores and retrieves data, the best way to get or send data to the applications, all the management tools, and so on. It makes the job a challenge almost every day, and almost every day you have to learn something new.
I especially love query tuning—getting an execution plan and the I/O and CPU costs of the query so that you can figure out which are the costly operations, which tables need new or better indexes, and which queries are badly written or just need a bit tweaking. It keeps you on your toes mentally, and you can get immediate feedback on how well you're doing your job. You take a query that runs in 45 seconds but is called several times a minute and make it run in 45 milliseconds, and you know that you just helped the company bottom line and really earned your salary.
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 a developer 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: Ours is a global company with about 4,000 employees. We have 300 people working in IT. There are only about 20 production servers with approximately 400 databases on them, the largest database being about 50GB. Those are pretty small systems by and large. However, we also have about 5 production support systems, 30 QA servers, and I think about 60 development servers, and they all have 10 to 20 databases on them, so there's a lot to do.
Robert Walters: What is a typical day for you at your company like?
Grant Fritchey: It depends on if you're working in development or production support. For a development DBA, you show up in the morning and check your e-mail and task assignments for the projects that you're supporting. You will be supporting multiple projects. So, you may have to run a deployment for one development team while you're working on tuning a query for a second. You'll have to go to a design meeting later in the day for a third project. During the day you would be answering questions and helping out different developers or fellow DBAs with any issues they run into.
As a production DBA, you have two things to look at. Were there any overnight incidents that need follow-up or extra work? You need to check your Operations Manager dashboard (a monitoring tool we use) to see whether there are servers under stress or noncritical jobs that may have failed over night. If there are issues, these are priority one, and you'll have to fix them. During the day there may be teams preparing for a production release. You'll have to verify that they're following the proper deployment processes and that their scripts run correctly on the production support servers. You may also help tune queries that are running slowly in production.
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 has been working with databases for the past 10 years. He currently works for MyWebGrocer, which is 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 10 production database servers, 6 standby servers, and 6 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 team. 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.
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. 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 meet usually once a month and during these meetings usually cover a topic. They are a great place to meet other IT professionals as well as a place to get job leads! Although there is no official Microsoft web site that lists all the SQL Server users group, there is one that lists quite a few worldwide. The URL is http://www.mssqltips.com/tip.asp?tip=949.