Querying Microsoft SQL Server 2012/2014

• Describe the basic architecture and concepts of Microsoft SQL Server 2014.
• Understand the similarities and differences between Transact-SQL and other computer languages.
• Write SELECT queries
• Query multiple tables
• Sort and filter data
• Describe the use of data types in SQL Server
• Modify data using Transact-SQL
• Use built-in functions
• Group and aggregate data
• Use subqueries
• Use table expressions
• Use set operators
• Use window ranking, offset and aggregate functions
• Implement pivoting and grouping sets
• Execute stored procedures
• Program with T-SQL
• Implement error handling
• Implement transactions

Administering Microsoft SQL Server 2012/2014 Databases

• Describe core database administration tasks and tools.
• Install and configure SQL Server 2014.
• Configure SQL Server databases and storage.
• Plan and implement a backup strategy.
• Restore databases from backups.
• Import and export data. Monitor SQL Server.
• Trace SQL Server activity.
• Manage SQL Server security.
• Audit data access and encrypt data.
• Perform ongoing database maintenance.
• Automate SQL Server maintenance with SQL Server Agent Jobs.
• Configure Database Mail, alerts and notifications.

Implementing a Data Warehouse with Microsoft SQL Server 2012/2014

• Describe data warehouse concepts and architecture considerations.
• Select an appropriate hardware platform for a data warehouse.
• Design and implement a data warehouse.
• Implement Data Flow in an SSIS Package.
• Implement Control Flow in an SSIS Package.
• Debug and Troubleshoot SSIS packages.
• Implement an ETL solution that supports incremental data extraction.
• Implement an ETL solution that supports incremental data loading.
• Implement data cleansing by using Microsoft Data Quality Services.
• Implement Master Data Services to enforce data integrity.
• Extend SSIS with custom scripts and components.
• Deploy and Configure SSIS packages.
• Describe how BI solutions can consume data from the data warehouse.

Developing Microsoft SQL Server Databases

• Introduce the entire SQL Server platform and its major tools. It will cover editions, versions, basics of network listeners, and concepts of services and service accounts.
• Determine appropriate data types to be used when designing tables, convert data between data types, and create alias data types.
• Be aware of good design practices regarding SQL Server tables and be able to create tables using T-SQL.
(Note: partitioned tables are not covered).
• Implement PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK and UNIQUE constraints, and investigate cascading FOREIGN KEY constraints.
• Determine appropriate single column and composite indexes strategies.
• Create tables as heaps and tables with clustered indexes. Also consider the design of a table and suggest an appropriate structure.
• Read and interpret details of common elements from execution plans.
• Design effective non-clustered indexes.
• Design and implement views
• Design and implement stored procedures.
• Work with table types, table valued parameters and use the MERGE statement to create stored procedures that update data warehouses.
• Design and implement functions, both scalar and table-valued. (Also describe where they can lead to performance issues).
• Perform basic investigation of a deadlock situation and lea how transaction isolation levels affect application concurrency.
• Use both traditional T-SQL error handling code and structured exception handling.
• Design and implement DML triggers
• Lea appropriate uses for SQL CLR integration and implement an existing .NET assembly within SQL Server.
• Store XML data and schemas in SQL Server.
• Perform basic queries on XML data in SQL Server.

Designing Database Solutions for SQL Server

• Introduction to Enterprise Data Architecture
• Multi-Server Configuration Management
• Monitoring SQL Server 2014 Health
• Consolidating Database Workloads with SQL Server 2014
• Introduction to Cloud Data Solutions
• Introduction to High Availability in SQL Server 2014
• Clustering with Windows Server and SQL Server 2014
• AlwaysOn Availability Groups
• Planning High Availability and Disaster Recovery
• Replicating Data