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