最新的Microsoft Administering a SQL Database Infrastructure - 70-764免費考試真題
You administer two Microsoft SQL Server 2016 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor.
Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services.
You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.
The distribution agent on ProdSrv2 regularly fails, displaying the following error message:
"Cannot access the file. Operating system error code 5 (Access is denied.)." You need to configure the distribution agent by granting only the minimum required access to all accounts.
What should you do?
Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services.
You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.
The distribution agent on ProdSrv2 regularly fails, displaying the following error message:
"Cannot access the file. Operating system error code 5 (Access is denied.)." You need to configure the distribution agent by granting only the minimum required access to all accounts.
What should you do?
正確答案: D
You administer a Microsoft SQL Server 2016 instance.
After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server.
What should you do?
After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server.
What should you do?
正確答案: B
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed.
SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.
You need to upgrade the database to SQL Server 2014. The solution must minimize downtime. Which upgrade steps should you recommend? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.
You need to upgrade the database to SQL Server 2014. The solution must minimize downtime. Which upgrade steps should you recommend? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
正確答案:
Explanation
References:https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/upgrading-mirrored-instanc
You are using dynamic management views to monitor an SQL Server server named SQL1. A database administrator named Dba1 must monitor the health of SQL1.
You need to ensure that Dba1 can access dynamic management views for SQL1.
The solution must use the principle of least privilege. Which permissions should you assign to Dba1?
You need to ensure that Dba1 can access dynamic management views for SQL1.
The solution must use the principle of least privilege. Which permissions should you assign to Dba1?
正確答案: B
說明：（僅 Fast2test 成員可見）
You work as a Database Administrator (DBA) for a company named ABC.com.
The company uses a Microsoft SQL Server 2012 infrastructure.
The sales and marketing departments contain a total of 60 users. Each user uses a custom application that stores data in a SQL Server database.
Each user has a separate database.
A server named ABC-SQL1 hosts the 60 databases.
You need to configure a backup solution for all the databases.
The solution must ensure that any new databases configured on ABC-SQL1 are automatically added to the backup schedule. The backup schedule consists of a full backup every night, a differential backup every hour and a transaction log backup every 15 minutes.
How should you configure the backup solution?
The company uses a Microsoft SQL Server 2012 infrastructure.
The sales and marketing departments contain a total of 60 users. Each user uses a custom application that stores data in a SQL Server database.
Each user has a separate database.
A server named ABC-SQL1 hosts the 60 databases.
You need to configure a backup solution for all the databases.
The solution must ensure that any new databases configured on ABC-SQL1 are automatically added to the backup schedule. The backup schedule consists of a full backup every night, a differential backup every hour and a transaction log backup every 15 minutes.
How should you configure the backup solution?
正確答案: A
You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database.
You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
正確答案: C
You administer a database that is used for reporting purposes. The database has a large fact table that contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered index on the ProductID column. New rows are inserted into the table every day.
Performance of queries that filter the Product ID column have degraded significantly.
You need to improve the performance of the queries.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Performance of queries that filter the Product ID column have degraded significantly.
You need to improve the performance of the queries.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
正確答案:
Explanation
Step 1: Drop the clustered columnstore index
Step 2: Create a clustered rowstore index on ProductID.
Rowstore indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of table scans.
Step 3: Create a nonclustered index on ProductID
You administer a Microsoft SQL Server 2016 database.
You have a SQL Server Agent job instance that runs using the service account.
You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account.
What should you use?
You have a SQL Server Agent job instance that runs using the service account.
You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account.
What should you use?
正確答案: B
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.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate.
You observe that the database is fragmented.
You need to optimize query performance.
Solution: You reorganize all indexes.
Does the solution meet the goal?
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.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate.
You observe that the database is fragmented.
You need to optimize query performance.
Solution: You reorganize all indexes.
Does the solution meet the goal?
正確答案: B
說明：（僅 Fast2test 成員可見）
You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso.
You also need to ensure minimum impact to the server.
What should you create?
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso.
You also need to ensure minimum impact to the server.
What should you create?
正確答案: F
You have multiple Microsoft SQL Server databases that are deployed in an Always On availability group. You configure the SQL Server Agent service to start automatically.
You need to automate backups for all user databases.
What should you create?
You need to automate backups for all user databases.
What should you create?
正確答案: B
說明：（僅 Fast2test 成員可見）
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login.
You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
You create a user account in the msdb database for the AgentPortalUser login.
You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
正確答案: B
說明：（僅 Fast2test 成員可見）
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 sections, 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 even records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You grant the VIEW SERVER STATE permission to User1.
Does this meet the goal?
After you answer a question in this sections, 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 even records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You grant the VIEW SERVER STATE permission to User1.
Does this meet the goal?
正確答案: B
說明：（僅 Fast2test 成員可見）
You are troubleshooting an application that runs a query.
The application frequently causes deadlocks. You need to identify the isolation level used by the query when a deadlock occurs.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
The application frequently causes deadlocks. You need to identify the isolation level used by the query when a deadlock occurs.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
正確答案: B
說明：（僅 Fast2test 成員可見）