Administering a SQL Database Infrastructure v1.0
Question 1
DRAG DROP -
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK
INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location.
You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
-> Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has
EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
-> Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.
You need to configure a new replica of AG1 on Instance6.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL statements to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:
Answer : 
Explanation:
Scenario: You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
Box 1: REPLICA -
MODIFY REPLICA ON modifies any of the replicas ofthe availability group.
Box 2: SYNCHRONOUS_COMMIT -
You must minimize latency between the nodes in AG1
AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }
Specifies whether the primary replica has to wait for the secondary availability group to acknowledge the hardening (writing) of the log records to disk before the primary replica can commit the transaction on a given primary database.
FAILOVER AUTOMATIC (box 4) requires SYNCHRONOUS_COMMIT
Box 3: REPLICA -
MODIFY REPLICA ON modifies any of the replicas of the availability group.
Box 4: AUTOMATIC -
You must minimize latency between the nodes in AG1
FAILOVER_MODE = { AUTOMATIC | MANUAL }
Specifies the failover mode of the availability replica that you are defining.
FAILOVER_MODE is required in the ADD REPLICA ON clause and optional in the MODIFY REPLICA ON clause.
AUTOMATIC enables automatic failover. AUTOMATIC is supported only if you also specify AVAILABILITY_MODE = SYNCHRONOUS_COMMIT.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql
Question 2
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK
INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location.
You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
-> Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has
EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
-> Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.
You need to create a backup plan for Instance4.
Which backup plan should you create?
- A. Weekly full backups, nightly differential. No transaction log backups are necessary.
- B. Weekly full backups, nightly differential backups, transaction log backups every 5 minutes.
- C. Weekly full backups, nightly differential backups, transaction log backups every 12 hours.
- D. Full backups every 60 minutes, transaction log backups every 30 minutes.
Answer : B
From Scenario:
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. The recovery point objective of Instancse4 is 60 minutes.
RecoveryPoint Objectives are commonly described as the amount of data that was lost during the outage and recovery period. You should minimize both the time required to restore the databases and the space required to store backups.
References:
http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives
Question 3
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK
INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location.
You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
-> Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has
EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
-> Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.
You need to reduce the amount of time it takes to backup OperationsMain.
What should you do?
- A. Modify the backup script to use the keyword SKIP in the FILE_SNAPSHOT statement.
- B. Modify the backup script to use the keyword SKIP in the WITH statement.
- C. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.
- D. Modify the full database backups script to stripe the backup across multiple backup files.
- E. Modify the backup script to use the keyword COPY_ONLY in the WITH statement.
- F. Run the following Transact-SQL statement for each file in OperationsMain: BACKUP DATABASE OperationsMain FILE ["¦]
Answer : D
Explanation:
One of the filegroup is read_only should be as it only need to be backup up once. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.
From Scenario:
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMainthat is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server
Question 4
DRAG DROP -
You have a database. The existing backups for the database and their corresponding files are listed in the following table.
You purchase a new server. You must restore the database to the new server.
You need to restore the data to the most recent time possible.
Which three files should you restore in sequence? To answer, move the appropriate files from the list of files to the answer area and arrange them in the correct order.
Select and Place:
Answer : 
Explanation:
Step 1: Full.
Start with the full backup.
Step 2: Diff_20160503_1700.bak -
Followed by the most recent differential backup.
Step 3: Log_20160503_1900.bak -
And finally the most recent log backup (the only log backup done after the most recent differential backup).
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/differential-backups-sql-server
Question 5
A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups.
DB1 experiences a failure. You must restore FG1 and then FG2.
You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online.
What should you specify when you run the recovery command?
- A. the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
- B. the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
- C. the WITH RECOVERY clause for both FG1 and FG2
- D. the WITH NORECOVERY clause for both FG1 and FG2
Answer : A
Question 6
DRAG DROP -
You have a test server that contains a database named DB1. Backups of the database are written to a single backup device. The backup device has a full, differential, and transaction log backup.
You discover that the database is damaged. You restore the database to the point at which the differential backup was taken.
You need to rebuild the database with data stored in the latest transaction logs.
How should you complete the Transact-SQL statement? To answer. drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:
Answer : 
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/
Question 7
You have a database named DB1 that is configured to use the full recovery model. You have a full daily backup job that runs at 02:00. The job backs up data from
DB1 to the file B:\DB1.bak.
You need to restore the DB1 database to the point in time of May 25, 2016 at 02:23 and ensure that the database is functional and starts to accept connections.
Which Transact-SQL statement should you run?
A.
B.
C.
D.
Answer : B
Question 8
HOTSPOT -
You manage a Microsoft-SQL Server database named salesOrders.
You need to verify the integrity of the database and attempt to repair any errors that are found. Repair must not cause any data to be lost in the database.
How should you complete the DBCC command? To answer, select the appropriate options in the answer area.
Hot Area:
Answer : 
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
Question 9
HOTSPOT -
You manage a Microsoft SQL Server environment. You have a database named salesOrders that includes a table named Table1.
Table1 becomes corrupt. You repair the table.
You need to verify that all the data in Table1 complies with the schema.
How should you complete the Transact-SQL code statement? To answer, select the appropriate Transact-SQL code segments in the dialog box in the answer area.
Hot Area:
Answer : 
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
Question 10
DRAG DROP -
You are configuring a new Microsoft SQL Server Always On Availability Group. You plan to configure a shared network location at \\DATA-CI1\SQL.
You need to create an availability group listener named AGL1 on port 1433.
In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
Answer : 
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
-> Connect to the server instance that hosts the primary replica.
-> Use the LISTENER option of the CREATE AVAILABILITY GROUP statement or the ADD LISTENER option of the ALTER AVAILABILITY GROUP statement.
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
Question 11
HOTSPOT -
You are configuring log shipping for a Microsoft SQL Server database named salesOrders.
You run the following Transact-SQL script:
You need to determine the changes that the script has on the environment.
How does the script affect the environment? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer : 
Explanation:
Box 1: is -
The dedicated backup file share is \\localhost\Backup
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.
Question 12
HOTSPOT -
You are planning the deployment of two new Always On Failover Cluster Instances (FCIs) of Microsoft SQL Server to a single Windows Server Cluster with three nodes. The planned configuration for the cluster is shown in the Server Layout exhibit. (Click the Exhibit button.)
The SAN team has configured storage for the cluster and sent the configuration to you in the email shown in the SAN Team Email exhibit. (Click the Exhibit button.)
Each node of the cluster has identical local storage available as shown in the Local Storage exhibit. (Click the Exhibit button.)
All local storage is on SSD.
You need to plan specific configurations for the new cluster.
For each of the following statement, select Yes if the statement is true. Otherwise, select No.
Hot Area:
Answer : 
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
Question 13
HOTSPOT -
You are planning to deploy log shipping for Microsoft SQL Server and store all backups on a dedicated fileshare.
You need to configure the servers to perform each log shipping step.
Which server instance should you configure to perform each action? To answer, select the appropriate server instances in the dialog box in the answer area.
Hot Area:
Answer : 
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
Question 14
HOTSPOT -
You manage a Microsoft SQL Server instance. You have a user named User1.
You need to grant the minimum permissions necessary to allow User1 to review audit logs.
For each action, which option should you use? To answer, select the appropriate options in the answer area.
Hot Area:
Answer : 
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.
Question 15
DRAG DROP -
You administer a Microsoft SQL Server database named Contoso. You create a stored procedure named Sales.ReviewInvoice by running the following Transact-
SQL statement:
You need to create a Windows-authenticated login named ContosoSearch and ensure that ContosoSearch can run the Sales.ReviewInvoices stored procedure.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:
Answer : 