I just started learning SQL Server, and I want to get certified, I checked internet, but I got confused.
There is exam 98-364, 70-461, 70-761, and i want to get MCSA in SQL
could some one explain to me what should i do …
–> My Answer:
>> MCSA or Microsoft Certified Solutions Associate in SQL Server can be earned on following areas with respective exams:
– Database Development (761, 762)
– Database Administration (764, 765)
– Business Intelligence (767, 768)
>> Exam 98-364 is to get Microsoft Technology Associate (MTA) certification. It is the most basic exam that focuses just on basic SQL, and is for people new to technology, who need to validate their knowledge of fundamental concepts before moving on to more advanced certifications. This exam covers:
1. RDBMS, DBMS concepts and terms, like Normanization, PK, FK, etc.
2. Plain SQL querying knowledge, like DDL, DML, etc. and indexes.
3. Simple DBA stuff, like security, backup/restore, etc.
>> Exam 70-761 is also an advanced exam which is based upon SQL Server 2016, which is the latest version of SQL Server as of today. To earn MCSA you have to take 70-762 exam as well as mentioned above, check this link for MCSA SQL Server 2016: https://www.microsoft.com/en-in/learning/mcsa-sql-2016-certification.aspx
>> Exam 70-461 is also an advance level exam but now older based upon SQL Server 2012 or 2014. Check this link for MCSA SQL Server 2012/2014: https://www.microsoft.com/en-in/learning/mcsa-sql-certification.aspx
Please note: Exam 98-364 is not required here, and as mentioned above its just to make sure you have knowledge in working with basic SQL Querying.
In one of my previous post I talked about [Exam 70-461 for SQL Server 2012], and I really got good and big response, with ~250 comments. As SQL Server 2014 got released most of the folks asked me about the certification update.
So here in this post I’ll talk about the same exam 70-461 which is now updated for SQL Server 2014, and Microsoft has not changed the exam code for this version of SQL Server.
This updated certification exam is still based on SQL Server 2012 with new features of SQL Server 2014. Previously with SQL 2012 the exam was divided into 4 modules, please check my previous post, link above. Here with SQL 2014 update the exam is reshaped into 20 modules listed below:
5. Sorting and Filtering Data
– Sorting data with ORDER BY clause | MSDN
– Filtering data with WHERE clause MSDN
– Filtering data with TOP keyword | MSDN
– Using TOP-OFFSET clause | MSDN | TechNet
– Working with NULL/unknown values
8. Using Built-In Functions
– SQL 2014 new functions (Conversion, Logical)
20. Querying SQL Server Metadata
– Querying System Catalog Views | MSDN
– Querying System Catalog Functions | MSDN
– Using System Stored Procedures | MSDN
– Using Dynamic Management Objects (DMVs, DMFs) | MSDN
–> Check SQL Server 2014 videos here:
I’ll be updating the above post going forward and as I post something related to SQL Server 2012 & 2014.
Please provide your comments if you want me to talk about any of the above items, thanks !
I’m from a Database development background and working mostly with T-SQL code only. I passed Exam 70-461 back in Nov-2012, and now I’m planing to give 70-462 Exam i.e. Administering Microsoft SQL Server 2012 Databases.
This exam is intended for Database Administrators who perform:
– Configuration tasks
– and other responsibilities like:
– – setting up Database systems
– – making sure those systems operate efficiently
– – regularly storing, backing up, and securing data from unauthorized access
Thus being a Database Developer (primarily) this exam is going to be the most toughest and trickiest exam in this series as I don’t have DBA experience and haven’t worked on anything in Database Administration yet.
For preparation I’m referring to Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases book.
–> The Exam is divided into 6 modules:
1. Install and Configure
2. Maintain Instances and Databases
3. Optimize and Troubleshoot
4. Manage Data
5. Implement Security
6. Implement High Availability
You need to brush up on following Skills in the these Modules:
-:Check links with the below items for more information:-
1. Install and Configure | 19%
– Plan installation
– Install SQL Server and related services
– Implement a migration strategy
– Configure additional SQL Server components
– Manage SQL Server Agent
– PREP Links: Understanding surface area configuration | Hardware and software requirements for installing SQL Server 2012 | Quick-start installation of SQL Server 2012
2. Maintain Instances and Databases | 17%
– Manage and configure databases
– Configure SQL Server instances
– Implement a SQL Server clustered instance
– Manage SQL Server instances
– PREP Links: ALTER DATABASE file and filegroup options (Transact-SQL) | Contained databases | Data compression
3. Optimize and Troubleshoot | 14%
– Identify and resolve concurrency problems
– Collect and analyze troubleshooting data
– Audit SQL Server instances
– PREP Links: blocked process threshold server configuration option | Configure login auditing (SSMS) | Data collection
4. Manage Data | 19%
– Configure and maintain a back-up strategy
– Restore databases
– Implement and maintain indexes
– Import and export data
– PREP Links: Back up and restore of SQL Server databases | File restores (full recovery mode) | DBCC INDEXDEFRAG (Transact-SQL)
5. Implement Security | 18%
– Manage logins and server roles
– Manage database permissions
– Manage users and database roles
– Troubleshoot security
– PREP Links: Server-level roles | Permissions (database engine) | Database-level roles
6. Implement High Availability | 12%
– Implement AlwaysOn
– Implement Database Mirroring
– HADR (High Availability and Disaster Recovery)
– PREP Links: AlwaysOn Availability Groups (SQL Server) | Microsoft SQL Server AlwaysOn solutions guide for HADR | AlwaysOn architecture guide: Building a HADR solution by using AlwaysOn Availability Groups
-–> You can visit following Study materials to prepare for this Exam:
Microsoft Link for this Certification: http://www.microsoft.com/learning/en-us/exam-70-462.aspx
Book on AMAZON.com: Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases
Will try to update this blog post or will put up a new post with my learning while preparing for this exam.
All The Best!!!
After passing 70-461 exam last year I’m now preparing for 70-463 exam i.e. Implementing a Data Warehouse with Microsoft SQL Server 2012. This exam mainly focuses on implementing Data Warehouses with Dimension & Fact tables, working with SSIS packages and Data Quality solutions. Thus being a Database Developer (primarily) this exam will be bit tough for me as I’ve not worked with Dimensional modelling, SSIS and DQS.
For preparation I’m referring to Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012 book.
This exam is primarily intended for ETL and Data Warehouse (DW) developers who create Business Intelligence (BI) solutions, and whose responsibilities include Data Cleansing, and Extract Transform Load and Data Warehouse implementation.
–> The Exam is divided into 5 modules:
1. Design and Implement a Data Warehouse | 11%
– Introducing Star and Snowflake Schemas
– Design and Implement Dimensions
– Design and Implement Fact tables
– Managing the Performance of a Data Warehouse
– PREP links: Introduction to dimensions (Analysis Services – multidimensional data) | Dimension relationships | Columnstore indexes
2. Extract and Transform data | 23%
– Define Connection managers
– Design Data Flow
– Implement Data Flow with Transformations
– Control Flow tasks and Containers, Precedence Constraints
– Manage SSIS package execution
– Implement Script tasks in SSIS
– PREP links: Integration Services (SSIS) connections | Data flow | Slowly changing dimension transformation
3. Load Data | 27%
– Design control flow
– Implement package logic by using SSIS variables and parameter
– Implement Control flow
– Implement Data Load options
– Implement Script components in SSIS
– Slowly Changing Dimensions
– Preparing a Package for Incremental Load
– Package Transactions, Checkpoints, Event Handlers
– PREP links: Integration Services transactions | Developing a custom task | Integration Services (SSIS) parameters
4. Configure and Seploy SSIS solutions | 24%
– Troubleshoot Data Integration issues
– Install and Maintain SSIS components
– Implement Auditing, Logging, and Event handling
– Deploy SSIS solutions
– Configure SSIS security settings
– Data Mining Task and Transformation
– Preparing Data for Data Mining
– Implementing SSIS Fuzzy Transformations
– PREP links: Troubleshooting tools for package development | Load-balancing packages on remote servers by using SQL Server Agent | Integration Services (SSIS) logging
5. Build Data Quality Solutions (DQS) | 15%
– Install and maintain Data Quality services
– Creating and Maintaining a Knowledge Base
– Create a Data Quality project to clean data, Profiling Data and Improving Data Quality
– Using DQS and the DQS Cleansing Transformation
– Implement Master Data Management (MDM) solutions
– Using Master Data Services Add-in for Excel
– PREP links: Install Data Quality Services | Install Master Data Services | Master Data Services features and tasks
-–> You can visit following Study materials to prepare for this Exam:
Microsoft Link for this Certification: http://www.microsoft.com/learning/en-us/exam-70-463.aspx
I will try to update this blog post or will put up a new post with my learning while preparing for this exam.
All The Best!!!
It’s always good to give Certifications, it enhances your technical skills and prove your knowledge, and more over it looks good on your Resume!
Exactly last year I passed the “Querying Microsoft SQL Server 2012” (70-461) exam, and I blogged my experience [here]. Since then I get lot of hits on the post from all over the world. Then only I came to know that there are many people who want to pursue for this exam and other follow up exams in sequence.
People ask me about study materials, dumps (which I don’t recommend strongly). Few people have confusion to give which exam in what order. Some are not aware of the exams that lies in the Certification Path.
So, I have created a visual snapshot of all these exams for all SQL Server 2012 Certification exams at different levels:
So, you can start with any of the 3 exams at the bottom (first) level based upon your area of interest. A Dev can take 70-461, a DBA can start with 70-462, and a DataWarehouse Engineer can go with 70-463, and you can take them in any order.
1. Microsoft Certified Professional: As soon as you pass any one exam you are an MCP.
2. Microsoft Certified Solution Associate: After you are done with all three (70-461 + 70-462 + 70-463) you are an MCSA.
3. Microsoft Certified Solution Expert: After achieving MCSA, you can either go for MCSE in Data Platform (70-464 + 70-465) or Business Intelligence (70-466 + 70-467).
4. Microsoft Certified Solution Master: After achieving MCSA in Data Platform, you can go even further to MCSM by taking 70-468 & 70-469 exams.
–> Study material for:
– Exam 70-461: Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 | My Blog Post on 70-461.
– Exam 70-462: Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases | My Blog Post on 70-462
– Exam 70-463: Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012 | My Blog Post on 70-463
– Exam 70-464: Channel9 Video | Instructor-led – Developing Microsoft SQL Server 2012 Databases
– Exam 70-465: Channel9 Video | Instructor-led – Designing Database Solutions for Microsoft SQL Server 2012
– Exam 70-466: Channel9 Video | Instructor-led – Implementing Data Models and Reports with Microsoft SQL Server 2012
– Exam 70-467: Channel9 Video | Instructor-led – Designing Business Intelligence Solutions with Microsoft SQL Server 2012
– Exam 70-986: Not yet available
– Exam 70-987: Not yet available
– MCSE (DP: 464/465 and BI: 466/467) exams are updated with SQL 2014 topics.
– MCSA (461/462/463) exams will be having SQL 2012 content only.
For more details about the Certification Path and exams you can check Microsoft Official site: http://www.microsoft.com/learning/en-us/sql-certification.aspx
–> Download “SQL Server 2014 Full or Express version for practice: