Administering a SQL Database Infrastructure – Microsoft: 70-764 exam. Share the latest 70-764 dumps and Practice test questions for free. Real and effective exam questions and answers.70-764 pdf online Download, Examthings share 40 Practice test questions for FREE! Get the full 70-764 exam dumps: https://www.leads4pass.com/70-764.html (Total questions:380 Q&A)
[PDF] Free Microsoft MCSA 70-764 pdf dumps download from Google Drive: https://drive.google.com/open?id=10hn31bPob_2072mBH7U_7yOGGE02joOP
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Exam 70-764: Administering a SQL Database Infrastructure – Microsoft: https://www.microsoft.com/en-us/learning/exam-70-764.aspx
Latest effective Microsoft MCSA 70-764 Exam Practice Tests
QUESTION 1
You have an SQL Server 2016 server named SQL1. You are designing a performance monitoring solution.
You need to monitor the following events on SQL1:
A deadlock graph Missing column statistics CPU performance statistics A batch of completed Transact-SQL statements
Which two tools should you use? Each correct answer presents a complete solution.
A. dynamic management views
B. Database Engine Tuning Advisor
C. SQL Server Profiler
D. Activity Monitor
E. Data Profile Viewer
Correct Answer: BC
Explanation: B: Database Engine Tuning Advisor examines how queries are processed in the databases you specify.
When you run a Profiler Trace and feed it to the Database Engine Tuning Advisor, it also looks for missing column
statistics, and it can automatically create them for you.
C: Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency
between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler,
you can create a trace that records, replays, and displays deadlock events for analysis. Reference: Analyze Deadlocks
with SQL Server Profiler
https://msdn.microsoft.com/en-us/library/ms188246.aspx Reference: Mastering SQL Server Profiler, page 245
QUESTION 2
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 examining information about users, sessions, and processes in an on-premises Microsoft SQL Server 2016
Standard Edition server.
You need to identify waits for resources and return only the following information:
What should you use?
A. Activity Monitor
B. Sp_who3
C. SQL Server Management Studio (SSMS) Object Explorer
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Correct Answer: E
SQL Server Data Tools (SSDT) is a Microsoft Visual Studio environment for creating business intelligence solutions.
SSDT features the Report Designer authoring environment, where you can open, modify, preview, save, and deploy
Reporting Services paginated report definitions, shared data sources, shared datasets, and report parts.
References: https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx
QUESTION 3
You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify which transaction causes the deadlock.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_sessions dynamic management view.
B. Query the sys.dm_exec_requests dynamic management view.
C. Create a trace in SQL Server Profiler that contains the Deadlock graph event
D. Create an extended events session to capture deadlock information.
Correct Answer: D
Troubleshooting deadlocks
You have been receiving reports from users indicating that certain applications are returning deadlock errors. To
maximize the effectiveness of troubleshooting these problems, you decide to focus on the deadlocks that are hit most
frequently.
You create an Extended Events session that:
/ Configures deadlock event tracking for the session. / Specifies a target that aggregates based on an identifier for the
deadlock.
You run the Extended Events session, and after additional deadlocks are reported you are able to obtain aggregated
deadlock information, along with the complete XML deadlock graph for each source. Using this information you are able
to
pin point the most common deadlocks and start working on a solution.
Create an Extended Events Session
View Event Session Data
QUESTION 4
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.
A company has a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts
a database named DB1.
You need to ensure that you can perform auditing at the database level for DB1.
Solution: You migrate DB1 to a named instance on a server that runs Microsoft SQL Server 2016 Enterprise edition.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL
Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.
References:https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql- server-audit-database-
engine
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 sections, 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 6
You have a server named Server1 that has 16 processors.
You plan to deploy multiple instances of SQL Server 2016 to Server1. You need to recommend a method to allocate
processors to each instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Processor affinity
B. Windows System Resource Manager (WSRM)
C. Max Degree of Parallelism
D. Resource Governor
Correct Answer: A
CPU affinity management through Windows System Resource Manager is not recommended for SQL Server multi-
instance management. Instead, use the processor affinity settings in SQL Server. Reference: Server Properties
(Processors Page)
QUESTION 7
You have a SQL Server 2016 environment that includes four servers. The servers are configured as shown in the
following table.
You plan to configure Policy-Based Management to enforce the following rules:
On Server1, enable SQL Server password policies and enable the default trace. On Server3, ensure that the names of
user-defined stored procedures begin with the prefix “usp_” and ensure that all databases use a case-sensitive
collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution.
Choose all that apply.)
A. Server1
B. Server2
C. Server3
D. Seiver4
Correct Answer: AC
Need Standard or Enterprise edition of SQL Server.
QUESTION 8
You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by
using the following script:
Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution
must ensure that new statuses can be added in the future.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Implement change data capture on the Sales.Orders table.
B. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as
the order status changes.
C. 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.
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 9
You have a server that has SQL Server 2016 installed. The server contains 100 user databases.
You need to recommend a backup solution for the user databases.
The solution must meet the following requirements:
Perform a transaction log backup every hour.
Perform a full backup of each database every week. Perform a differential backup of each database every day. Ensure
that new user databases are added automatically to the backup solution.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Policy-Based Management
B. A Data Definition Language (DDL) trigger
C. SQL Server Agent jobs
D. A maintenance plan
Correct Answer: D
Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly
backed up, and free of inconsistencies.
Maintenance plans can be created to perform the following task (among others):
Back up the database and transaction log files. Database and log backups can be retained for a specified period. This
lets you create a history of backups to be used if you have to restore the database to a time earlier than the last
database
backup. You can also perform differential backups.
Reference: Maintenance Plans
QUESTION 10
You have a query that is used by a reporting dashboard.
Users report that the query sometimes takes a long time to run.
You need to recommend a solution to identify what is causing the issue.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Set the blocked process threshold, and then run SQL Server Profiler.
B. Set the blocked process threshold, and then create an alert.
C. Enable trace flag 1204, and then create an alert.
D. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.
Correct Answer: B
Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds or longer.
–Make sure you don\\’t have any pending changes SELECT * FROM sys.configurations WHERE value value_in_use;
GO
exec sp_configure \\’show advanced options\\’, 1; GO RECONFIGURE GO
exec sp_configure \\’blocked process threshold (s)\\’, 20; GO RECONFIGURE GO Step 2: Set up a trace to capture the
blocked process report. Run it as a server side trace.
Reference: blocked process threshold Server Configuration Option
QUESTION 11
You have a customer who has several SQL Server 2012 database servers.
You are designing a data warehouse for the customer. The data warehouse will use columnstore indexes.
The customer identifies that the following must be supported for the column store indexes.
Data manipulation language (DML) statements Nonclustered columnstore indexes Clustered columnstore indexes
Partitioning You need to identify which technology requires the customer to implement an SQL Server 2016 database.
What should you identify?
A. clustered columnstore indexes
B. nonclustered columnstore indexes
C. data manipulation language (DML) statements
D. partitioning
Correct Answer: A
Explanation: SQL Server 2016 has the features of SQL Server 2012 plus updateable clustered columnstore indexes.
This feature is required here as DML statements must be supported in the warehouse. Reference: Columnstore Indexes
Described https://msdn.microsoft.com/en-us/library/gg492088(v=sql.120).aspx
QUESTION 12
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 need to examine information about logins, CPU times, and Disk I/O on a particular database in Microsoft Azure.
What should you use?
A. Activity Monitor
B. Sp_who3
C. SQL Server Management Studio (SSMS) Object Explorer
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Correct Answer: A
Activity Monitor displays information about SQL Server processes and how these processes affect the current instance
of SQL Server. Activity Monitor is a tabbed document window with the following expandable and collapsible panes:
Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries.
The Activity User Tasks Pane shows information for active user connections to the instance, including the following
column:
*
Login: The SQL Server login name under which the session is currently executing.
The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the
instance over the last 30 seconds, including the following column:
*
CPU (ms/sec): The rate of CPU use by the query
References:https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx
QUESTION 13
You use SQL Server 2016 to maintain the data used by applications at your company.
You want to execute two statements.
You need to guarantee that either both statements succeed, or both statements fail together as a batch.
Which code should you use?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Correct Answer: D
Structure should be: BEGIN TRY BEGIN TRANSACTION COMMIT TRANSACTION END TRY BEGIN CATCH
ROLLBACK TRANSACTION END CATCH.
Reference: TRY…CATCH (Transact-SQL)
QUESTION 14
You have a database named DB1.
You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same
identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.
Occasionally, the identifying value must be reset to its initial value.
You need to design a mechanism to hold the identifying values for the stored procedure to use.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored
procedure. Reset the value by using an ALTER SEQUENCE statement as needed.
B. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored
procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the
value as needed by using a different ALTER SEQUENCE statement.
C. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by
using the stored procedure. Reset the value as needed by using an UPDATE statement.
D. Create an identity column in each of the three tables. Use the same seed and the same increment for each table.
Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the
columns as needed.
Correct Answer: A
*
an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR
function.
*
ALTER SEQUENCE
Includes argument:
RESTART [ WITH ]
The next value that will be returned by the sequence object. If provided, the RESTART WITH value must be an integer
that is less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. If the
WITH value is omitted, the sequence numbering restarts based on the original CREATE SEQUENCE options.
*
CREATE SEQUENCE
Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that
generates a sequence of numeric values according to the specification with which the sequence was created. The
sequence of
numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart
(cycle) when exhausted.
Sequence Numbers
QUESTION 15
You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per
hour.
Queries on DB1 are taking longer than normal to complete.
You run the following Transact-SQL statement:
SELECT * FROM sys.database_query_store_options
You determine that the Query Store is in Read-Only mode.
You need to maximize the time that the Query Store is in Read-Write mode.
Which Transact-SQL statement should you run?
A. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
B. ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)
C. ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS =
14));
D. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)
Correct Answer: C
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime
statistics and inactive queries. By default, Query Store is configured to keep the data for 30 days which may be
unnecessarily
long for your scenario.
Avoid keeping historical data that you do not plan to use. This will reduce changes to read- only status. The size of
Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio
or the
following script to configure time-based cleanup policy:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS =
14));
References:https://docs.microsoft.com/en-us/sql/relational-databases/performance/best- practice-with-the-query-store
QUESTION 16
Your network contains an Active Directory domain that has two groups named Group1 and Group2.
The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access
to various storage media.
The SQL Server instances have a database that contains a table named Table1.
Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2.
You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the
value of Value1.
What should you recommend?
A. A dynamic management view
B. Filegroups
C. Snapshot isolation
D. User-defined views
Correct Answer: D
A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns
and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database. The rows and
columns of data come from tables referenced in the query defining the view and are produced dynamically when the
view is referenced.
A view acts as a filter on the underlying tables referenced in the view. http://msdn.microsoft.com/en-
us/library/ms190174.aspx
Incorrect:
Not A: Dynamic management views and functions return server state information that can be used to monitor the health
of a server instance, diagnose problems, and tune performance.
http://technet.microsoft.com/en-us/library/ms188754(v=sql.120).aspx
QUESTION 17
You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
Rebuild the indexes by using a new fill factor.
Run a custom T-SQL command.
Back up the databases.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy
Correct Answer: C
Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly
backed up, and free of inconsistencies. Reference: Maintenance Plans
QUESTION 18
You are creating a database that will store usernames and credit card numbers for an application.
You need to recommend a solution to store and reuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Data encryption
B. Transparent Data Encryption (TDE)
C. Encrypting File System (EPS)
D. Data hashing
Correct Answer: B
If we are going to encrypt credit card number for storage, then we should have Data Encryption Key (DEK) for
encrypting the credit card number. Reference: http://msdn.microsoft.com/en-us/library/bb934049.aspx
QUESTION 19
You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify the isolation level used by the query when a deadlock occurs.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_requests dynamic management view.
B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
C. Query the sys.dm_exec_sessions dynamic management view.
D. Enable trace flag 1222, and then view the SQL Server error log.
Correct Answer: C
* sys.dm_exec_sessions Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-
scope view that shows information about all active user connections and internal tasks. Include the column:
transaction_isolation_level smallint Transaction isolation level of the session. 0 = Unspecified 1 = ReadUncomitted 2 =
ReadCommitted 3 = Repeatable 4 = Serializable 5 = Snapshot Is not nullable.
Reference: sys.dm_exec_sessions (Transact-SQL)
QUESTION 20
You have a database that stores information for a shipping company. You create a table named Customers by running
the following Transact-SQL statement. (Line numbers are included for reference only.)
You need to ensure that salespeople can view data only for the customers that are assigned to them. Which Transact-
SQL segment should you insert at line 07?
A. RETURNS varchar(20)WITH Schemabinding
B. RETURNS dbo.CustomersORDER BY @salesPerson
C. RETURNS tableORDER BY @salesPerson
D. RETURNS tableWITH Schemabinding
Correct Answer: D
The return value can either be a scalar (single) value or a table. SELECT 1 just selects a 1 for every row, of course.
What it\\’s used for in this case is testing whether any rows exist that match the criteria: if a row exists that matches the
WHERE clause, then it returns 1, otherwise it returns nothing. Specify the WITH SCHEMABINDING clause when you
are creating the function. This ensures that the objects referenced in the function definition cannot be modified unless
the function is also modified.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact- sql
QUESTION 21
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 need to configure a Microsoft SQL Server instance to ensure that a user named Mail1 can send mail by using
Database Mail.
Solution: You add the DatabaseMailUserRole to Mail1 in the msdb database.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database in order to prevent anyone
from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a
member of DatabaseMailUserRole in order to send emails with the exception of sysadmin who has all privileges.
Note: Database Mail was first introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found
in previous versions.
References:http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administrati on/DBA_20.shtml
QUESTION 22
You have two SQL Server 2016 instances named SQLDev and SQLProd.
You plan to create a new database on SQLProd that will use SQL Server Authentication.
You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the
database on SQLDev even if they do not have a login on the SQLDev instance.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Triggers
B. Contained database
C. SQL Server Analysis Services (SSAS) scripts
D. Extended Events
E. SQL Server Integration Services (SSIS) scripts
Correct Answer: B
A fully contained database includes all the settings and metadata required to define the database and has no
configuration dependencies on the instance of the SQL Server Database Engine where the database is installed.
Reference: Contained Databases
QUESTION 23
You deploy a SQL Server instance named SQLProd that uses SQL Server 2016.
You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution
must minimize the amount of custom code required.
What should you recommend?
A. Statistics
B. A dynamic management view
C. A trigger
D. User-defined views
Correct Answer: B
Dynamic management views and functions return server state information that can be used to monitor the health of a
server instance, diagnose problems, and tune performance. Transactions can be monitored.
Reference: Dynamic Management Views and Functions (Transact-SQL)
QUESTION 24
You have a SQL Server 2016 database named DB1.
You plan to import a large number of records from a SQL Azure database to DB1.
You need to recommend a solution to minimize the amount of space used in the transaction log during the import
operation.
What should you include in the recommendation?
A. The bulk-logged recovery model
B. The full recovery model
C. A new partitioned table
D. A new log file
E. A new file group
Correct Answer: A
Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs
bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media
failure
and, for bulk operations, provides the best performance and least log space usage.
Note:
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.
Reference: Recovery Models (SQL Server)
QUESTION 25
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.
A company has a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts
a database named DB1.
You need to ensure that you can perform auditing at the database level for DB1.
Solution: You migrate DB1 to a named instance on a server than runs Microsoft SQL Server 2016 Standard edition.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL
Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.
References: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql- server-audit-database-
engine
QUESTION 26
You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The
databases are stored on a server named SQL1 that has SQL Server 2016 installed.
You plan to deploy an additional server that has SQL Server 2016 installed.
You need to design a high-availability solution for the databases that meets the following requirements:
If SQL1 fails, the databases must be available.
Users must be able to run reports against a secondary copy of the databases.
What should you include in the design?
More than one answer choice may achieve the goal. Select the BEST answer.
A. AlwaysOn availability groups
B. Database mirroring
C. Log shipping
D. Failover Clustering
Correct Answer: A
The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-
level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the
availability of a set of user databases for an enterprise. An availability group supports a failover environment for a
discrete set of user databases, known as availability databases that fail over together.
Reference: Failover and Failover Modes (AlwaysOn Availability Groups)
QUESTION 27
You deploy a database by using SQL Server 2016. The database contains a table named Table1.
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
Correct Answer: A
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.
Reference: About Change Data Capture (SQL Server)
QUESTION 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.
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 rebuild all indexes.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
You can remedy index fragmentation by either reorganizing an index or by rebuilding an index.
References:https://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx
QUESTION 29
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 30
You are creating a database that will store usernames and passwords for an application.
You need to recommend a solution to store the passwords in the database.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. One-way encryption
B. Encrypting File System (EFS)
C. Transparent Data Encryption (TDE)
D. Reversible encryption
Correct Answer: C
*
Transparent Data Encryption (TDE) is a special case of encryption using a symmetric key. TDE encrypts an entire
database using that symmetric key called the database encryption key. The database encryption key is protected by
other keys or certificates which are protected either by the database master key or by an asymmetric key stored in an
EKM module.
*
SQL Server provides the following mechanisms for encryption: Transact-SQL functions Asymmetric keys Symmetric
keys Certificates Transparent Data Encryption
Reference: Transparent Data Encryption (TDE)
QUESTION 31
You create a stored procedure that retrieves all of the rows from a table named Table1.
You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if
another transaction is modifying rows in Table1 simultaneously. What should you recommend?
A. Snapshot isolation
B. A database snapshot
C. Filegroups
D. Indexes
Correct Answer: A
Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique
transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version.
The transaction works with the most recent row versions having a sequence number before the sequence number of the
transaction. Newer row versions created after the transaction has begun are ignored by the transaction.
Reference: Transaction Isolation Levels
QUESTION 32
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 collect performance metrics on multiple Microsoft SQL Server instances and store the data in a single repository.
You need to examine disk usage, query statistics, and server activity without building custom counters.
What should you use?
A. Activity Monitor
B. Sp_who3 stored procedure
C. Object Explorer in the Microsoft SQL Server Management Studio (SSMS)
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Correct Answer: D
The data collector is a core component of the data collection platform for SQL Server 2017 and the tools that are
provided by SQL Server. The data collector provides one central point for data collection across your database servers
and applications. This collection point can obtain data from a variety of sources and is not limited to performance data
QUESTION 33
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 monitor WingDB and gather information for troubleshooting issues. What should you use?
A. sp_updatestats
B. sp_lock
C. sys.dm_os_waiting_tasks
D. sys.dm_tran_active_snapshot_database_transactions
Correct Answer: B
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are
happening on your system. This procedure returns much of its information from the syslock info in the master database,
which is a system table that contains information on all granted, converting, and waiting lock requests.
Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development
work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL
Server
Database Engine, use the sys.dm_tran_locks dynamic management view.
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later.
Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be
granted.
References:https://docs.microsoft.com/en-us/sql/relational-databases/system-stored- procedures/sp-lock-transact-sql
QUESTION 34
You administer a SQL Server 2016 instance.
Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day,
which could be causing issues.
You want to write a query of the system views that will report the following:
Number of users that have a connection to the server Whether a user\\’s connection is active Whether any connections
are blocked What queries are being executed
Whether the database restore is still executing and, if it is, what percentage of the restore is complete.
Which system objects should you use in your query to best achieve this task?
A. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
B. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
C. sys.sysprocesses, sys.dm_exec_query_text, sys.objects
D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text
Correct Answer: D
*
sys.dm_exec_requests
Returns information about each request that is executing within SQL Server.
*
sys.dm_exec_sessions
Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows
information about all active user connections and internal tasks. This information includes client version, client program
name, client login time, login user, current session setting, and more.
*
sys.dm_exec_query_text
Returns the text of the SQL batch that is identified by the specified sql_handle.
sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_sessions (Transact-SQL)
Incorrect:
*
sys.dm_exec_query_stats
Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query
statement within the cached plan, and the lifetime of the rows are tied to the plan itself.
*
sys.objects
Contains a row for each user-defined, schema-scoped object that is created within a database.
QUESTION 35
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 observe that several indexes are fragmented.
You need to rebuild the indexes.
What should you use?
A. Activity Monitor
B. Sp_who3
C. Object Explorer in the SQL Server Management Studio (SSMS)
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Correct Answer: C
How to: Rebuild an Index (SQL Server Management Studio) To rebuild an index References:
https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx
QUESTION 36
You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2016 to Server1. Each instance will have multiple databases.
You need to recommend a method to allocate processor time to each database.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Resource Governor
B. Max Degree of Parallelism
C. Windows System Resource Manager (WSRM)
D. Processor affinity
Correct Answer: A
SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource
consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that
incoming application requests can use.
Reference: Resource Governor
Incorrect:
D: PROCESS AFFINITY
Enables hardware threads to be associated with CPUs.
QUESTION 37
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.
A company has a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts
a database named DB1.
You need to ensure that you can perform auditing at the database level for DB1.
Solution: You migrate DB1 to the default instance on a server that runs Microsoft SQL Server 2016 Standard edition.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL
Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.
References:https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql- server-audit-database-
engine
QUESTION 38
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.
Clients connect to databases by using line-of-business applications. Developers connect by using SQL Server
Management Studio (SSMS).
You need to provide permissions to a service account that will be used to provision a new database for a client.
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
Correct Answer: C
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
References:https://docs.microsoft.com/en-us/sql/relational- databases/security/authentication-access/server-level-roles
QUESTION 39
You plan to create a database.
The database will be used by a Microsoft .NET application for a special event that will last for two days.
During the event, data must be highly available.
After the event, the database will be deleted.
You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any
existing applications.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. SQL Server 2016 Enterprise
B. SQL Server 2016 Standard
C. SQL Azure
D. SQL Server 2016 Express with Advanced Services
Correct Answer: B
Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL) supported by Standard and Enterprise editions only.
Reference: Features Supported by the Editions of SQL Server 2016
QUESTION 40
You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements:
The strategy must not disrupt backup operations. DB1 must be online while the changes are deployed. You must be
able to undo quickly any changes made to objects.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database
to another server and recover the original objects from the restored database.
B. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
C. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
D. Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to
another server and recover the original objects from the restored database.
Correct Answer: C
Reference:
Database Snapshots (SQL Server)
Share 40 of the latest and effective 70-764 exam dumps and Practice test questions for free,100% real and effective exam questions and answers! Get the full 70-764 dumps:https://www.leads4pass.com/70-764.html
(Total questions:380 Q&A)
[PDF] Free Microsoft MCSA 70-764 pdf dumps download from Google Drive: https://drive.google.com/open?id=10hn31bPob_2072mBH7U_7yOGGE02joOP
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Lead4pass Promo Code 12% Off
related: https://www.certificationdemo.com/new-cisco-642-998-dumps-pdf/