MCSE Data Platform

MCSE Data Platform
Pre-requisite: MCSA Server 2012

A. Developing MS SQL 2012 Databases  464

• Implement Programming Objects (21%)
  • Write automation scripts.
    • This objective may include but is not limited to: automate backup testing; shrink file; check index fragmentation; archive data; run an SQL Server Integration Services (SSIS) job; check disk space; automate backups
  • Design and implement stored procedures.
    • This objective may include but is not limited to: create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of stored procedure results; create stored procedure for data access layer; analyze and rewrite procedures and processes; program stored procedures, with T-SQL and CLR#; use table valued parameters; encryption
  • Design T-SQL table-valued and scalar functions.
    • This objective may include but is not limited to: ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); turn scripts that use cursors and loops into a SET based operation
  • Create, use, and alter user-defined functions (UDFs).
    • This objective may include but is not limited to: understand deterministic, non-deterministic functions; using cross apply with UDFs; Common Language Runtime (CLR)
  • Create and alter views (complex statements).
    • This objective may include but is not limited to: set up and configure partitioned tables and partitioned views; design a best practice for using views and stored procedures and remove the direct usage of tables
• Implement Database Objects (31%)
  • Create and alter tables (complex statements).
    • This objective may include but is not limited to: develop an optimal strategy for using temporary objects (table variables and temporary tables); how not to rely on triggers solely as a means to manage a table; data version control and management; create tables without using the built in tools; understand the difference between @Table and #table
  • Design, implement, and troubleshoot security.
    • This objective may include but is not limited to: grant, deny, revoke; unable to connect; execute as; certificates; loginless user; database roles and permissions; contained users; change permission chain
  • Design the locking granularity level.
    • This objective may include but is not limited to: choose the right lock mechanism for a given task, handling and/or avoiding deadlocks; fix locking and blocking issues caused by previous development or third-party apps; analyze a deadlock scenario to alleviate the issue; impact of isolation level and ado defaults; impact of locks and lock escalation; reduce locking scenarios; how isolation levels affect blocking and locking; identify bottlenecks in the data design and improve
  • Maintain indexes.
    • This objective may include but is not limited to: 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; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild versus reorg and index; create a tuning and maintenance strategy for proactive operations
  • Implement data types.
    • This objective may include but is not limited to: use appropriate data types; develop a CLR data type; understand the difference between @Table and #table; impact of GUID (newid, newsequentialid) on database performance, indexing and privacy; use spatial data; LOB data types; understand when and how to use column store and sparse columns; implicit and explicit conversions, integer math
  • Create and modify constraints (complex statements).
    • This objective may include but is not limited to: create constraints on tables; define constraints; performance implications
  • Work with XML Data.
    • This objective may include but is not limited to: implement XML; use XML (Query, Input, Output); transform XML data into relational data; retrieve relational data as XML; FOR XML; design a strategy to transform XML into relational data; design a strategy to query and modify XML data; understand xml data types and their schemas and interoperability, limitations, and restrictions; implement XML schemas and handling of XML data; how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML
• Design Database Objects (24%)
  • Design tables.
    • This objective may include but is not limited to: data design patterns; develop normalized and de-normalized SQL tables; understand the difference between physical tables, temp tables, temp table variables and common table expressions; design transactions; design views; describe advantages / disadvantages of using a GUID as a clustered index; understand performance implications of # versus @ temp tables and how to decide which to use, when and why; use of set based rather than row based logic; encryption (other than TDE); table partitioning; filestream and filetable
  • Design for concurrency.
    • This objective may include but is not limited to: develop a strategy to minimize concurrency; handle concurrency to minimize locking and eliminate as much blocking as possible, and to avoid deadlocks; manage the transactions to limit the time to hold lock and have fast transactions (maximize concurrency); define locking and concurrency strategy; impact of read committed snapshot / snapshot isolation; understand what it solves and what it costs
  • Create and alter indexes.
    • This objective may include but is not limited to: create indexes and data structures; create filtered indexes; create an indexing strategy; design and optimize indexes; design indexes and statistics; assess which indexes on a table are likely to be used given different search arguments (SARG); column store indexes; semantic indexes
  • Design data integrity.
    • This objective may include but is not limited to: design table data integrity policy (checks, private key/foreign key, uniqueness, XML schema); select a primary key; data usage patterns
  • Design for implicit and explicit transactions.
    • This objective may include but is not limited to: manage transactions; use transactions in code; ensure data integrity by using transactions; use transactions inside the database using T-SQL and from the “outside” via C#/VB; distributed transaction escalation
• Optimize and Troubleshoot Queries (24%)
  • Optimize and tune queries.
    • This objective may include but is not limited to: tune a badly performing query; identify long running queries; review and optimize code; analyze execution plans to optimize queries; tune a query that is poorly written; tune queries using execution plans and database tuning advisor (DTA); design advanced queries: pivots, utilizing common table expressions (CTE), design the database layout and optimize queries (for speed and/or data size); understand different data types; basic knowledge of query hints; tune query workloads, using realistic data sets not being production data sets ; demonstrate use of recursive CTE; full text search; control execution plans
  • Troubleshoot and resolve performance problems.
    • This objective may include but is not limited to: interpret performance monitor data; impact of recovery modal on database size, and recovery. How to clean up if .MDF and .LDF files get to large; identify and fix transactional replication problems; detect and resolve server hung, failure; identify and troubleshoot data access problems
  • Optimize indexing strategies.
    • This objective may include but is not limited to: develop optimal strategy for clustered indexes; analyze index usage; know the difference between the type of indexes and when to choose one over the other; optimize indexing for data warehousing vs. optimize Indexing for Online Transaction Processing (OLTP); generate appropriate indexes and statistics with include columns; apply effective and efficient indexes, including the use of INCLUDE lists; full-text indexing
  • Capture and analyze execution plans.
    • This objective may include but is not limited to: collect and read execution plan; review an execution plan to spot potential performance issues; read an execution plan; create an index based on an execution plan; row-based logic versus. set-based logic, batching, splitting implicit transactions
  • Collect performance and system information.
    • This objective may include but is not limited to: use Data Management Views to determine performance issues; from system metadata; gather trace information by using the SQL Server Profiler; develop monitoring strategy for production database; run a profiler trace and analyze the results; run profiler for troubleshooting application; collect output from the Database Engine Tuning Advisor; extended events

B. Designing Database Solutions for SQL 2012 465

• Design Database Security (15%)
  • Design an application strategy to support security.
    • This objective may include but is not limited to: design security; implement schemas and schema security; design maintenance (SQL logins versus integrated authentication, permissions, mirroring issues, etc.); use appropriate mechanisms to enforce security roles, signed stored procedures, etc.; encryption; contained logins
  • Design database, schema, and object security parameters.
    • This objective may include but is not limited to: design a database schema that meets security requirements; schema ownership; ownership chaining; cross database chaining
  • Design instance-level security configurations.
    • This objective may include but is not limited to: implement separation of duties using different login roles; design/implement a data safety strategy that meets the requirements of the installation; choosing authentication type, logon triggers, regulatory requirements; transparent data encryption; Data Description language (DDL) triggers
• Design Databases and Database Objects (32%)
  • Design a database model.
    • This objective may include but is not limited to: design a logical schema; design a normalized database; design data access and data layer architecture; understand the relational model; design a normalized data model; design a database schema; create/maintain a schema upgrade and downgrade script which include the most optimal schema deployment and data migration; review common modeling practices: Entity-Attribute-Value (EAV), generalization/specialization, star-schema etc.; optimize the design for normalization to the right level for the application looking forward to possible scenarios in the future; design security architecture; relational database design-Design/modify database schemas; design appropriately normalized and data typed table schemas to meet business requirements; Design a strategy to use linked servers, security, providers, distributed transactions ; understand impact of collation, ANSI NULLS, QUOTED IDENTIFIER; interpret a database design to match a set of statements that describe the design
  • Design tables.
    • This objective may include but is not limited to: data design patterns; develop normalized and de-normalized SQL tables; understand the difference between physical tables, temp tables, temp table variables and common table expressions; design transactions; design views; describe advantages / disadvantages of using a GUID as a clustered index; understand performance implications of # vs. @ temp tables and how to decide which to use, when and why; how to use table valued parameters to sps; use of set based rather than row based logic; filestream and filetable; semantic engine; sequences; row/page compression; data type selection
  • Design for concurrency.
    • This objective may include but is not limited to: develop a strategy to minimize concurrency; handle concurrency to minimize locking and eliminate as much blocking as possible, and to avoid deadlocks; manage the transactions to limit the time to hold lock and have fast transactions (maximize concurrency); define locking and concurrency strategy; impact of read committed snapshot/snapshot isolation; understand what it solves and what it costs
  • Design T-SQL stored procedures.
    • This objective may include but is not limited to: write a stored procedure to meet a given set of requirements; design a best practice for using views and stored procedures and remove the direct usage of tables
  • Design a management automation strategy.
    • This objective may include but is not limited to: create a data archiving solution; create jobs to ensure good server health as DBCC Checkdb, statistics updates; improve database maintenance (DB Index, backup etc.) with custom script that execute some task only on when some value are overpassed (defragment/rebuild index); design automation and auditing (jobs, alerts, operators, SSIS, CDC, auditing, DDL triggers); automate (setup, maintenance, monitoring) across multiple databases and multiple instances; data flow and batch processing: testing load on database plus different stages
  • Design for implicit and explicit transactions.
    • This objective may include but is not limited to: manage transactions; use transactions in code; ensure data integrity by using transactions; trycatch; commit; throw
• Design Database Structure(29%)
  • Design for business requirements.
    • This objective may include but is not limited to: business to data translations; Identify which SQL Server components to use to support business requirements; Design a normalization area; de-normalize technically (versus. by remodeling) by using SQL Server features (materialization via indexed views etc.)
  • Design physical database and object placement.
    • This objective may include but is not limited to: identify bad database architectural decisions; filestream and filetable; logical vs physical design; file groups
  • Design a table and index partitioning strategy.
    • This objective may include but is not limited to: develop optimal strategy for indexing; data distribution; archiving
  • Design a migration, consolidation, and upgrade strategy.
    • This objective may include but is not limited to: upgrade with minimal downtime; database deployments; multiple databases in same solution; contained databases
  • Design SQL Server instances.
    • This objective may include but is not limited to: spec out hardware for new instances; design an instance; design SQL to use only certain CPUs (affinity masks, etc.); design clustered instances including Microsoft Distributed Transaction Control (MSDTC); memory allocation
  • Design backup and recovery.
    • This objective may include but is not limited to: database snapshots; recovery models; transaction log backups; when to use differentials; file backup; striped backups
• Design a Troubleshooting and Optimization Solution (24%)
  • Design a maintenance strategy for database servers.
    • This objective may include but is not limited to: online rebuilds versus offline rebuilds; maintenance plans; rebuild indexes; defrag indexes; check DB; statistics; grow the database; manage backups and history; retention policy
  • Troubleshoot and resolve concurrency issues.
    • This objective may include but is not limited to: examine deadlocking issues using the SQL server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via DMV or other MS product; diagnose blocking, live locking and deadlocking; diagnose waits; performance detection with built in DMVs; know what affects performance
  • Design and implement a High Availability solution.
    • This objective may include but is not limited to: understand the traditional failover clustering solution; configure failover clustering; design readable mirrors; create a highly available configuration with low RTO; design and ensure uptime to relevant TOS/RLAs (includes monitoring, patching, etc.) ; design and implement a replication architecture; implement a mirroring solution using HADRON
  • Design a solution to monitor performance and concurrency.
    • This objective may include but is not limited to: identify performance monitor counters to monitor; monitor for performance and bottlenecks, including Wait Stats; design a top consumer queries monitoring and review strategy; monitor for missing statistics and create them when needed
  • Design a monitoring solution at the instance level.
    • This objective may include but is not limited to: design auditing strategies including XE, Profiler, Perfmon and DMV usage; set up file and table growth monitoring; collect performance indicators and counters; content management systems; policiesdate/replace OS, update/replace app, scale out, update/replace SQL