Advertisements

Archive

Archive for the ‘Certifications’ Category

Preparing for 70-462 Exam : Administering Microsoft SQL Server 2012 Databases | Certification

May 1, 2015 10 comments

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:
– Installation
– Maintenance
– 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!!!

Advertisements

Preparing for 70-463 Exam : Implementing a Data Warehouse with Microsoft SQL Server 2012

December 5, 2013 19 comments

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

Book on AMAZON.com: Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012
 

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!!!


SQL Server 2012 Certification Path

November 6, 2013 16 comments

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:

SQL Server 2012 Certification Path

SQL Server 2012 Certification Path

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
 

–> UPDATE:

– 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:

SQL Server 2014 Download


Passed 70-461 Exam : Querying Microsoft SQL Server 2012

November 16, 2012 266 comments

 
Yesterday (Nov 15th 2012) I went through this exam and passed it. From long back I was looking an opportunity to give at least a single SQL Server Certification Exam to check my worth.

Just a week back I scheduled a slot for this Exam (for yesterday). I was looking for some reading material for the same but I couldn’t get any. Though there is a book from Microsoft Press i.e. Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012, but it was not available at my nearest.

As I maintain this blog on SQL Server and also evangelize on SQL Server 2012 latest features from long back, so I thought to give it a try and wow I went thru in a single shot!!!

Here by this post I want to provide my readers some information and knowledge about this Exam, so that they can plan well for this and look forward to take up this exam.
 

–> Exam Highlights:

– There were total 54 Questions
– Total time to attempt the paper was 3 Hrs.
– Total Marks: 1000
– Passing Marks: 700
– The Questions in the Exam were:
1. Mostly Objective Type.
2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
3. And very few were Multiple Choice.
4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.
 

–> The Exam is divided into 4 modules:
 

1. Create Database Objects | 24%

– CREATE/ALTER/DROP Database, [link], [video]

– CREATE/ALTER/DROP Table, [link], [video]

– ADD/DROP/Rename table’s Columns, [video]

– Data Types [video]:
– – (XML, DATETIME, SPATIAL, VARCHAR)

– Constraints [link], [video]
– – (Primary Key, Unique Key, Foreign Key, NOT NULL, Check, Default)

– Computed Columns [video]:

– Views [link]

– Indexed Views (SCHEMABINDING, COUNT_BIG(*), CLUSTERED INDEX)

– Stored Procedures, [link].

– DML Triggers (INSERTED, UPDATED, UPDATE function) vs CHECK Constraint

– TRIGGER for VIEWS (INSTEAD OF)

– UDF (Functions), [link].

– SP vs UDF, [link].
 

2. Work with Data | 27%

– New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), [link].

– FETCH-OFFSET, [link].

– SEQUENCE, [link].

– Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [link], [video].

– OVER() Window Function, [link]

– JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), [link].

– APPLY Operators (CROSS APPLY vs OUTER APPLY), [link].

– CTE and Sub-Queries, [link].

– PIVOT, [link].

– ROLLUP, CUBE & GROUPING SETS, [link].

– Dynamic SQL

– ANY, SOME, ALL

– CASE vs ISNULL vs COALESCE, [link].

– FOR XML RAW/AUTO/PATH [ELEMENTS], [link].

– Implementing XML Schemas and Handling of XML data
 

3. Modify Data | 24%

– Stored Procedure (with EXECUTE AS, RECOMPILE)

– MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT), [link].

– EXCEPT vs INTERSECT
– UNION vs UNION ALL, [link].

– SCALAR vs TABLE Valued Functions.

– Use of APPLY with UDFs, [link].

– VARCHAR(MAX) and .WRITE(), [link].
 

4. Troubleshoot & Optimize | 25%

– Using Statistics

– SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted), [link].

– TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT), [link].

– ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)

– TRY/CATCH, [link].

– RAISE vs THROW [link]

– CURSORS (Row-Based) vs SET Based Approach

– Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)

– Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))

 
For “SQL Server 2014” exam certification check here.

 
–> You can check following Study materials to prepare for this Exam:

Microsoft Link for this Certification: https://www.microsoft.com/learning/en-us/exam-70-461.aspx

Books on AMAZON.com:
Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Microsoft SQL Server 2012 T-SQL Fundamentals
 

–> Download “SQL Server 2014 Express” (free) version to practice T-SQL Queries:

 
Join SQL Server 2016 groups on [LinkedIn] and [Facebook] for more updates.

 
All The Best!!!