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 – Microsoft” – 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 plan to migrate the DB to Azure.
You verify that all objects are valid for Azure SQL database. You need to ensure that users and logins are migrated to
Azure.
What should you do?
A. Use the Copy Database wizard
B. Use the Database Transfer wizard
C. Use the SQL Management Studio to deploy the DB to Azure
D. Back up the database from the local server and restore it to Azure
Correct Answer: CD
QUESTION 2
You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups.
You need to configure the encryption options for backups.
What should you configure?
A. a certificate
B. an MD5 hash
C. a DES key
D. an AES 256-bit key
Correct Answer: D
To encrypt during backup, you must specify an encryption algorithm, and an encryptor to secure the encryption key. The
following are the supported encryption options: Encryption Algorithm: The supported encryption algorithms are: AES
128, AES 192, AES 256, and Triple DES Encryptor: A certificate or asymmetric Key
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption
QUESTION 3
You use Microsoft SQL Server 2016 to write code for a transaction that contains several statements.
There is high contention between readers and writers on several tables used by your transaction. You need to minimize
the use of the tempdb space.
You also need to prevent reading queries from blocking writing queries.
Which isolation level should you use?
A. SERIALIZABLE
B. SNAPSHOT
C. READ COMMITTED SNAPSHOT
D. REPEATABLE READ
Correct Answer: C
For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the
following reasons:
It consumes less tempdb space than snapshot isolation.
Etc.
References: https://msdn.microsoft.com/en-us/library/ms188277.aspx
QUESTION 4
An organization deploys Microsoft SQL Server instances at the main office and the organization\\’s remote office. SQL
The server is not using Windows failover clustering.
Remote employef s report connection issues when they attempt to run queries against a specific database that is hosted
at the main office.
You need to ensure that remote employees can retrieve data from a database instance that is hosted in the remote
office.
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:
Database mirroring is a solution for increasing the availability of a SQL Server database.
Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.
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.
Your company is developing a new business intelligence application that will access data in a Microsoft Azure SQL
Database instance. All objects in the instance have the same owner.
A new security principal named BI_User requires permission to run stored procedures in the database. The stored
procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT
operations or dynamic SQL commands.
The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions,
you should use the principle of least privilege.
You need to create the required security principals and grant the appropriate permissions.
Solution: You run the following Transact-SQL statement in the database:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
We need to add a login.
Secondly, it is enough to grant EXECUTE permissions on the stored procedures for database roles you want to be able
to access the data. We do not need to add roles to this user.
Note: One method of creating multiple lines of defense around your database is to implement all data access using
stored procedures or user-defined functions. You revoke or deny all permissions to underlying objects, such as tables,
and
grant EXECUTE permissions on stored procedures. This effectively creates a security perimeter around your data and
database objects.
Best Practices
Simply writing stored procedures isn\\’t enough to adequately secure your application. You should also consider the
following potential security holes.
Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.
Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role.
All users inherit permissions from the public. Therefore denying permissions to public means that only owners and
sysadmin
members have access; all other users will be unable to inherit permissions from membership in other roles.
Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access
all database objects.
References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server
QUESTION 6
DRAG DROP
You have a table that contains a column named Department. Each user is assigned to a database role name that
matches the Department column value. Users authenticate to the database with accounts that are assigned to their department’s database role. You belong to the db_owner role and have the rights to run the EXECUTE AS USER
statement.
Users must only view records that match their department.
You need to implement Microsoft SQL Server row-level security (RLS) and test the implementation.
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: Create a filter predicate by using a SQL Server inline table-valued user-defined function.
Create a new schema and an inline table-valued function.
Step 2: Create a security policy
Create a security policy adding the function as a filter predicate.
The CREATE SECURITY POLICY command creates a security policy for row level security.
Step 3: Run EXECUTE AS USER
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security
QUESTION 7
You administer a Microsoft SQL Server 2016 server.
You plan to deploy new features to an application.
You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?
A. Query the sys.dm_db_index_usage_stats DMV.
B. Query the sys.dm_db_missing_index_details DMV.
C. Use the Database Engine Tuning Advisor.
D. Query the sys.dm_db_missing_index_columns DMV.
Correct Answer: C
The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can
use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal
set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server. Using the DTA, you can perform the following tasks. Troubleshoot the performance of a
specific problem query Tune a large set of queries across one or more databases Perform an exploratory what-if
analysis of potential physical design changes Manage storage space References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuning-advisor
QUESTION 8
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 9
You have a database named DB1. You complete a full backup on January 1, 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:
Correct Answer:
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 10
You have a database named DB1.
DB1 contains a table named Sales. The SalesPerson that has an index named AK_SalesPerson_rowguid.
Queries that do not use the index take approximately 10 times longer to complete than queries that use the index. You
discover that AK_SalesPerson_rowguid has severe fragmentation. You need to recommend a solution to defragment
the index.
The solution must ensure that the index can be used by queries during the defragmentation.
What statement should you use? To answer, drag the appropriate elements to the correct locations. Each element may
be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content
Select and Place:
Correct Answer:
Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve
availability similar to the reorganize option, you should rebuild indexes online. Use: ALTER INDEX REBUILD WITH
(ONLINE = ON)
References: Reorganize and Rebuild Indexes https://msdn.microsoft.com/enus/library/ms189858.aspx
QUESTION 11
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 12
You are a database administrator for an organization. You manage a mission-critical database named DB1 that is
hosted on a server named LON-SQL1.
An audit record for transactions that occur in DB1 is written to a file every five seconds. If an audit record cannot be
recorded, the transaction must be terminated. You create a shared folder named Audits on a server named LON-SVR1.
You need to implement the process for recording audit records.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segment in
the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Correct Answer:
Box 1: Server Audit The CREATE SERVER AUDIT command creates a server audit object using SQL Server Audit. Box
2: FILE(FILEPATH=\\’\\LON-SVR1\Audits\\\’) An audit record for transactions that occur in DB1 are written to a file every
five seconds. You create a shared folder named Audits on a server named LON-SVR1. Box 3: QUEUE_DELAY=5000
QUEUE_DELAY =integer Determines the time, in milliseconds, that can elapse before audit actions are forced to be
processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second),
which is the default. Box 4: ON_FAILURE= FAIL_OPERATION FAIL_OPERATION Database actions fail if they cause
audited events. Actions, which do not cause audited events can continue, but no audited events can occur. The audit
continues to attempt to log events and resumes if the failure condition is resolved. Use this option when maintaining a
complete audit is more important than full access to the Database Engine.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql
QUESTION 13
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.
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:
Correct Answer:
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 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
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
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.
