Becoming Certified in Microsoft SQL Server About Me

Becoming Certified in Microsoft SQL Server

About Me • Chris Hyde • Senior Consultant with Leidos Health (formerly SAIC) • MCSA – SQL Server 2008, MCITP • 14+ Years experience with SQL Server • Contact Me: – Email: chrishyde 325@gmail. com – Twitter: @Chris. Hyde 325

Presentation Summary • What is SQL Server certification? • Why become certified? • SQL Server 2008 certifications and exams • SQL Server 2012 certifications and exams • Changes for SQL Server 2014 • How do I prepare for the exams? • Exam tips and tricks

What is SQL Server Certification ? • A series of professional titles granted by Microsoft to those who have proven their knowledge and skills of a particular version of SQL Server through successfully completing one or more certification exams • For example, for the 2012 version we have the Microsoft Certified Solutions Associate (MCSA) and the Microsoft Certified Solutions Expert (MCSE) • Changing requirements for each version reflect Microsoft’s view of the role of the SQL Server professional • Not a formal licensing program

Why Become Certified? • • Learn new techniques and design patterns Discover and practice using new features Demonstrate your commitment to professional improvement Differentiate yourself in the marketplace

SQL Server 2008 Certifications: MCTS • Microsoft Certified Technology Specialist (MCTS) – SQL Server 2008, Implementation and Maintenance (70 -432) – SQL Server 2008, Database Development (70 -433) – SQL Server 2008, Business Intelligence Development and Maintenance (70 -448)

SQL Server 2008 Certifications, MCITP, pt. 1 • Microsoft Certified Information Technology Professional (MCITP) • MCITP: Database Administrator 2008 – TS: Microsoft SQL Server 2008, Implementation and Maintenance (70 -432) – Pro: Designing, Optimizing, and Maintaining a Database Server Infrastructure Using Microsoft SQL Server 2008 (70 -450 expires July 31, 2014)

SQL Server 2008 Certifications, MCITP, pt. 2 • MCITP: Database Developer 2008 – TS: Microsoft SQL Server 2008, Database Development (70 -433) – PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 (70 -451 expires July 31, 2014) • MCITP: Business Intelligence Developer 2008 – TS: Microsoft SQL Server 2008, BI Development (70 -448) – PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008 (70 -452 discontinued)

SQL Server 2008 Certifications, MCSA • Microsoft Certified Solutions Associate (MCSA) • MCSA: SQL Server 2008 – TS: Microsoft SQL Server 2008, Implementation and Maintenance (70 -432) – TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance (70 -448)

SQL Server 2008 Certifications, MCM • Microsoft Certified Master (MCM) – Retired as of December 31, 2013 – SQL Server 2008 Microsoft Certified Master: Knowledge Exam -970) – SQL Server 2008 Microsoft Certified Master: Lab Exam (88 -971) (88

SQL Server 2012 Certifications, MTA • Microsoft Technology Associate (MTA) • MTA: Database Fundamentals – Database Fundamentals (98 -364)

SQL Server 2012 Certifications, MCSA • Microsoft Certified Solutions Associate (MCSA) • MCSA: SQL Server 2012 – Querying Microsoft SQL Server 2012 (70 -461) – Administering Microsoft SQL Server 2012 Databases (70 -462) – Implementing a Data Warehouse with Microsoft SQL Server 2012 (70 -463)

SQL Server 2012 Certifications, MCSE, pt. 1 • Microsoft Certified Solutions Expert (MCSE) • MCSE: Data Platform – Complete MCSA: SQL Server 2012 – Developing Microsoft SQL Server 2012 Databases (70 -464) – Designing Database Solutions for SQL Server 2012 (70 -465)

SQL Server 2012 Certifications, MCSE, pt. 2 • MCSE: Business Intelligence – Complete MCSA: SQL Server 2012 – Implementing Data Models and Reports with Microsoft SQL Server 2012 (70 -466) – Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70 -467) • Recertification every three years!

SQL Server 2012 Certifications, MCSM • Microsoft Certified Solutions Master (MCSM) • MCSM: Data Platform – Retired as of December 31, 2013 – MCSM: Data Platform Knowledge Exam (88 -986) – MCSM: Data Platform Lab Exam (88 -987) – Never offered!

Exam Structure • 45 -50 Questions • Approximately two hours long • Different types of questions – Multiple choice, single answer or multiple answers required – Linked questions using the same set of answer choices – Build a solution from multiple components – Using the GUI

Example Question 1, pt. 1 • The Human. Resources. Employee table of the Adventure. Works 2012 database includes information on each employee's accrued sick leave hours. You want to determine the minimum sick leave hours accrued by any single employee who has a job title like Marketing Manager. • Which of the following Transact-SQL statements can you use to accomplish this task? • (Source: Microsoft Training Kit for 70 -461 exam)

Example Question 1, pt. 2 • A. SELECT MIN(Sick. Leave. Hours) FROM Human. Resources. Employee WHERE Job. Title LIKE 'Marketing Manager%'; • B. SELECT AVG(Sick. Leave. Hours) FROM Human. Resources. Employee WHERE Job. Title LIKE 'Marketing Manager%'; • C. SELECT MAX(Sick. Leave. Hours) FROM Human. Resources. Employee WHERE Job. Title LIKE 'Marketing Manager%'; • D. SELECT VAR(Sick. Leave. Hours) FROM Human. Resources. Employee WHERE Job. Title LIKE 'Marketing Manager%';

Example Question 2, pt. 1 • You are managing a SQL Server 2012 database that is several terabytes in size. The database's size means that backup operations are lengthy. Because of their unusual length, you must give special consideration to the process of scheduling backups. • As part of this process, you must determine which operations cannot be performed while backup is in progress. • Which of the following operations CANNOT run during a database backup operation or a transaction log backup? (Each correct answer presents a complete solution. Choose two. ) • (Source: Microsoft Training Kit for 70 -462 exam)

Example Question 2, pt. 2 • A. UPDATE • B. DELETE • C. SHRINK DATABASE • D. ALTER DATABASE with the ADD FILE option. • E. INSERT

Example Question 3, pt. 1 • You want to generate a list of integers and their natural logarithms between 1 and 20. • Combine the following Transact-SQL statements in the correct order to accomplish this task. • (Source: Microsoft Training Kit for 70 -461 exam)

Example Question 3, pt. 2 • • • BEGIN SET @test. Value = 1. 0; SET @test. Value = @test. Value + 1. 0; DECLARE @test. Value float; SELECT @test. Value as ‘Value’, LOG(@test. Value) as ‘Result; SELECT @test. Value as ‘Value’, EXP(@test. Value) as ‘Result; SELECT @test. Value as ‘Value’, SQRT(@test. Value) as ‘Result; WHILE @test. Value < 20. 0 WHILE @test. Value < 21. 0 END;

Example Question 3, pt. 3 • • DECLARE @test. Value float; SET @test. Value = 1. 0; WHILE @test. Value < 21. 0 BEGIN SELECT @test. Value as ‘Value’, LOG(@test. Value) as ‘Result; SET @test. Value = @test. Value + 1. 0; END;

Exam Contents, pt. 1 • 98 -364 – Database Fundamentals – Understanding Core Database Concepts (20 -25%) – Create Database Objects (20 -25%) – Manipulate Data (25 -30%) – Understand Data Storage (15 -20%) – Administer a Database (10 -15%)

Exam Contents, pt. 2 • 70 -461 – Querying Microsoft SQL Server 2012 – Create Database Objects (24%) – Work with Data (27%) – Modify Data (24%) – Troubleshoot & Optimize (25%)

Exam Contents, pt. 3 • 70 -462 – Administering Microsoft SQL Server 2012 Databases – Install and Configure (19%) – Maintain Instances and Databases (17%) – Optimize and Troubleshoot (14%) – Manage Data (19%) – Implement Security (18%) – Implement High Availability (12%)

Exam Contents, pt. 4 • 70 -463 – Implementing a Data Warehouse with Microsoft SQL Server 2012 – Design and Implement a Data Warehouse (11%) – Extract and Transform Data (23%) – Load Data (27%) – Configure and Deploy SSIS Solutions (24%) – Build Data Quality Solutions (15%)

Exam Contents, pt. 5 • 70 -464 – Developing Microsoft SQL Server 2012 Databases – Implement Database Objects (31%) – Implement Programming Objects (21%) – Design Database Objects (24%) – Optimize and Troubleshoot Queries (24%) • From April 24, 2014, exam will change to include updates that relate to SQL Server 2014 tasks

Exam Contents, pt. 6 • 70 -465 – Designing Database Solutions for Microsoft SQL Server 2012 – Design Database Structure (29%) – Design Databases and Database Objects (32%) – Design Database Security (15%) – Design a Troubleshooting and Optimization Solution (24%) • From April 24, 2014, exam will change to include updates that relate to SQL Server 2014 tasks

Exam Contents, pt. 7 • 70 -466 – Implementing Data Models and Reports with Microsoft SQL Server 2012 – Build an Analysis Services Database (38%) – Manage, Maintain, and Troubleshoot an SSAS Database (18%) – Build a Tabular Data Model (17%) – Build a Report with SQL Server Reporting Services (SSRS) (28%) • From April 24, 2014, exam will change to include updates that relate to SQL Server 2014 tasks

Exam Contents, pt. 8 • 70 -467 – Designing Business Intelligence Solutions with Microsoft SQL Server 2012 – Plan Business Intelligence (BI) Infrastructure (15%) – Design BI Infrastructure (16%) – Design a Reporting Solution (24%) – Design BI Data Models (34%) – Design an ETL Solution (11%) • From April 24, 2014…

Preparing for the Exam • Microsoft Classroom Training • Boot camps • Microsoft Virtual Academy • The magic blue books! – Practice tests – Create a study guide – Build or find a suitable practice server and test data • Schedule the exam!

Tips and Tricks for the Exam, pt. 1 • Relax! • Second Shot program – free retakes through May 31, 2014 • Figure out what the question is asking • View all the exhibits and read all the answer choices • Answer the Microsoft way!

Tips and Tricks for the Exam, pt. 2 • No penalty for incorrect answers • Eliminate obviously incorrect answers • Focus on new features • Use information from other questions to help you • Use information from the answer choices (dangerous!) • Identify key concepts, and practice on your lab system

Resources • Microsoft Learning Website – http: //www. microsoft. com/learning/en/us/sql-certification. aspx • Microsoft Virtual Academy – http: //www. microsoftvirtualacademy. com/product-training/sql-server • SQL Saturday 279 Website (this presentation) – http: //www. sqlsaturday. com/279/schedule. aspx • Generating Test Data (article by Jeff Moden) – http: //www. sqlservercentral. com/articles/Data+Generation/87901/

Challenge! • Complete your first (or next) exam within three months! • Two or more years of experience • Schedule exam for three months from now • DBAs take exam 70 -462 • Developers take exam 70 -461 • 80 hours study time for first exam