Tijdens de ICT-cursus Cert-Kit – 70-764 (Administering a SQL Database Infrastructure) worden de volgende onderdelen behandeld:
Microsoft SQL Server 2016: Encryption
- Start the course
- Describe the encryption features supported in SQL Server 2016
- Describe Transparent Data Encryption and the keys used for encrypting and decrypting data
- Use Transparent Data Encryption
- Describe the features and limitations of Always Encrypted
- Use the Always Encrypted Wizard
- Configure Always Encrypted using SQL Server Management Studio
- Configure Always Encrypted using PowerShell
- Describe support and limitations for encryption with Azure SQL Database
- Describe the benefits, requirements, restrictions, methods, and recommended practices for backup encryption
- Create an encrypted backup
- Recognize the purpose of cell-level encryption and how to use Transact-SQL and symmetric encryption to configure it
- Enable encrypted connections to a database
- Troubleshoot encryption
- Configure encryption in SQL Server 2016
Microsoft SQL Server 2016: Data Access and Permissions
- Start the course
- Describe the types of database users and their purpose
- Create a database user using SQL Server Management Studio
- Create a database user using Transact-SQL
- Describe available server permissions
- Recognize the steps involved in granting a user access to a database and its objects
- Grant access to database objects
- Revoke access to database objects
- Describe:
- Database-level roles and their purpose
- Server-level roles and their purpose
- The purpose of application roles
- Recognize why you would use and how to create a custom role
- Describe the purpose of row-level security and how to implement it
- Implement row-level security using T-SQL and predicates
- Implement row-level security using T-SQL and predicates
- Describe the purpose of dynamic data masking
- Define dynamic data masking
- Describe the benefits and features of using Azure SQL Database
- Create and configure a new Azure SQL Database
- Connect to and manage an Azure SQL Database using a local instance of SSMS
- Configure data access and permissions in SQL Server 2016
Microsoft SQL Server 2016: Auditing
- Start the course
- Describe why auditing is required and auditing methods
- Use:
- Triggers for auditing
- Temporal tables for auditing
- Temporal tables for auditing
- View the audit log using SQL Server Management Studio or sys.fn_get_audit_file
- Describe the purpose of Extended Events
- Describe the purpose of SQL Server Audit, auditing levels, and components
- Define an audit using SQL Server Audit
- Describe the purpose of actions and actions groups
- Create server audit specifications
- Create database audit specifications
- Use dynamic management and system views to manage an audit
- Compare auditing in Azure SQL Database with SQL Server auditing and describe its purpose
- Configure auditing for Azure SQL Database
- Describe the purpose of audit logs and reports when auditing in Azure SQL Database
- Analyze audit logs and reports when auditing in Azure SQL Database
- Configure auditing in SQL Server 2016
Microsoft SQL Server 2016: Backing Up Databases
- Start the course
- Describe the reasons for backing up a database and supported backup types
- Determine the appropriate strategy for backing up a database
- Determine the appropriate media for backing up a database
- Recognize how to plan a retention policy for backing up databases
- Create a backup using SQL Server Management Studio
- Create a backup using Transact-SQL
- Describe how transaction log backup works
- Create an automated backup job in SQL Server Management Studio
- Describe the features and benefits of backing up a database to the Azure Blob Storage Service
- Describe best practice and troubleshooting tips for backing up to the Azure Blob Storage Service
- Back up a database to Azure
- Describe the purpose of the three recovery models
- View and change recovery models using SQL Server Management Studio
- Recognize how to view and change recovery models using Transact-SQL
- Describe tasks that should be completed after changing the recovery model
- Manage and implement a backup strategy in SQL Server 2016
Restoring Databases
- Start the course
- Recognize what is involved in restoring a database, what can be restored, and restore scenario models
- Describe the purpose and prerequisites of restoring a database backup under the simple recovery model
- Restore a database under the simple recovery model
- Describe the purpose of and considerations for restoring a database under the full recovery model
- Restore a database under the full recovery model
- Restore a database under the full recovery model
- Automate and test restores
- Describe point-in-time recovery and perform point-in-time recovery using SQL Server Management Studio
- Perform point-in-time recovery using Transact-SQL
- Describe the goal of file restore and how it differs in the simple recovery model versus the full recovery model
- Restore:
- Files and filegroups to a new location
- Files and filegroups over existing files
- Recognize when a page restore is useful, as well as its limitations and restrictions
- Restore pages using SQL Server Management Studio and Transact-SQL
- Describe the purpose and:
- Process of piecemeal restore
- Requirements of piecemeal restore in the simple recovery model
- Requirements of piecemeal restore in the full recovery model
- Restore a database using a piecemeal restore
- Restore a database in SQL Server 2016
Managing Database Integrity
- Start the course
- Describe the purpose of the Database Consistency Checker
- Use the Database Consistency Checker in SQL Server Management Studio
- Use the catalog view to see information about progress and current phase of execution
- Describe the types of consistency checks appropriate for databases
- Perform consistency checks on tables and indexes
- Recognize common consistency check errors and their resolutions
- Recognize possible causes of database corruption
- Detect corruption in a SQL Server database
- Recover from database corruption
- Use SQL Server 2016 tools to ensure database consistency
Monitoring Database Activity and Queries
- Start the course
- Recognize the types of activities that can be monitored using Activity Monitor
- Use Activity Monitor in SQL Server Management Studio to monitor database activity
- Identify blocks using Activity Monitor and stored procedures
- Describe the role of the data collector
- Describe the components that make up the data collection system
- Configure the data collector
- Use System Monitor to monitor resource usage
- Describe the purpose of utility control point and the requirements for its use
- Use the Create UCP wizard to create a SQL Server utility control point
- Complete the creation of a SQL Server utility control point
- Describe:
- The purpose of the Query Store, its features, and configuration options
- Scenarios for using the Query Store
- The features, benefits, and architecture of performance monitoring with Extended Events
- Create an Extended Events session
- Recognize how Extended Events can be used to troubleshoot performance issues
- Describe the features, benefits, and architecture of SQL Trace
- Describe the uses of SQL Trace
- Identify issues with execution plans
- Monitor database activity in SQL Server 2016
Monitoring SQL Server Instances
- Start the course
- Describe Database Mail, mail profiles, and the importance of security
- Describe the logging levels and the need for a retention policy in Database Mail
- Configure Database Mail
- Recognize the features, benefits, and components of SQL Server Agent
- Describe the purpose of SQL Server Agent operators, the types of contact methods, and notifications
- Create a SQL Server Agent operator using SQL Server Management Studio
- Describe the purpose of SQL Server Agent alerts, how they are triggered, and what they can do
- Recognize the troubleshooting process for SQL Server Agent alerts and notifications
- Create SQL Server Agent alerts and configure their actions using SQL Server Management Studio
- Create alerts that respond to user-defined events
- Define actions a SQL Server Agent should take in the event of job failure
- Describe the purpose and components of Policy-Based Management
- Configure Policy-Based Management properties and failure notification alerts
- Create a Policy-Based Management policy using SQL Server Management Studio
- Recognize how to manage an existing Policy-Based Management policy
- iIentify used and available space on data volumes
- Recognize what affects the performance of a server and when to use baseline measurements
- Recognize performance issues that may affect SQL Server
- Monitor instances in SQL Server 2016
Managing Indexes and Statistics
- Start the course
- Describe the types of indexes available in SQL Server and the importance of managing them properly
- Detect fragmented indexes and determine the degree of fragmentation
- Repair index fragmentation using SQL Server Management Studio
- Repair index fragmentation using Transact-SQL
- Use the Missing Indexes feature to identify missing indexes and to create them, if needed
- Find and remove underutilized indexes
- Describe the purpose of columnstore indexes, as well as why and when to use them
- Improve columnstore index performance
- Defragment columnstore indexes
- Identify and correct outdated statistics
- Describe the purpose and characteristics of the auto_update_statistics option
- Implement Auto Update Statistics
- Implement statistics for large tables
- Manage indexes in SQL Server 2016
High Availability and Disaster Recovery
- Start the course
- Recognize the purpose and benefits of log shipping
- Configure log shipping with SQL Server Management Studio
- Configure log shipping with Transact-SQL
- Describe log shipping management tasks such as adding databases, removing log shipping, failing over, and changing roles
- Recognize the tables and stored procedures used for monitoring log shipping
- Describe:
- Features and benefits of AlwaysOn Availability Groups
- The purpose of the failover process and the available failover modes
- Capabilities provided by Windows Server Failover Clustering and its relationship to AlwaysOn components
- Describe how high availability is accomplished by monitoring cluster resources
- Create a new availability group
- Change the failover mode in an AlwaysOn availability group
- Configure read-only routing for an availability group using Transact-SQL
- Describe the purpose of distributed availability groups
- Create a distributed availability group with Transact-SQL
- Configure cluster shared volumes
- Manage high availability in SQL Server 2016
Volg ons: