Course Agenda

Exam 70-461: Querying Microsoft SQL Server 2012

1. Introduction to Microsoft SQL Server 2012

• Microsoft SQL Server 2012 Basics
• Getting Started with SQL Server Management Studio

2. Getting Started with SQL Azure

• Overview
• Working with SQL Azure

3. Introduction to T-SQL Querying

• T-SQL
• Sets
• Predicate Logic
• Logical Order of Operations in SELECT Statements

4. Writing SELECT Queries

• Writing Simple SELECT Statements
• Eliminating Duplicates with DISTINCT
• Using Column and Table Aliases
• Writing Simple CASE Expressions

5. Querying Multiple Tables

• Joins
• Querying with Inner Joins
• Querying with Outer Joins
• Querying with Cross Joins and Self Joins

6. Sorting and Filtering Data

• Sorting Data
• Filtering Data with a WHERE Clause
• Filtering with the TOP and OFFSET-FETCH Options
• Working with Unknown and Missing Values

7. Working with SQL Server 2012 Data Types

• SQL Server 2012 Data Types
• Working with Character Data
• Working with Date and Time Data

8.Using Built-In Functions

• Writing Queries with Built-In Functions
• Using Conversion Functions
• Using Logical Functions
• Using Functions to Work with NULL

9. Grouping and Aggregating Data

• Using Aggregate Functions
• Using the GROUP BY Clause
• Filtering Groups with HAVING

10. Using Subqueries

• Writing Self-Contained Subqueries
• Writing Correlated Subqueries
• Using the EXISTS Predicate with Subqueries

11. Using Table Expressions

• Using Derived Tables
• Using Common Table Expressions
• Using Views
• Using Inline Table-Valued Functions

12. Using Set Operators

• Writing Queries with the UNION Operator
• Using EXCEPT and INTERSECT
• Using APPLY

13. Using Window Ranking, Offset, and Aggregate Functions

• Creating Windows with OVER
• Exploring Window Functions

14.Pivoting and Grouping Sets

• Writing Queries with PIVOT and UNPIVOT
• Working with Grouping Sets

15. Querying SQL Server Metadata

• Querying System Catalog Views and Functions
• Executing System Stored Procedures
• Querying Dynamic Management Objects

16. Executing Stored Procedures

• Querying Data with Stored Procedures
• Passing Parameters to Stored Procedures
• Creating Simple Stored Procedures
• Working with Dynamic SQL

17. Programming with T-SQL

• T-SQL Programming Elements
• Controlling Program Flow

18. Implementing Error Handling

• Using TRY/CATCH Blocks
• Working with Error Information

19. Implementing Transactions

• Transactions and the Database Engine
• Controlling Transactions

20. Improving Query Performance

• Factors in Query Performance
• Displaying Query Performance Data

Exam 70-462: Administering Microsoft SQL Server 2012 Databases

1. Introduction to SQL Server 2012 and its Toolset

• SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services

2. Preparing Systems for SQL Server 2012

• SQL Server Architecture
• Planning Server Resource Requirements
• Pre-Installation Testing for SQL Server

3.Installing and Configuring SQL Server 2012

• Preparing to Install SQL Server
• Installing SQL Server
• Upgrading and Automating Installation

4. Working with Databases

• SQL Server Databases
• Working with Files and Filegroups
• Moving Database Files

5. SQL Server 2012 Recovery Models

• Backup Strategies
• SQL Server Transaction Logging
• Planning a SQL Server Backup Strategy

6.Backup of SQL Server 2012 Databases

• Backing Up Databases and Transaction Logs
• Managing Database Backups
• Working with Backup Options

7. Restoring SQL Server 2012 Databases

• The Restore Process
• Restoring Databases
• Working with Point-in-Time Recovery
• Restoring System Databases and Individual Files

8. Importing and Exporting Data

• Transferring Data to/from SQL Server
• Importing and Exporting Table Data
• Inserting Data in Bulk

9. Authenticating and Authorizing Users

• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorization Across Servers

10.Assigning Server and Database Roles

• Working with Server Roles
• Working with Fixed Database Roles
• Creating User-Defined Database Roles

11. Authorizing Users to Access Resources

• Authorizing User Access to Objects
• Authorizing Users to Execute Code
• Configuring Permissions at the Schema Level

12. Auditing SQL Server Environments

• Options for Auditing Data Access in SQL
• Implementing SQL Server Audit
• Managing SQL Server Audit

13. Automating SQL Server 2012 Management

• Working with SQL Server Agent
• Managing SQL Server Agent Jobs

14. Configuring Security for SQL Server Agent

• SQL Server Agent Security Basics
• Configuring Credentials
• Configuring Proxy Accounts

15.Monitoring SQL Server 2012 with Alerts and Notifications

• Configuration of Database Mail
• Monitoring SQL Server Errors
• Configuring Operators, Alerts, and Notifications

16. Performing Ongoing Database Maintenance

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance

17. Tracing Access to SQL Server 2012

• Capturing Activity using SQL Server Profiler
• Improving Performance with the Database Engine Tuning Advisor
• Working with Tracing Options

18. Monitoring SQL Server 2012

• Monitoring Activity
• Capturing and Managing Performance Data
• Analyzing Collected Performance Data

19. Managing Multiple Servers

• Working with Multiple Servers
• Virtualizing SQL Server
• Deploying and Upgrading Data-Tier Applications

20. Troubleshooting Common SQL Server 2012 Administrative Issues

• SQL Server Troubleshooting Methodology
• Resolving Service-Related Issues
• Resolving Concurrency Issues
• Resolving Login and Connectivity Issues

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

1.Data Warehousing

• Overview of Data Warehousing
• Considerations for a Data Warehouse Solution

2. Data Warehouse Hardware

• Considerations for Building a Data Warehouse
• Data Warehouse Reference Architectures and Appliances

3.Designing and Implementing a Data Warehouse

• Logical Design for a Data Warehouse
• Physical Design for a Data Warehouse

4. Creating an ETL Solution with SSIS

• ETL with SSIS
• Exploring Source Data
• Implementing Data Flow

5.Implementing Control Flow in an SSIS Package

• Control Flow
• Creating Dynamic Packages
• Using Containers
• Managing Consistency

6. Debugging and Troubleshooting SSIS Packages

• Debugging an SSIS Package
• Logging SSIS Package Events
• Handling Errors in an SSIS Package

7. Implementing an Incremental ETL Process

• Incremental ETL
• Extracting Modified Data
• Loading Modified Data

8. Incorporating Data from the Cloud into a Data Warehouse

• Overview of Cloud Data Sources
• SQL Server Database
• The Windows Azure Marketplace

9. Enforcing Data Quality

• Data Quality
• Using Data Quality Services to Cleanse Data
• Using Data Quality Services to Match Data

10. Using Master Data Services

• Master Data Services
• Implementing a Master Data Services Model
• Using the Master Data Services Add-in for Excel

11. Extending SQL Server Integration Services

• Using Custom Components in SSIS
• Using Scripts in SSIS

12. Deploying and Configuring SSIS Packages

• SSIS Deployment
• Deploying SSIS Projects
• Planning SSIS Package Execution

13. Consuming Data in a Data Warehouse

• Business Intelligence
• Reporting
• Data Analysis