Get the latest Microsoft MCSA 70-764 dumps and 70-764 pdf, pass exam certification

Work hard! It’s not hard to get certified! Share the latest Microsoft MCSA 70-764 exam dump for free. online 70-764 exam practice tests. the latest 70-764 exam questions and answers, and guarantee your skills and exam experience!
“Administering a SQL Database Infrastructure ” – 70-764 exam pass4itsure.com expert recommendation! Top pass rate!

microsoft learning

Get the latest Microsoft MCSA 70-764 pdf

[PDF] Free Microsoft 70-764 pdf dumps download from Google Drive: https://drive.google.com/open?id=1nweN7smpSUzgpRXVuZ9joMmky2RFaDWG

[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1gdQrKIsiLyDEsZ24FxsyukNPYmpSUDDO

Valid information provided by Microsoft officials

Exam 70-764: Administering a SQL Database Infrastructure – Microsoft: https://www.microsoft.com/en-us/learning/exam-70-764.aspx
This exam is intended for database professionals who perform installation, maintenance, and configuration tasks. Other responsibilities include setting up database systems, making sure those systems operate efficiently, and regularly storing, backing up, and securing data from unauthorized access.

pass4itsure 70-764 exam Skills measured

This exam measures your ability to accomplish the technical tasks listed below.

  • Configure data access and auditing (20–25%)
  • Manage backup and restore of databases (20–25%)
  • Manage and monitor SQL Server instances (35–40%)
  • Manage high availability and disaster recovery (20–25%)

The latest Microsoft MCSA 70-764 exam practice questions test your strength

QUESTION 1
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 a Microsoft SQL Server environment that has multiple databases. A database named DB1 has multiple
online file groups. It is configured to use the full recovery model. A full backup is preformed nightly and transaction logs
are performed on the hour. A large number of records are accidentally deleted at 17:20.
You need to perform a point-in-time recovery. Which option should you use first?
A. backup compression
B. backup encryption
C. file snapshot backup
D. mirrored backup media sets
E. SQL Server backup to URL
F. SQL Server Managed Backup to Azure
G. tail-log backup
H. back up and truncate the transaction log
Correct Answer: G
To back up the tail of the log (that is, the active log), check Back up the tail of the log, and leave database in the
restoring state.
A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. Back up the active log
(a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary
database. Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of
Transact-SQL. References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-2017

QUESTION 2
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 the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and
Microsoft Azure SQL Database environments.
Clients connect to databases by using line-of-business applications. Developers connect by using SQL Server
Management Studio (SSMS).
You need to provide permissions to a service account that will be used to provision a new database for a client.
Which permission should you grant?
A. DDLAdmin
B. db_datawriter
C. dbcreator
D. dbo
E. View Database State
F. View Server State
G. View Definition
H. sysadmin
Correct Answer: C
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
References: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles

QUESTION 3
You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2016.
You attempt to add the [Corpnet\User1] login to the database.
However, you receive the following error message:
“User already exists in current database.”
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original
permissions.
You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?
A. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner]
ADD MEM3ER [Corpnet\User1];
B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];
C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
D. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1];
Correct Answer: C

QUESTION 4
You have a database hosted on SQL Server 2012 R2.
The database contains 5 million rows.
You need to recommend a repeatable method to migrate the database to SQL Azure.
Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a SQL Server Integration Services (SSIS) package, and then run the package.
B. Back up the database, and then restore the database.
C. Extract a data-tier application, and then import the application.
D. Generate scripts to create all of the all database objects and all of the data, and then execute the scripts by using
SQL Azure.
Correct Answer: A
SQL Server Integration Services Most flexibility Data Transfer Efficiency: Good
– SSIS can be used to perform a broad range of data migration tasks. SSIS provides support for complex workflow and
data transformation between the source and destination. It is a good choice to transfer of data for databases that
require
many changes to work on Microsoft Azure SQL Database.
You can use SSIS data transfer packages with another mechanism for transferring the database schema, such as a
Data-tier Application package.

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 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 database that includes a table named Candidate.
You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the
column.
Solution: You run the following query:
Does this meet the goal?
A. Yes
B. No
Correct Answer: A
FULLSCAN: Compute statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100
PERCENT have the same results. References: https://technet.microsoft.com/en-us/library/ms187348(v=sql.110).aspx

QUESTION 6
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 need to configure a Microsoft SQL Server instance to ensure that a user named Mail1 can send mail by using
Database Mail.
Solution: You add the DatabaseMailUserRole to Mail1 in the msdb database.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database in order to prevent anyone
from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a
member of DatabaseMailUserRole in order to send emails with the exception of sysadmin who has all privileges.
Note: Database Mail was first introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found
in previous versions.
References: http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml

QUESTION 7
You are implementing a SQL Server 2016 five-node failover cluster.
You need to choose a quorum configuration.
Which configuration should you use?
A. Distributed File System (DFS)
B. Node Majority
C. Cluster Shared Volume (CSV)
D. Node and Disk Majority
Correct Answer: D
Node and Disk Majority (recommended for clusters with an even number of nodes)

QUESTION 8
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 are a database administrator for a company that has an on-premises Microsoft SQL Server environment and
Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer
uses a dedicated instance. The environments that you manage are shown in the following table.pass4itsure 70-764 exam question q8

You need to monitor WingDB and gather information for troubleshooting issues. What should you use?
A. sp_updatestats
B. sp_lock
C. sys.dm_os_waiting_tasks
D. sys.dm_tran_active_snapshot_database_transactions
E. Activity Monitor
Correct Answer: B
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are
happening on your system. This procedure returns much of its information from the syslock info in the master database,
which
is a system table that contains information on all granted, converting, and waiting lock requests.
Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development
work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL
Server
Database Engine, use the sys.dm_tran_locks dynamic management view.
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later.
Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be
granted.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql

QUESTION 9
You administer a Microsoft SQL Server 2016 database.
Users report that an application that accesses the database displays an error, but the error does not provide meaningful
information.
No entries are found in the SQL Server log or Windows event logs related to the error. You need to identify the root
cause of the issue by retrieving the error message.
What should you do?
A. Create an Extended Events session by using the sqlserver.error_reported event.
B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
C. Flag all stored procedures for recompilation by using sp_recompile.
D. Execute sp_who.
Correct Answer: A
Trapping SQL Server Errors with Extended Events
One very useful usage of Extended Events is the ability to trap SQL Server error without the need to have a server trace
running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in.
This means that we a solution to monitor and trap errors as soon as they happen can be easily created, in order to help
developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big
applications, where the code base is quite old and there is no- one really knowing everything of the solution.
To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the
following script:
CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
Etc.
References: http://sqlblog.com/blogs/davide_mauri/archive/2013/03/17/trapping-sql-server-errors-with-extended-events.aspx

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 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
Correct Answer: A
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 11
You administer a single server that contains a Microsoft SQL Server 2016 default instance.
You plan to install a new application that requires the deployment of a database on the server. The application login
requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases.
What should you do?
A. Use the SQL Server default instance and configure an affinity mask.
B. Install a new named SQL Server instance on the server.
C. Use the SQL Server default instance and enable Contained Databases.
D. Install a new default SQL Server instance on the server.
Correct Answer: B
SQL Server supports multiple instances of SQL Server on a single server or processor, but only one instance can be the
default instance. All others must be named instances. A computer can run multiple instances of SQL Server
concurrently, and each instance runs independently of other instances. References: https://msdn.microsoft.com/en-us/library/ms143531(v=SQL.105).aspx

QUESTION 12
Overview
General Overview
ADatum Corporation has offices in Miami and Montreal.
The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a
WAN link that has 5-ms latency. A. Datum standardizes its database platform by using SQL Server 2014 Enterprise
edition.
Databases
Each office contains databases named Sales, Inventory, Customers, Products, Personnel, and Dev.
Servers and databases are managed by a team of database administrators. Currently, all of the database administrators
have the same level of permissions on all of the servers and all of the databases.
The Customers database contains two tables named Customers and Classifications.
The following graphic shows the relevant portions of the tables:pass4itsure 70-764 exam question q12

The following table shows the current data in the Classifications table: The Inventory database is updated frequently.

pass4itsure 70-764 exam question q12-1

The database is often used for reporting.
A full backup of the database currently takes three hours to complete.
Stored Procedures
A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from
five different tables from the Sales and Customers databases in a table named Table1.
After Table1 is created, the reporting process reads data from Table1 sequentially several times. After the process is
complete, Table1 is deleted.
A stored procedure named USP_2 is used to generate a product list. The product list contains the names of products
grouped by category.
USP_2 takes several minutes to run due to locks on the tables the procedure accesses.
The locks are caused by USP_1 and USP_3.
A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called
in sequence from within a transaction.
Currently, if one of the UPDATE statements fails, the stored procedure fails. A stored procedure named USP_4 calls
stored procedures in the Sales, Customers, and Inventory databases.
The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an
EXECUTE AS clause.
All nested stored procedures handle errors by using structured exception handling. A stored procedure named USP_5
calls several stored procedures in the same database. Security checks are performed each time USP_5 calls a stored
procedure.
You suspect that the security checks are slowing down the performance of USP_5. All stored procedures accessed by
user applications call nested stored procedures.
The nested stored procedures are never called directly.
Design Requirements
Data Recovery
You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Time
Objective (RTO) of 5 minutes.
You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point
Objective (RPO) of one day.
Classification Changes
You plan to change the way customers are classified. The new classifications will have four levels based on the number
of orders. Classifications may be removed or added in the future. Management requests that historical data be
maintained for the previous classifications. Security A group of junior database administrators must be able to manage
security for the Sales database. The junior database administrators will not have any other administrative rights. A.
Datum
wants to track which users run each stored procedure.
Storage
ADatum has limited storage. Whenever possible, all storage space should be minimized for all databases and all
backups.
Error Handling
There is currently no error handling code in any stored procedure.
You plan to log errors in called stored procedures and nested stored procedures. Nested stored procedures are never
called directly.
You need to recommend a solution to minimize the amount of time it takes to execute USP_2. What should you
recommend?
A. A database snapshot
B. A table variable
C. A temporary table
D. Snapshot isolation
Correct Answer: C
Scenario:
A stored procedure named USP_2 is used to generate a product list. USP_2 takes several minutes to run due to locks
on the tables the procedure accesses.

QUESTION 13
You administer a Microsoft SQL Server 2016 database.
You provide temporary securityadmin access to User1 to the database server.
You need to know if User1 adds logins to securityadmin.
Which server-level audit action group should you use?
A. SERVER_STATE_CHANGE_GROUP
B. SERVER_PRINCIPAL_IMPERSONATION_GROUP
C. SUCCESSFUL_LOGIN_GROUP
D. SERVER_ROLE_MEMBER_CHANGE_GROUP
Correct Answer: D
SERVER_ROLE_MEMBER_CHANGE_GROUP This event is raised whenever a login is added or removed from a fixed
server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures.
Equivalent to the Audit Add Login to Server Role Event Class.
References: http://technet.microsoft.com/en-us/library/cc280663.aspx

Follow Pass4itsure free sharing of YouTube channels

We offer more ways to make it easier for everyone to learn, and YouTube is the best tool in the video. Follow channels: https://www.youtube.com/channel/UCTP5RClZrtMxtRkSvIag0DQ/videos get more useful exam content.

Watch the Microsoft MCSA 70-764 video tutorial online

Share Pass4itsure coupons for free

pass4itsure 70-764 coupon

Reasons to choose Pass4itsure

Pass4itsure offers the latest exam practice questions and answers free of charge! Update all exam questions throughout the year, with a number of professional exam experts! To make sure it works! Maximum pass rate, best value for money! Helps you pass the exam easily on your first attempt.

about pass4itsure 70-764 exam dumps

This maybe you’re interested

Summarize:

Collecting the latest and most effective Microsoft MCSA 70-764 exam practice questions to help you improve exam success, 70-764 pdf and 70-764 video learning make it easier to gain knowledge! Full 70-764 exam dump: Experts recommend real leader Pass4itsure. Click here to easily pass the 70-764