70-458

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