MCSA: SQL Server certification demonstrates your skills and breakthrough insights in developing and maintaining the next wave of mission-critical environments. Earning this certification qualifies you for a position as a software as a database developer or database analyst.

The modules of this program

20461D: Querying Microsoft SQL Server 2014

20462D: Administering Microsoft SQL Server 2014 Databases

20463D: Implementing Data Warehouse with Microsoft SQL Server 2014

Duration: 120 hours

COURSE OUTLINE

20461D: Querying Microsoft SQL Server 2014

This course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014.

This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.

The main purpose of the course is to prepare people for the exam “70-461: Writing Queries Using Microsoft® SQL Server® 2014 Transact-SQL.” This exam will be the underlying exam for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.

Candidates suitable for this course:

• As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.
• This course is designed for customers who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014, but also the important capabilities across the SQL Server data platform.
• This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals.
• The course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, report writers, business analysts and client application developers.

After completing this course, you will be able to:

• Describe the basic architecture and concepts of Microsoft SQL Server 2014.
• Understand the similarities and differences between Transact-SQL and other computer languages
• 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
• Improve the throughput and response time of applications by using tasks and asynchronous operations
• Integrate unmanaged libraries and dynamic components into a C# application
• Examine the metadata of types by using reflection, create and use custom attributes, generate code at runtime, and manage assembly versions.
• Encrypt and decrypt data by using symmetric and asymmetric encryption.

20462D: Administering Microsoft SQL Server 2014 Databases

This course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2014 database.

The course focuses on teaching individuals how to use SQL Server 2014 product features and tools related to maintaining a database.

Note: This course is designed for customers who are interested in learning SQL Server 2014. It covers the new features in SQL Server 2014, but also the important capabilities across the SQL Server data platform.

Candidates suitable for this course:

• The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.
• The secondary audience for this course is individuals who develop applications that deliver content from SQL Server databases.

After completing this course, you will be able to:

• Describe core database administration tasks and tools.
• Install and configure SQL Server 2014.
• Configure SQL Server databases and storage.
• Plan and implement a backup strategy.
• Restore databases from backups.
• Import and export data. Monitor SQL Server.
• Trace SQL Server activity.
• Manage SQL Server security.
• Audit data access and encrypt data.
• Perform ongoing database maintenance.
• Automate SQL Server maintenance with SQL Server Agent Jobs.
• Configure Database Mail, alerts and notifications.

20463D: Implementing Data Warehouse with Microsoft SQL Server 2014

This course describes how to implement a data warehouse platform to support a BI solution.

Students will learn how to create a data warehouse with Microsoft® SQL Server® 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.

Note: This course is designed for customers who are interested in learning SQL Server 2014. It covers the new features in SQL Server 2014, but also the important capabilities across the SQL Server data platform.

Candidates suitable for this course:

• This course is intended for database professionals who need to fulfil a Business Intelligence Developer role. They will need to focus on hands-on work creating BI solutions including Data Warehouse implementation, ETL, and data cleansing.
• Primary responsibilities include: 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.

After completing this course, you will be able 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.