Тест №70-465: Designing Database Solutions for Microsoft SQL Server 2012

Продолжительность:
Языки теста: English
Online тест:
Кол-во вопросов:
Мин.проходной балл:

Темы:

Skills Being MeasuredThis exam measures your ability to accomplish the technical tasks listed below.The percentages indicate the relative weight of each major topic area on the exam.The higher the percentage, the more questions you are likely to see on that content area on the exam. The information after “This objective may include but is not limited to” is intended to further define or scope the objective by describing the types of skills and topics that may be tested for the objective. However, it is not an exhaustive list of skills and topics that could be included on the exam for a given skill area. You may be tested on other skills and topics related to the objective that are not explicitly listed here.

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