Exam 70-764 Administering A SQL Database Infrastructure: MCSA / MCSE For Microsoft SQL Server 2016

MCSA / MCSE for Microsoft SQL Server 2016
Exam 70-764
Administering a SQL Database
Infrastructure
Version 17.35 (440 Questions)
SQL 2016 Data Management and

Database Development Analytics
IT Certification Guaranteed, The Easy Way!
NO.1 You have a database. The existing backups for the database and their corresponding files are
listed in the following table.
You purchase a new server. You must restore the database to the new server.
You need to restore the data to the most recent time possible.
Which three files should you restore in sequence? To answer, move the appropriate files from the list
of files to the answer area and arrange them in the correct order.
Answer:
2
Explanation
Step 1: Full.
Start with the full backup.
Step 2: Diff_20160503_1700.bak
Followed by the most recent differential backup.
Step 3: Log_20160503_1900.bak
And finally the most recent log backup (the only log backup done after the most recent differential
backup).
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/differential-backups-sql-
server
NO.2 You deploy a database by using SQL Server 2014.

The database contains a table named Table1.
3
You need to recommend a solution to track all of the deletions executed on Table1. The solution
must minimize the amount of custom code required.
What should you recommend?
A. Change data capture
B. Statistics
C. A trigger
D. Master Data Services
Answer: A
Explanation
Change data capture is designed to capture insert, update, and delete activity applied to SQL Server
tables, and to make the details of the changes available in an easily consumed relational format. The
change tables used by change data capture contain columns that mirror the column structure of a
tracked source table, along with the metadata needed to understand the changes that have
occurred.
NO.3 You have two SQL Server instances named SQLDev and SQLProd that have access to various
storage media.
You plan to synchronize SQLDev and SQLProd.
You need to recommend a solution that meets the following requirements:
* The database schemas must be synchronized from SQLDev to SQLProd.
* The database on SQLDev must be deployed to SQLProd by using a package.
* The package must support being deployed to SQL Azure.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST
answer.
A. A database snapshot
B. A data-tier application
C. Change data capture
D. SQL Server Integration Services (SSIS)
Answer: B
Explanation
* SIS supports connections to SQL Database by using the ADO.NET provider. OLEDB is not supported
at this time. You can build the SSIS package connecting to SQL Database and create the data flow
tasks the same way as you would against a typical on premise SQL Server.
http://technet.microsoft.com/en-us/library/ee210546.aspx
NO.4 You have an application that queries a database. Users report that the application is slower
than expected.
You discover that several server process identifiers (SPIDs) have PAGELATCH_UP and PAGELATCH_EX
waits. The resource descriptions of the SPIDs contains 2:1:1.
You need to resolve the issue.
What should you do?
A. Reduce the Fill factor of all the clustered indexes.
B. Reduces the number of table variales used in the application.
C. Use identity integers as primary key fields in the tables.
D. Move the database files to SSD storage.
4
Answer: D
Explanation
Faster disks would reduce this problem.
NO.5 You manage a SQL Server 2014 instance that contains a database named DB1.
Users report that some queries to DB1 take longer than expected.
Although most queries run in less than one second, some queries take up to 20 seconds to run. You
need to view all of the performance statistics for each database file.
Which method should you use?
A. Query the sys.dm_os_tasks dynamic management view.
B. Query the sys.dm_os_performance_counters dynamic management view.
C. Query the sys.dm_io_virtual_file_stats dynamic management function.
D. Examine the Data File I/O pane in Activity Monitor.
Answer: C
Explanation
sys.dm_io_virtual_file_stats Returns I/O statistics for data and log files.
NO.6 You want to reproduce the same SQL Server 2016 installation configuration across five servers.
Which of the following files will you generate by using SQL Server Setup to accomplish this goal?
A. Configuration.xml
B. Setup.ini
C. Setup.xml
D. ConfigurationFile.ini
Answer: D
NO.7 You administer a Microsoft SQL Server 2016 database that has multiple tables in the Sales
schema.
Some users must be prevented from deleting records in any of the tables in the Sales schema. You
need to manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort.
What should you do?
A. Create a custom database role that includes the users. Deny Delete permissions on the Sales
schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to
the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in
the Sales schema for the custom database role.
Answer: A
NO.8 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
5
questions wilt not appear in the review screen.

You have a database named DB1 that contains a table named Table1. You need to audit all updates to
Table1.
Solution: You convert Table1 to a system-versioned temporal table. Does this meet the goal?
A. Yes
B. No
Answer: A
NO.9 You plan to deploy a database to SQL Azure. You are designing two stored procedures named
USP_1 and USP_2 that have the following requirements:
* Prevent data read by USP_1 from being modified by other active processes.
* Allow USP_2 to perform dirty reads.
You need to recommend the isolation level for the stored procedures.
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.
Answer:
Explanation
SP1 - repeatable read; SP2 - read uncommited
Note:
- SP1: repeatable read a repeatable read scan retains locks on every row it touches until the end of
the transaction. Even rows that do not qualify for the query result remain locked. These locks ensure
that the rows touched by the query cannot be updated or deleted by a concurrent session until the
current transaction completes (whether it is committed or rolled back).
6
- SP2: read uncommitted permits repeatable reads
NO.10 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 8am, 12pm, 4pm and 8pm.
You plan to deploy some changes to CorpDB at after the 4pm 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.
Answer: C F
NO.11 You create a Management Data Warehouse instance and enable data collection. You have an
Active Directory Domain.......... DS) group named SQLDevelopers.
Members of the SQLDevelopers group must be able to run reports that show information about
Microsoft SQL Server........ SQL Server activity.
You need to grant the developers permissions to run the reports by using the principle of least
privilege.
What should you do?
A. In the msdb database, add SQLDevelopers to the mdwdmin role.
B. In the msdb database, add SQLDevelopers to the mdw_reader role.
C. In the MDW database, add SQLDevelopers to the db_datareader role.
D. In the msdb database, add SQLDevelopers to the mdw_admin role.
Answer: C
Explanation
Members of the mdw_reader role have Read access to the management data warehouse. Because
the purpose of this role is to support troubleshooting by providing access to historical data, members
of this role cannot view other elements of the management data warehouse schema.
Note: The Server Activity data collection set collects information about resources used by the SQL
Server instance (processor, memory, disk I/O, and network usage), SQL Server instance activity (batch
requests, logouts, SQL compilations and recompilations, transactions, user connections, and logins)
and waits Incorrect:
Members of the mdw_admin role have Read, Write, Update, and Delete access to the management
data warehouse.
Members of this role can perform the following operations:
7
* Change the management data warehouse schema when required (for example, adding a new table
when a new collection type is installed).
* Run maintenance jobs on the management data warehouse, such as archive or cleanup.
NO.12 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 maintain a Microsoft SQL Server instance that contains the following databases SalesDb1,
SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table
shows the configuration of each database.
The backup strategies for each database are described in the following table.
Each full or differential backup operation writes into a new file and uses a different sequence
number. You observe the following database corruption issues.
SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading
data pages.
You must display the following information about the corrupted pages:
* database name
8
* impacted file id
* impacted file physical name
* impacted page id
* event type that identifies the error type
* error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query
statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet
the following requirements:
* Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
* Capture queries based on resource consumption.
* Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table
in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should
only update statistics that were automatically created and have not been updated for 30 days. The
update should be based on all data in the table.
You need to write the query the maintenance plan will use to update the statistics.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the
appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and
arrange them in the correct order.
9
Answer:
10
Explanation
11
Step 1: Use SalesDB2...

We specify the specific database SalesDB2, not the master.
Step 2:
Step 3:
From scenario: The plan should only update statistics that were automatically created and have not
been updated for 30 days.
Step 4:
Declare the curser using WITH FULLSCAN.
References:
https://solutioncenter.apexsql.com/how-to-automate-and-schedule-sql-server-index-
defragmentation/
NO.13 Overview
Application Overview
Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.
12
Contoso is designing a new version of the ERP application. The previous version of the ERP
application used SQL Server 2008 R2.
The new version will use SQL Server 2014.
The ERP application relies on an import process to load supplier data. The import process updates
thousands of rows simultaneously, requires exclusive access to the database, and runs daily.
You receive several support calls reporting unexpected behavior in the ERP application. After
analyzing the calls, you conclude that users made changes directly to the tables in the database.
Tables
The current database schema contains a table named OrderDetails.
The OrderDetails table contains information about the items sold for each purchase order.
OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase
order.
The product price is stored in a table named Products. The Products table was defined by using the
SQL_Latin1_General_CP1_CI_AS collation.
A column named ProductName was created by using the varchar data type. The database contains a
table named Orders.
Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older
than 12 months are stored in a table named OrdersOld.
The previous version of the ERP application relied on table-level security.
Stored Procedures
The current version of the database contains stored procedures that change two tables. The
following shows the relevant portions of the two stored procedures:
Customer Problems
Installation Issues
13
The current version of the ERP application requires that several SQL Server logins be set up to
function correctly. Most customers set up the ERP application in multiple locations and must create
logins multiple times.
Index Fragmentation Issues
Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers
defragment the indexes more frequently. All of the tables affected by fragmentation have the
following columns that are used as the clustered index key:
Backup Issues
Customers who have large amounts of historical purchase order data report that backup time is
unacceptable.
Search Issues
Users report that when they search product names, the search results exclude product names that
contain accents, unless the search string includes the accent.
Missing Data Issues
Customers report that when they make a price change in the Products table, they cannot retrieve the
price that the item was sold for in previous orders.
Query Performance Issues
Customers report that query performance degrades very quickly. Additionally, the customers report
that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the
monthly import process, database administrators receive many supports call from users who report
that they cannot access the supplier data. The database administrators want to reduce the amount of
time required to import the data.
Design Requirements
File Storage Requirements
The ERP database stores scanned documents that are larger than 2 MB. These files must only be
accessed through the ERP application. File access must have the best possible read and write
performance.
Data Recovery Requirements
If the import process fails, the database must be returned to its prior state immediately.
Security Requirements
You must provide users with the ability to execute functions within the ERP application, without
having direct access to the underlying tables.
Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.
You need to recommend a solution that addresses the index fragmentation and index width issue.
What should you include in the recommendation? (Each correct answer presents part of the solution.
Choose all that apply.)
A. Change the data type of the lastModified column to smalldatetime.
B. Remove the lastModified column from the clustered index.
C. Change the data type of the modifiedBy column to tinyint.
14
D. Change the data type of the id column to bigint.

E. Remove the modifiedBy column from the clustered index.
F. Remove the id column from the clustered index.
Answer: B E
Explanation
Scenario: Index Fragmentation Issues Customers discover that clustered indexes often are
fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the
tables affected by fragmentation have the following columns that are used as the clustered index
key:
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.
Answer: C
NO.15 General Overview

You are the Senior Database Administrator (DBA) for a software development company named
Leafield Solutions. The company develops software applications custom designed to meet customer
requirements.
Requirements Leafield Solutions has been asked by a customer to develop a web-based Enterprise
Resource Planning and Management application. The new application will eventually replace a
desktop application that the customer is currently using. The current application will remain in use
while the users are trained to use the new webbased application.
You need to design the SQL Server and database infrastructure for the web-based application.
Databases
You plan to implement databases named Customers, Sales, Products, Current_Inventory, and
TempReporting.
The Sales database contains a table named OrderTotals and a table named SalesInfo.
A stored procedure named SPUpdateSalesInfo reads data in the OrderTotals table and modifies data
15
in the SalesInfo table.

The stored procedure then reads data in the OrderTotals table a second time and makes further
changes to the information in the SalesInfo table.
The Current_Inventory database contains a large table named Inv_Current. The Inv_Current table has
a clustered index for the primary key and a nonclustered index. The primary key column uses the
identity property.
The data in the Inv_Current table is over 120GB in size. The tables in the Current_Inventory database
are accessed by multiple queries in the Sales database.
Another table in the Current_Inventory database contains a self-join with an unlimited number of
hierarchies.
This table is modified by a stored procedure named SPUpdate2.
An external application named ExternalApp1 will periodically query the Current_Inventory database
to generate statistical information. The TempReporting database contains a single table named
GenInfo.
A stored procedure named SPUPdateGenInfo combines data from multiple databases and generates
millions of rows of data in the GenInfo table.
The GenInfo table is used for reports.
When the information in GenInfo is generated, a reporting process reads data from the Inv_Current
table and queries information in the GenInfo table based on that data.
The GenInfo table is deleted after the reporting process completes. The Products database contains
tables named ProductNames and ProductTypes.
Current System
The current desktop application uses data stored in a SQL Server 2005 database named
DesABCopAppDB.
This database will remain online and data from the Current_Inventory database will be copied to it as
soon as data is changed in the Current_Inventory database.
SQL Servers
A new SQL Server 2012 instance will be deployed to host the databases for the new system. The
databases will be hosted on a Storage Area Network (SAN) that provides highly available storage.
Design Requirements
Your SQL Server infrastructure and database design must meet the following requirements:
* Confidential information in the Current_ Inventory database that is accessed by ExternalApp1 must
be securely stored.
* Direct access to database tables by developers or applications must be denied.
* The account used to generate reports must have restrictions on the hours when it is allowed to
make a
* connection.
* Deadlocks must be analyzed with the use of Deadlock Graphs.
* In the event of a SQL Server failure, the databases must remain available.
* Software licensing and database storage costs must be minimized.
* Development effort must be minimized.
* The Tempdb databases must be monitored for insufficient free space.
* Failed authentication requests must be logged.
* Every time a new row is added to the ProductTypes table in the Products database, a user defined
function that validates the row must be called before the row is added to the table.
* When SPUpdateSalesInfo queries data in the OrderTotals table the first time, the same rows must
16
be returned along with any newly added rows when SPUpdateSalesInfo queries data in the
OrderTotals table the second time.
You need to enable users to modify data in the database tables using UPDATE operations.
You need to implement a solution that meets the design requirements.
What should you configure?
A. You should configure a server role.
B. You should configure a database role.
C. You should configure functions that use the EXECUTE AS statement.
D. You should configure stored procedures that use the EXECUTE AS statement.
Answer: D
NO.16 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:
The following table shows the current data in the Classifications table:
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.
17
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 meet the security requirements of the junior database
administrators.
What should you include in the recommendation?
A. A server role
18
B. A database role
C. A credential
D. A shared login
Answer: C
Explanation
- Scenario: A group of junior database administrators must be able to view the server state of the SQL
Server instance that hosts the Sales database. The junior database administrators will not have any
other administrative rights.
- Credentials provide a way to allow SQL Server Authentication users to have an identity outside of
SQL Server. Credentials can also be used when a SQL Server Authentication user needs access to a
domain resource, such as a file location to store a backup.
NO.17 You administer two Microsoft SQL Server 2012 servers.

Each server resides in a different, untrusted domain.
You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?
A. Configure the SQL Server service account to use Network Service.
B. Use a server certificate.
C. Use a database certificate.
D. Configure the SQL Server service account to use Local System.
Answer: B
NO.18 You need to address the Sales Director's requirements regarding the customer classification.
You need to recommend a solution for changing the classifications.
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.
Answer: D
NO.19 Note: This question is part of a series of question that present the same scenario. Each
question in the series contains I 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 Saver instance. Each instance hosts many database.
You observe I/O corruption on some of the instance.
You need to perform the following actions:
* identify databases where the PAGE verity option is not set.
* Configure full page protection for the identified databases.
Solution: You run the following Transact-SQL statement:
19
For each database that you identify, you run the following Transact-SQL statement:
Does the solution meet the goal?

A. Yes
B. No
Answer: A
NO.20 You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics setting on
the database named DB1 has been turned off.
You see no evidence that any maintenance has been occurring. You want to set up monitoring to see
if query performance is being affected. You need to set up a monitoring process that will capture any
cases where statistics could have been useful if they existed.
What should you do?
A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key
columns in the database.
B. Use the missing_column_statistics extended event.
C. Query the sys.statistics system view to see all cases where the statistics were last needed.
D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes,
filtering on is_hypothetical.
Answer: B
Explanation
The Missing Column Statistics event class indicates that column statistics that could have been useful
for the optimizer are not available. By monitoring the Missing Column Statistics event class, you can
determine if there are statistics missing for a column used by a query.
This can cause the optimizer to choose a less efficient query plan than expected.
NO.21 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.
20
You need to ensure that the query optimizer always uses the query plan that you prefer.
Solution: You create a plan guide for the query by using the desired query plan and the
sp_create_plan_guide stored procedure.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation
The KEEPFIXED 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
NO.22 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.
You have a user database named HRDB that contains sensitive human resources data. The HRDB
backup files must be encrypted.
You need to grant the correct permission to the service account that backs up the HRDB database.
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
Answer: G
Explanation
Restoring the encrypted backup: SQL Server restore does not require any encryption parameters to
be specified during restores. It does require that the certificate or the asymmetric key used to
encrypt the backup file be available on the instance that you are restoring to. The user account
performing the restore must have VIEW DEFINITION permissions on the certificate or key.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-
encryption
NO.23 You have a Microsoft SQL Server database named DB1. Nested and recursive triggers for DB1
are disabled.
There is an existing DDL trigger named TableAudit in the database. The trigger captures the name of
each table as the table is created.
You define a policy for the database by using SQL Server policy-based management. The policy
21
requires that all table names use the prefix tbl. You set the evaluation mode for the policy to On
change: prevent.
You observe that developers are able to add new tables that do not include the required prefix in the
table name.
You need to ensure the policy is enforced.
What should you do?
A. Disable the TableAudit trigger.
B. Enable nested triggers.
C. Change the policy evaluation mode to On Schedule.
D. Enable recursive triggers.
Answer: B
Explanation
If the nested triggers server configuration option is disabled, On change: prevent will not work
correctly.
Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not
comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL
triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/administer-
servers-by-using
NO.24 Which feature should you enable and configure so session requests addressed to a specific
instance can be allocated to different processor resources based on session request properties?
A. Resource Governor
B. Windows System Resource Manager
C. Processor affinity
D. I/O affinity
Answer: C
You are configuring a highly-available database solution using an AlwaysOn availability group on two
servers running SQL Server 2012.
The two servers are in separate datacenters.
The two datacenters are connected by a fast WAN link with a network latency of less than 10ms.
Which of the following failover types should you configure for the availability group?
A. You should configure the asynchronous manual failover failover type.
B. You should configure the synchronous manual failover failover type.
C. You should configure the synchronous automatic failover failover type.
D. You should configure the Asynchronous automatic failover failover type.
Answer: C
NO.26 You have an SQL Server 2014 server. You plan to create four stored procedures that will use
transactions. The stored procedures will be configured as shown in the following table.
22
You need to recommend an isolation level for each stored procedure. The solution must support the
concurrency strategy of each stored procedure and must minimize locks.
To answer, drag the appropriate isolation levels to the correct stored procedures. Each isolation level
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.
Answer:
Explanation
SP1 - SNAPSHOT, SP2 - REPEATABLE READ, SP3 - READ COMMITTED, SP4 - SERIALIZABLE Referenc
es: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms173763.aspx
NO.27 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
23
apply only to that question.

You are deploying a Microsoft SQL Server architecture to support a new mission-critical application.
The application includes a dedicated reporting component that performs read-only operations
against the application's databases.
You need to implement a solution that meets the following requirements:
Modifications to the application to support the new architecture are not permitted.
What 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 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
Answer: C
Explanation
Synchronous-commit mode emphasizes high availability over performance, at the cost of increased
transaction latency.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-
modes-always-on
NO.28 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 server named Server1 that has Microsoft SQL Server installed.
Server1 has SQL Server Adult configured to send audit even records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You grant the VIEW ANY DEFINITION permission to User1.
Does this meet the goal?
A. Yes
B. No
Answer: B
Explanation
Each feature and command for SQL Server Audit has individual permission requirements.
Unless otherwise specified, viewing catalog views requires a principal to have one of the following:
* The VIEW SERVER STATE permission.
* The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog
* view).
* Membership in the sysadmin fixed server role.
* The CONTROL SERVER permission.
* The ALTER ANY AUDIT permission.
A principal must have the VIEW SERVER STATE or ALTER ANY AUDIT permission to use the Dynamic
24
Management Views.
References: https://technet.microsoft.com/en-us/library/cc280665(v=sql.105).aspx
NO.29 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 databasae from the local server and restore it to azure
Answer: C D
NO.30 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
Answer: D
Explanation
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
NO.31 You plan to implement a fault tolerance solution for a Microsoft SQL Server database. The
solution must provide failover storage on the local network.
You need to ensure that the solution can route traffic to failover storage by using SMB 3.0. Which
storage option should you use?
A. Cluster Shared Volumes
B. Microsoft Azure Blob storage
C. Always On availability group
D. Stretch Database
Answer: A
Explanation
Clustered Shared Volumes (CSV) is a new clustered file system in Windows Server that is a layer of
abstraction above the NTFS file system in a WSFC environment. It allows all Nodes in the failover
cluster to read and write to the CSV volume. CSV leverages the investments Microsoft have made in
SMB 3.0, such as SMB Direct and SMB Multichannel.
SQL Server 2014 was the first version of SQL Server to support CSVs.
25

Exam 70-764 Administering A SQL Database Infrastructure: MCSA / MCSE For Microsoft SQL Server 2016

Uploaded by

Copyright:

Available Formats

Exam 70-764 Administering A SQL Database Infrastructure: MCSA / MCSE For Microsoft SQL Server 2016

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Exam 70-764 Administering A SQL Database Infrastructure: MCSA / MCSE For Microsoft SQL Server 2016

Uploaded by

Copyright:

Available Formats

MCSA / MCSE for Microsoft SQL Server 2016

SQL 2016 Data Management and

NO.2 You deploy a database by using SQL Server 2014.

questions wilt not appear in the review screen.

- SP2: read uncommitted permits repeatable reads

Step 1: Use SalesDB2...

D. Change the data type of the id column to bigint.

NO.15 General Overview

in the SalesInfo table.

The Inventory database is updated frequently.

NO.17 You administer two Microsoft SQL Server 2012 servers.

Does the solution meet the goal?

apply only to that question.

NO.29 You plan to migrate the db to azure.

NO.30 You administer a Microsoft SQL Server 2016 database.

You might also like