070-765 無料問題集「Microsoft Provisioning SQL Databases」
You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that the backup size is as small as possible.
Which backup should you perform every two hours?
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that the backup size is as small as possible.
Which backup should you perform every two hours?
正解：I
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You have just completed a new Microsoft SQL Server installation.
You need to configure a new SQL Server Agent alert to send an email to the DBA team for severity 20 errors.
Which three actions should you perform? Each correct answer presents part of the solution. (Choose three.)
You need to configure a new SQL Server Agent alert to send an email to the DBA team for severity 20 errors.
Which three actions should you perform? Each correct answer presents part of the solution. (Choose three.)
正解：A、B、F
解答を投票する
You have a table named Table1 that contains 500 million records and is 200 GB. The indexes for Table! are configured as shown in the following table.
You need to reduce the fragmentation. The solution must minimize disk I/O and the impact on users.
Which approach should you use for each index? To answer, drag the appropriate approaches to the correct indexes. Each approach 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.
You need to reduce the fragmentation. The solution must minimize disk I/O and the impact on users.
Which approach should you use for each index? To answer, drag the appropriate approaches to the correct indexes. Each approach 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.
正解：
Explanation:
References:
https://blog.sqlauthority.com/2012/04/04/sql-server-cleaning-up-sql-server-indexes-defragmentation-fillfactor-video/
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017
You have a database named DB1.
You discover that DB1 is corrupt.
You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message.
You need to repair the database corruption as quickly as possible. The solution must minimize data loss.
What should you do?
You discover that DB1 is corrupt.
You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message.
You need to repair the database corruption as quickly as possible. The solution must minimize data loss.
What should you do?
正解：E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You administer a Microsoft SQL Server 2014 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.
Which Transact-SQL query should you use?
You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.
Which Transact-SQL query should you use?
正解：G
解答を投票する
You manage an on-premises, multi-tier application that has the following configuration:
Two SQL Server 2012 databases named SQL1 and SQL2
Two application servers named AppServer1 and AppServer2 that run IIS
You plan to move your application to Azure.
You need to ensure that during an Azure update cycle or a hardware failure, the application remains available.
Which two deployment configurations should you implement? Each correct answer presents part of the solution.
Two SQL Server 2012 databases named SQL1 and SQL2
Two application servers named AppServer1 and AppServer2 that run IIS
You plan to move your application to Azure.
You need to ensure that during an Azure update cycle or a hardware failure, the application remains available.
Which two deployment configurations should you implement? Each correct answer presents part of the solution.
正解：C、E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You plan to deploy Microsoft SQL Server on a Microsoft Azure Virtual machine. The virtual machine will have a 30-TB database and will have 10 1-TB VHDs for the database.
You need to configure the storage to meet the following requirements:
Evenly distribute read and write operations across the VHDs.
Minimize the read and write time.
Which storage configuration should you use?
You need to configure the storage to meet the following requirements:
Evenly distribute read and write operations across the VHDs.
Minimize the read and write time.
Which storage configuration should you use?
正解：E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
Background
You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile parts.
The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application has approximately 30,000 concurrent users and relies heavily on the use of temporary tables.
The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You detach the database and copy it to the new server.
You are performing tuning on a SQL Server database instance. The application which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30 stored procedures that are regularly used by the application.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button).
Exhibit:
You need to set the size of the log files for the tempdb database on SRV1.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Hot Area:
You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile parts.
The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application has approximately 30,000 concurrent users and relies heavily on the use of temporary tables.
The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You detach the database and copy it to the new server.
You are performing tuning on a SQL Server database instance. The application which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30 stored procedures that are regularly used by the application.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button).
Exhibit:
You need to set the size of the log files for the tempdb database on SRV1.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Hot Area:
正解：
Explanation:
The ALTER DATABASE with MODIFY FILE command can make a file size bigger (but not smaller).
Example:
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
Note: MODIFY FILE
Specifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tdeprotected-database-to-another-sql-server
A new Azure Active Directory security principal named [email protected] should have access to select all current and future objects in the Reporting database. You should not grant the principal any other permissions. You should use your Active Directory Domain Services (AD DS) account to authenticate to the Azure SQL database.
You need to create the new security principal.
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.
You need to create the new security principal.
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:
To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database (here the Reporting database) with an Azure AD identity (not with a SQL Server account) that has access to the database.
Step 2: CREATE USER ... FROM EXTERNAL PROVIDER
To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER <Azure_AD_principal_name>
FROM EXTERNAL PROVIDER;
Step 3:
Grant the proper reading permissions.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You discover that queries take longer than expected to execute. You also discover that the top wait type for the SQL Server instance is ASYNC_ETWORKJO.
You need to resolve the performance issue.
What should you do?
You discover that queries take longer than expected to execute. You also discover that the top wait type for the SQL Server instance is ASYNC_ETWORKJO.
You need to resolve the performance issue.
What should you do?
正解：C
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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.
You have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL Server in an Always On Availability Group. You expect to have less than 1 million IO transaction per month.
You need to recommend a storage solution for the SQL Servers. The solution must minimize costs.
Which storage option should you use?
You have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL Server in an Always On Availability Group. You expect to have less than 1 million IO transaction per month.
You need to recommend a storage solution for the SQL Servers. The solution must minimize costs.
Which storage option should you use?
正解：C
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
From SQL Server Management Studio (SSMS), a junior database administrator accidentally deletes several rows from the Azure SQL database and breaks the connection to Azure.
You need to resume Stretch Database operations.
Which two stored procedures should you use? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
From SQL Server Management Studio (SSMS), a junior database administrator accidentally deletes several rows from the Azure SQL database and breaks the connection to Azure.
You need to resume Stretch Database operations.
Which two stored procedures should you use? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
正解：B、D
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains unique solution. Determine whether the solution meets the stated goals.
You deploy a new Microsoft Azure SQL Database instance to support a variety of mobile applications 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 use the Always Encrypted Wizard to encrypt all possible columns for the tables in the primary instance.
Does the solution meet the goal?
You deploy a new Microsoft Azure SQL Database instance to support a variety of mobile applications 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 use the Always Encrypted Wizard to encrypt all possible columns for the tables in the primary instance.
Does the solution meet the goal?
正解：B
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You are deploying a DS-13 series virtual machine (VM) to run Microsoft SQL Server in Azure. You plan to migrate a large data warehouse to the SQL Server instance. The data warehouse is currently 15 terabytes (TB) in size.
You expect growth of 5 TB per year. You cannot increase the size of the VM.
You need to design a storage strategy to support the expected growth of the data warehouse over the next five years.
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.
You expect growth of 5 TB per year. You cannot increase the size of the VM.
You need to design a storage strategy to support the expected growth of the data warehouse over the next five years.
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:
The DS-13 has 16TB storage. However, the database is 15TB and is expected to grow by 5TB a year over the next 5 years for a total of 25TB over the 5 years.
References:
https://cloudblogs.microsoft.com/sqlserver/2015/04/23/azure-premium-storage-provides-highest-performance-for-sql-server-in-azure-vm/