Inhoud ICT-cursus Cert-Kit – 70-764 (Administering a SQL Database Infrastructure)

Inhoud van de ICT-cursus Cert-Kit - 70-764 (Administering a SQL Database Infrastructure) | De Kantooropleider

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
Print pagina