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?

Reveal Solution Hide Solution

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 processed in an on-premises Microsoft SQL Server Database Engine instance.

You need to return information about processes that are not idle, that belong to a specific user, or that belong to a specific session.

What should you use?

Reveal Solution Hide Solution

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?

Reveal Solution Hide Solution

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?

Reveal Solution Hide Solution

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?

Reveal Solution Hide Solution

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:

– a list of all databases on the SQL Server instance, along with information about the database files, their paths, and names

– a list of the queries recently executed that use most of memory, disk, and network resources

What should you use?

Reveal Solution Hide Solution

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 have an on-premises server that runs Microsoft SQL Server 2016 Standard Edition.

You need to identify missing indexes.

What should you use?

Reveal Solution Hide Solution

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.

You need to configure monitoring for Tailspin Toys. In the table below, identify the monitoring tool that you must use for each activity. NOTE: Make only one selection in each column.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Monitoring from application: Transact-SQL

Transact-SQL can be used to monitor a customized application.

Trend analysis: System Monitor

System Monitor can provide trend analysis.

From question:

Tailspin Toys has a custom application that accesses a hosted database named TSpinDB. The application

will monitor TSpinDB and capture information over time about which database objects are accessed and

how frequently they are accessed.

References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/performancemonitoring-

and-tuning-tools

DRAG DROP

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance.

The environments that you manage are shown in the following table.

You need to implement a process for importing data into WingDB.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Step 1: Perform a full backup of the database and enable the bulk-logged recovery model.

Not: Simple recovery model. With the Simple recovery model we cannot minimize data loss.

Step 2: Import the data

Step 3: Backup the tail of the transaction log. For databases that use full and bulk-logged recovery, database backups are necessary but not sufficient. Transaction log backups are also required.

Note: Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models­sql-server

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.

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.

Reveal Solution Hide Solution

Correct Answer:

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.

References: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server­audit-and-database-audit-specification