Description
In this course you will learn:
- SQL Server supports mission-critical applications and big data solutions—without having to buy expensive add-ons or high-end appliances
- Learn the essentials of Microsoft's latest release, SQL Server 2016. Use tools like SQL Management Studio and the Configuration Monitor to deploy and manage databases both on premises and in the cloud.
- Learn how to create new databases, build tables, define relationships, write queries, and use stored procedures and built-in functions
- how to manipulate XML and JSON data, and perform crucial database administration tasks, such as backups and restores, security enhancements, and performance monitoring.
- how to extend SQL Server by integrating it with cloud services like Microsoft Azure.
Syllabus :
- 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
- Chapter Quiz
2. Installing SQL Server 2016
- Basic installation
- Advanced installation options
- Install SQL Management Studio
- Chapter Quiz
3. Working with Tools
- Using SQL Server Configuration Manager
- Basics SQL Server Management Studio
- More about SQL Server Management Studio
- Activity Monitor
- Profiler
- Chapter Quiz
4. Creating and Configuring Databases
- Create a database
- Advanced database configuration options
- Install a sample database
- Chapter Quiz
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
- Chapter Quiz
6. Creating Views in Databases
- Create a view
- Understanding the advanced options of views
- Chapter Quiz
7. Understanding Programmability
- Stored procedures
- Parameterized stored procedures
- Built-in functions
- Scalar functions
- Chapter Quiz
8. Working with XML
- Introduction to XML
- Write queries to return XML
- Shred XML
- Working with XML schemas
- Storing XML
- Chapter Quiz
9. Working with JSON
- Working with JSON
- Converting JSON to tabular data
- Storing JSON
- Chapter Quiz
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
- Chapter Quiz
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
- Chapter Quiz
12. Security
- Overview of authentication in SQL Server
- Instance-level logins
- Database-level users
- Encrypt data
- Dynamic masking
- Chapter Quiz
13. Performance
- Basics of indexes
- Create an index
- Columnstore index
- Explore execution plans
- Overview of Query Store
- Chapter Quiz
14. Cloud
- Back up to Azure
- Set up Stretch Database
- Monitor Stretch Database
- Chapter Quiz