Week 1 Manage data

Configure and maintain a backup strategy
- Manage different backup models,
- including point-in-time recovery;
- protect customer data even if backup media is lost;
- perform backup/restore based on proper strategies including backup redundancy;
- recover from a corrupted drive;
- manage a multi-terabyte database;
- implement and test a database implementation and a backup strategy (multiple files for user database and tempdb,
- spreading database files, backup/restore);
- back up an SQL Server environment; back up system databases
Restore databases
- Restore a database secured with TDE;
- recover data from a damaged DB;
- restore to a point in time;
- file group restore;
- page level restore
Implement and maintain indexes
- Inspect physical characteristics of indexes and perform index maintenance;
- identify fragmented indexes;
- identify unused indexes;
- implement indexes; defrag/rebuild indexes;
- set up a maintenance strategy for indexes and statistics;
- optimise indexes (full, filter); statistics (full, filter) force or fix queue;
- when to rebuild vs. reorg and index;
- full text indexes; column store indexes
Import and export data
- Transfer data;
- bulk copy;
- bulk insert

Week 2 Implement security

Manage logins and server roles
- Configure server security;
- secure the SQL Server using Windows Account/SQL Server accounts, server roles;
- create log in accounts;
- manage access to the server, SQL Server instance, and databases;
- create and maintain user-defined server roles;
- manage certificate logins
Manage database permissions
- Configure database security; database level permissions;
- protect objects from being modified
Manage users and database roles
- Create access to server/database with least privilege;
- manage security roles for users and administrators;
- create database user accounts; contained logins
Troubleshoot security
- Manage certificates and keys;
- endpoints

Week 3 Implement high availability

Implement AlwaysOn
- Implement a mirroring solution using AlwaysOn;
- failover
Implement database mirroring
- Set up mirroring;
- monitor the performance of database mirroring
Implement replication
- Troubleshoot replication problems;
- identify appropriate replication strategy

Week 4 Design and implement a data warehouse

Design and implement dimensions
- Design shared/conformed dimensions;
- determine whether you need support for slowly changing dimensions;
- determine attributes;
- design hierarchies;
- determine whether you need star or snowflake schema;
- determine the granularity of relationship with fact tables; determine the need for auditing or lineage;
- determine keys (business transactional or your own data warehouse/surrogate keys);
- implement dimensions;
- implement data lineage of a dimension table
Design and implement fact tables
- Design a data warehouse that supports many to many relationships;
- appropriately index a fact table using columnstore indexes;
- partitioning; additive measures; semi-additive measures;
- non-additive measures;
- implement fact tables;
- determine the loading method for the fact tables;
- implement data lineage of a fact table;
- design summary aggregation tables

Week 5 Extract and transform data

Design data flow
- Define data sources and destinations;
- distinguish blocking and non-blocking transformations;
- use different methods to pull out changed data from data sources;
- determine appropriate data flow components;
- determine the need for supporting Slowly Changing Dimensions (SCD);
- determine whether to use SQL Joins or SSIS lookup or merge join transformations;
- batch processing vs. row by row processing;
- determine the appropriate transform to use for a specific task;
- determine the need and method for identity mapping and deduplicating;
- fuzzy lookup, fuzzy grouping and Data Quality Services (DQS) transformation;
- determine the need for text mining;
- determine the need for custom data sources, destinations and transforms;
- determine what to do with erroneous rows;
- determine auditing needs;
- determine sampling needs for data mining;
- trusted/authoritative data sources, including warehouse metadata
Implement data flow
- Debug data flow;
- use the appropriate data flow components;
- SQL/SSIS data transformation;
- create SSIS packages that support slowly changing dimensions;
- use the Lookup task in SSIS;
- map identities using SSIS Fuzzy Lookup;
- specify a data source and destination;
- use data flows;
- different categories of transformations;
- read, transform and load data;
- understand which transforms to use to accomplish a specific business task;
- data correction transformation;
- performance tune an SSIS dataflow;
- optimise Integration Services packages for speed of execution;
- maintain data integrity, including good data flow
Implement script tasks in SSIS
- Determine whether it is appropriate to use a script task;
- extend the capability of a control flow;
- perform a custom action as needed (not on every row) during a control flow

Week 6 Load data

Design control flow
- Determine control flow;
- determine containers and tasks that are needed;
- determine precedence constraints;
- design an SSIS package strategy with rollback, staging and transaction control;
- decide between one package or multiple packages;
- determine event handlers;
- determine variables;
- determine parameters on package and project level;
- determine connection managers and whether they are package or project level;
- determine the need for custom tasks;
- determine how much information you need to log from a package;
- determine the need for checkpoints;
- determine security needs
Implement package logic by using SSIS variables and parameters
- User variables;
- variable scope, data type;
- implement parameterisation of properties using variables;
- use variables in precedence constraints;
- refer to SSIS system variables;
- design dynamic SSIS packages;
- package configurations (file or SQL tables);
- expressions;
- package and project parameters;
- project level connection managers;
- implement dynamic package behaviour;
- configure packages in SSIS for different environments, package configurations (xmlconfiguration file, SQLServer table, registry entry; parent package variables, environment variable);
- parameters (package and project level);
- project connection managers;
- property expressions (use expressions for connection managers)
Implement control flow
- Checkpoints;
- debug control flow;
- implement the appropriate control flow task to solve a problem;
- data profiling;
- use sequence containers and loop containers;
- manage transactions in SSIS packages;
- manage parallelism;
- use precedence constraint to control task execution sequence;
- create package templates;
- use the execute package task
Implement data load options
- Implement a full and incremental data load strategy;
- plan for an incremental update of the relational Data Mart

Week 7 Configure and deploy SSIS solutions

Troubleshoot data integration issues
- Performance issues;
- connectivity issues;
- execution of a task or transformation failed;
- logic issues;
- demonstrate awareness of the new SSIS logging infrastructure;
- troubleshoot a failed package execution to determine the root cause of failure;
- troubleshoot SSIS package failure from an invalid datatype;
- implement break points;
- data viewers;
- profile data with different tools;
- batch cleanup
Implement auditing, logging and event handling
- Audit package execution by using system variables;
- propagate events;
- use log providers; log an SSIS execution;
- create alerting and notification mechanisms;
- use Event Handlers in SSIS to track ETL events and errors;
- implement custom logging
Deploy SSIS solutions
- Create and configure an SSIS catalogue;
- deploy SSIS packages by using the deployment utility;
- deploy SSIS packages to SQL or file system locations;
- validate deployed packages;
- deploy packages on multiple servers;
- install custom components and tasks;
- deploy SSIS packages by using DTUTIL

Week 8 Build Data Quality solutions

Install and maintain Data Quality Services
- Installation prerequisites;
- use Data Quality Server Installer;
- add users to the DQ roles;
- identity analysis, including data governance
Implement master data management solutions
- Install Master Data Services (MDS);
- implement MDS;
- create models, entities, hierarchies, collections and attributes;
- define security roles;
- import/export;
- subscriptions
Create a data quality project to clean data
- Profile Online Transaction Processing (OLTP) and other source systems;
- data quality knowledge base management;
- create a data quality project;
- use Data Quality Client;
- improve data quality;
- identity mapping and deduplicating;
- handle history and data quality;
- manage data quality/cleansing

	Created by Ben Odunjo about 8 years ago

Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2