70-764 SQL Server 2016 Database Administration (DBA)

Learn to authenticate logins in SQL Server Management Studio by creating Active Directory users and groups and assigning them as logins for a web application.

Grant access to databases by creating logins and mapping them to database users, including guest access, while understanding security tokens and single sign-on concepts.

Demonstrate creating a password-protected login, assigning it to a server role, creating a database role, creating a user for the login, and managing tokens and role membership.

Enable contained database authentication, apply changes with reconfigure, create a partially contained database, assign users, and rollback by dropping the database.

Demonstrates assigning fixed and user-defined server roles by mapping a login to the public role, verifying membership, and granting permissions to administer databases and perform bulk operations.

Create and manage logins and users, map them to databases, and grant roles such as db_datareader to allow selecting tables, then clean up by dropping test accounts.

Explore user-defined database roles, including creating roles with ownership, managing membership, and applying grant, deny, and revoke statements, plus application roles and schema permissions.

Explore creating and using a user-defined database role in SQL Server, grant select on a schema, and assign users to the Human Resources role to manage permissions across HR tables.

Learn how to authorize users to access database resources by applying grants, denies, and revokes at object, schema, and column levels, with grant options and cascading revokes.

Create a login named month3 login with a policy password, map it to a database user, grant select on product inventory and shelf field. Test access and revert to admin.

This lab demonstrates configuring permissions at the schema level by granting and denying execute rights on schema objects, including stored procedures, functions, and tables, and verifying access for a user.

Implement SQL Server audit using extended events, create server and database audit specifications, configure targets and action groups, and query audit results with dynamic management views.

Create and enable a separate audit to store data, then configure a database audit specification on the Adventure walk to capture login events and data changes.

Learn to create and enable a custom audit in SQL Server 2016, define a user-defined audit specification, log order discounts, and then disable and drop the audit.

Explore creating and enabling SQL Server audit specifications, including file audit and log audit outputs, and viewing audited data from sample selections to illustrate audit output.

Learn always encrypted and tde by encrypting a customer phone number with deterministic or nondeterministic methods, and manage column master key, service master key, and certificate for backup and restore.

Demonstrate cell-level encryption in SQL Server 2016 by creating a symmetric key to encrypt a credit card column and decrypting data with decryption by password.

Explore SQL Server transaction logs, write-ahead logging, and the WAL protocol for full recovery and durability. Learn how log growth via virtual log files and truncation preserves atomicity and rollback.

Master backup types in SQL Server 2016: full, differential, and transaction log backups. Learn how recovery model drives options, and how media sets and append or overwrite choices shape backups.

Learn to back up databases and transaction logs using full, differential, and log backups, organized into media sets and backup sets, via sql backup statements or the management studio.

Design and enforce backup retention and testing policies, verify backup integrity and metadata, and manage backup history, media sets, and restore readiness for SQL Server 2016 database administration.

Verify backups by querying backup media family and backup sets to confirm database name, start and finish dates, expiration, and backup file integrity.

Learn the sql server 2016 restore process, including selecting backups, three-phase recovery (copy redo undo), and restoring full, differential, and log backups to a precise point in time.

Explore restoring a SQL Server database using full, differential, and transaction log backups, with no recovery or recovery options to bring online for users.

Learn how to perform point-in-time database restoration in SQL Server 2016, including restoring full, differential, and transaction log backups, using no recovery, file relocation, and restricted access.

Learn to automate SQL Server tasks with SQL Server Agent, manage jobs and schedules, implement alerts and notifications, and enable proactive administration with maintenance plans and System Center Operations Manager.

Learn how to manage SQL Server Agent jobs, view and interpret job history, configure retention, and troubleshoot failures by checking status, history, execution, and dependencies.

Create and schedule SQL Server Agent jobs to automate backups and security, define job steps, and monitor weekly runs in the module 8 lab.