Exam 70-458 – Objective 7 – Configure and deploy SSIS solutions

Troubleshoot data integration issues Performance issues Performance Counters (http://msdn.microsoft.com/en-us/library/ms137622.aspx) Connectivity issues Execution of a task or transformation failed Logic issues Demonstrate awareness of the new SSIS logging infrastructure (http://msdn.microsoft.com/en-us/library/ms140246.aspx) Troubleshoot a failed package execution to determine the root cause of failure Troubleshoot SSIS package failure from an invalid datatype Implement break points To see variables […]

Read More »

Exam 70-458 – Objective 8 – Build Data Quality solutions

Install and maintain Data Quality Services Installation prerequisites (http://msdn.microsoft.com/en-us/library/gg492277.aspx#PreInstallationTasks) Server – Min 2 GB, Rec 4 GB+ RAM, SQL Server 2012 Database Engine Client – .NET Framework 4, IE 6.0 SP1+ Use Data Quality Server Installer Install Data Quality Services Run DQSInstaller.exe Add users to the DQ roles (http://msdn.microsoft.com/en-us/library/hh213045.aspx) IN DQS_MAIN database add to roles Identity […]

Read More »

Exam 70-458 – Objective 5 – Extract and transform data

Design data flow Define data sources and destinations (http://technet.microsoft.com/en-us/library/ms140080%28v=sql.110%29.aspx) Distinguish blocking and non-blocking transformations Integration Services: Performance Tuning Techniques Understanding SSIS Data Flow Buffers (SQL Server Video) Use different methods to pull out changed data from data sources Change Data Capture Run sys.sp_cdc_enable_db on database then for each table run sys.sp_cdc_enable_table There are CDC transforms […]

Read More »

Exam 70-458 – Objective 6 – Load data

Design control flow Determine control flow (http://msdn.microsoft.com/en-us/library/ms137681.aspx) Determine containers and tasks that are needed Tasks (http://msdn.microsoft.com/en-us/library/ms139892.aspx) Execute SQL, Execute Package, Execute process File System task Send mail task Task grouping Containers (http://msdn.microsoft.com/en-us/library/ms137728.aspx) Sequence (http://msdn.microsoft.com/en-us/library/ms139855.aspx) For loop (http://msdn.microsoft.com/en-us/library/ms139956.aspx) Foreach loop (http://msdn.microsoft.com/en-us/library/ms141724.aspx) Determine precedence constraints (http://msdn.microsoft.com/en-us/library/ms141261.aspx) Simple – Tasks execute in parallel by default Constraints and expressions […]

Read More »

Exam 70-458 – Objective 4 – Design and implement a data warehouse

Design and implement dimensions Design shared/conformed dimensions Conformed dimensions have same meaning to each fact table (http://www.jamesserra.com/archive/2011/11/conformed-dimensions/) Determine whether you need support for slowly changing dimensions (http://msdn.microsoft.com/en-us/library/ms141715.aspx) If old values must be retained then you need this Determine attributes (http://msdn.microsoft.com/en-us/library/ms174760.aspx) Design hierarchies (http://msdn.microsoft.com/en-us/library/ms174935.aspx) Determine whether you need star or snowflake schema (http://dwhlaureate.blogspot.com/2012/06/difference-between-star-snowflake.html) Star schema Table […]

Read More »

Exam 70-458 – Objective 3 – Implement high availability

Implement AlwaysOn Implement a mirroring solution using AlwaysOn Availability modes (http://technet.microsoft.com/en-us/library/ff877931.aspx) Asynchronous-commit mode Synchronous-commit mode Steps to perform Create mirroring endpoint (http://technet.microsoft.com/en-us/library/ff878259.aspx) Enable AlwaysON (http://technet.microsoft.com/en-us/library/ff878259.aspx) Create an availability group (http://technet.microsoft.com/en-us/library/ff878307.aspx) ALTER AVAILABILITY GROUP GroupA ADD DATABASE DB Create an availability group listener (http://msdn.microsoft.com/en-us/library/hh213080.aspx) Add a secondary replica (http://technet.microsoft.com/en-us/library/hh213078.aspx) ALTER DATABASE db SET HADR AVAILABILITY GROUP […]

Read More »

Exam 70-458 – Objective 2 – Implement Security

Manage logins and server roles Configure server security (http://msdn.microsoft.com/en-us/library/bb283235.aspx) Secure the SQL Server using Windows Account/SQL Server accounts, server roles Server-Level Roles (http://msdn.microsoft.com/en-us/library/ms188659.aspx) Create log in accounts (http://msdn.microsoft.com/en-us/library/aa337562.aspx) Manage access to the server, SQL Server instance, and databases Database Roles (http://msdn.microsoft.com/en-us/library/ms189121.aspx) Create and maintain user-defined server roles (http://msdn.microsoft.com/en-us/library/ee677627.aspx) Manage certificate logins CREATE LOGIN Example D […]

Read More »

Exam 70-458 – Objective 1 – Manage Data

Configure and maintain a backup strategy Manage different backup models, including point in time recovery Recovery models (http://technet.microsoft.com/en-us/library/ms189275.aspx) FULL SIMPLE – No point in time recovery BULK-LOGGED – Can recover to last backup, no point in time recovery Should take transaction log backup before and after Protect customer data even if backup media is lost […]

Read More »