Geeks With Blogs
Deepak Puri
IT Professionals working in Sql Server can gain their MCSA: SQL Server 2012 certification by following this post. I passed these 3 exams in August 2012.


I) Exam 70-461: Querying Microsoft SQL Server 2012
This exam should be a walk in the park for most of the people who have worked in Sql Server for even a year. As far as new features are concerned I believe there is nothing you will learn that you couldn't do earlier anyways but definitely a Good Revision. There are some new features added with the release of Sql Server 2012 and one benefit of learning them is that when you come across Sql code written by some of the newbies, you will understand it straight away. Using in day to day life, I will leave it to the Individual's discretion. Ok, now going back to some of the material I looked over before giving the exam that made me clear this exam. I want to note here a good source of learning the below concepts (actually for all 3 exams) is of course Books Online (its free, can be downloaded from Microsoft and incorporates as the F1 help in Sql Server management studio). If you are new to Sql Server the classroom based training provided by Microsoft will help you pass this exam, if you are having trouble paying the big amount for the course, shoot me an email and I will try and arrange something for you or check for any discounts that can be availed. Enjoy & Goodluck!

I will list below Headings and then the Concept involved, if you know the concept already move to the next point, if not then lookup in Books online or your favorite reference material.

P.S I will try and put my notes under all of the below headings but running short of time now, so please excuse me. However let me know if it is required and I will make time for it sooner.

1. Sql Server 2012 and Sql Azure Introduction
- You can find some online videos at the msdn website with good introduction to this. Good idea here to look up the different editions and the versions of sql server in history. Some understanding on how Sql Server is built along the lines of Containers (tables, views, etc), Boundaries (security, accounts, etc.) and Files (data files, log files, filegroups) will help. 
- Projects: can be created in SSMS like they are in Visual studio, have a look at that.
- Sql Azure: Windows Azure Account, Sql Server and then Sql Database, the 3 key concepts involved.
- Key distinctions b/w Sql azure and On-premise Sql server.
- Sign up for a 3 months windows azure account to get a feel of Sql azure, its free :)

2. T-SQL querying (sets, predicate logic and logical order of operations)
- TSql is declarative not procedural Concept.
- DML, DDL and DCL 
- TSql language elements: (predicates and operators, functions, variables, expressions, batch operators, control of flow, comments)
- Difference b/w @ and @@. The single is used for naming variable and the double is reserved by sql server for system functions.
- Control of flow (if...else; while; break; continue; begin...end)
- Error Handling (try/catch)
- Transaction Control (begin transaction; commit/rollback transaction) 
- Batch operators (GO; GO n times features)

3. Select Queries and Joins

4. Sorting and Filtering data

5. Sql Server 2012 data types

6. Functions

(Ranking, Offset and Aggregate)

7. Set Operators (Union, Except, Intersect, Apply)

8. Grouping

9. Pivoting and Grouping sets

10. Subqueries

11. Table Expressions

12. Metadata

13. Stored Procedures

14. Program Flow

15. Error Handling

16. Transactions

17. Query Performance


II) Exam 70-462: Administering Microsoft SQL Server 2012 Databases
This exam is also not too hard, actually quite easy, seriously quite easy :) 
Alright, here is some preparation i did before giving this exam. I looked over the below topics, for reference material to look up these topics please see above, the Exam 70-461 description.

1. Tool set

2. Architecture, Planning server resources and Pre-installation testing

3. Configuring

4. Databases, Files and Filegroups
- moving database files

5. Recovery Models

6. Backup & Restoring

7. Importing & Exporting data

8. Authentication & Authorization

9. Server and Database Roles

10. Authorizing users to specific resources

11. Auditing

12. Automation

13. Security for Sql Server Agent

14. Monitoring (Alerts & Notifications)

15. Ongoing Maintenance

16. Tracing

17. Monitoring for Performance

18. Managing Multiple Servers

19. Troubleshooting common admin issues


III) Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
For this exam if you haven't worked with SSIS much it can be a challenge. I would suggest do some online labs or some sort of practice at work or home, along the lines of some basic package building and shortcuts since in the exam there may be some questions about it. But overall really there isn't much to know, here is a list of things i looked over before the exam, for reference material to look up these topics please see above, the 70-461 description.

1. Some introduction to Data Warehousing from other Expert's view.
(a few online videos will give a good grasp of the aim for the subject)

2. Hardware

3. Designing & Implementing (Logical / Physical)

4. ETL with SSIS

5. Control Flow in a package

6. Debugging and Troubleshooting packages

7. Incremental ETL

8. Incorporating Cloud data in a Warehouse

9. Data Quality (Enforcing)

10. Master Data Services

11. Extending SSIS

12. Deploying and Configuring packages

13. Consuming a Sql Server Data Warehouse


Ok, well that's it for now, Goodbye and Goodluck.

In my next post I will tell my journey to MCSE: Data Platform (Sql Server 2012) for the DBA's.

P.S I will try and put some of my notes under ALL OF THE ABOVE headings but running short of time now, so please excuse me. However let me know if it is required and I will make time for it.

Posted on Saturday, February 16, 2013 9:51 AM Certifications , Sql Server | Back to top


Comments on this post: MCSA: Sql Server 2012 Certification

comments powered by Disqus

Copyright © deepakpuri | Powered by: GeeksWithBlogs.net