2018 March New Microsoft 70-764 Exam Dumps with PDF and VCE Free Updated Today! Following are some new 70-764 Real Exam Questions:
1.|2018 Latest 70-764 Exam Dumps (PDF & VCE) 332Q&As Download:
https://www.braindump2go.com/70-764.html
2.|2018 Latest 70-764 Exam Questions & Answers Download:
https://drive.google.com/drive/folders/0B75b5xYLjSSNdlF6dzFQVE9kUjA?usp=sharing
QUESTION 172
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 Microsoft Azure SQL database that has Blob Auditing configured.
You need to review the audit logs.
Solution: You download the log files by using Microsoft Azure Storage Explorer, and then you open the files by using Microsoft SQL Server Management Studio (SSMS).
Does this meet the goal?
A. Yes
B. No
Answer: A
Explanation:
There are several methods you can use to view blob auditing logs:
After downloading several files or a subfolder that contains log files, you can merge them locally by using Merge Audit Files in SQL Server Management Studio.
Use the Azure portal.
Use the system function sys.fn_get_audit_file (T-SQL) to return the audit log data in tabular format.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
QUESTION 173
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 Microsoft Azure SQL database that has Blob Auditing configured.
You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.
Does this meet the goal?
A. Yes
B. No
Answer: B
Explanation:
The fn_get_audit_file, not dm_db_audit_file, the returns information from an audit file created by a server audit in SQL Server.
This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel:
SELECT * FROM sys.fn_get_audit_file (‘https://mystorage.blob.core.windows.net/sqldbauditlogs/ ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel’,default,default); Note: Blob auditing logs are saved as a collection of blob files within a container named sqldbauditlogs.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql
QUESTION 174
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 Microsoft Azure SQL database that has Blob Auditing configured.
You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.
Does this meet the goal?
A. Yes
B. No
Answer: B
Explanation:
The fn_get_audit_file, not dm_db_audit_file, the returns information from an audit file created by a server audit in SQL Server.
This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel:
SELECT * FROM sys.fn_get_audit_file (‘https://mystorage.blob.core.windows.net/sqldbauditlogs/ ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel’,default,default); Note: Blob auditing logs are saved as a collection of blob files within a container named sqldbauditlogs.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql
QUESTION 175
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 maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.
The backup strategies for each database are described in the following table.
Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.
SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:
– database name
– impacted file id
– impacted file physical name
– impacted page id
– event type that identifies the error type
– error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:
– Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
– Capture queries based on resource consumption.
– Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
You need to view the information about the corrupted pages on SalesDb3.
How should you complete the Transact-SQL statement?
Select two.
A. SELECT * FROM msdb.. corrupted_pages
B. SELECT * FROM msdb..suspect_pages
C. SELECT * FROM system..corrupted_pages
D. SELECT * FROM system..suspect_pages
E. WHERE event_type = 1
F. WHERE event_type = 2
G. WHERE event_type = 3
Answer: BF
Explanation:
suspect_pages contains one row per page that failed with a minor 823 error or an 824 error. Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is updated in the event_type column.
The suspect_pages table resides in the msdb database.
SalesDb3 has pages with checksum errors. Checksum errors have the event_type value 2.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server
QUESTION 176
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 offices in Vancouver, Tokyo, and Paris. The company uses Microsoft SQL Server 2016 Standard edition. You must design a reporting solution that uses data from a point of sale (POS) application’s transactional database.
After data is entered into the POS system, users must be able to run reports within 24 hours. Latency must be minimized.
You need to implemented a solution that minimizes licensing costs.
What should you implement?
A. a Microsoft Azure Stretch Database
B. log shipping
C. an Always On Availability Group with all replicas in synchronous-commit mode
D. a file share witness
E. a Microsoft SQL Server failover cluster instance (FCI)
F. a Windows cluster with a shared-nothing architecture
G. an Always On Availability Group with secondary replicas in asynchronous-commit mode
Answer: A
Explanation:
Stretch Database targets transactional databases with large amounts of cold data, typically stored in a small number of tables. These tables may contain more than a billion rows.
Stretch Database provides the following benefits:
Provides cost-effective availability for cold data
Doesn’t require changes to queries or applications
Streamlines on-premises data maintenance
Keeps your data secure even during migration
References: https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/stretch-database
QUESTION 177
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 need to deploy a new Microsoft SQL Server environment that meets the following requirements:
– The SQL Server instance must be highly available.
– There must be minimal downtime incurred during hardware failure or operating system maintenance.
– All instance-level security settings and SQL Server Agent jobs must be available without additional synchronization tasks.
What should you implement?
A. a Microsoft Azure Stretch Database
B. log shipping
C. an Always On Availability Group with all replicas in synchronous-commit mode
D. a file share witness
E. a Microsoft SQL Server failover cluster instance (FCI)
F. a Windows cluster with a shared-nothing architecture
G. an Always On Availability Group with secondary replicas in asynchronous-commit mode
Answer: E
Explanation:
As part of the SQL Server Always On offering, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level–a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets.
When there is hardware or software failure of a server, the applications or clients connecting to the server will experience downtime. When a SQL Server instance is configured to be an FCI (instead of a standalone instance), the high availability of that SQL Server instance is protected by the presence of redundant nodes in the FCI.
References: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server
QUESTION 178
You have a database named DB1.
Users report that a database application that updates the data in DB1 is unresponsive.
You need to identify which process prevents the application from responding.
What should you do?
A. Run DBCC INPUTBUFFER.
B. Query sys.dm_exec_session_wait_stats.
C. Run sp_autostats.
D. Run sp_who.
Answer: B
Explanation:
Sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session.
You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-session-wait-stats-transact-sql
QUESTION 179
You have 10 Microsoft SQL Server 2016 servers.
You deploy a management data warehouse named DW1. You configure DW1 to gather all the performance data from the servers.
You configure a Data Collector on a SQL server named SV1.
You query the data warehouse on DW1 and discover that data from SV1 is unavailable.
You need to ensure that you can review the performance data from SV1 when you query DW1.
What should you do?
A. Start the SQL Server Agent service on DW1.
B. Execute the msdb.sp_syscollector_set_warehouse_connection_user stored procedure on SV1.
C. Execute the msdb.sp_syscollector_enable_collector stored procedure on DW1.
D. Start the SQL Server Agent service on SV1.
Answer: C
Explanation:
sp_syscollector_enable_collector enables the data collector.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-syscollector-enable-collector-transact-sql
QUESTION 180
You manage a Microsoft SQL Server environment. You plan to configure Database Mail.
You need to ensure that all users can access a private Database Mail profile.
What should you do?
A. On the tempdb database, add the DatabaseMailUserRole to userid 0.
B. On the msdb database, add the DatabaseMailUserRole to the public user.
C. On the master database, add the DatabaseMailUserRole to the public user.
D. On the profile, grant access to the public user.
Answer: B
Explanation:
For each private profile, Database Mail maintains a list of users that are permitted to send e-mail using that profile. Public profiles are available to users or roles in the msdb database who are also members of the DatabaseMailUserRole.
By default, a profile is private, and no users are granted access to the profile. To make the profile public, grant access to the user ‘public’ or the user id 0.
Note: Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.
References: https://technet.microsoft.com/en-us/library/ms189879(v=sql.105).aspx
QUESTION 181
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?
A. sp_autostats
B. AUTO_UPDATE_STATISTICS_ASYNC
C. SET AUTO_UPDATE_STATISTICS ON
D. CREATE STATISTICS
Answer: C
Explanation:
You can turn on automatic statistics update by running this SQL statement:
SET AUTO_UPDATE_STATISTICS ON
Incorrect Answers:
A: sp_autostats without options just displays the settings.
B: The AUTO_UPDATE_STATISTICS_ASYNC option affects how automatic statistics updates are applied to your SQL Server database. When this option is enabled, the Query Optimizer will not wait for the update of statistics, but will run the query first and update the outdated statistics afterwards. When this option is disabled, the Query Optimizer will update the outdated statistics before compiling the query therefore possibly getting a better plan based on the most current statistics. This is referred to as synchronous statistics updates.
References: https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/
QUESTION 182
You are a database administrator at Contoso, Ltd. You are preparing to move a global sales application from a development environment to a production environment.
You have a database named Contoso that has a schema named Sales. All objects in the database have the same owner. The schema has a large number of views and stored procedures. None of the stored procedures perform IDENTITY_INSERT operations or dynamic SQL commands. You create all views by using the WITH SCHEMABINDING option.
All employees in the sales division are members of an Active Directory Domain Services (AD DS) security group named Contoso\Sales.
The following database objects are the only objects queried when a user from Contoso\Sales is using the application.
Views
SalesReport
SalesInvoice
Stored procedures
InvoiceExecute ?performs read/write operations
InvoiceSearch ?performs read-only operations
When granting permissions to the database, you should use the principle of least privilege.
You create a new user-defined database role named SalesRole and add Contoso\Sales as a member of SalesRole.
You need to grant all employees in the Sales division permission to use the views and stored procedures.
Which two solutions will meet the requirements? Each correct answer presents a complete solution.
A. Grant the SELECT permission on the Sales schema to SalesRole.
Grant the EXECUTE permission on the Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
B. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to Contoro\Sales.
Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to Contoso\Sales.
C. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole.
Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
D. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole.
Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
Grant the SELECT permission on all tables referenced by Sales.SalesReport, Sales.SalesInvoice, and Sales.InvoiceSearch to SalesRole.
Grant the SELECT, INSERT, UPDATE, and DELETE permissions on all tables referenced by Sales.InvoiceExecute to SalesRole.
Answer: BC
Explanation:
Incorrect Answers:
A: There is no Sales Schema.
D: No need for DELETE permissions.
!!!RECOMMEND!!!
1.|2018 Latest 70-764 Exam Dumps (PDF & VCE) 332Q&As Download:
https://www.braindump2go.com/70-764.html
2.|2018 Latest 70-764 Study Guide Video:
https://youtu.be/50l_7j_PnRA