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
Continue reading


Preparation for the 70-467 SQL BI exam

Microsoft does not provide any preparation materials for the 70-467 exam [Designing Business Intelligence Solutions with Microsoft SQL Server]. There are Microsoft Virtual Academy videos here or here, which are nice to watch but definitelly not enough to be able to answer any particular question. Microsoft does though provide a list of topics here 70-467, so, while preparing I was collecting related links.

Plan business intelligence (BI) infrastructure (15–20%)
Plan for performance
• Optimize batch procedures: extract, transform, load (ETL) in SSIS/SQL; Link
• Optimize processing phase in Analysis Services, Partitions, Lazy Aggregation; Link, Link1, Link2, Link3
• Configure Proactive Caching within SQL Server Analysis Services (SSAS) for different scenarios; Link, Link1
• Understand performance consequences of named queries in a data source view; Link
• SSAS Performance Counters; Link, Link1
• Analyze and optimize performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; Link
• Understand the difference between partitioning for load performance versus query performance in SSAS; Link, Link1 (Lazy Aggregations)
• Appropriately index a fact table; Link
• Optimize Analysis Services cubes in SQL Server Data Tools; Link
• Create aggregations; Link, Link1
Continue reading