070-765 無料問題集「Microsoft Provisioning SQL Databases」
You are a database administrator for a Microsoft SQL Server 2014 database named AdventureWorks2012.
You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)
You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)
You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
正解：C
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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 Microsoft SQL Server on a Microsoft Azure virtual machine that has a database named DB1.
You discover that DB1 experiences WRITE_LOG waits that are longer than 50 ms.
You need to reduce the WRITE_LOG wait time.
Solution: Add additional data files to DB1.
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 Microsoft SQL Server on a Microsoft Azure virtual machine that has a database named DB1.
You discover that DB1 experiences WRITE_LOG waits that are longer than 50 ms.
You need to reduce the WRITE_LOG wait time.
Solution: Add additional data files to DB1.
Does this meet the goal?
正解：B
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
Database DB1 must use two CPU cores.
Queries that were running on database DB2 prior to migration do not complete.
You need to configure the databases.
In the table below, identify the parameter that must be configured for each databases.
Select one option for DB1, and one option for DB2. Select one option for each column.
Queries that were running on database DB2 prior to migration do not complete.
You need to configure the databases.
In the table below, identify the parameter that must be configured for each databases.
Select one option for DB1, and one option for DB2. Select one option for each column.
正解：
Explanation:
Query_optimizer_hotfixes
DB1: MAXDOP
You can use the max degree of parallelism (MAXDOP) option to limit the number of processors to use in parallel plan execution.
DB2: LEGACY_CARDINALITY_ESTIMATION
The CE (Cardinality Estimation) predicts how many rows your query will likely return. The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.
Legacy CE: For a SQL Server database set at compatibility level 120 and above, the CE version 70 can be can be activated by using the at the database level by using the ALTER DATABASE SCOPED CONFIGURATION.
Example:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
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 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?
正解：C
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You administer a SQL Server 2014 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the following requirements are met:
Which Transact-SQL statement should you use?
You need to ensure that the following requirements are met:
Which Transact-SQL statement should you use?
正解：H
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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?
正解：C
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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 use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).
You load data to the database by using Azure Data Factory.
You need to reduce the amount of time it takes to load the data.
Solution: You move the database to a Premium database that has 125 eDTUs.
Does this 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.
You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).
You load data to the database by using Azure Data Factory.
You need to reduce the amount of time it takes to load the data.
Solution: You move the database to a Premium database that has 125 eDTUs.
Does this meet the goal?
正解：B
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You need to create an alert that is triggered when the number of seconds that a page stays in the buffer pool without references is less than 1.000.
In the New Alert dialog box. how should you configure the alert? To answer, select the appropriate option from each list in the answer area.
In the New Alert dialog box. how should you configure the alert? To answer, select the appropriate option from each list in the answer area.
正解：
Explanation:
References:
You administer a Microsoft SQL Server 2014 database that contains a table named OrderDetail.
You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline.
Which Transact-SQL batch should you use?
You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline.
Which Transact-SQL batch should you use?
正解：A
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You have database that contains a 400-GB table that is read-only.
You need to enable the Stretch Database feature.
How should you complete the statement? To answer, drag the appropriate values to the correct targets. Each value 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.
NOTE: Each correct selection is worth one point.
Select and Place:
You need to enable the Stretch Database feature.
How should you complete the statement? To answer, drag the appropriate values to the correct targets. Each value 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.
NOTE: Each correct selection is worth one point.
Select and Place:
正解：
Explanation:
Section: Deploy and migrate applications
To configure an existing table for Stretch Database, run the ALTER TABLE command.
Here's an example that migrates the entire table and begins data migration immediately.
USE <Stretch-enabled database name>;
GO
ALTER TABLE <table name>
SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) ) ;
GO
References:
https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/enable-stretch-database-for-atable
Note: This questions 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.
You manage on-premises and Microsoft Azure SQL Database instances for a company. Your environment must support the Microsoft SQL Server 2012 ODBS driver.
You need to encrypt only specific columns in the database.
What should you implement?
You manage on-premises and Microsoft Azure SQL Database instances for a company. Your environment must support the Microsoft SQL Server 2012 ODBS driver.
You need to encrypt only specific columns in the database.
What should you implement?
正解：F
解答を投票する
You have a single server on premises Microsoft SQL Server deployment.
You must migrate the environment to an Azure virtual machine (VM). You must minimize costs while maintaining the same level of performance as the on-premises SQL environment.
You need to evaluate the number and types of read/write operations for the existing deployment.
Which tool should you use?
You must migrate the environment to an Azure virtual machine (VM). You must minimize costs while maintaining the same level of performance as the on-premises SQL environment.
You need to evaluate the number and types of read/write operations for the existing deployment.
Which tool should you use?
正解：C
解答を投票する