SQL Server Whitepaper

HOW TO BECOME A DATABASE ADMINISTRATOR

HOW TO BECOME A DATABASE ADMINISTRATOR

You’re reading this because you think becoming a database administrator (DBA) may be the right career path for you. Our goal here is to give you some insight into what the job looks like when using SQL Server, show you at a high level the skills you need to get that first job in the field, and give you suggestions for how to acquire those skills and find your first DBA job working with SQL Server.

SQL SERVER

It’s important to understand that a great majority of DBAs focus on a single product; SQL Server is our focus here, but others focus on Oracle, DB2, MySQL, and other platforms. Focus is necessary because database platforms are complex products that take years to master. It’s very hard to reach expert level with multiple platforms. Focus also matters because most businesses look at each product as a silo. There may be a team of DBAs, but within that team typically each DBA specializes on a single product, or even a subset of a product. From a practical perspective it makes sense to focus on SQL Server if your employer uses the product. If they do not, learning SQL Server is possible but much harder without the opportunity to work with it a production environment solving real problems.

WHAT IS SQL SERVER?

Microsoft SQL Server™ is software that runs on a Windows server for the purpose of allowing users to store and query data in a database using a language called Transact SQL (TSQL). End users rarely interact directly with SQL Server, instead accessing data through reports, web pages, and custom applications. Database administrators use SQL Server Management Studio (SSMS) for managing the software, the databases, and the users that are authorized to query the data in the databases.

Three other products ship ‘in the box’ with SQL Server and are very commonly used by database administrators. SQL Server Reporting Services (SSRS) is used to create, store, and execute reports against a variety of data sources, including SQL Server. SQL Server Integration Services (SSIS) is used for loading and transforming data. SQL Server Analysis Services (SSAS) is used to create “cubes” typically used in data warehouse scenarios.

SQL Database is a Windows Azure hosted service that provides almost all of the features of SQL Server. It is also possible to run SQL Server in a virtual machine hosted on Windows Azure (or other cloud hosting service).

WHAT DOES A DATABASE ADMINISTRATOR DO?

Database Administrators are tasked with all facets of managing SQL Server ranging from installation and patching to creating databases to managing permissions that allow users to actually use the databases. But more than anything, they are responsible for protecting the data and insuring that performance levels are maintained. They often act as internal advisors on how to use SQL Server efectively. It is a critical role because if a database is ofine or data is lost, the business and its customers immediately feel the impact.

PRODUCTION DBA

This is the role most people think of when discussing database administration. DBAs have full access to production databases and usually the ones in QA or Development as well. Much of the work a Production DBA does is in response to a request or an event, often in the form of change requests or help desk tickets. Frequent interruptions are common so a good DBA must be good at juggling tasks and prioritizing which tasks need to be done first. It can be a slow paced job or a fast paced one – it varies day to day and company to company. It’s a very different role than that of a developer who may work on a projects for weeks or months at a time.

A typical week for a Production DBA might include these types of tasks:

• Applying changes to database objects that have been previously tested and approved.
• This usually happens during a designated ‘change window’ that is after normal business hours.
• Refreshing a database in development using a copy of production data and obfuscating any sensitive or confidential data (backup/restore)
• Reviewing any failed jobs and either fixing or referring to the appropriate developer for research
• Troubleshooting a sudden performance slowdown with a key application
• Granting a new developer access to read/write data in a database
• Deploying SSIS packages and creating a job to run them
• Reviewing proposed database changes to make sure they meet company standards, have no security issues, and will use an appropriate amount of server resources

BECOMING A DBA

The hard part about becoming a DBA is getting the first job. No hiring manager wants to give a user with no track record the keys to the most valuable thing the company owns – the data. A single mistake by a DBA can cause an outage or loss of data that can result in loss of revenue or in the worst case the failure of the company. Of course any DBA may (and probably will) make mistakes; but the challenge for the hiring manager is that the first time DBA often makes mistakes because they didn’t understand the full impact of their actions. Many companies only have a single DBA and can’t take the risk of hiring a ‘Junior DBA’ because there is no one on staf with the time or skills to coach them.

Very few DBAs start out as DBAs. Many start out as developers and as they work with the database (almost all applications need at least one database) they find that they are drawn to the management side of things. Many start out as network/systems administrators who start working with the database by doing installs and patches, learning the basics and then gradually adding skills. Others start as report writers or doing ETL work (extract, transform, and load). Regardless of their starting point, they show an afnity for data and databases – it comes naturally to them.

Most first time DBAs are hired from existing staf. They have learned how the business works, have demonstrated their work ethic, earned trust, and shown that afnity for data we mentioned earlier. The combination of those is often enough to convince a manager to take a chance on moving an internal candidate into the DBA role if they know the candidate is interested and the candidate has done something to be prepared for the opportunity. If you want to become a DBA you have to be ready for that opportunity by building skills above and beyond those you use at work – you need to learn the basic skills of a DBA.

LEARNING TO BECOME A DBA

Learning the basics requires a place to learn – your own private lab. For hardware you need a reasonably modern PC/laptop running a recent version of Windows enough space for the install and some databases (30G free to start with), and at least 4G of memory (more is better, but not required). You can always invest more later - start small. Install the free Developer Edition of SQL Server on your virtualization platform of choice Download the software and figure it out as you go. If you make mistakes, that’s good – you’ll learn from them! Set it up once, then tear it down and do it again. Take your time and pay attention, think about what you see and what you don’t know.

Note: Another option is to use virtual machines hosted in Azure. You can accomplish the same learning, but Azure isn’t free. Remember that you have to “turn of” machines when done, just doing a shutdown doesn’t stop the billing. If your employer has MSDN subscriptions you may be able to get some Azure hours that way.

DATABASE ADMINISTRATOR SKILLS TO LEARN

There is so much to learn; how do you decide what to learn first? Focus on theory or on practice? My philosophy is that you should learn the tasks you’re going to do most often as a DBA, the kind of task you’re likely to encounter in your first week. If you can do the basics and succeed that gives you time to grow and learn about the stuf that you’ll use less often. Here are some skills the beginning DBA should have:

• Install multiple instances of SQL Server and apply the latest service pack, set up jobs and email notifications
• Create and manage logins, users, database level roles, and be able to explain and fix an ‘orphaned’ user
• Fully understand how to use GRANT, REVOKE, and DENY
• Create and modify tables, views, stored procedures, and functions (SSMS & TSQL)
• Write queries using INNER, LEFT, FULL, and CROSS JOINS plus the use of CROSS/OUTER APPLY
• Use GROUP BY, COUNT, WHERE, HAVING, UNION, and UNION ALL in TSQL queries
• Understand recovery models and how to change them
• Know how to do ad hoc and scheduled FULL, DIFFERENTIAL, and LOG backups
• Know how to restore a backup, including how to restore the database with a different name and/or to a different location
• Understand how indexes and statistics work, how to create them, and how to maintain them

That’s not everything you need to know, but if you can do all of these, then you should able to credibly interview for a junior/intermediate level DBA position.

HOW DO YOU LEARN BEST?

As you set out to acquire the skills above you have to figure out what works for you. Some people learn efectively from books, others do not. Some like video where they can see the task being done, others prefer a written step by step article that they can follow at their own pace. Some prefer to go to a class where they can get immediate feedback, others prefer to puzzle out the answers on their own. Most people will end up using a hybrid of books, articles, videos, classes, and forum discussions to learn, but use what works for you. To be successful you’re going to have to be able to learn things on your own. It’s just not practical to pay for all the classes you would need to take to learn all the things you will need to know.

There are many successful DBAs who are entirely self-taught. They buy books, read articles, and watch presentations, then go back and try out the techniques until they understand them and can do them. This is the most cost efective method, with a cost approaching zero if you don’t count the time investment, if it works for you.

SQL SERVER ADMINISTRATION CLASSES

The fastest way to get started is to take a 5 day class that focuses on SQL Server administration. You can expect to pay anywhere from about $2,000 USD to $4,000 USD for the class, plus any travel needed. Oftentimes, employers will consider paying for or subsidizing this type of educational opportunity. While you can’t learn all you need to know in 5 days, a course of this nature will get you started and over the initial steep learning curve. Start with local training providers and expand your search from there.

This is a way to jumpstart your learning – it will not make you immediately employable as a DBA.

You may find online versions of classes. They tend to be more cost efective because no travel is involved, but it depends on whether it works for you whether the savings is worthwhile. Online learning is diferent than being in a classroom no matter how hard they work to create that environment online. When you evaluate these options keep in mind that there are many sources for recorded training available for free or minimal cost. It makes sense to spend the additional money on a true online class if you really need the guidance of an instructor.

LOW-COST TRAINING

There is a lot of free training available on the internet. Sites like SQLServerCentral and MSSQLTips have content relevant to any beginning DBA posted in articles and tips. Many DBAs blog about lessons learned and how to do common tasks. PASS (Professional Association for SQL Server)Chapter meetings, SQLSaturday, and 24 Hours of PASS have live presentations from industry experts.

Even some software vendors ofer educational courses for free. Books are still a great way to learn (see Resources) and the nice thing about SQL Server is that if you’re working on the basics you can buy a used book from a version or two back to save money.

DATABASE ADMINISTRATOR CERTIFICATIONS

When thinking about learning it’s logical to consider certifications. Beginners often look at certifications as proof of competence that they can show a hiring manager. In practice few managers will see it that way because they have seen candidates that passed the exam but didn’t have real world skills. It doesn’t mean a certification doesn’t have value. It’s a way to demonstrate interest and efort, whether it’s an internal manager or the recruiter who isn’t sure that you are ready to interview for a DBA role.

Microsoft maintains certification tracks for SQL Server, which are updated as new releases come out. Usually, the entry level certification can be acquired with one or two tests. Should you take these exams? Our recommendation is that you start by learning the tasks above first because they are the ones you will use and once you feel very comfortable with them you can begin studying for an entry level exam as a way to validate and extend your skills. Exams are very efective at driving learning if you take the time to learn rather than just doing the minimum needed to pass. Each Microsoft exam currently costs about $165 USD and typically takes 1 to 2 hours to complete. Microsoft publishes a book that supports each set of exam objectives and it’s worth buying, but it shouldn’t be your only study resource. Remember to refer back frequently to the exam objectives until you feel you have reached competency (but not necessarily mastery) on each of them.

HOW TO ACQUIRE DBA JOB EXPERIENCE

Until you get the first DBA job – the one that requires experience – you’re going to have to really work at building the experience of solving real problems. One approach is to see if you can work with whoever fills the DBA role now for your employer when you have free time or when they are doing something interesting. Potentially you can expand this so that you become the unofcial backup to the DBA, someone they trust to do routine tasks if the primary DBA is not available.

What if you don’t have a DBA? This is very common with smaller companies. Typically someone on the team will have DBA level access but not really be a DBA which can lead to a lot of administrative work not being done or less than optimal solutions being created. As you see pain points you may be able to work on the problem and come up with a recommended solution. Go slow, ask questions, and pay attention to how the other people involve react when you ofer up suggestions. Getting the chance to learn and experiment is far more important than ‘winning’ every discussion.

Keep an eye out for chance to volunteer for any project that might drive learning. It might be an ofcial work project or a volunteer opportunity for a local non-profit or a small consulting engagement after hours. If you can work with SQL Server and gain experience, that’s a big win for you even if you’re working for free.

FIND A ROLE MODEL OR MENTOR

It’s hard to learn to be a good DBA without a role model. However, It can be done. Most of us did it that way because it’s very common to be the only DBA in a company but it’s definitely doing it the hard way. At a minimum you want to find a role model locally, someone who walks the walk of the DBA and is willing to give you some time for Q&A once in a while. Ideally, this person could be an active mentor: someone you respect, who sees potential in you, and is willing to invest efort to help you grow.

User groups and former employers are great places to look for someone to fill this role. Start slow, let them get to know you and see if the chemistry builds. Try to build the relationship to the point where you can meet or talk for an hour every week or every other week. Ask them to tell you about their week. What did they work on? What problem did they solve? Why do they do things a certain way? You can also use that time to ask their opinion on various parts of technology as you learn, but don’t look at them as a tutor. They expect you to figure out most of it and they’ll help you stay focused and moving on the path you’ve selected.

THE SQL SERVER COMMUNITY

Once you get started with SQL Server you’ll start to hear about the SQL Community: a catch-all name for the many people and web sites that collectively engage on SQL Server topics. It’s a group of people that are smart, engaging, and approachable. All you have to do is participate: ask questions, answer questions, attend events - participate on whatever level suits you and you’ll soon start to feel like you’ve truly joined a community as well as a craft.

To stretch the analogy, there are a lot of neighborhoods within the community. You may like the forums on SQLServerCentral or MSDN, or you may find Thttpsw://twittiert.cotme/sqlparss is a better fit (start by following the #sqlserver, #sqlpass, and #sqlhelp hashtags). Make it a habit to connect with people you meet on LinkedIn ( join the PhttpsA://wwSw.linSkedin.gcomr/goroupus/61p756 as a minimum).

CONVINCING THE HIRING MANAGER

Most hiring managers want to hire someone that is an expert at everything, but they usually hire someone that has the skills to cover the core areas and seems like they will be a good fit. Your task is to show them what you know and how you learned it, and to know the things you don’t know. Play the cards you have: relevant experience, any classes or certifications, books read, etc. Be ready to talk about your contacts in the business and your learning/career plan. Remember that they are trusting you with their most valuable asset so you have to show them you’re calm, steady, and thoughtful under pressure (such as in interview!).

YOUR FIRST DBA JOB

You did it, you’re a DBA! Now what? It takes a few weeks to get up to speed as you learn the servers, people, and processes. Here are five tips you should implement every day:

YOUR ACTION PLAN

We’ve told you about the role of the DBA and many of the things that are involved in learning the skills, now let’s translate that to an action plan. This is only an outline. We recommend that you schedule time every day or at least every week to study and that you have a roadmap of the skills you want to learn. You should supplement that with daily/weekly reading on related topics via various newsletters and blogs.

WHAT DO YOU NEED TO LEARN?

Make a “learning list” of all the things you think you need to know or that you want to learn more about. Start with the list earlier in the document (Skills to Learn First), cross of any you know, and add other skills you need to learn or enhance as you go. You’ll never be done with the list, you’ll just work your way through the beginner stuf into more advanced topics and new features. It’s a pattern you can use your entire career.

SET GOALS

Goals help to drive progress and help you measure progress. Put these on the calendar:

TRACK YOUR PROGRESS

Build the habit of logging every learning session. Enter the date, about how much time you spent, the topic, the source (URL/book/etc.), and a sentence or two about what you worked on and where you should pick up next time. Use Word, Excel, Notepad – whatever works for – but write it down! It’s there to help you stay on course and resume if you get knocked of course, but it might also be something you show to a recruiter or hiring manager to convince them just how hard you’re working to get to your goal.

FREE TOOLS/STUFF

The following is a sampling of the free tools and solutions available for SQL Server. Typically each vendor in the SQL Server market has one or more free tools as well (usually registration is required, but no fee) and they are often very useful.

VENDOR LIST

All of these vendors sell a variety of tools that are useful to the DBA and almost every company will have tools from at least one company on this list. In general they fall into two categories, monitoring and productivity. It’s worthwhile trying out products in both categories from multiple vendors because it’s almost certain you’ll work with companies that already rely on them.