MCSA

Latest Microsoft MCSA 70-764 exam dumps, 70-764 Exam Practice Tests | 100% Free

Easily pass the Microsoft MCSA 70-764 exam: “Administering a SQL Database Infrastructure” – https://www.pass4itsure.com/70-764.html (Q&As: 442) Free share the latest 70-764 dumps questions and answers! Practice the test online! Improve your skills and exam experience!

Download Microsoft MCSA 70-764 PDF Online

[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 Skills measured

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

Configure data access and auditing (20–25%)

  • Configure encryption
  • Configure data access and permissions
  • Configure auditing

Manage backup and restore of databases (20–25%)

  • Develop a backup strategy
  • Restore databases
  • Manage database integrity

Manage and monitor SQL Server instances (35–40%)

  • Monitor database activity
  • Monitor queries
  • Manage indexes
  • Manage statistics
  • Monitor SQL Server instances

Manage high availability and disaster recovery (20–25%)

  • Implement log shipping
  • Implement AlwaysOn Availability Groups
  • Implement failover cluster instances

Latest effective Microsoft MCSA 70-764 Exam Practice Tests

QUESTION 1
You work as a Database Administrator (DBA) for a company named ABC.com.
The company uses a Microsoft SQL Server 2012 infrastructure.
You have a database named CorpDB.
A full backup of CorpDB is taken every week.
A differential backup of CorpDB is taken every night at midnight.
A transaction log backup of CorpDB is taken at 8 am 12 pm, 4 pm, and 8 pm.
You plan to deploy some changes to CorpDB at after the 4 pm log backup completes.
You need to ensure that you can undo the changes quickly if the deployment fails.
During the deployment of the changes, no other changes must be made by users to the database. You need a backup
and recovery strategy for deploying the changes.
Which two of the following actions would meet the backup and recovery requirements whilst ensuring the regular
backup schedule is not disrupted? (Choose two.)
A. Take a full backup of the database before deploying the changes.
B. Take a copy-only backup of the database before deploying the changes.
C. Take a snapshot of the database before deploying the changes.
D. Restore the database from the backup.
E. Restore the snapshot to another server and recover the required objects from the snapshot.
F. Revert the database to the snapshot.
Correct Answer: CF

 

QUESTION 2
You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales
system.
The company takes full backups every week; differential backups on the days with no full backups; and hourly
transaction backups.
These backups are stored on a backup server in the company\\’s data center. Every week, the company places the full
backup on a tape and sends it to a third-party backup storage system. The company is worried that a disaster might
occur
that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or
purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Set up SQL Server Always On with a SQL Azure database as a replica.
B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
C. Put the differential backup on tape and send it to the third-party backup storage system.
D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different
geographical location.
Correct Answer: D
SQL Server 2012 was the first version to provide the ability to back up databases to the Cloud, and SQL Server 2014
improves on the process. Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob
Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.

 

QUESTION 3
You are planning to deploy log shipping for Microsoft SQL Server and store all backups on a dedicated file share.
You need to configure the servers to perform each log shipping step.
Which server instance should you configure to perform each action? To answer, select the appropriate server instances
in the dialog box in the answer area.
Hot Area:pass4itsure 70-764 exam question q3

Correct Answer:

pass4itsure 70-764 exam question q3-1

Note: Before you configure log shipping, you must create a share to make the transaction log backups available to the
secondary server.
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a
primary server instance to one or more secondary databases on separate secondary server instances. The transaction
log
backups are applied to each of the secondary databases individually. An optional third server instance, known as the
monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.
Box 1: Primary server instance.
The primary server instance runs the backup job to back up the transaction log on the primary database.
backup job: A SQL Server Agent job that performs the backup operation, logs history to the local server and the monitor
server, and deletes old backup files and history information.
When log shipping is enabled, the job category “Log Shipping Backup” is created on the primary server instance.
Box 2: Secondary server instance
Each of the three secondary server instances runs its own copy job to copy the primary log backup file to its own local
destination folder.
copy job: A SQL Server Agent job that copies the backup files from the primary server to a configurable destination on
the secondary server and logs history on the secondary server and the monitor server. When log shipping is enabled on
a
database, the job category “Log Shipping Copy” is created on each secondary server in a log shipping configuration.
Box 3: Secondary server instance.
Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto
the local secondary database.
restore job: A SQL Server Agent job that restores the copied backup files to the secondary databases. It logs history on
the local server and the monitor server and deletes old files and old history information. When log shipping is enabled
on
a database, the job category “Log Shipping Restore” is created on the secondary server instance.
References: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-logshipping-sql-server

 

QUESTION 4
You plan to create a database.
The database will be used by a Microsoft .NET application for a special event that will last for two days.
During the event, data must be highly available.
After the event, the database will be deleted.
You need to recommend a solution to implement the database while minimizing costs.
The solution must not affect any existing applications.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. SQL Server 2014 Enterprise
B. SQL Server 2014 Standard
C. SQL Azure
D. SQL Server 2014 Express with Advanced Services
Correct Answer: B
Explanation: Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL) supported by Standard and Enterprise
editions only. References: Features Supported by the Editions of SQL Server 2014.

 

QUESTION 5
Overview
You are a database administrator for a company named Litware, Inc.
Litware is a book publishing house. Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed.
The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the
application.
You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.
App1_DB will remain in production.
Requirements
Planned Changes
You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.
All database files will be stored in a highly available SAN.
Database1 will contain two tables named Orders and OrderDetails.
Database1 will also contain a stored procedure named usp_UpdateOrderDetails.
The stored procedure is used to update the order information. The stored procedure queries the Orders table twice each
time the procedure executes. The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.
Database1 will contain several queries that access data in the Database2 tables.
Database2 will contain a table named Inventory.
Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.
The column that is used as the primary key will use the identity property.
Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table
that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad
does
not change until the next data creation process. Data from Database2 will be accessed periodically by an external
application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as
changes
occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.
Business Requirements
You have the following requirements:
Costs for new licenses must be minimized.
Private information that is accessed by Application must be stored in a secure format.
Development effort must be minimized whenever possible.
The storage requirements for databases must be minimized.
System administrators must be able to run real-time reports on disk usage.
The databases must be available if the SQL Server service fails.
Database administrators must receive a detailed report that contains allocation errors and data corruption.
Application developers must be denied direct access to the database tables. Applications must be denied direct access
to the tables.
You must encrypt the backup files to meet regulatory compliance requirements.
The encryption strategy must minimize changes to the databases and to the applications.
During performance testing, you discover that the database INSERT operations against the Inventory table are slow.
You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations. What
should you recommend?
A. Partition the nonclustered index.
B. Partition the Inventory table. snapshot replication
C. Create a column store index. Master Data Services
D. Drop the clustered index. change data capture
Correct Answer: A
Scenario:
Database2 will contain a table named Inventory. Inventory will contain over 100 GB of data. The Inventory table will
have two indexes: a clustered index on the primary key and a nonclustered index.
The column that is used as the primary key will use the identity property.

 

QUESTION 6
You administer a Microsoft SQL Server 2016 database.
You want to make a full backup of the database to a file on disk.
In doing so, you need to output the progress of the backup.
Which backup option should you use?
A. STATS
B. COMPRESSION
C. CHECKSUM
D. IN IT
Correct Answer: A

 

QUESTION 7
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 answers choices, but the text of the scenario is
exactly the same in each question in this series.
Start of repeated scenario.
You are a database administrator for a company that has on-premises Microsoft SQL Server environment. There are
two domains in separate forests. There are no trust relationships between the domains. The environment hosts several
customer databases, and each customer uses a dedicated instance running SQL Server 2016 Standard edition. The
customer environments are shown in the following table.pass4itsure 70-764 exam question q7

End of repeated scenario.
You need to monitor WingDB and gather information for troubleshooting issues.
Which two tools should you use? Each correct answer presents a complete solution.
A. sys.dm_tran_locks
B. sp_lock
C. sys.dm_tran_active_snapshot_database_transactions
D. Activity Monitor
E. sp_monitor
Correct Answer: BD
The performance issues is related to locking.
B: sp_lock reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource
to which the lock applies.
D: The Activity Monitor in SQL Server Management Studio is useful for ad hoc views of current activity and graphically
displays information about:
Processes running on an instance of SQL Server.
Blocked processes.
Locks.
User activity.
Incorrect Answers:
E: System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use,
enabling you to monitor server performance and activity using predefined objects and counters or user-defined counters
to
monitor events. System Monitor (Performance Monitor in Microsoft Windows NT 4.0) collects counts and rates rather
then data about the events (for example, memory usage, number of active transactions, number of blocked locks, or
CPU
activity). You can set thresholds on specific counters to generate alerts that notify the operator
References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-2017

 

QUESTION 8
You work as a Database Administrator (DBA) for a company named ABC.com.
The company uses a Microsoft SQL Server 2012 infrastructure.
You have a database named CorpDB. CorpDB contains a table named SalesInfo.
You discover that some table has been deleted from the SalesInfo table.
You are unable to find out who deleted the information.
You need to implement a solution to monitor the deletion of any further information from the SalesInfo table. You want to
minimize the development effort required for the solution.
What should you configure?
A. You should configure table permissions.
B. You should configure a user role.
C. You should configure change data capture.
D. You should configure a trigger.
Correct Answer: C

 

QUESTION 9
You use SQL Server 2014.
You need to create a single object that inserts a provided value into Table1, and then returns a count of the records in
Table1.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of
the code blocks.
Select and Place:pass4itsure 70-764 exam question q9

Correct Answer:

pass4itsure 70-764 exam question q9-1

 

QUESTION 10
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 an on-premises Microsoft SQL Server environment.
SQL Server backups should be stored as Microsoft Azure blob pages. The connection process from the SQL Server
instances to Azure should be encrypted.
You need to store backups as Azure blob pages. Which option should you use?
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: F
SQL Server Managed Backup to Microsoft Azure manages and automates SQL Server backups to Microsoft Azure Blob
storage. You can choose to allow SQL Server to determine the backup schedule based on the transaction workload of
your database. Or you can use advanced options to define a schedule. The retention settings determine how long the
backups are stored in Azure Blob storage.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-managed-backup-to-microsoft-azure?view=sql-server-2017

 

QUESTION 11
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 answers 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 q11

You need to configure auditing for the Adventure Works environment.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:pass4itsure 70-764 exam question q11-1

Correct Answer:

pass4itsure 70-764 exam question q11-2

Box 1: CREATE SERVER AUDIT
Create a server audit.
You must implement auditing to record access to data that is considered sensitive by the company.
Create a database audit
Box 2: ALTER SERVER AUDIT
Enable the server audit.
Box 3: CREATE DATABASE AUDIT
Create the database audit specification.
Box 4: FOR SERVER AUDIT
You must implement auditing for all objects in the DAV schema.
References: https://docs.microsoft.com/en-us/sql/relationaldatabases/security/auditing/create-a-server-audit-and-database-audit-specification

 

QUESTION 12
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.
Your company has several Microsoft SQL Server instances. Each instance hosts many databases.
You observe I/O corruption on some of the instances.
You need to perform the following actions:
Identify databases where the PAGE VERIFY option is not set.
Configure full page protection for the identified databases.
Solution: You run the following Transact-SQL statement:pass4itsure 70-764 exam question q12

For each database that you identify, you run the following Transact-SQL statement:

pass4itsure 70-764 exam question q12-1

Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
We should check for CHECKSUM, not NONE or TORN_PAGE_DETECTION
References: https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-page-verify-database-option-to-checksum?view=sql-server-2017

 

QUESTION 13
Your company has offices in Seattle and Montreal.
The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. Server1 is located
in the Seattle office. Server2 is located in the Montreal office. The latency of the WAN link between the Montreal office and the Seattle office is more than 200 ms.
You plan to implement an AlwaysOn availability group on both servers.
You need to recommend a failover type for the availability group. What should you recommend?
A. Synchronous manual failover
B. Synchronous automatic failover
C. Asynchronous automatic failover
D. Asynchronous manual failover
Correct Answer: D

The latest Microsoft MCSA 70-764 dumps help you get on the ladder of success!
Get the latest updates from the 70-764 exam: https://www.pass4itsure.com/70-764.html (Q&As: 442) For more effective exam dumps, follow me!

Pass4itsure Promo Code 15% Off

pass4itsure 70-764 coupon

Why Choose Pass4itsure?

Pass4itsure is the best provider of IT learning materials and the right choice for you to prepare for the Microsoft 70-764 exam. Other brands started earlier, but the price is relatively expensive and the questions are not the newest. Pass4itsure provides the latest real questions and answers with the lowest prices, help you pass 70-764 exam easily at first try.

why pass4itsure 70-764 exam dumps

Maybe you might like the exam questions and answers