NEW QUESTION 1

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database.
You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?

• A. User mapped to a certificate
• B. SQL user without login
• C. Domain user
• D. SQL user with login

Answer: C

NEW QUESTION 2

Overview
You are a database administrator for a company named Litware, Inc.
Litware is a book publishing house. Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed. The web application will be accessed at www.litwareinc.com. Both internal employees and external partners
will use the application.
You have an existing desktop application that uses a SQL Server 2008 database named App1_DB. App1_DB will remain in production.
Requirements Planned Changes
You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.
All database files will be stored in a highly available SAN. Database1 will contain two tables named Orders and OrderDetails.
Database1 will also contain a stored procedure named usp_UpdateOrderDetails.
The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.
The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.
Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.
Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.
Business Requirements
You have the following requirements:
Costs for new licenses must be minimized.
Private information that is accessed by Application must be stored in a secure format.
Development effort must be minimized whenever possible.
The storage requirements for databases must be minimized.
System administrators must be able to run real-time reports on disk usage.
The databases must be available if the SQL Server service fails.
Database administrators must receive a detailed report that contains allocation errors and data corruption.
Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.
You must encrypt the backup files to meet regulatory compliance requirements.
The encryption strategy must minimize changes to the databases and to the applications. You need to recommend a disk monitoring solution that meets the business requirements. What should you include in the recommendation?

• A. a SQL Server Agent alert
• B. a dynamic management view
• C. a maintenance plan
• D. an audit

Answer: B

Explanation:
Dynamic Management Views and Functions (Transact-SQL)

NEW QUESTION 3

You administer all the deployments of Microsoft SQL Server 2021 in your company. A database contains a large product catalog that is updated periodically.
You need to be able to send the entire product catalog to all branch offices on a monthly basis. Which configuration should you use?

• A. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
• B. SQL Server that includes an application database configured to perform transactional replication
• C. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary
• D. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
• E. SQL Server that includes an application database configured to perform snapshot replication
• F. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
• G. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• H. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode

Answer: E

Explanation:
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Using snapshot replication by itself is most appropriate when one or more of the following is true:
Data changes infrequently.
It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
Replicating small volumes of data.
A large volume of changes occurs over a short period of time.
References: https://docs.microsoft.com/en-us/sql/relational-databases/replication/snapshot-replication

NEW QUESTION 4

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.
Start of repeated scenario
You are a database administrator for a company that has an on-premises Microsoft SQL Server environment There are two domains in separate forests. There are no trust relationships between the domains. The environment hosts several customer databases, and each customer uses a dedicated instance running SQL Server 2021 Standard edition. The customer environments are shown in the following table.

End of repeated scenario
You need to configure the Contoso instance.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 5

You are planning to deploy a database to Windows Azure SQL Database.
You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:
If more than one row is updated, an error must be raised to the application and the update must be discarded.
The stored procedure must be designed to maximize concurrency.
What should you include in the design? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
- Read Committed is SQL Server's default isolation level.
- @@ROWCOUNT eturns the number of rows affected by the last statement.
- Using TRY...CATCH in a transaction
The following example shows how a TRY...CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.

- Generate a constraint violation error.

NEW QUESTION 6

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
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

Answer: B

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

NEW QUESTION 7

You plan to deploy a database by using SQL Server 2014. Your company identifies the following requirements for the database:
The name of all stored procedures must start with "usp_"always.
All distribution statistics must be updated daily
You need to identify which feature must be used to meet each database requirement.
Which features should you identify? To answer, drag the appropriate feature to the correct database requirement in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
- Policy-Based Management Each Stored Procedure that are created and that will be created has to have prefix "USP_".
- Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

NEW QUESTION 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 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 assign the db_datareader role 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 Management Views.
References: https://technet.microsoft.com/en-us/library/cc280665(v=sql.105).aspx

NEW QUESTION 9

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:

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

NEW QUESTION 10

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
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 view the information about the corrupted pages on SalesDb3. How should you complete the Transact-SQL statement?
Select two.

• A. SELECT * FROM msdb.. corrupted_pages
• B. SELECT * FROM msdb..suspect_pages
• C. SELECT * FROM system..corrupted_pages
• D. SELECT * FROM system..suspect_pages
• E. WHERE event_type = 1
• F. WHERE event_type = 2
• G. WHERE event_type = 3

Answer: BF

Explanation:
suspect_pages contains one row per page that failed with a minor 823 error or an 824 error. Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is updated in the event_type column.
The suspect_pages table resides in the msdb database.
SalesDb3 has pages with checksum errors. Checksum errors have the event_type value 2. References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-ser

NEW QUESTION 11

You work as a Database Administrator (DBA) for a company named ABC.com.
The company uses a Microsoft SQL Server 2012 infrastructure. Computer users use custom applications that store data in SQL Server databases hosted on a server named ABC-SQL1.
You discover that the Windows Application event log on ABC-SQL1 contains error events.
You need to configure a solution that will run an application whenever an event with a specific event ID number is generated in the Windows Application event log.
What should you configure?

• A. You should configure an alert and a job.
• B. You should configure an alert and a maintenance plan.
• C. You should configure a trigger and a maintenance plan.
• D. You should configure a trigger and a job.
• E. You should configure an alert and a trigger.

Answer: A

NEW QUESTION 12

Overview
You are a database administrator for a company named Litware, Inc.
Litware is a book publishing house. Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed. The web application will be accessed at www.litwareinc.com. Both internal employees and external partners
will use the application.
You have an existing desktop application that uses a SQL Server 2008 database named App1_DB. App1_DB will remain in production.
Requirements Planned Changes
You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.
All database files will be stored in a highly available SAN.
Database1 will contain two tables named Orders and OrderDetails.
Database1 will also contain a stored procedure named usp_UpdateOrderDetails.
The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.
The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.
Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.
Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.
Business Requirements
You have the following requirements:
Costs for new licenses must be minimized.
Private information that is accessed by Application must be stored in a secure format.
Development effort must be minimized whenever possible.
The storage requirements for databases must be minimized.
System administrators must be able to run real-time reports on disk usage.
The databases must be available if the SQL Server service fails.
Database administrators must receive a detailed report that contains allocation errors and data corruption.
Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.
You must encrypt the backup files to meet regulatory compliance requirements.
The encryption strategy must minimize changes to the databases and to the applications.
You need to recommend a solution for Application1 that meets the security requirements. What should you include in the recommendation?

• A. Encrypted columns
• B. Certificate Authentication
• C. Secure Socket Layer (SSL)
• D. Signed stored procedures

Answer: D

Explanation:
- Scenario:
- Data from Database2 will be accessed periodically by an external application named Application1
- Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.
Tutorial: Signing Stored Procedures with a Certificate

NEW QUESTION 13

Contoso. Ltd. has Microsoft SQL Server databases that support a custom application. The current SQL Server environment consists of two servers: ContosoSQL1 and ComosoSQL2. These two servers participate in an Always On Availability Group named ContosoAG1 that is configured to use synchronous-commit with automatic failover. The secondary replica is not configured for read-only access.
The application performs both transactional processing and historical data retrieval in a database named ContosoDB. The application includes an inventory management module. The inventory management module and database have experienced performance issues.
Users report mat a Query named inventoryQuery1 takes a long time to complete. The query is shown as follows:
SELECT ProductNumber, Name, ProductLine FROM Production.Product
WHERE ProductNumber = N’ <specific product>’
The query plan used by SQL Server for this query is shown in the exhibit.

Various Performance issues, including frequent long-term blocking episodes, prevent business users from completing their daily tasks. You suspect the tempdb database resources could be responsible. You must create blocking reports for the contosoDB database to identify issues.
You plan to use Extended Events to review all Transact SQL statements that are run against the ContosoSQL1 instance. The output from the Extended Events session must contain both start and stop events and must be written to a file. You must configure the Extended Events session to minimize possible data loss and reduce the effect on server performance.
You plan to deploy an additional secondary replica named contosoSQL3 to ContosoAG1. Ready-only traffic must be load-balanced between the two secondary replicas, regardless of which instance is the primary replica. Contoso plans to add an additional dedicated reporting system that will reply on real-time data from the transactional database.
The company plans to improve their high availability/disaster recovery (HA/DR) solution. As part of the planned improvements, you will back up all databases from ContosoSQL1 directory to an off-site location.
You need to create the blocking reports.
Which three actions should you perform in sequence?

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 14

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

Answer: A

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

NEW QUESTION 15

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2021. You attempt to add the [CorpnetUser1] login to the database.
However, you receive the following error message: "User already exists in current database."
You need to configure the [CorpnetUser1] login to be able to access the Orders database and retain the original permissions.
You need to achieve this goal by using the minimum required permissions. Which Transact-SQL statement should you use?

• A. DROP USER [User1]; CREATE USER [CorpnetUser1] FOR LOGIN [CorpnetUser1]; ALTER ROLE [db_owner] ADD MEM3ER [CorpnetUser1];
• B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [CorpnetUser1];
• C. ALTER USER [CorpnetUser1] WITH LOGIN [CorpnetUser1];
• D. ALTER ROLE [db owner] ADD MEMBBR [CorpnetUser1];

Answer: C

NEW QUESTION 16

Overview
Application Overview
Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.
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
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 security requirement. What should you recommend?

• A. Revoke user permissions on the table
• B. Create stored procedures that manipulate dat
• C. Grant the users the EXECUTE permission on the stored procedures.
• D. Grant the users the SELECT permission on the table
• E. Create views that retrieve data from the tables.Grant the users the SELECT permission on the views.
• F. Deny the users SELECT permission on the table
• G. Create views that retrieve data from the table
• H. Grant the users the SELECT permission on the views.
• I. Deny the users the SELECT permission on the table
• J. Create stored procedures that manipulate data.Grant the users the EXECUTE permission on the stored procedures.

Answer: C

Explanation:
- Security Requirements
You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.

NEW QUESTION 17

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it As a result, these questions 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

NEW QUESTION 18

You have a database named WideWorldlmporters that contains the following tables: TableA and TableB. Both tables are legacy tables. TableA contains a column that uses the ntext data type, TableB contains a column that uses the nvarchar(max) data type.
The indexes in each table have become highly fragmented. You must reduce the fragmentation in both tables. You must permit concurrent user Queries during defragmentation even if the queues take longer to complete than usual.
When possible, the REBUILD option is preferred. Table statistics must be updated at the end of this maintenance.
In the table below, identify the strategy that must be used for each table.
NOTE: Make only one selection in each column, fcach correct selection is worth one point

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 19

You plan to install two SQL Server 2012 environments named Environment1 and Environment2. Your company identifies the following availability requirements for each environment:
Environment1 must have mirroring with manual failover implemented.
Environment2 must have AlwaysOn with automatic failover implemented.
You need to identify the minimum number of SQL Server 2012 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.
How many servers should you identify? To answer, drag the appropriate number to the correct environment in the answer area

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Environment1 – 2; Environment2 - 2 References:
http://msdn.microsoft.com/en-us/library/ms189852.aspx
http://msdn.microsoft.com/en-us/library/hh510230.aspx

NEW QUESTION 20

You have the following Microsoft SQL Server instances:

You have a database named DB1 that is hosted on SQL1.
Users in the branch office must be able to access DB1 from SQL3.
You need to configure an AlwaysOn Availability Group and limit latency on the secondary server. Which settings should you configure?

• A. FAILOVER_MODE = AUTOMATIC
• B. AVAILABILITY MODE = SYNCHRONOUS_COMMIT
• C. FAILOVER_MODE = MANUAL
• D. AVAILABILITY MODE = ASYNCHRONOUS_COMMIT

Answer: D

Explanation:
Asynchronous-commit mode is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances. If every secondary replica is running under asynchronous-commit mode, the primary replica does not wait for any of the secondary replicas to harden the log. Rather, immediately after writing the log record to the local log file, the primary replica sends the transaction confirmation to the client. The primary replica runs with minimum transaction latency in relation to a secondary replica that is configured for asynchronous-commit mode. If the current primary is configured for asynchronous commit availability mode, it will commit transactions asynchronously for all secondary replicas regardless of their individual availability mode settings.

NEW QUESTION 21

You administer a single server that contains a Microsoft SQL Server 2021 default instance on which several production databases have been deployed.
You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions. You need to ensure that the login for the
ticketing application cannot access other production databases.
What should you do?

• A. Use the SQL Server default instance and enable Contained Databases.
• B. Use the SQL Server default instance and configure a user-defined server rol
• C. Add the login for the ticketing application to this role.
• D. Install a new named SQL Server instance on the server.
• E. Install a new default SQL Server instance on the server.

Answer: C

Explanation:
SQL Server supports multiple instances of SQL Server on a single server or processor, but only one instance can be the default instance. All others must be named instances. A computer can run multiple instances of SQL Server concurrently, and each instance runs independently of other instances.
References: https://msdn.microsoft.com/en-us/library/ms143531(v=SQL.105).aspx

NEW QUESTION 22

You are designing a monitoring application for a new SQL Server 2014 instance.
You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

• A. The SQL Server error log
• B. The sys.dm_os_wait_stats dynamic management view
• C. The DBCC SQLPERF(WAITSTATS) command
• D. SQL Server Profiler

Answer: B

Explanation:
sys.dm_os_wait_stats
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
Columns include: waiting_tasks_count
Number of waits on this wait type.
This counter is incremented at the start of each wait.

NEW QUESTION 23

You are deploying a Microsoft Azure SQL Database environment:
You need to configure the environment to meet the following requirements:
Access to databases must be logged.
Email alerts must be sent to the database administrator if anomalous activity is detected.
What should you do? To answer, drag the appropriate solutions to the correct requirements. Each solution 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: Enable database auditing Box 2: Enable threat detection
SQL Database Threat Detection runs multiple sets of algorithms which detect potential vulnerabilities and SQL injection attacks, as well as anomalous database access patterns (such as access from an unusual location or by an unfamiliar principal). Security officers or other designated administrators get email notification once a threat is detected on the database. Each notification provides details of the suspicious activity and recommends how to further investigate and mitigate the threat.
References:

NEW QUESTION 24

You plan to deploy SQL Server 2012. You must create two tables named Table 1 and Table 2 that will have the following specifications:
Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
Table2 will contain a column named Column2 that is the product of two other columns in Table2. Both Table1 and Table2 will contain more than 1 million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables.
Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Column1 – Sparse; Column2 - Computed
- Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.
- A Persisted column would be faster to retrieve.
- A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change.
References:
http://msdn.microsoft.com/en-us/library/cc280604.aspx http://msdn.microsoft.com/en-us/library/ms186241.aspx

NEW QUESTION 25

You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. You add two Windows groups as logins for the server:
CORPEmployees - All company employees
CORPHRAdmins - HR administrators only
HR Administrators are also company employees.
You need to grant users access according to the following requirements:
CORPEmployees should have SELECT access to the Employees table.
Only users in CORPHRAdmins should have SELECT access to the SalaryDetails table.
Logins are based only on Windows security groups.
What should you do?

• A. Create a database role called Employees.Add CORPEmployees to the db_datareader role.Add all company employees except HR administrators to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.
• B. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader role, Add all HR administrators to the HRAdmins role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.
• C. Create two database roles: Employees and HRAdmins.Add all company employees to the Employees role.Add HR administrators to the HRAdmins role.Grant SELECT access to all tables except SalaryDetails to the Employees role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the Employees role.
• D. Create a database role called Employees.Add all HR administrators to the db_datareader role.Add all company employees to the Employees role.Grant SELECT access to all tables except the SalaryDetails table to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.

Answer: D

NEW QUESTION 26

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

Answer: D

Explanation:
The data collector is a core component of the data collection platform for SQL Server 2021 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

NEW QUESTION 27

You plan to install two SQL Server 2014 environments named Environment1 and Environment2. Your company identifies the following availability requirements for each environment:
Environment1 must have mirroring with automatic failover implemented.
Environment2 must have Always On with automatic failover implemented.
You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.
How many servers should you identify? To answer, drag the appropriate number to the correct environment in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Environment1 – 3; Environment2 - 2

NEW QUESTION 28

You manage a Microsoft SQL Server instance. You have a user named User1.
You need to grant the minimum permissions necessary to allow User1 to review audit logs.
For each action, which option should you use? To answer, select the appropriate options in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: securityadmin
To access log files for instances of SQL Server that are online, this requires membership in the securityadmin fixed server role.
Box 2: sys.server_audit_specifications
sys.server_audit_specifications contains information about the server audit specifications in a SQL Server audit on a server instance.

NEW QUESTION 29
......