The Microsoft 70-764 exam is difficult to pass. Be that as it may, you can’t simply allow yourself to come up short. Real 70-764 dumps, get success easily. https://www.pass4itsure.com/70-764.html provides you with real Microsoft 70-764 dumps and succeeded in the 70-764 exam.
Updated 70-764 Exam Dumps Study Material 2020
Get Free PDF Dumps for 70-764 Exam: https://drive.google.com/file/d/1aHQ8uHLZ3AHx4_uwNXgvf6N59HRyR8Su/view?usp=sharing
[2020] Latest Microsoft 70-764 Dumps Practice Test
Certifications: Microsoft Certified Solutions Associate
Exam Code: 70-764
Exam Name: Administering a SQL Database
QUESTION 1
You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media.
You plan to synchronize SQLDev and SQLProd.
You need to recommend a solution that meets the following requirements: The database schemas must be
synchronized from SQLDev to SQLProd. The database on SQLDev must be deployed to SQLProd by using a package.
The package must support being deployed to SQL Azure.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. A database snapshot
B. A data-tier application
C. Change data capture
D. SQL Server Integration Services (SSIS)
Correct Answer: B
* SIS supports connections to SQL Database by using the ADO.NET provider. OLEDB is not supported at this time. You
can build the SSIS package connecting to SQL Database and create the data flow tasks the same way as you would
against a typical on premise SQL Server. http://technet.microsoft.com/en-us/library/ee210546.aspx
QUESTION 2
You manage Microsoft SQL Server databases for an organization.
You need to configure the databases to meet the following requirements:
Encrypt the data at rest.
Ensure that unencrypted values for specific columns can only be viewed by using a decryption key.
Ensure that decrypted columns are only accessible by using database views.
Which actions should you perform? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Correct Answer:
To protect all databases use a certificate in the master database Note: TDE encrypts the storage of an entire database
by using a symmetric key called the database encryption key. The database encryption key can also be protected using
a certificate, which is protected by the database master key of the master database. The encryption uses a database
encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a
symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key
protected by an EKM module. Box 2: Configure DDM Dynamic data masking helps prevent unauthorized access to
sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the
application layer. DDM can be configured on the database to hide sensitive data in the result sets of queries over
designated database fields, while the data in the database is not changed.
References: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/enable-tde-on-sql-serverusing-ekm?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-datamasking
QUESTION 3
You run the sp_who system stored procedure. A process is blocked by a long-running operation.
You need to identify details of the processes involved in the deadlock including the resources that are being accessed.
You must able to view the deadlock chain.
What should you use?
A. DBCC OPENTRAN()
B. Extended events
C. Query performance insights
D. Data collector
Correct Answer: B
SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as
much or as little information as is necessary to troubleshoot or identify a performance problem. Incorrect Answers:
A: DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN
displays information about the oldest active transaction and the oldest distributed and nondistributed replicated
transactions, if any, within the transaction log of the specified database.
References: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sqlserver-2017
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-opentran-transact-sql?view=sqlserver-2017
QUESTION 4
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
Correct Answer: E
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-clusterinstances-sql-server
QUESTION 5
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 have a database named DB1 that is 640 GB and is updated frequently.
You enable log shipping for DB1 and configure backup and restore to occur every 30 minutes.
You discover that the hard disks on the database server are almost full.
You need to reduce the amount of disk space used by the log shipping process.
Solution: You increase the frequency of the transaction log backups to every 10 minutes.
Does this meet the goal?
A. Yes
B. No
Correct Answer: B
You should compress the transaction log backups.
References: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sqlserver?view=sql-server-2017
QUESTION 6
HOTSPOT
A server named AzureSqlSvr1 hosts several Microsoft Azure SQL Database instances.
You need to configure audit logs for all databases to ensure that events are captured when a database schema is
created, altered, or dropped.
How should you complete the Azure PowerShell command? To answer, select the appropriate options in the answer
area.
NOTE: Each correct selection is worth one point.
Hot Area:
Box 1: Set-AzureRmSqlServerAuditing
The Set-AzureRmSqlServerAuditing cmdlet changes the auditing settings of an Azure SQL server. After the cmdlet runs
successfully, auditing of the Azure SQL databases that are defined in the specified Azure SQL server is enabled.
Box 2: AuditActionGroup
Parameters include AuditActionGroup, but not AuditAction.
Box 3: SCHEMA_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP tracks whenever create, alter or drop is executed on a schema object such as
a table, view or stored procedure.
If you enable this group in a server audit specification, it will track changes to schema objects in all of the databases of
this instance of SQL Server. If you enable the group in a database audit specification, it will track changes to schema
objects within that database only.
Incorrect Answers:
The Set-AzureRmSqlDatabaseAuditing cmdlet changes the auditing settings of an Azure SQL database.
References:
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverauditing
https://www.ultimatewindowssecurity.com/sqlserver/auditpolicy/auditactiongroups/schema_object_change_group.aspx
QUESTION 7
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 are deploying a Microsoft SQL Server architecture to support a new mission-critical application. The application
includes a dedicated reporting component that performs read-only operations against the application\’s databases.
You need to implement a solution that meets the following requirements:
Provide maximum uptime for the databases.
Include automatic failover in the event of a hardware problem on the primary server.
Separate the reporting workload from the read/write transactional processing workload and contain real-time data.
Modifications to the application to support the new architecture are not permitted. 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 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
Correct Answer: C
Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
References: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modesalways-on-availability-groups?view=sql-server-2017
QUESTION 8
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 profile, grant access to the public user.
B. On the msdb database, add the DatabaseMailUserRole to the public user.
C. On the master database, add the DatabaseMailUserRole to userid 0.
D. On the master database add the DatabaseMailUserRole to the public user.
Correct Answer: C
To make a profile public, specify a **@principal_id** of 0 or a **@principal_name** of public. A public profile is available
to all users in the msdb database, though users must also be a member of DatabaseMailUserRole to execute
sp_send_dbmail.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-addprincipalprofile-sp-transact-sql
QUESTION 9
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 are examining information about users, sessions, and processes in an on-premises Microsoft SQL Server 2016
Standard Edition server.
You need to identify waits for resources and return only the following information: a list of all databases on the SQL
Server instance, along with information about the database files, their paths, and names a list of the queries recently
executed that use most of memory, disk, and network resources What should you use?
A. Activity Monitor
B. sp_who3 stored procedure
C. SQL Server Management Studio (SSMS) Object Explorer
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Correct Answer: E
SQL Server Data Tools (SSDT) is a Microsoft Visual Studio environment for creating business intelligence solutions.
SSDT features the Report Designer authoring environment, where you can open, modify, preview, save, and deploy
Reporting Services paginated report definitions, shared data sources, shared datasets, and report parts.
References: https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx
QUESTION 10
You work as a Database Administrator (DBA) for a company named ABC.com.
The company uses a Microsoft SQL Server 2012 infrastructure. Computer users use custom applications that store data
in SQL Server databases hosted on a server named ABC-SQL1.
You discover that the Windows Application event log on ABC-SQL1 contains error events.
You need to configure a solution that will run an application whenever an event with a specific event ID number is
generated in the Windows Application event log.
What should you configure?
A. You should configure an alert and a job.
B. You should configure an alert and a maintenance plan.
C. You should configure a trigger and a maintenance plan.
D. You should configure a trigger and a job.
E. You should configure an alert and a trigger.
Correct Answer: A
QUESTION 11
You plan to install two SQL Server 2014 environments named Environment1 and Environment2. Your company
identifies the following availability requirements for each environment:
Environment1 must have mirroring with automatic failover implemented.
Environment2 must have Always On with automatic failover implemented.
You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to
ensure that all data remains available if a physical server fails.
How many servers should you identify? To answer, drag the appropriate number to the correct environment in the
answer area.
Select and Place:
QUESTION 12
You administer a Microsoft SQL Server 2016 database.
The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database
online.
You want to perform a tail-log backup. You need to leave the primary database in a restoring state.
Which option of the BACKUP LOG command should you use?
A. NO_TRUNCATE
B. NORECOVERY
C. STANDBY
D. FORMAT
Correct Answer: B
QUESTION 13
Overview
Application Overview
Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.
Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL
Server 2008 R2.
The new version will use SQL Server 2014.
The ERP application relies on an import process to load supplier data. The import process updates thousands of rows
simultaneously, requires exclusive access to the database, and runs daily. You receive several support calls reporting
unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to
the tables in the database.
Tables
The current database schema contains a table named OrderDetails.
The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the
product ID, quantities, and discounts applied to each product in a purchase order. The product price is stored in a table
named
Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.
A column named ProductName was created by using the varchar data type. The database contains a table named
Orders.
Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are
stored in a table named OrdersOld.
The previous version of the ERP application relied on table-level security.
Stored Procedures
The current version of the database contains stored procedures that change two tables. The following shows the
relevant portions of the two stored procedures:
Customer Problems
Installation Issues
The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most
customers set up the ERP application in multiple locations and must create logins multiple times.
Index Fragmentation Issues
Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the
indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the
clustered index key:
Customer Problems
Installation Issues
The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most
customers set up the ERP application in multiple locations and must create logins multiple times.
Index Fragmentation Issues
Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the
indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the
clustered index key:
was sold for in previous orders.
Query Performance Issues
Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot
run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database
administrators receive many supports call from users who report that they cannot access the supplier data. The
database administrators want to reduce the amount of time required to import the data.
Design Requirements
File Storage Requirements
The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through
the ERP application. File access must have the best possible read and write performance.
Data Recovery Requirements
If the import process fails, the database must be returned to its prior state immediately.
Security Requirements
You must provide users with the ability to execute functions within the ERP application, without having direct access to
the underlying tables.
Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.
You need to recommend a solution that reduces the time it takes to import the supplier data. What should you include in
the recommendation?
A. Enable instant file initialization.
B. Reorganize the indexes.
C. Disable Resource Governor.
D. Enable Auto Update Statistics.
Correct Answer: C The ERP application relies on an import process to load supplier data. The import process updates thousands of rows
simultaneously, requires exclusive access to the database, and runs daily.
Pass4itsure Practice Dumps Features
Latest Discount Code “2020PASS” – Pass4itsure
Pass4itsure discount code 2020 “2020PASS“, Please read the picture carefully to get 12% off!
Summarize
I believe that the Microsoft 70-764 dumps question will definitely help you complete the exam.
For information about Microsoft 70-764 exam dumps, please visit: https://www.pass4itsure.com/70-764.html
2020 Latest Microsoft 70-764 exam dumps (PDF ) free share: https://drive.google.com/file/d/1aHQ8uHLZ3AHx4_uwNXgvf6N59HRyR8Su/view?usp=sharing
