MCSA SQL Server 2012

The Pairview difference

Pairview is a specialist provider of career services and technical and management training. We concentrate on targeting the industry sectors where there are severe skill-shortages in the UK. At Pairview, we do much more than providing IT and management training – we care about your career and are passionate about helping you flourish.

We can help you – just like hundreds of other university graduates and experienced professionals across the UK we have helped.  We analyse your background and interests to develop a personalised career path programme in areas that most suit you. We also can provide single courses that are specially tailored to your career development, help you develop highly sought-after expertise and will immediately put you in demand and boost your earning potential.

Microsoft Certified Solutions Associate: SQL Server 2012 – The ticket to database development or database analysis careers

MCSA: SQL Server 2012 certification demonstrates your essential skills and breakthrough insights in developing and maintaining the next wave of mission-critical environments. Earning this professional qualification will make you stand out from competitive applicants and qualify you for a position as a Database Developer or Database Analyst with an average starting salary of £38,000-£42,000 per annum. This 13-day course package provides you with the knowledge and technical skills ranging from querying SQL Server and administering SQL Server to implementing a data warehouse with SQL Server, which are required to pass three associated exams to attain the MCSA.

Exam 70-461 measures your ability to:

• Create database objects

• Work with data

• Modify data

• Troubleshoot and optimise

Exam 70-462 measures your ability to:

• Install and configure

• Maintain instances and databases

• Optimize and troubleshoot

• Manage data, Implement security

• Implement high availability

Exam 70-463 measures your ability to:

• Design and implement a data warehouse

• Extract and transform data

• Load data

• Configure and deploy SSIS solutions

• Build data quality solutions

Course package benefits

• Full training materials built around the syllabus

• Practical lab facilities

• Exam preparation and mock examinations

• Official exams 70-461, 70-462 and 70-463 required to achieve the MCSA certification

• Free one-on-one consultation with Pairview’s Career Consultants about your career path and work placement

• Refreshment and lunch

1. Querying Microsoft® SQL Server 2012® and Exam 70-461 | 4 days

You will learn how to:

• Write SELECT queries

• Query multiple tables

• Sort and filter data

• Describe the use of data types in SQL Server

• Modify data using Transact-SQL

• Use built-in functions

• Group and aggregate data

• Use subqueries

• Use table expressions

• Use set operators

• Use window ranking, offset and aggregate functions

• Implement pivoting and grouping sets

• Execute stored procedures

• Program with T-SQL

• Implement error handling

• Implement transactions

At the end of the course, you will be prepared for and take exam 70-461

      2. Administering Microsoft® SQL Server 2012® Databases and Exam 70-462 | 5 days

You will learn how to:

• Plan and install SQL Server

• Describe system databases, the physical structure of databases, and the most common configuration options related to them

• Explain the concept of the transaction log and the SQL Server recovery models and implement different backup strategies that are available with SQL Server

• Back up SQL Server databases

• Restore SQL Server databases

• Import and export data by using wizards, the bulk copy utility, and data-tier applications

• Work with SQL Server security models, logins, users, and contained databases

• Work with server roles and database roles

• Use permissions to secure resources

• Use SQL Server audit and Transparent Data Encryption

• Work with SQL Server Agent to configure and execute jobs

• Implement SQL Server Agent security, proxy accounts, and credentials

• Configure database mail, alerts, and notifications

• Create database maintenance plans

• Use SQL Server Profiler, SQL Trace, and Distributed Relay

• Use Distributed Management Views and configure data collection

 At the end of the course, you will be prepared for and take exam 70-462.

      3. Implementing a Data Warehouse with Microsoft® SQL Server 2012® and Exam 70-463 | 4 days

You will learn to:

• Describe data warehouse concepts and architecture considerations

• Select an appropriate hardware platform for a data warehouse

• Design and implement a data warehouse

• Implement Data Flow in an SSIS Package

• Implement Control Flow in an SSIS Package

• Debug and Troubleshoot SSIS packages

• Implement an ETL solution that supports incremental data extraction

• Implement an ETL solution that supports incremental data loading

• Implement data cleansing by using Microsoft Data Quality Services

• Implement Master Data Services to enforce data integrity

• Extend SSIS with custom scripts and components

• Deploy and Configure SSIS packages.

• Describe how BI solutions can consume data from the data warehouse.

• Implementing a data warehouse

• Developing SSIS packages for data extraction, transformation, and loading

• Enforcing data integrity by using Master Data Services

• Cleansing data by using Data Quality Services

At the end of the course, you will be prepared for and take exam 70-463.

This course package is suitable to anyone with a university degree in any subject. Although not a requirement, you should ideally have a background in management, science, IT or mathematics, or study some technical and numerate subjects in your degree. Such a background will be an advantage to help you drive a successful career in database analysis and business intelligence.

• Basic working knowledge of Windows operating systems and Microsoft Excel

• A prior knowledge of RDBMS, tables, columns and relationships would be helpful.

• Database Developer

• Database Analyst

• Management Information Analyst

• Business Intelligence Specialist