Certificationdemo shares online exam exercise questions all year round! Microsoft MCSA 70-764 exam “Administering a SQL Database Infrastructure” https://www.leads4pass.com/70-764.html (442 Q&As). Continue to study and we provide an updated cisco 70-764 exam practice questions and answers. You can practice the test online!
Table of Contents:
- Latest Microsoft MCSA 70-764 pdf
- Test your Microsoft MCSA 70-764 exam level
- Related 70-764 Popular Exam resources
- Get Lead4Pass Coupons (12% OFF)
- What are the advantages of Lead4pass?
Latest Microsoft MCSA 70-764 pdf
[PDF] Free Microsoft MCSA 70-764 pdf dumps download from Google Drive: https://drive.google.com/open?id=10hn31bPob_2072mBH7U_7yOGGE02joOP
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%)
Who should take this 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.
Microsoft MCSA 70-764 Exam Practice Questions
QUESTION 1
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 2
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 configure log shipping to backup and restore by using a single shared folder.
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-sql-server?view=sql-server-2017
QUESTION 3
You plan to deploy SQL Server 2014. You are designing two stored procedures named SP1 and SP2 that have the
following requirements:
-Prevent data read by SP1 from being modified by other active processes.
-Prevent SP2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure.
The solution must maximize concurrency. Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
Select and Place:
Correct Answer:
SP1 – repeatable read; SP2 – read commited
-REPEATABLE READ This isolation level includes the guarantees given by SNAPSHOT isolation level. In addition,
REPEATABLE READ guarantees that for any row that is read by the transaction, at the time the transaction commits
the row has not been changed by any other transaction. Every read operation in the transaction is repeatable up to the
end of the transaction.
– Committed Read is SQL Server\\’s default isolation level. It ensures that an operation will never read data another
application has changed but not yet committed.
QUESTION 4
You administer a Microsoft SQL Server 2016 default instance. The instance is hosted by a server that has a local
firewall configured.
The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server.
You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive
to client connections.
What should you do?
A. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
B. Execute the following Transact-SQL command: sp_configure \\’remote admin connections\\’,
C. Execute the Reconfigure command.
D. Execute the following Transact-SQL command: sp_configure \\’remote access\\’, 1
E. Restart the SQL Server Agent Service.
F. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.
Correct Answer: ABC
SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running
server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even
when the
server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By
default, the DAC is only available from a client on the server. To enable client applications on remote computers to use
the
DAC, use the remote admin connections option of sp_configure.
By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434
The following example enables the DAC from a remote computer.
sp_configure \\’remote admin connections\\’, 1;
GO
RECONFIGURE;
GO
References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/remote-admin-connections-server-configuration-option
QUESTION 5
You manage a database that uses the FULL recovery model and has a 60-minute recovery point objective (RPO). Full
database backups are performed at 23:00 Monday through Friday.
Each full database backup takes 6 hours to complete. Approximately 5% of the data changes each weekday.
Backups must be performed as often as possible to reduce the number of files needed either a full recovery to any given
time. You must minimize the duration of the backup tasks that are performed between 04:00 and 23:00.
You need to reconfigure backups.
Which time slot or schedule should you use for each backup type?
Hot Area:
Correct Answer:
QUESTION 6
You administer a Microsoft SQL Server 2016 database that includes a table named Application.Events.
Application.Events contains millions of records about user activity in an application.
Records in Application.Events that are more than 90 days old are purged nightly.
When records are purged, table locks are causing contention with inserts.
You need to be able to modify Application.Events without requiring any changes to the applications that utilize
Application.Events.
Which type of solution should you use?
A. Partitioned tables
B. Online index rebuild
C. Change data capture
D. Change tracking
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 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.
You need to implement a process for importing data into WingDB.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions
to the answer area and arrange them in the correct order.
Select and Place: 
Correct Answer:
Step 1: Perform a full backup of the database and enable the bulk-logged recovery model.
Not: Simple recovery model.
With the Simple recovery model we cannot minimize data loss.
Step 2: Import the data
Step 3: Backup the tail of the transaction log.
For databases that use full and bulk-logged recovery, database backups are necessary but not sufficient. Transaction
log backups are also required.
Note: Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or
simple recovery model. A database can be switched to another recovery model at any time.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backuprestore/recovery-models-sql-server
QUESTION 8
A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup
strategy that creates backups for the filegroups.
DB1 experiences a failure. You must restore FG1 and then FG2.
You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After
the restoration of FG2 completes, the database must be online.
What should you specify when you run the recovery command?
A. the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
B. the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
C. the WITH RECOVERY clause for both FG1 and FG2
D. the WITH NORECOVERY clause for both FG1 and FG2
Correct Answer: A
QUESTION 9
You are a database administrator for Contoso, Ltd. You configure a Microsoft SQL Server failover cluster with four
nodes by using Windows Server 2012 R2 Datacenter Edition and SQL Server 2016 Enterprise edition.
A server in the datacenter needs to be replaced. The server is part of the SQL Server Failover Instance (FCI).
You need to remove the FCI node for the server that will be replaced.
What should you do?
A. Evict the node from Failover Cluster Manager.
B. Run the Remove-ClusterResource Windows PowerShell cmdlet.
C. Run the Remove-Cluster Windows PowerShell cmdlet.
D. Remove the shared storage from Failover Cluster Manager.
Correct Answer: A
Recover from an irreparable failure Use the following steps to recover from an irreparable failure. The failure could be
caused, for example, by the failure of a disk controller or the operating system. In this case, failure is caused by
hardware failure in Node 1 of a two-node cluster.
1.
After Node 1 fails, the SQL Server FCI fails over to Node 2.
2.
Evict Node 1 from the FCI. To do this, from Node 2, open the Failover Cluster Manager snap-in, right-click Node1, click
Move Actions, and then click Evict Node.
3.
Verify that Node 1 has been evicted from the cluster definition.
4.
Install new hardware to replace the failed hardware in Node 1.
5.
Using the Failover Cluster Manager snap-in, add Node 1 to the existing cluster. For more information, see Before
Installing Failover Clustering.
6.
Ensure that the administrator accounts are the same on all cluster nodes.
7.
Run SQL Server Setup to add Node 1 to the FCI. For more information, see Add or Remove Nodes in a SQL Server
Failover Cluster (Setup). References: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/recoverfrom-failover-cluster-instance-failure
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 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 enable compression for the transaction log backups.
Does this meet the goal?
A. Yes
B. No
Correct Answer: A
SQL Server 2017 supports backup compression. When creating a log shipping configuration, you can control the
backup compression behavior of log backups by choosing one of the following options: Use the default server setting,
Compress backup, or Do not compress backup
Note: SQL Server 2008 Enterprise and later versions support backup compression.
References: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-2017
QUESTION 11
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 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.
You need to recommend a solution for Application1 that meets the security requirements. What should you include in
the recommendation?
A. Encrypted columns
B. Certificate Authentication
C. Secure Socket Layer (SSL)
D. Signed stored procedures
Correct Answer: D
-Scenario:
-Data from Database2 will be accessed periodically by an external application named Application1
– Application developers must be denied direct access to the database tables. Applications must be denied direct
access to the tables. Tutorial: Signing Stored Procedures with a Certificate
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.
You support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for
a report that users run frequently.
The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used.
You observe that this is due to the distribution of data within a specific table that the query uses.
You need to ensure that the query optimizer always uses the query plan that you prefer.
Solution: You create a copy of the plan guide for the query plan.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
PLAN should be used as it forces the query optimizer not to recompile a query due to changes in statistics. References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
QUESTION 13
You work as a Database Administrator (DBA) for a company named ABC.com.
The company has a Windows Azure subscription.
The company uses a cloud based SQL Server environment hosted on SQL Azure.
Developers in your company are creating an ecommerce website.
You are designing a database for the website.
The database will be hosted on SQL Azure.
The database will store and reuse web site login details and customer credit card numbers.
You need to ensure the username, passwords and credit card details are securely stored in the database.
Which of the following would be the most suitable secure storage solution?
A. Secure Sockets Layer (SSL)
B. IPSec
C. Data encryption
D. Transparent Data Encryption (TDE)
E. Encrypting File System (EFS)
Correct Answer: C
Related 70-764 Popular Exam resources
Get Lead4Pass Coupons(12% OFF)
What are the advantages of Lead4pass?
We have a number of Cisco, Microsoft, IBM, CompTIA and other exam experts. We update exam data throughout the year.
Top exam pass rate! We have a large user base. We are an industry leader! Choose Lead4Pass to pass the exam with ease!
Summarize:
The free Microsoft MCSA 70-764 exam dumps can help you improve your skills and exam experience! To pass the cisco 70-764 exam at once: https://www.leads4pass.com/70-764.html We make Microsoft 70-764 videos and 70-764 pdf for you to learn! I hope you can pass the exam easily.