SQL Server 2019
(SQL-SVR-2019.AP1) / ISBN : 978-1-64459-261-8
About This Course
Skills You’ll Get
Get the support you need. Enroll in our Instructor-Led Course.
Interactive Lessons
21+ Interactive Lessons | 130+ Quizzes | 100+ Flashcards | 100+ Glossary of terms
Gamified TestPrep
100+ Pre Assessment Questions | 100+ Post Assessment Questions |
Hands-On Labs
21+ LiveLab | 20+ Video tutorials | 01+ Hours
Introduction
- Acknowledgments
Getting started with SQL Server tools
- SQL Server setup
- Tools and services installed with the SQL Server Database Engine
- Performance and reliability monitoring tools
- SQL Server Reporting Services
- SQL Server Management Studio (SSMS)
- Azure Data Studio
- SQL Server Data Tools
- A note on discontinued and deprecated features
Introducing database server components
- Memory
- Central Processing Unit
- Storing your data
- Connecting to SQL Server over the network
- High availability concepts
- Securing SQL Server
- Understanding virtualization and containers
Designing and implementing an on-premises database infrastructure
- Introduction to SQL Server database architecture
- Data files and filegroups
- Recording changes in the transaction log
- Table partitioning
- Compressing data
- Managing the temporary database
- Configuration settings
- Container orchestration with Kubernetes
Installing and configuring SQL Server instances and features
- What to do before installing SQL Server
- Installing a new instance
- Post-installation server configuration
- Post-installation configuration of other features
Installing and configuring SQL Server on Linux
- What is Linux?
- Considerations for installing SQL Server on Linux
- Installing SQL Server on Linux
- Configuring SQL Server for Linux
- Caveats of SQL Server on Linux
Provisioning and configuring SQL Server databases
- Adding databases to a SQL Server instance
- Moving and removing databases
Understanding table features
- Reviewing table design
- Special table types
- Storing BLOBs
- Table partitioning
- Capturing modifications to data
Maintaining and monitoring SQL Server
- Detecting, preventing, and responding to database corruption
- Maintaining indexes and statistics
- Managing database file sizes
- Monitoring activity by using DMOs
- Monitoring with the SQL Assessment API
- Utilizing extended events
- Capturing performance metrics with DMOs and Data Collectors
- Protecting important workloads by using Resource Governor
- Understanding the new servicing model
Automating SQL Server administration
- Components of SQL Server automated administration
- Maintaining SQL Server
- Using SQL Server Maintenance Plans
- Strategies for administering multiple SQL Servers
- Using PowerShell to automate SQL Server administration
Developing, deploying, and managing data recovery
- Preparing for data recovery
- Understanding different types of backups
- Understanding backup devices
- Creating and verifying backups
- Restoring a database
- Defining a recovery strategy
Implementing high availability and disaster recovery
- Overview of high availability and disaster recovery technologies in SQL Server
- Configuring failover cluster instances
- Designing availability groups solutions
- Configuring availability groups in SQL Server on Linux
- Administering availability groups
Administering security and permissions
- Authentication modes
- Security principals
- Permissions
- Common security administration tasks
Protecting data through encryption, privacy and auditing
- Privacy in the modern era
- Introducing security principles and protocols
- Protecting the data platform
- Auditing with SQL Server and Azure SQL Database
- Securing Azure infrastructure as a service
Performance tuning SQL Server
- Understanding isolation levels and concurrency
- Understanding durability settings for performance
- Understanding how SQL Server executes your query
- Understanding advanced engine features for tuning queries
Understanding and designing indexes
- Designing clustered indexes
- Designing rowstore nonclustered indexes
- Understanding columnstore indexes
- Understanding indexing in memory-optimized tables
- Understanding index statistics
- Understanding other types of indexes
Designing and implementing hybrid and Azure database infrastructure
- Cloud computing and Microsoft Azure
- Cloud models and SQL Server
- Cloud security
- Other data services in Azure
Provisioning Azure SQL Database
- Provisioning a logical SQL server
- Provisioning a database in Azure SQL Database
- Provisioning an elastic pool
- Managing database space
- Security in Azure SQL Database
- Preparing Azure SQL Database for disaster recovery
Provisioning Azure SQL Database managed instance
- What is a Managed Instance?
- Creating a managed instance
- Establishing a connection
- Migrating data to managed instance
- Deleting a managed instance
- Network requirements for managed instances
- Managed Instance administration features
- Azure SQL Analytics
- Security of Managed Instance
- Data protection features
Migrating to SQL Server solutions in Azure
- Migration Services Options
- Common causes for migration failures
- Integration Runtime options for Migration Services
- Best Practices for security and resiliency during migration
Leveraging big data and machine learning
- A big data primer
- Introducing Big Data Clusters
- Deploying Big Data Clusters
- Benefits of PolyBase
- Operationalizing analytics with Machine Learning Server
- Launchpad service
Getting started with SQL Server tools
- Exporting Data with the Import/Export Wizard
Introducing database server components
- Installing the Failover Cluster Feature
Designing and implementing an on-premises database infrastructure
- Designing Multiple Filegroups
Installing and configuring SQL Server instances and features
- Installing SQL Server Analysis Services
- Installing SQL Server Integration Services
Installing and configuring SQL Server on Linux
- Configuring SQL Server
Provisioning and configuring SQL Server databases
- Creating a SQL Login
- Using the ALTER Command with the ROLLBACK IMMEDIATE Command
Understanding table features
- Creating a Table
Maintaining and monitoring SQL Server
- Using CHECKDB
- Managing Resource Utilization Using Resource Governor
Automating SQL Server administration
- Configuring Database Mail
Developing, deploying, and managing data recovery
- Creating a Differential Backup
- Creating a Full Database Backup
Administering security and permissions
- Granting Permissions to Create Tables
Protecting data through encryption, privacy and auditing
- Creating a New Server Audit and Configuring a Server Audit Specification
Performance tuning SQL Server
- Optimizing Queries
Understanding and designing indexes
- Creating a Non-Clustered Index
- Creating a Columnstore Index
- Configuring Full-Text Indexing
Designing and implementing hybrid and Azure database infrastructure
- Creating a Virtual Machine in Azure