70-764: Administering a SQL Database Infrastructure

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for a report that users run frequently.

The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used. You observe that this is due to the distribution of data within a specific table that the query uses.

You need to ensure that the query optimizer always uses the query plan that you prefer.

Solution: You add the KEEPFIXED PLAN query hint to the query.

Does the solution meet the goal?

Yes
No

Correct answer: A

Explanation:

PLAN forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables. References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

PLAN forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.

References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a data warehouse that stores sales data. One fact table has 100 million rows.

You must reduce storage needs for the data warehouse.

You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.

Solution: You remove any clustered indexes and load the table for processing.

Does the solution meet the goal?

Yes
No

Correct answer: B

Explanation:

You should use a column-store index References:https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview

You should use a column-store index

References:

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a server named Server1 that has Microsoft SQL Server installed.

Server1 has SQL Server Audit configured to send audit event records to a file.

You need to ensure that a database user named User1 can review the audit data.

Solution: You grant the VIEW ANY DEFINITION permission to User1.

Does this meet the goal?

Yes
No

Correct answer: B

Explanation:

Each feature and command for SQL Server Audit has individual permission requirements. Unless otherwise specified, viewing catalog views requires a principal to have one of the following:The VIEW SERVER STATE permission. The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view). Membership in the sysadmin fixed server role. The CONTROL SERVER permission. The ALTER ANY AUDIT permission. A principal must have the VIEW SERVER STATE or ALTER ANY AUDIT permission to use the Dynamic Management Views. References:https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql?view=sql-server-ver15

Each feature and command for SQL Server Audit has individual permission requirements.

Unless otherwise specified, viewing catalog views requires a principal to have one of the following:

The VIEW SERVER STATE permission.
The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view).
Membership in the sysadmin fixed server role.
The CONTROL SERVER permission.
The ALTER ANY AUDIT permission.

A principal must have the VIEW SERVER STATE or ALTER ANY AUDIT permission to use the Dynamic Management Views.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql?view=sql-server-ver15

You work as a Database Administrator (DBA) at ABC.com.

All databases are hosted on Windows Server 2012 servers running SQL Server 2012.

The Sales department uses a database named SalesDB.

SalesDB contains a large table named Orders that lists every order ever received by the company. You want to improve the performance of SalesDB.

You want to configure the database to provide the fastest possible access to the most recent orders.

Historical orders can be stored using a slower storage solution.

How can you achieve this goal?

By configuring database mirroring.
By configuring a failover cluster.
By partitioning the Orders table.
By partitioning a partitioned view of the Orders table.

Correct answer: C

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?

Weekly full backups, weekly differential backups, nightly transaction log backups.
Weekly full backups, nightly differential backups, transaction log backups every 5 minutes.
Weekly full backups, nightly differential backups, transaction log backups every 12 hours.
Weekly full backups, differential backups every 30 minutes. No transaction log backups are necessary.

Correct answer: B

Explanation:

Scenario: Instance4 has a Recovery point objective of 60 minutes.You should minimize both the time required to restore the databases and the space required to store backups. Instance4 is engaged in heavy read-write I/O

Scenario: Instance4 has a Recovery point objective of 60 minutes.

You should minimize both the time required to restore the databases and the space required to store backups.

Instance4 is engaged in heavy read-write I/O

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a database that includes a table named Candidate.

You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.

Solution: You run the following query:

Does this meet the goal?

Yes
No

Correct answer: B

Explanation:

RESAMPLE: Update each statistic using its most recent sample rate.References: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

RESAMPLE: Update each statistic using its most recent sample rate.

References: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales system.

The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups.

These backups are stored on a backup server in the company's data center. Every week, the company places the full backup on a tape and sends it to a third-party backup storage system. The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.

You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.

What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

Set up SQL Server Always On with a SQL Azure database as a replica.
Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
Put the differential backup on tape and send it to the third-party backup storage system.
Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.

Correct answer: D

Explanation:

SQL Server 2012 was the first version to provide the ability to back up databases to the Cloud, and SQL Server 2014 improves on the process. Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.

SQL Server 2012 was the first version to provide the ability to back up databases to the Cloud, and SQL Server 2014 improves on the process.

Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.

You have a SQL Server instance on a server named Server1. You need to recommend a solution to perform the following tasks every week:

Rebuild the indexes by using a new fill factor.
Run a custom T-SQL command.
Back up the databases.

What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

A trigger
An alert
A maintenance plan
Windows PowerShell
A system policy

Correct answer: C

Explanation:

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.

A customer that uses an on-premises instance reports that queries take a long time to complete.

You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.

Which Transact-SQL segment should you use?

sys.index_columns
UPDATE STATISTICS
CREATE STATISTICS
SET AUTO_CREATE_STATISTICS ON

Correct answer: D

Explanation:

AUTO_UPDATE_STATISTICS { ON | OFF } ON specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017#auto_update_statistics

AUTO_UPDATE_STATISTICS { ON | OFF }

ON specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017#auto_update_statistics

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

A company has an on-premises Microsoft SQL Server environment.

SQL Server backups should be stored as Microsoft Azure blob pages. The connection process from the SQL Server instances to Azure should be encrypted.

You need to store backups as Azure blob pages. Which option should you use?

backup compression
backup encryption
file snapshot backup
mirrored backup media sets
SQL Server backup to URL
SQL Server Managed Backup to Azure
tail-log backup
back up and truncate the transaction log

Correct answer: F

Explanation:

SQL Server Managed Backup to Microsoft Azure manages and automates SQL Server backups to Microsoft Azure Blob storage. You can choose to allow SQL Server to determine the backup schedule based on the transaction workload of your database. Or you can use advanced options to define a schedule. The retention settings determine how long the backups are stored in Azure Blob storage. References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-managed-backup-to-microsoft-azure?view=sql-server-2017

SQL Server Managed Backup to Microsoft Azure manages and automates SQL Server backups to Microsoft Azure Blob storage. You can choose to allow SQL Server to determine the backup schedule based on the transaction workload of your database. Or you can use advanced options to define a schedule. The retention settings determine how long the backups are stored in Azure Blob storage.

References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-managed-backup-to-microsoft-azure?view=sql-server-2017