MCSA SQL Server 2012/2014 Solutions Associate
Overview

MCSA SQL Server 2012/2014 Solutions Associate
The Microsoft Certified Solutions Associate (MCSA) SQL Server 2012/2014 certification will equip learners with the essential skills in developing and maintaining the next wave of mission critical SQL database environments. Including querying and administering SQL servers as well as implementing data warehouses to support and secure business infrastructures.
Study time: Full time – 4 days| Part time – 2 to 3 weeks
Vendor: Microsoft
Business support: Our customer success team measures and ensures engagement
Prerequisites: Developers with two+ years experience and good working knowledge of relational databases
Assessment: Exam based – MCSA 70-461, 70-462 + 70-463
Ideal for: Database developer, database analyst ETL and data warehouse developers who create business intelligence solutions
Meet the Trainer
“By 2020, the accumulated volume of big data will increase from 4.4 zettabytes to roughly 44 zettabytes or 44 trillion GB.”
Content overview
70-461: Querying Microsoft SQL Server 2012
- Create and alter tables using T-SQL syntax- simple statements
- Create tables without using the built-in tools ALTER, DROP, ALTER, COLUMN and CREATE
- Create and alter views
- Create indexed views
- Create views without using the built-in tools CREATE, ALTER and DROP
- Query data by using SELECT statements
- Implement subqueries
- Identify problematic elements in query plans
- Pivot and unpivot
- Apply operator
- Cte statement with statement
- Implement data types
- Query and manage XML data
- Create and alter stored procedures
- Modify data by using INSERT, UPDATE, and DELETE statements
- Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL
- Know which SQL statements are best to solve common requirements
- Use output statement
- Optimise queries
- Understand statistics
- Read query plans
- Plan guides
- DMVs hints
- Statistics IO
- Dynamic vs. parameterised queries
- Describe the different join types – HASH, MERGE, LOOP – and describe the scenarios they would be used in managing transactions
70-462: Administering Microsoft SQL Server 2012 Databases
- Plan installation
- Evaluate installation requirements
- Design the installation of SQL server and its components
- Install SQL server and related services
- Test connectivity
- Enable and disable features
- Install SQL server database engine and SSIS
- Configure an OS disk
- Configure additional SQL server components
- Manage SQL server agent
- Manage and configure databases
- Configure SQL server instances
- Implement a SQL server clustered instance
- Identify and resolve concurrency problems
- Audit SQL server instances
- Implement a security strategy for auditing and controlling the instance
- Manage different backup models
- Restore a database secured with TDE
- Recover data from a damaged DB
- Restore to a point in time
- File group restore
- Page level restore import and export data
- Transfer data
- Manage logins and server roles
- Configure database security
- Manage users and database roles
- Implement always
- Set up mirroring and monitor the performance of database mirroring
- Troubleshoot replication problem and identify appropriate replication strategy
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
- Design and implement dimensions
- Design and implement fact tables
- Design a data warehouse that supports many to many relationships
- Define connection managers
- Plan the configuration of connection managers
- Package level or project level connection manager
- Define a connection string and parameterisation of connection strings
- Design and implement data flow
- Manage SSIS package execution
- Implement package logic by using SSIS variables and parameters
- Implement control flow
- Implement script components in SSIS
- Troubleshoot data integration issues
- Install and maintain SSIS components
- Configure SSIS security settings
- Install and maintain data quality services
- Installation prerequisites
Our Promise
Trusted by…