In How to Become Certified in SQL Server 2012: Part I, we looked at the revamped certification track for the Microsoft Certified Solutions Associate (MCSA), which has replaced the Microsoft Certified Technology Specialist (MCITP) track. To recap, the MCSA for SQL Server 2012 is the base level certification for Microsoft’s relational database solution, and is followed by the Microsoft Certified Solutions Expert (MCSE) and the Microsoft Certified Solutions Master (MCSM). In order to achieve the MCSA for SQL Server 2012, you must pass three exams – Querying Microsoft SQL Server 2012 (Exam 70-461), Administering Microsoft SQL Server 2012 Databases (Exam 70-462), and Implementing a Data Warehouse with Microsoft SQL Server 2012 (Exam 70-463).
We have already covered the first of these three exams (Exam 70-461) in the previous post, and today we will look at Administering Microsoft SQL Server 2012 Databases (Exam 70-462). We will look at what skills will be measured, their relative weights, and how to prepare for the exam.
Overview: Exam 70-462, Administering Microsoft Server 2012 Databases
For this exam, the subject areas are more evenly weighted, with a difference of only a few percentage points between each. The most heavily weighted are Installation and Configuration, and Managing Data. After those two, in descending order of weight, come Implementing Security, Maintaining Instances and Databases, Optimizing and Troubleshooting, and Implementing High Availability.
For the Installation and Configuration section, you will need to know how to properly plan an installation, including the setup of drives, services, accounts, filegroups, partitions, etc. You should understand how to enable and disable related services, migrate to SQL Server 2012 from a prior version, and configure various SQL Server components. You will also need to be familiar with the SQL Server Agent, what it does and how it can be used to automate tasks.
For Managing Data, the main areas of study should be backup strategies, database restoration procedures, as well as how to properly implement and maintain good indexes. Expect questions about what filtered indexes are and how to create them. In addition, this section will include questions about importing and exporting data using bulk copy and bulk insert.
Implementing Security is all about managing server logins and roles, database permissions, user and database roles, certificates and keys, and endpoints. You will see questions that require you to determine the best way to configure security settings if a given user requires access to a table and is a member of a particular role on that server, so make sure you understand how the least privilege rules work.
For the section about Maintaining Instances and Databases, you will need to know how to configure and manage databases, instances, and clustered instances, including all the server level settings that are controlled with sp_configure.
Optimizing and Troubleshooting will test your ability to deal with deadlocks and other concurrency issues, collect and analyze troubleshooting data using SQL Server Profiler and traces, and come up with a strategy for auditing your instances.
Lastly, the section about Implementing High Availability will test your knowledge of mirroring, AlwaysOn Availability Groups, and replication in general.
Preparation
As we mentioned in the previous post, there are a number of ways to prepare for this exam. The best way remains getting hands-on experience with the subject material. Download an evaluation copy of SQL Server 2012 and get some firsthand experience with each of these areas. There are many online resources and tutorials that will include various practice exercises, and many of these use the sample database environment that comes with the evaluation of SQL Server. Of course we still recommend taking a class from a Microsoft Training Partner if you can. There are also numerous books, videos, and online training programs available to help you, and these can also be found using the website below. Finally, if you find that it suits you better, and you have the motivation and aptitude, you can always use the self-training method.
However you choose to learn the material, you will want to take some practice exams to check your knowledge level. Depending on your selection of provider, this can be costly, but it may just prevent you from taking the real test before you are ready. Search online for a provider of practice exams, and at least check out whatever free options there are so that you have a feel for some of the types of questions you will be asked, as well as how they will be asked. For most scenarios, you need to be prepared to provide a BEST response out of more than one potentially correct answer, and you should be able to go through the answers and start removing ones that are clearly wrong, so that if you have to make an educated guess, you can eliminate one or two invalid responses before doing so.
Microsoft’s exams tend to be weighted more heavily towards questions based on new features, to distinguish the SQL Server 2012 exams from their 2008 counterparts. Make sure you are at least aware of the new features in SQL Server 2012 before taking this exam.
With all this knowledge, preparation, and practice, we hope that you are able to sit through the exam and come out with a passing score. Microsoft is even running a promotion through March 2014 that offers you a “Second Chance” at these exams, so there is no better time than now to decide that you are going to take the next step towards your MCSA: SQL Server 2012.
Additional Reading & Reference:
Exam 70-462: Administering Microsoft SQL Server 2012 Databases –http://www.microsoft.com/learning/en-us/exam-70-462.aspx