Microsoft SQL Server 2016 Essential Training

Learn about SQL Server 2016. Find out how to build new databases, tables, and queries; back up and restore SQL Server; and integrate with cloud services like Microsoft Azure.

Introduction

• Welcome
• What you should know before starting
• Using the exercise files

1. Overview of SQL Server 2016

• Understanding core concepts of SQL Server 2016
• SQL Server editions

2. Installing SQL Server 2016

• Basic installation
• Advanced installation options
• Install SQL Management Studio

3. Working with Tools

• Using SQL Server Configuration Manager
• Basics SQL Server Management Studio
• More about SQL Server Management Studio
• Activity Monitor
• Profiler

4. Creating and Configuring Databases

• Create a database
• Advanced database configuration options
• Install a sample database

5. Creating Tables in a Database

• Create a table using Management Studio
• Understanding data column options
• Create a table using T-SQL
• Create relationships between tables
• In-memory tables
• Temporal tables

6. Creating Views in Databases

• Create a view
• Understanding the advanced options of views

7. Understanding Programmability

• Stored procedures
• Parameterized stored procedures
• Built-in functions
• Scalar functions

8. Working with XML

• Introduction to XML
• Write queries to return XML
• Shred XML
• Working with XML schemas
• Storing XML

9. Working with JSON

• Working with JSON
• Converting JSON to tabular data
• Storing JSON

10. Backup and Restore

• Overview of backup and restore in SQL Server
• Basic backups
• Basic restore
• Create maintenance plans to automate backups
• Add a cleanup task to a maintenance plan

11. Monitoring and Management

• Review logs in SQL Server 2016
• Audit logins
• Understanding dynamic management views
• Using database console commands (DBCC)
• Overview of high availability in SQL Server 2016

12. Security

• Overview of authentication in SQL Server
• Instance-level logins
• Database-level users
• Encrypt data
• Dynamic masking

13. Performance

• Basics of indexes
• Create an index
• Columnstore index
• Explore execution plans
• Overview of Query Store

14. Cloud

• Back up to Azure
• Set up Stretch Database
• Monitor Stretch Database

Conclusion

• Next steps