Tracy Boggiano's SQL Server  Blog Rotating Header Image

Exam Prep

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

Troubleshoot data integration issues Performance issues Performance Counters ( 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 To see variables […]

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

Install and maintain Data Quality Services Installation prerequisites ( 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 ( IN DQS_MAIN database add to roles Identity […]

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

Design data flow Define data sources and destinations ( 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 […]

Exam 70-458 – Objective 6 – Load data

Design control flow Determine control flow ( Determine containers and tasks that are needed Tasks ( Execute SQL, Execute Package, Execute process File System task Send mail task Task grouping Containers ( Sequence ( For loop ( Foreach loop ( Determine precedence constraints ( Simple – Tasks execute in parallel by default Constraints and expressions […]

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 ( Determine whether you need support for slowly changing dimensions ( If old values must be retained then you need this Determine attributes ( Design hierarchies ( Determine whether you need star or snowflake schema ( Star schema Table […]

Exam 70-458 – Objective 3 – Implement high availability

Implement AlwaysOn Implement a mirroring solution using AlwaysOn Availability modes ( Asynchronous-commit mode Synchronous-commit mode Steps to perform Create mirroring endpoint ( Enable AlwaysON ( Create an availability group ( ALTER AVAILABILITY GROUP GroupA ADD DATABASE DB Create an availability group listener ( Add a secondary replica ( ALTER DATABASE db SET HADR AVAILABILITY GROUP […]

Exam 70-458 – Objective 2 – Implement Security

Manage logins and server roles Configure server security ( Secure the SQL Server using Windows Account/SQL Server accounts, server roles Server-Level Roles ( Create log in accounts ( Manage access to the server, SQL Server instance, and databases Database Roles ( Create and maintain user-defined server roles ( Manage certificate logins CREATE LOGIN Example D […]

Exam 70-458 – Objective 1 – Manage Data

Configure and maintain a backup strategy Manage different backup models, including point in time recovery Recovery models ( 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 […]

Exam 70-458 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 Study Resources

I passed the 70-457 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 today.  Next I will be attempting the 70-458, as I study I’ll put my notes and links to relevant materials here.  I hope to take and pass the exam by the end of March 2014. I’ll add links […]