Which isolation levels should you recommend?

DRAG DROP
You plan to deploy SQL Server 2012.
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.
? Prevent USP_2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure. The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.

image216 - Which isolation levels should you recommend?

Answer:

image218 - Which isolation levels should you recommend?
Explanation:
Note:
* REPEATABLE READ
This isolation level includes the guarantees given by SNAPSHOT isolation level. In addition, REPEATABLE READ guarantees that for any row that is read by the transaction, at the time the transaction commits the row has not been changed by any other transaction. Every read operation in the transaction is repeatable up to the end of the transaction.
* Committed Read is SQL Server’s default isolation level. It ensures that an operation will never read data another application has changed but not yet committed.
Reference: Transaction Isolation Levels

How should you complete the Transact-SQL statement?

HOTSPOT
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.

image008 - 
How should you complete the Transact-SQL statement?
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.

image014 - 
How should you complete the Transact-SQL statement?

Answer:

image016 - 
How should you complete the Transact-SQL statement?
Explanation:
Box 1: CREATE SERVER AUDIT
Create the 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 AUDIIT
You must implement auditing for all objects in the ADVSchema.

Which code should you use?

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?

image154 - 
Which code should you use?
A. Option A
B. Option B
C. Option C
D. Option D
D. Option E

Answer: D
Explanation:
Structure should be:
BEGIN TRY
BEGIN TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH.
Reference: TRY…CATCH (Transact-SQL)

What should you recommend?

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

Answer: D
Explanation:
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

Which five Transact-SQL segments should you use to develop the solution?

DRAG DROP
You are the database administrator for a Microsoft SQL Server instance. You develop an Extended Events package to look for events related to application performance. You need to change the event session to include SQL Server errors that are greater than error severity 15.
Which five 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.

image127 - Which five Transact-SQL segments should you use to develop the solution?

Answer:

image129 - Which five Transact-SQL segments should you use to develop the solution?
Explanation:
Step 1: ALTER EVENT SESSION Contoso1 ON SERVER
Step 2: ADD EVENT …
Step 3: (ACTION …
Step 4: WHERE…
Step 5: ) GO
Example: To start an Extended Events sessions in order to trap SQL Server errors with severity greater than 10,just run the following script:
CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username, sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
WHERE ([severity]>10)
)
ADD TARGET package0.event_file
(
SET filename=N’D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap.xel’
)
WITH
(
STARTUP_STATE=OFF
)
GO
References:

Which two tools should you use?

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

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

Which system objects should you use in your query to best achieve this task?

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

Answer: D
Explanation:
* 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.
References:
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.

Which upgrade steps should you recommend?

DRAG DROP
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.
You need to upgrade the database to SQL Server 2016. The solution must minimize downtime.
Which upgrade steps should you recommend?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

image200 - Which upgrade steps should you recommend?

Answer:

image202 - Which upgrade steps should you recommend?
Explanation:
Note:
* To perform the rolling upgrade
Step 1: For each mirroring session, whose mirror server instance has just been upgraded, wait for the session to synchronize. Then, connect to the principal server instance, and manually fail over the session.
Step 2: Upgrade each server instance that is now the mirror server in all mirroring sessions in which it is a partner.
Step 3: Resume the mirroring session.
* When upgrading server instances to SQL Server 2016, you can reduce downtime for each mirrored database to only a single manual failover by performing a sequential upgrade, known as a rolling upgrade. A rolling upgrade is a multi-stage process that in its simplest form involves upgrading the server instance that is currently acting as the mirror server in a mirroring session, then manually failing over the mirrored database, upgrading the former principal server, and resuming mirroring
Reference: Minimize Downtime for Mirrored Databases When Upgrading Server Instances

HOTSPOT

HOTSPOT
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 have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

image025 - HOTSPOT
You have an Always On Availability group named AG1.
The details for AG1 are shown in the following table.

image027 - HOTSPOT
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups.
The recovery point objective (RPO) for each instance is shown in the following table.

image029 - HOTSPOT
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
The wait statistics monitoring requirements for the instances are described in the following table.

image031 - HOTSPOT
You need to create the connection strings for the operations and reporting systems.
In the table below, identify the option that must be specified in each connection string.
NOTE: Make only one selection in each column.

image033 - HOTSPOT

Answer:

image035 - HOTSPOT
Explanation:
Reporting system: Connect to any current read-only replica instance
We configure Read-OnlyAccess on an Availability Replica. We select Read-intent only. Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
From Scenario: Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
Operating system: Connect to the current primary replica SQL instance
By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group.
From scenario: Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
References:https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server

What should you specify when you run the recovery command?

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

Answer: A