Preparation for the 70-465 exam, Designing solutions for SQL Server

I’m starting my preparation for the final SQL Server exam in the series, the 70-465, Designing Database Solutions for Microsoft SQL Server. I will be collecting links in this post.

There are also some videos available: Link1 nad Link2.
Exam topics are taken from here:

Design a database structure (20-30%)

• Business to data translations, identify which SQL Server components to use to support business requirements,
• Design a normalization area, Link;
• De-normalize technically (versus by remodeling) by using SQL Server features (materialization via indexed views and more) Link, Link1
• Design a physical database, including file placement, FILESTREAM, FILETABLE, file groups, and RAID; Link;
• Configure system database settings
• Develop the optimal strategy for indexing, Link
• archive using partitions and tables, Link
• design columnstore indexes, Link
• design XML indexes; Link
• Upgrade with minimal downtime; Link, Link1
• design a cross-cluster migration; Link
• Plan a database deployment, including Windows PowerShell, Server Core, and contained databases;
• Migrate to SQL Database;
• Migrate query plans;
• Design a migration strategy using Distributed Replay Controller;
• Design a SQL Server virtualization strategy;
• Identify hardware for new instances;
• SQL Server Distributed Replay; Link
• Design CPU affinity;
• Design clustered instances using Microsoft Distributed Transaction Control (MSDTC);
• Define instance memory allocation;
• Design installation strategies, including sysprep, slipstream, and SMB file server;
• Define cross db ownership chaining
• Design a backup strategy based on business needs, including differential, file, log, striped, and Microsoft Azure Blob Storage Service;
• design a database snapshot strategy;
• design appropriate recovery models; design a system database backup strategy; recover Tail-Log backups

Design databases and database objects (30-35%)

• Design a logical schema;
• design a data access and data layer architecture; design a database schema;
• design a security architecture;
• design a cross-server instance database model, including linked servers, security, providers, distributed transactions, distributed partitioned views, and Service Broker
• Design tables appropriately, including physical tables, temp tables, temp table variables, common table expressions, commonstore indexes, user defined table types, FILESTREAM, FILETABLE, and In-Memory OLTP;
• design views and table valued functions;
• design a compression strategy, including row and page;
• select an appropriate data type;
• design computed columns
• Develop a strategy to maximize concurrency;
• define a locking and concurrency strategy;
• design a transaction isolation strategy, including server database and session;
• design triggers for concurrency
• Create stored procedures; design a data access strategy using stored procedures;
• design appropriate stored procedure parameters, including input, output, and Table Valued;
• design error handling;
• design an In-Memory OLTP strategy for stored procedures
• Create a data archiving solution;
• design automation and auditing, including jobs, alerts, operators, SSIS, CDC, auditing, DDL triggers, and Windows PowerShell;
• automate across multiple databases and instances;
• design data batch processing: design a database load test;
• deploy to different environments, including development, staging, and production
• Manage transactions, including time, savepoint, and mark; design for implicit and explicit transactions; ensure data integrity by using transactions; design error handling for transactions, including TRY, CATCH, and THROW

Design database security (15-20%)

• Design security, including security roles, signed stored procedures, encryption, contained logins, EXECUTE AS, and credentials; implement schemas and schema security;
• design security maintenance, including SQL logins, integrated authentication, permissions, and mirroring
• Design a database schema that meets security requirements, schema ownership, ownership chaining, cross database chaining
• Implement separation of duties using different login roles; choose an authentication type, including logon triggers, regulatory requirements, and certificates;
• implement data encryption, including database master key and configuration;
• implement Data Description Language (DDL) triggers; define a secure service account

Design a troubleshooting and optimization solution (20-25%)

• Design maintenance plans;
• design index maintenance, including rebuild, defragmentation, statistics, online rebuilds, offline rebuilds, and thresholds;
• maintain physical and logical consistency (DBCC);
• manage database files, including LDF, MDF, In-Memory OLTP, and garbage collection;
• define a retention policy
• Troubleshoot and resolve concurrency issues
• Examine deadlocking issues using SQL Server logs and trace flags;
• design a reporting database infrastructure, including replicated databases;
• monitor concurrency, including Dynamic Management Views (DMV);
• diagnose blocking, including live locking and deadlocking;
• diagnose waits;
• use Extended Events;
• implement query hints to increase concurrency
• Use OPTION keyword (OPTIMIZE FOR); Link
• Configure failover clustering, including multi-subnet;
• design readable mirrors;
• create a highly available configuration with low recovery time; design and ensure uptime requirements, including
• monitoring and patching;
• design and implement a database mirroring architecture;
• design and implement a replication architecture;
• implement a mirroring solution, including AlwaysOn and Availability Groups;
• design geographical fault-tolerance using Microsoft Azure SQL Database
• Identify performance monitor counters;
• monitor for performance and bottlenecks, including Wait Stats;
• design a query monitoring and review strategy;
• monitor for missing statistics
• Design auditing strategies, including Extended Events, Event traces, SQL Audit, Profiler-scheduled or event-based • maintenance, Performance Monitor, and DMV usage;
• set up file and table growth monitoring;
• collect performance indicators and counters;
• create jobs to monitor server health;
• audit using Windows Logs



Leave a Comment here

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s