Easyhometraining shares the latest Microsoft MCSA 70-764 exam dumps for free exam practice tests and online downloads! “Administering a SQL Database Infrastructure” 70-764 exam. Ready to pass the 70-764 exam please click https://www.pass4itsure.com/70-764.html (full exam dump)

Share a free Microsoft MCSA 70-764 video tutorial

Microsoft MCSA 70-764 Exam pdf

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

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

Exam 70-764: Administering a SQL Database Infrastructure: https://www.microsoft.com/en-us/learning/exam-70-764.aspx

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%)

Microsoft MCSA 70-764 Online Exam Practice Questions

QUESTION 1
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.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The
environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate. You
observe that the database is fragmented.
You need to optimize query performance.
Solution: You run the DBCC CHECKDB command.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
DBCC CHECKDB only checks the logical and physical integrity of all the objects in the specified database. It does not
update any indexes, and does not improve query performance. References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

 

QUESTION 2
You need to address the Sales Director\\’s requirements regarding the customer classification. You need to recommend
a solution for changing the classifications.
What should you recommend?
A. Add each classification change to a new row in the Customers table.
B. Record each change to the classification of each customer in a new row in the Customers table.
C. Add a new row to the Customers table for each new classification.
D. Record each change to the classification of each customer in a new table in the Customers database.
Correct Answer: D

 

QUESTION 3
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 q3

The following table shows the current data in the Classifications table:

pass4itsure 70-764 exam question q3-1

The Inventory database is updated frequently.
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 disaster recovery solution for the Dev database. What should you include in the
recommendation?
A. The simple recovery model and full backups
B. The full recovery model, full backups, and transaction log backups
C. The full recovery model, full backups, and differential backups
D. The bulk-logged recovery model and full backups
Correct Answer: A
Scenario:
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.
– The simple recovery model provides the simplest form of backup and restore. This recovery model supports both
database backups and file backups, but does not support log backups. Transaction log data is backed up only with the
associated user data.
The absence of log backups simplifies managing backup and restore. However, a database can be restored only to the
end of the most recent backup.
Incorrect Answers:
B: The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to
improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.

 

QUESTION 4
You are designing two stored procedures named Procedure1 and Procedure2. You identify the following requirements:
Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure.
Procedure2 must use business logic that resides in a Microsoft .NET Framework assembly.
You need to identify the appropriate technology for each stored procedure.
Which technologies should you identify? To answer, drag the appropriate technology to the correct stored procedure in
the answer area. (Answer choices may be used once, more than once, or not at all.)
Select and Place:pass4itsure 70-764 exam question q4

Procedure 1 – A table-valued parameter (TVP); Procedure 2 – Common language runtime (CLR)
References: http://msdn.microsoft.com/en-us/library/ms131102.aspx http://msdn.microsoft.com/en-us/library/bb522446.aspx http://msdn.microsoft.com/en-us/library/bb510489.aspx

 

QUESTION 5
You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named
Sales.Orders with the following script.pass4itsure 70-764 exam question q5

Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that that you can retrieve the following information:
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDate. Update rows as the
order status changes.
B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status.
Insert new rows into the table as the order status changes.
C. Implement change data capture on the Sales.Orders table.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the
value of each column from null to the appropriate date as the order status changes.
Correct Answer: B


QUESTION 6
You administer a Microsoft SQL Server 2016 failover cluster that contains two nodes named Node A and Node B.
A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster.
What should you do?
A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A.
B. Use Node A to install SQL Server on Node C.
C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
D. Use Cluster Administrator to add a new Resource Group to Node B.
Correct Answer: C
1.
To add a node to an existing SQL Server failover cluster
Insert the SQL Server installation media, and from the root folder, double-click Setup.exe. To install from a network
share, navigate to the root folder on the share, and then double-click Setup.exe.
2.
The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster
instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.Etc. References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/add-or-remove-nodes-in-a-sql-server-failover-cluster-setup

 

QUESTION 7
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 q7

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

pass4itsure 70-764 exam question q7-1

Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
We should check for CHECKSUM not 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 8
You have a SQL Server 2012 database named DB1.
You have a backup device named Device1.
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions.
The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
A. BACKUP LCG DB1 TO Device1 WITH COPY_ONLY
B. BACKUP LOG DB1 TO Device1
C. BACKUP LOG DB1 TO Device1 WITH NCRECCVERY
D. BACKUP LOG D31 TO Device1 WITH TRUNCATE ONLY
Correct Answer: B
http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx
http://msdn.microsoft.com/en-us/library/ms190925.aspx

 

QUESTION 9
You use SQL Server 2014 Enterprise Edition.
Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data
from the year 2010 and prior.
Management has decided to archive all AUDITDATA records from 2010 and prior.
Management wants the records to be removed from the database entirely and provided to the backup team as a zipped
text file. The data must no longer reside in the database.
There is very little tolerance for performance degradation in your environment. You need to remove all 2010 and prior
data from the AuditData table by using the least amount of system resources possible. Develop the solution by selecting
and arranging the required SQL actions in the correct order.
You may not need all of the actions.
Select and Place:pass4itsure 70-764 exam question q9

Correct Answer:

pass4itsure 70-764 exam question q9-1

Note:
– Create a new partitioned table with the partition function you want, and then insert the data from the old table into the
new table by using an INSERT INTO…SELECT FROM statement.
-SPLIT RANGE ( boundary_value )
Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ
from the existing boundary ranges of the partition function.
Based on boundary_value, the Database Engine splits one of the existing ranges into two.
Of these two, the one where the new boundary_value resides is considered the new partition.
-BCP can be used top produce the zipped text file.
-Example: plitting a partition of a partitioned table or index into two partitions
The following example creates a partition function to partition a table or index into four partitions.
ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five
partitions.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
-Split the partition between boundary_values 100 and 1000
-to create two partitions between boundary_values 100 and 500
–and between boundary_values 500 and 1000.
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (500);

 

QUESTION 10
You have a database named DB1. You complete a full backup on January1, 2018 to a backup set named DB1_Backup.
You create a differential backup January 2, 2018 to the same backup set. You perform transaction log backups each
day at 1:00 PM.
DB1 experiences a catastrophic failure.
You need to restore the database to January 3, 2018 at 11:00 AM.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segment from the list of Transact-SQL segments to the answer area and arrange them in the correct
order.
Select and Place:pass4itsure 70-764 exam question q10

Correct Answer:

pass4itsure 70-764 exam question q10-1

This example restores a database, differential database, and transaction log backup of the MyAdvWorks database.
Step 1:
— Assume the database is lost at this point. Now restore the full
— database. Specify the original full database backup and NORECOVERY.
— NORECOVERY allows subsequent restore operations to proceed.
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY;
GO
Step 2:
— Now restore the differential database backup, the second backup on
— the MyAdvWorks_1 backup device.
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH FILE = 2,
NORECOVERY;
Step 3:
— Now restore each transaction log backup created after
— the differential database backup.
RESTORE LOG MyAdvWorks
FROM MyAdvWorks_log1
WITH NORECOVERY; GO RESTORE LOG MyAdvWorks FROM MyAdvWorks_log2 WITH RECOVERY; GO
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-differential-database-backup-sql-server

 

QUESTION 11
You administer a Microsoft SQL Server 2016 server that hosts a transactional database and a reporting database.
The transactional database is updated through a web application and is operational throughout the day.
The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:pass4itsure 70-764 exam question q11

At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the
transactional database. You need to ensure that the transactional database is restored. You also need to ensure that
data loss is minimal.
What should you do?
A. Perform a partial restore.
B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before
the time of failure from the most recent differential backup.
C. Perform a point-in-time restore.
D. Restore the latest full backup.
E. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
F. Perform a page restore.
G. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most
recent full backup.
H. Restore the latest full backup. Then, restore the latest differential backup.
Correct Answer: F
The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Typically,
pages that are candidates for restore have been marked as “suspect” because of an error that is encountered when
accessing the page.
Note: Requirements for Restoring Pages
A page restore is subject to the following requirements:
The databases must be using the full or bulk-logged recovery model.Etc.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server

 

QUESTION 12
Note: This question is a 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 designing a new high availability (HA) solution for a set of application databases. The solution will be contained
within a single datacenter and provided instance-level HA protection.
You need to deploy a solution that allows for minimal downtime if the production server goes offline.
Which 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).
References: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server


QUESTION 13
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

Share Pass4itsure discount codes for free

pass4itsure coupon

About 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!
It helps you pass the exam easily on your first attempt.

why pass4itsure

Summarize:

How do I pass the Microsoft MCSA 70-764 exam? You need to be prepared for it! You need the latest and most effective learning materials and proper practices to pass the 70-764 exam. “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 offers you the latest exam materials! You can use the materials to prepare to help you achieve excellent results!

This maybe you’re interested