Microsoft Provisioning SQL Databases (070-765) Free Practice Test
Question 1
You administer two Microsoft SQL Server 2014 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?
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?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 2
You administer two Microsoft SQL Server 2014 servers. Each server resides in a different, untrusted domain.
You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?
You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 3
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan.
You need to implement real-time encryption of the database and all backups.
Solution: You enable Transparent Data Encryption (TDE) on the primary instance.
Does the solution meet the goal?
You need to implement real-time encryption of the database and all backups.
Solution: You enable Transparent Data Encryption (TDE) on the primary instance.
Does the solution meet the goal?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 4
User report that a query takes a long time to execute. The query has the following wait statistics.
Which resource causes the issue?
Which resource causes the issue?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 5
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine.
You must enable Always Encrypted for columns in a database.
You need to configure the key store provider.
What should you do?
You must enable Always Encrypted for columns in a database.
You need to configure the key store provider.
What should you do?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 6
You manage a large data warehouse.
You must archive data that is more than two years old. The data must all be accessible without code changes. You create a new volume for the data archive and create a new file group named Archive.
You need to develop a storage that will minimize space utilization and protect data from accidental deletion.
Which four 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.
You must archive data that is more than two years old. The data must all be accessible without code changes. You create a new volume for the data archive and create a new file group named Archive.
You need to develop a storage that will minimize space utilization and protect data from accidental deletion.
Which four 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.
Correct Answer:
Explanation:
Step 1: Create an archive table on the new filegroup
Step 2: Create a partition scheme aligned to primary and archive filegroups Creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups.
Step 3: Apply partition scheme to tables
Step 4: Compress the archive table
References:
https://www.sqlshack.com/archiving-sql-server-data-using-partitions/
Question 7
You administer a Microsoft SQL Server 2014 database named Orders.
Orders contains a table named OrderShip that is defined as follows:
A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.
Customers require a procedure that will return orders for all customers from a specified country.
You create a new procedure:
Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?
Orders contains a table named OrderShip that is defined as follows:
A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.
Customers require a procedure that will return orders for all customers from a specified country.
You create a new procedure:
Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 8
You have a database named DB1 that contains a table named Tabie1. Table1 has a non-clustered index named index1.
You discover that index1 is corrupt.
You need to repair index1.
Which statement should you execute?
You discover that index1 is corrupt.
You need to repair index1.
Which statement should you execute?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 9
You plan to create a database in Microsoft Azure to manage sales data for an organization.
You need to create an Azure SQL Database that meets standard naming requirements.
Which names should you use? To answer, select the appropriate options in the answer area.
Note: Each correct selection is worth one point.
You need to create an Azure SQL Database that meets standard naming requirements.
Which names should you use? To answer, select the appropriate options in the answer area.
Note: Each correct selection is worth one point.
Correct Answer:
Question 10
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You have a mission-critical application that stores data in a Microsoft SQL Server instance. The application runs several financial reports. The reports use a SQL Server-authenticated login named Reporting_User. All queries that write data to the database use Windows authentication.
Users report that the queries used to provide data for the financial reports take a long time to complete. The queries consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the application also take a long time to complete.
You need to improve performance of the application while still allowing the report queries to finish.
Solution: You configure the Resource Governor to set the MAXDOP parameter to 0 for all queries against the database.
Does the solution meet the goal?
You have a mission-critical application that stores data in a Microsoft SQL Server instance. The application runs several financial reports. The reports use a SQL Server-authenticated login named Reporting_User. All queries that write data to the database use Windows authentication.
Users report that the queries used to provide data for the financial reports take a long time to complete. The queries consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the application also take a long time to complete.
You need to improve performance of the application while still allowing the report queries to finish.
Solution: You configure the Resource Governor to set the MAXDOP parameter to 0 for all queries against the database.
Does the solution meet the goal?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 11
You administer a Microsoft SQL Server 2014 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
Correct Answer: C
Question 12
You create an availability group that has replicas named HA/Server01 and HA/Server02.
Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica.
What should you do?
Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica.
What should you do?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 13
You administer a Microsoft SQL Server 2014 failover cluster.
You need to ensure that a failover occurs when the server diagnostics returns query_processing error.
Which server configuration property should you set?
You need to ensure that a failover occurs when the server diagnostics returns query_processing error.
Which server configuration property should you set?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 14
You have Microsoft SQL Server on a Microsoft Azure virtual machine. You discover that a database has 1.000 corrupt pages. You need to repair the corrupt pages. Which statement should you execute?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).