MCSA SQL Certification Package

Master the fundamentals of enterprise-class database management using Microsoft SQL Server with this course bundle. From basic installation and maintenance, through to advanced deployments that support Business Intelligence applications, the MCSA SQL Server bundle contains everything required to sit and pass the associated Microsoft examination.

Moving quickly from basic concepts to advanced techniques, this cost-effective course bundle is best suited to:

• Server managers who are responsible for managing and maintaining their corporate SQL Server databases.
• Developers and programmers who need to understand the SQL Server architecture for building data warehousing and Business Intelligence applications.
• Technical staff looking to upgrade their skills and get ahead and take on database management responsibilities.
• Individuals who would like to facilitate a career change into database management with a globally-recognised vendor certification.

The MCSA SQL Server Certification bundle contains four separate courses to give a complete grounding in database management, particularly with regards to data warehousing and Business Intelligence applications. Delivered completely online using the e-learning platform, students can access content whenever, wherever and however they choose. A careful blend of instructor-led videos, interactive content and eGuides takes students step-by-step through each concept, ensuring they have a good understanding of each topic before moving on.

The MCSA SQL Server Certification bundle also includes access to the eCareers Live Labs, helping students develop and refine their practical database querying and management skills with access to dedicated live equipment. This set-up allows them to put their knowledge to the test and recreate each of the scenarios they encounter in their studies. By completing this course students will not only fully understand the concepts behind Microsoft SQL Server 2012, but also develop the skills needed to properly manage, maintain and manipulate a database in a real world environment as well passing the included MCSA certification exam.

key learning points

Composed of four in-depth standalone courses, the MCSA SQL Certification bundle contains everything needed to manage a SQL Server database environment – particularly those being used for data warehousing or business intelligence applications.

The first course – Querying SQL Server 2012 – teaches candidates how to access and manipulate data stored in the tables using Transact-SQL (T-SQL):

• Understanding the Transact-SQL (T-SQL) language, and how to build efficient queries, stored procedures and views that return key information quickly and efficiently.
• How to combine datasets, rank results and extend tables programmatically without needing a GUI tool.
• Optimise T-SQL statements for the most efficient operation and resource consumption.
• Extend reporting and data transfer capabilities using XML.

The next course – Administering SQL Server 2012 Databases - then takes students through the tools and techniques required to manage and maintain a SQL Server environment. Participants will learn to:

• Appreciate the correct way to install and use add-on components, instances and services.
• Build systems for high availability environments using AlwaysOn, mirroring and replication to keep data online at all times.
• Protect information from loss using database backup, restore and disaster recovery (DR) techniques.
• Secure database contents by configuring permissions, alerts, logins, roles and more.
• Perform troubleshooting, maintenance and optimisation on database tables to reduce instances of failure or corruption.

The third course – Implementing a Data Warehouse with SQL Server 2012 – introduces the concepts behind data warehousing, and building extremely large database stores:

• Designing and building multi-dimension databases using SQL Server 2012.
• Using Integration Services and Control Flow to improve the movement of data.
• Debugging and troubleshooting data warehouse problems.
• Deploying and securing packages and databases from unauthorised access or use.

The fourth and final course – Microsoft SQL Server 2008 Business Intelligence Development and Maintenance – looks at using SQL Server databases for Business Intelligence and data mining purposes, covering:

• How to work with large datasets across multiple data sources and platforms as is common in most modern BI applications and reports.
• Building Online Analytical Processing (OLAP) cubes and other data mining structures such as pivot tables, PowerPivot solutions and Multidimensional Expressions (MDX) to deliver data and insights quickly and efficiently to analysts.
• Choosing between the Unified Dimensional Model (UDM) and Business Intelligence Semantic models to develop custom business data reporting routines.
• Developing complex SQL queries to mine multiple datasets and building reports using Crescent.

advantages of this course

Once the four constituent courses have been completed, students will be fully prepared to work in enterprise-class database environments, and to sit and pass the included MCSA examination.

Other benefits of completing this course include:

• The skills required to help work in database administration or on enterprise-class database deployment projects.
• The knowledge and techniques to become a valued member of the data management team.
• The technical skills needed to specify and deploy large-scale database systems.
• Valuable database management skills that will improve job prospects and employment choices.

Once all of the practical exercises have been complete, students will have all the knowledge and experience they need to earn their MCSA qualification – particularly valuable proof of their skills and ability.

• Units of study

Microsoft 70-448: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

• SSIS Packages
• Debugging and Error Handling in SSIS
• Deploying and Configuring SSIS Packages
• Securing and Executing SSIS Packages; Service Administration
• Data Analysis
• Extending SSAS Cubes
• Extending SSAS Cubes
• Securing and Administering SSAS
• Working With SSAS Data Mining
• Developing SSAS Reports
• Extending and Deploying SSRS Reports
• Scheduling Deployed Reports and Data Sources
• Configuring And Administering the SSRS Server

Microsoft 70-461: Querying SQL Server 2012

• Getting Started with SQL Server 2012
• Working with T-SQL
• Writing SELECT Queries
• Working with SQL Data Types
• Sorting and Filtering Data
• Querying Data from Multiple Tables
• Modifying Data
• Working with SQL Server Built-in Functions
• Programming in T-SQL
• Implementing Stored Procedures
• Working with Subqueries and Table Expressions
• Working with Set Operators, Conditional Operators, and Window Functions
• Working with PIVOT, UNPIVOT, and Grouping Sets
• Managing Error Handling and Transactions
• Querying SQL Server System
• Optimizing Query Performance

Microsoft 70-462: Administering SQL Server 2012 Databases

• Identifying the SQL Server Platform
• Deploying SQL Server
• Configuring SQL Server
• Managing Databases in SQL Server 2012
• Managing SQL Server Security
• Implementing Advanced Security Settings
• Applying Encryption and Compression
• Working with Indexes and Log Files
• Working with Backup and Restore
• Implementing High Availability
• Optimizing Server Performance
• Troubleshooting Issues and Recovering Databases
• Performing Advanced Database Management Tasks

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

• Introduction to Data Warehouse
• Creating Dimensions and Changing Granularity of Dimensions
• Creating Fact Tables and ColumnStore Indexes
• Implementing Data Warehouse in SQL Server 2012
• Working with Integration Services
• Managing Control Flow
• Working with Dynamic Variables
• Implementing Data Flow
• Managing Data Flow
• Managing SSIS Package Execution
• Debugging and Troubleshooting
• Deploying Packages
• Securing Packages and Databases
• Working with MDS and Windows