70-458

Seite 1

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