SQL Server - Preparation for an Interview

In short this is some preparation for a phone interview.

• Integration Services Transformations
• SSIS Questions
• SSIS Tutorials
• Handling Slowly ChangingDimensions in SSIS
• Types of TSQL statements 
• Slowly changing dimension
• What is a Dimension, Fact and SCD?
• SSIS SCD Transformation Part 1 Type 0 Fixed Attribute
• Interview Answers
• Profiler
• DTA: Database Engine Tuning Advisor (From Query, SP, TraceFile)
• SSMS: SQL Server Management Studio
• SAC: Surface Area Configuration Utility, 2008 Policy-Based Management
• SSCM: System Center Configuration Manager 2007 (RTM and SP1) 
• PerfMon
• DMVs(Missing Indexes, Unused Indexes, Duplicates, Population Density)
• performance tuning:
• query optimization1, query optimization2, Execution Plans(Index and table scan vs seeks, etc.), 
• Graphical Execution Plans, Execution Plans
• The Basics of SQL Server Execution Plans
• Query Processing Enhancements on Partitioned Tables and Indexes
• The Basics of Execution Plans and SHOWPLAN in SQL Server
• SQL Server 2008 - Reading execution plans.

• read-committed-snapshot-isolation-level
• performance monitor and other related monitoring and troubleshooting tools.
• Experience troubleshooting and resolving database issues including, but not limited to:
• integrity1, Integrity2 (Unique record constraints)
• performance
• Using profiler to detect blocking and deadlocking 
• replication (Transactional,  Merge,  Snapshot)
• log shipping
• connectivity(Linked Servers, OpenRowSet, OpenQuery, ODBC,OLEDB, ADO.net)
• security 
• Ability to detect and troubleshoot SQL Server related CPU/memory, I/O, disk space and other resource contention.  (Task Manager, Perfmon, Management Reports, Activity Monitor now under Facets on the Server Menu. The same place the Activity Monitor now resides.
• Proficiency with indexes, including, but not limited to, management, checks, configuration and patching.
• Strong knowledge of backups, restores, recover models, database shrink operations, DBCC commands, clustering, database mirroring vs  replication(Transactional,  Merge,  Snapshot) vs LogShipping vs Clustering.
• Functional knowledge of Windows servers, security delegation(Active Directory) , SPNs and storage components(SANS Disks).
• Expert experience in implementing operational automation.
• Exceptional verbal and written communication skills; must be able to document processes and procedures clearly and effectively (e.g. runbooks, topology, etc.).
• SSIS, Transformations, New in 2008, New in SSIS 2012 
• New in 2012

Master Data Services

Master Data Services

Efficiently Manage Data with the New Master Data Services Add-inn for Excel

SQL Server Series: Master Data Services in SQL Server 2012

Microsoft SQL Server Analysis Services Dimension Management

http://www.sqlchick.com/entries/2011/4/24/resources-for-learning-master-data-services.html

http://dutchdatadude.com/installing-master-data-services-add-in-for-excel-2013
http://msftdbprodsamples.codeplex.com/releases/view/100029

Data Quality Services

Data Quality Services

Data Quality Services SQL server 2012

Understanding SQL Server 2012 Data Quality Services

Balanced Data Distributor
dqsinstaller.exe -upgrade
http://www.microsoft.com/en-us/download/confirmation.aspx?id=35578

 

Data Mining Demos

Data Mining - Adventure Works SESSION: Data Mining in SQL Server Analysis Services (Brian Knight)   

Power View Demo - SQL Server 2012 RC0

AdventureWorks2012

Sales Forecasting with Excel and the SQL Server 2012 Data Mining

SSAS: Building your first Cube from scratch

SSIS term extraction components

Resources

SQLPASSTV
SQLServerDataMining.com

My SQL Server DBATools library function and procedure index.

Brent Ozar
Capacity and performance planning.
Always On ; Always On Code
Use Windows 2012
Patching is manual and self job.

Monitoring and Tuning require union of MSDB databases.