Explanation:

Box 1: RESTORE

Box 2: RECOVERY

The RESTORE … WITH RECOVERY option puts the database into a useable state, so users can access a restored database.

References: https://www.mssqltips.com/sqlservertutorial/112/recovering-a-database-that-is-in-the­restoring-state/

Explanation:

Box 1: CHECKDB

DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database.

Partial syntax:

DBCC CHECKDB

[ ( database_name | database_id | 0

[ , NOINDEX

| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

….

Box 2: REPAIR_REBUILD

DBCC CHECKDB …REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST |REPAIR_REBUILD specifies that DBCC CHECKDB repair the found errors.

REPAIR_REBUILD performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

Explanation:

Box 1: CHECKCONSTRAINTS

DBCC CHECKCONSTRAINTS checks the integrity of a specified constraint or all constraints on a specified table in the current database.

Box 2: ALL_CONSTRAINTS

ALL_CONSTRAINTS checks all enabled and disabled constraints on the table if the table name is specified or if all tables are checked;otherwise, checks only the enabled constraint.

Note: Syntax: DBCC CHECKCONSTRAINTS

[

(

table_name | table_id | constraint_name | constraint_id

)

]

[ WITH

[ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]

[ , ] [NO_INFOMSGS ]

]

References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkconstraints-transact-sql

Explanation:

Step 1: Launch the Failover Cluster Manager and..

To support theAlways On availability groups feature, ensure that every computer that is to participate in one or more availability groups meets requirements including:

* Ensure that each computer is a node in a WSFC (Windows Server Failover Clustering).

Step 2: Add andconfigure the replica and…

All the server instances that host availability replicas for an availability group must use the same SQL Server collation.

Step 3: Enable the SQL Server 2016 Always On Availability Group feature.

Enable the Always On availability groups feature on each server instance that will host an availability replica for any availability group. On a given computer, you can enable as many server instances for Always On availability groups as your SQL Server installation supports.

Step 4: Create the Always On Availability Group and..

Using Transact-SQL to create or configure an availability group listener

Step 5: Select the Full data synchronization method and…

References: https://technet.microsoft.com/en-us/library/jj899851(v=sc.12).aspx https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or­configure-an-availability-group-listener-sql-server

Explanation:

Box 1: is

The dedicated backup file share is \localhostBackup

Box 2: does not run

The only thing with a name related to ADATM-SQL11 is the schedule name.

Box 3: 72 hours

4320 minutes equals 72 hours.

Note: @backup_retention_period= ] backup_retention_period

Isthe length of time, in minutes, to retain the log backup file in the backup directory on the primary server. backup_retention_period is int, with no default, and cannot be NULL.

Box 4: 15 minutes.

[ @freq_subday_type = ] freq_subday_type

Specifies the units for freq_subday_interval. freq_subday_typeis int, with a default of 0, and can be one of these values.

Here it is 4, which means minutes.

[ @freq_subday_interval = ] freq_subday_interval

The number of freq_subday_type periods to occur between eachexecution of a job. freq_subday_intervalis int, with a default of 0.

Note: Interval should be longer than 10 seconds. freq_subday_interval is ignored in those cases where freq_subday_type is equal to 1.

Here it is 15.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-log-shipping-primary-database-transact-sql

Explanation:

Box 1: Yes

tempdb on local storage. FCIs now support placement of tempdb on local non-shared storage, such as a local solid-state-drive, potentially offloading a significant amount of I/O from a shared SAN.

Prior to SQL Server 2012, FCIs required tempdb to be located on a symmetrical shared storage volume that failed over with other system databases.

Box 2: No

The VNN is set on the group level, not on the instance level.

Database client applications can connect directly to a SQL Server instance network name, or they may connect to a virtual network name (VNN) that is bound to an availability group listener. The VNN abstracts the WSFC cluster and availability group topology, logically redirecting connection requests to the appropriate SQL Server instance and database replica.

The logical topology of a representative AlwaysOn solution is illustrated in this diagram:

Box 3: No

You don’t configure the SAN from a SQL Server, instead you can use a Microsoft Server server.

References: http://download.microsoft.com/download/d/2/0/d20e1c5f-72ea-4505-9f26-fef9550efd44/microsoft%20sql%20server%20alwayson%20solutions%20guide%20for%20high%20availability%20and%20disaster%20recovery.docx

Explanation:

Note: Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server.

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

Box 1: Primary server instance.

The primary server instance runs the backup job to back up the transaction log on the primary database.

backup job: A SQL Server Agent job that performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. When log shipping is enabled, the job category "Log Shipping Backup" is created on the primary server instance.

Box 2: Secondary server instance

Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.

copy job: A SQL Server Agent job that copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. When log shipping is enabled on a database, the job category "Log Shipping Copy" is created on each secondary server in a log shipping configuration.

Box 3: Secondary server instance.

Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.

restore job: A SQL Server Agent job that restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. When log shipping is enabled on a database, the job category "Log Shipping Restore" is created on the secondary server instance.

References: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server

Explanation:

Box 1: securityadmin

To access log files for instances of SQL Server that are online, this requires membership in the securityadmin fixed server role.

Box 2: sys.server_audit_specifications

sys.server_audit_specifications contains information about the server audit specifications in a SQL Server audit on a server instance.