Тест №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