MCSA SQL Server 2012

Exame 70-465 – Designing Database Solutions for Microsoft SQL Server 2012

Código: 70-465
Prova Designing Database Solutions for Microsoft SQL Server 2012
Idiomas Inglês
Preço US$ 100.00
Duração 3 horas
Número de questões 56
Nota de aprovação 700 pontos (70%)
Treinamento oficial recomendado Ainda não disponível

Síntese dos Assuntos

  • Design database structure
  • Design database objects
  • Design databases security
  • Design a troubleshooting and optimization solution

Tópicos do Exame

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 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 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 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; policies