Microsoft SQL Server 2016 Certification (70-764) Advance

Unlock the power of SQL Server 2016 for your mission-critical applications and big data needs! This course provides a comprehensive understanding of Microsoft's latest release, empowering you to deploy and manage databases effectively – whether on-premises or in the cloud. You'll discover how to leverage tools like SQL Management Studio and the Configuration Monitor for streamlined database administration.

Key Skills You'll Master:

• Database Creation & Design: Learn to create new databases, build robust tables using both SQL Server Management Studio (SSMS) and T-SQL, and define crucial relationships between tables.
• Querying & Data Manipulation: Develop proficiency in writing powerful queries to retrieve data, including working with XML and JSON data formats.
• Stored Procedures & Functions: Utilize stored procedures and built-in functions to automate tasks and enhance database functionality.
• Database Administration: Gain practical experience with essential database administration tasks, including backups and restores, implementing robust security enhancements, and proactive performance monitoring.
• Data Integrity & Recovery: Learn to identify and correct database corruption, maintain database consistency, and effectively recover from potential data loss scenarios.
• Performance Optimization: Master techniques for monitoring database activity, identifying blocking activity, managing tempdb, and optimizing query performance using the Query Store.
• Index Management: Learn to identify and repair index fragmentation, create missing indexes, and drop underutilized indexes to boost query speeds. Understand and manage columnstore indexes.
• Statistics Management: Properly manage statistics to ensure query optimization, including updating outdated statistics and implementing auto-update statistics.
• Cloud Integration: Explore how to seamlessly integrate SQL Server with cloud services like Microsoft Azure for enhanced scalability and flexibility.
• High Availability & Disaster Recovery: Implement DR and high availability solutions using ALWAYS-ON Availability Groups (with a step-by-step demo).
• Encryption: Implement various encryption methods including cell-level encryption, transparent data encryption, always encryption, and backup encryption to protect sensitive data.

Course Topics Include:

• Basics:
  • Installing SQL Management Studio
  • Using the SQL Server Configuration Monitor
  • Creating a database
  • Creating tables with SSMS and T-SQL
  • Creating relationships between tables
  • Creating views
  • Writing queries to return XML
  • Working with XML and JSON
  • Backing up and restoring SQL Server
  • Monitoring and management
  • Security fundamentals
  • Performance basics
• Advanced SQL 2016 (SQL Database Infrastructure - 70-764 Certification Exam):
  • Encryption: Comprehensive coverage of encryption techniques.
  • Database Integrity: Identifying and resolving database corruption.
  • Database Consistency: Maintaining data integrity.
  • Database Activity Monitoring: Tracking and analyzing database activity.
  • Performance Monitoring: Using extended events and the data collector.
  • Index Management: Identifying, creating, and managing indexes.
  • Statistics Management: Maintaining optimal statistics for query performance.
  • Cloud Integration: Connecting SQL Server to Azure.
  • High Availability: Implementing Always On Availability Groups for disaster recovery.

Looking for SQL Server job opportunities? Explore our SQL-Server Interview Questions section to prepare for your next interview!