NEW QUESTION 1

You administer a Microsoft SQL Server 2021 database that includes a table named dbo.Log. This table contains millions of records about user activity in an application.
Records in dbo.Log that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention withinserts.
You need to be able to modify dbo.Log without requiring any changes to the applications that utilize dbo.Log. Which type of solution should you use?

• A. Extended events
• B. Columnstore index
• C. Partitioned tables
• D. Read committed snapshot

Answer: C

NEW QUESTION 2

You have an SQL Server 2014 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.
References: https://msdn.microsoft.com/en-us/library/ms188246.aspx

NEW QUESTION 3

You have two SQL Server 2012 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 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. SQL Server Integration Services (SSIS) scripts
• B. Extended Events
• C. Triggers
• D. SQL Server Analysis Services (SSAS) scripts
• E. Contained database

Answer: E

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

NEW 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 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 5

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 unavailable to users while the changes are deployed.
You must be able to undo quickly the entire operation.
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.

Answer: C

NEW QUESTION 6

Background Corporate Information
Fabrikam, Inc. is a retailer that sells electronics products on the Internet. The company has a headquarters site and one satellite sales office. You have been hired as the database administrator, and the company wants you to change the architecture of the Fabrikam ecommerce site to optimize performance and reduce downtime while keeping capital expenditures to a minimum. To help with the solution, Fabrikam has decided to use cloud resources as well as on-premise servers.
Physical Locations
All of the corporate executives, product managers, and support staff are stationed at the headquarters office. Half of the sales force works at this location. There is also a satellite sales office. The other half of the sales force works at the satellite office in order to have sales people closer to clients in that area. Only sales people work at the satellite location.
Problem Statement
To be successful, Fabrikam needs a website that is fast and has a high degree of system uptime. The current system operates on a single server and the company is not happy with the single point of failure this presents. The current nightly backups have been failing due to insufficient space on the available drives and manual drive cleanup often needing to happen to get past the errors. Additional space will not be made available for backups on the HQ or satellite servers. During your investigation, you discover that the sales force reports are causing significant contention.
Configuration Windows Logins
The network administrators have set up Windows groups to make it easier to manage security. Users may belong to more than one group depending on their role. The groups have been set up as shown in the following table:

Server Configuration The IT department has configured two physical servers with Microsoft Windows Server 2012 R2 and SQL Server 2014 Enterprise Edition and one Windows Azure Server. There are two tiers of storage available for use by database files only a fast tier and a slower tier. Currently the data and log files are stored on the fast tier of storage only. If a possible use case exists, management would like to utilize the slower tier storage for data files. The servers are configured as shown in the following table:

Database
Currently all information is stored in a single database called ProdDB, created with the following script:

The Product table is in the Production schema owned by the ProductionStaff Windows group. It is the main table in the system so access to information in the Product table should be as fast as possible. The columns in the Product table are defined as shown in the following table:

The SalesOrderDetail table holds the details about each sale. It is in the Sales schema owned by the SalesStaff Windows group. This table is constantly being updated, inserted into, and read. The columns in the SalesOrderDetail table are defined as shown in the following table:

Database Issues
The current database does not perform well. Additionally, a recent disk problem caused the system to go down, resulting in lost sales revenue. In reviewing the current system, you found that there are no automated maintenance procedures. The database is severely fragmented, and everyone has read and write access.
Requirements Database
The database should be configured to maximize uptime and to ensure that very little data is lost in the event of a server failure. To help with performance, the database needs to be modified so that it can support in-memory data, specifically for the Product table, which the CIO has indicated should be a memory optimized table. The auto-update statistics option is set off on this database. Only product managers are allowed to add products or to make changes to the name, description, price, cost, and supplier. The changes are made in an internal database and pushed to the Product table in ProdDB during system maintenance time. Product managers and others working at the headquarters location also should be able to generate reports that include supplier and cost information.
Customer data access
Customers access the company's website to order products, so they must be able to read product information such as name, description, and price from the Product table. When customers place orders, stored procedures called by the website update product quantity on-hand values. This means the product table is constantly updated at random times.
Customer support data access
Customer support representatives need to be able to view and not update or change product information. Management does not want the customer support representatives to be able to see the product cost or any supplier information.
Sales force data access
Sales people at both the headquarters office and the satellite office must generate reports that read from the Product and SalesOrderDetail tables. No updates or inserts are ever made by sales people. These reports are run at random times and there can be no reporting downtime to refresh the data set except during the monthly
maintenance window. The reports that run from the satellite office are process intensive queries with large data sets. Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet committed should be ignored.
Historical Data
The system should keep historical information about customers who access the site so that sales people can see how frequently customers log in and how long they stay on the site.
The information should be stored in a table called Customer Access. Supporting this requirement should have minimal impact on production website performance.
Backups
The recovery strategy for Fabrikam needs to include the ability to do point in time restores and minimize the risk of data loss by performing transaction log backups every 15 minutes.
Database Maintenance
The company has defined a maintenance window every month when the server can be unavailable. Any maintenance functions that require exclusive access should be accomplished during that window.
Project milestones completed
Revoked all existing read and write access to the database, leaving the schema ownership in place.
Configured an Azure storage container secured with the storage account name MyStorageAccount with the primary access key StorageAccountKey on the cloud file server.
SQL Server 2014 has been configured on the satellite server and is ready for use.
On each database server, the fast storage has been assigned to drive letter F:, and the slow storage has been assigned to drive letter D:.
You need to distribute functionality across the three servers. Which function should you assign to each server? To answer, drag the appropriate functions to the correct servers. Each function 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:
HQ office server - AlwaysOn primary replica;
Satellite office server - AlwaysOn secondary replica; Cloud server - file backup server

NEW QUESTION 7

You are the administrator of a Microsoft SQL Server 2021 server. Some applications consume significant resources.
You need to manage the server workload by restricting resource-intensive applications. You need to dynamically limit resource consumption.
What should you do?

• A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources.
• B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor.
• C. Configure Extended Events to monitor and restrict resource limits allowed by each application type.
• D. Create a new Plan Guide with a Scope Type of sql and define the resource limits for each application.

Answer: B

NEW QUESTION 8

You administer a Microsoft SQL Server 2021 server.
One of the databases on the server supports a highly active OLTP application. Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time. What should you do?

• A. use SQL Profiler to trace all queries that are processing on the serve
• B. Filter queries that have a Duration value of more than 1,000.
• C. Use sp_configure to set a value for blocked process threshol
• D. Create an extended event session.
• E. Use the Job Activity monitor to review all processes that are actively runnin
• F. Review the Job History to find out the duration of each step.
• G. Run the sp_who command from a query window.
• H. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

Answer: B

NEW QUESTION 9

You administer a Microsoft SQL Server 2021 instance that has multiple databases. You have a two-node SQL Server failover cluster.
The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.
You need to reduce the I/O workload on the SAN at a minimal cost. What should you do?

• A. Move user databases to a local disk.
• B. Expand the tempdb data and log files.
• C. Modify application code to use table variables.
• D. Move the tempdb files to a local disk.

Answer: D

Explanation:
You can configure TempDB on a local disk when you, for example, installing your SQL Server cluster. References: https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

NEW QUESTION 10

You manage a SQL Server 2014 instance that contains a database named DB1. Users report that some queries to DB1 take longer than expected.
Although most queries run in less than one second, some queries take up to 20 seconds to run. You need to view all of the performance statistics for each database file.
Which method should you use?

• A. Query the sys.dm_os_tasks dynamic management view.
• B. Query the sys.dm_os_performance_counters dynamic management view.
• C. Query the sys.dm_io_virtual_file_stats dynamic management function.
• D. Examine the Data File I/O pane in Activity Monitor.

Answer: C

Explanation:
sys.dm_io_virtual_file_stats Returns I/O statistics for data and log files.

NEW QUESTION 11

You have a database named DB1.
DB1 contains a table named Sales.SalesPerson that has an index named AK_SalesPerson_rowguid. Queries that do not use the index take approximately 10 times longer to complete than queries that use the
index. You discover that AK_SalesPerson_rowguid has severe fragmentation. You need to recommend a solution to defragment the index.
The solution must ensure that the index can be used by queries during the defragmentation.
What statement should you use? To answer, drag the appropriate elements to the correct locations. Each element 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:
Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.
Use: ALTER INDEX REBUILD WITH (ONLINE = ON)
References: Reorganize and Rebuild Indexes https://msdn.microsoft.com/en-us/library/ms189858.aspx

NEW QUESTION 12

You have 10 Microsoft SQL Server 2021 servers.
You deploy a management data warehouse named DW1. You configure DW1 to gather all the performance data from the servers.
You configure a Data Collector on a SQL server named SV1.
You query the data warehouse on DW1 and discover that data from SV1 is unavailable.
You need to ensure that you can review the performance data from SV1 when you query DW1. What should you do?

• A. Start the SQL Server Agent service on DW1.
• B. Execute the msdb.sp_syscollector_set_warehouse_connection_user stored procedure on SV1.
• C. Execute the msdb.sp_syscollector_enable_collector stored procedure on DW1.
• D. Start the SQL Server Agent service on SV1.

Answer: C

Explanation:
sp_syscollector_enable_collector enables the data collector. References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-syscollector-enable-colle

NEW QUESTION 13

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.
You plan to delegate encryption operations to a user.
You need to grant the user permission to implement cell-level encryption while following the principle of least privilege.
Which permission should you grant?

• A. DDLAdmin
• B. db_datawriter
• C. dbcreator
• D. dbo
• E. View Database State
• F. View ServerState
• G. View Definition
• H. sysadmin

Answer: G

Explanation:
The following permissions are necessary to perform column-level encryption, or cell-level encryption.
CONTROL permission on the database.
CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.
ALTER permission on the table.
Some permission on the key and must not have been denied VIEW DEFINITION permission. References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data

NEW QUESTION 14

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 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.
You have a database named DB1 that is 640 GB and is updated frequently.
You enabled log shipping for DB1 and configure backup and restore to occur every 30 minutes. You discover that the disks on the data server are almost full.
You need to reduce the amount of disk space used by the log shipping process.
Solution: You increase the frequency of the transaction log backups to every 10 minutes. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 15

You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: TableA and TableB.
TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of rows in each partition. Most changes to the data in TableA affect recently added data. The UPDATE STATISTICS for TableA takes longer to complete than the allotted maintenance window.
Thousands of operations are performed against TableB each minute. You observe a large number of Auto Update Statistics events for TableB.
You need to address the performance issues with each table.
In the table below, identify the action that will resolve the issues for each table. NOTE: Make only one selection in each column.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Table A: Auto_update statistics off
Table A does not change much. There is no need to update the statistics on this table. Table B: SET AUTO_UPDATE_STATISTICS_ASYNC ON
You can set the database to update statistics asynchronously: ALTER DATABASE YourDBName
SET AUTO_UPDATE_STATISTICS_ASYNC ON
If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments
References:
https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/

NEW QUESTION 16

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 deploying a Microsoft SQL Server architecture to support a new mission-critical application. The application includes a dedicated reporting component that performs read-only operations against the application’s databases.
You need to implement a solution that meets the following requirements: Modifications to the application to support the new architecture are not permitted. What should you implement?

• A. a Microsoft Azure Stretch Database
• B. log shipping
• C. an Always On Availability Group with all replicas in synchronous-commit mode
• D. a file share witness
• E. a SQL Server failover cluster instance (FCI)
• F. a Windows Cluster with a shared-nothing architecture
• G. an Always On Availability group with secondary replicas in asynchronous-commit mode

Answer: C

Explanation:
Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on

NEW QUESTION 17

Background Corporate Information
Fabrikam, Inc. is a retailer that sells electronics products on the Internet. The company has a headquarters site and one satellite sales office. You have been hired as the database administrator, and the company wants you to change the architecture of the Fabrikam ecommerce site to optimize performance and reduce downtime while keeping capital expenditures to a minimum. To help with the solution, Fabrikam has decided to use cloud resources as well as on-premise servers.
Physical Locations
All of the corporate executives, product managers, and support staff are stationed at the headquarters office. Half of the sales force works at this location. There is also a satellite sales office. The other half of the sales force works at the satellite office in order to have sales people closer to clients in that area. Only sales people work at the satellite location.
Problem Statement
To be successful, Fabrikam needs a website that is fast and has a high degree of system uptime. The current system operates on a single server and the company is not happy with the single point of failure this presents. The current nightly backups have been failing due to insufficient space on the available drives and manual drive cleanup often needing to happen to get past the errors. Additional space will not be made available for backups on the HQ or satellite servers. During your investigation, you discover that the sales force reports are causing significant contention.
Configuration Windows Logins
The network administrators have set up Windows groups to make it easier to manage security. Users may belong to more than one group depending on their role. The groups have been set up as shown in the following table:

Server Configuration The IT department has configured two physical servers with Microsoft Windows Server
2012 R2 and SQL Server 2014 Enterprise Edition and one Windows Azure Server. There are two tiers of storage available for use by database files only a fast tier and a slower tier. Currently the data and log files are stored on the fast tier of storage only. If a possible use case exists, management would like to utilize the slower tier storage for data files. The servers are configured as shown in the following table:

Database
Currently all information is stored in a single database called ProdDB, created with the following script:

The Product table is in the Production schema owned by the ProductionStaff Windows group. It is the main table in the system so access to information in the Product table should be as fast as possible. The columns in the Product table are defined as shown in the following table:

The SalesOrderDetail table holds the details about each sale. It is in the Sales schema owned by the SalesStaff Windows group. This table is constantly being updated, inserted into, and read. The columns in the SalesOrderDetail table are defined as shown in the following table:

Database Issues
The current database does not perform well. Additionally, a recent disk problem caused the system to go down, resulting in lost sales revenue. In reviewing the current system, you found that there are no automated maintenance procedures. The database is severely fragmented, and everyone has read and write access.
Requirements Database
The database should be configured to maximize uptime and to ensure that very little data is lost in the event of a server failure. To help with performance, the database needs to be modified so that it can support in-memory data, specifically for the Product table, which the CIO has indicated should be a memoryoptimized table. The auto-update statistics option is set off on this database. Only product managers are allowed to add products or to make changes to the name, description, price, cost, and supplier. The changes are made in an internal database and pushed to the Product table in ProdDB during system maintenance time. Product managers and others working at the headquarters location also should be able to generate reports that include supplier and cost information.
Customer data access
Customers access the company's website to order products, so they must be able to read product information such asname, description, and price from the Product table. When customers place orders, stored procedures calledby the website update product quantityon-hand values. This means the product table is constantly updated at randomtimes.
Customer support data access
Customer support representatives need to be able to view and not update or change product information. Management does not want the customer support representatives to be able to see the product cost or any supplier information.
Sales force data access
Sales people at both the headquarters office and the satellite office must generate reports that read from the Product and SalesOrderDetail tables. No updates or inserts are ever made by sales people. These reports are run at random times and there can be no reporting downtime to refresh the data set except during the monthly maintenance window. The reports that run from the satellite office are process intensive queries with large data sets. Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet committed should be ignored.
Historical Data
The system should keep historical information about customers who access the site so that sales people can see how frequently customers log in and how long they stay on the site.
The information should be stored in a table called Customer Access. Supporting this requirement should have minimal impact on production website performance.
Backups
The recovery strategy for Fabrikam needs to include the ability to do point in time restores and minimize the risk of data loss by performing transaction log backups every 15 minutes.
Database Maintenance
The company has defined a maintenance window every month when the server can be unavailable. Any maintenance functions that require exclusive access should be accomplished during that window.
Project milestones completed
Revoked all existing read and write access to the database, leaving the schema ownership in place.
Configured an Azure storage container secured with the storage account name MyStorageAccount with the primary access key StorageAccountKey on the cloud file server.
SQL Server 2014 has been configured on the satellite server and is ready for use.
On each database server, the fast storage has been assigned to drive letter F:, and the slow storage has been assigned to drive letter D:.
You need to implement changes to the system to reduce contention and improve performance of the SalesOrderDetail table.
Which three actions should you perform? Each correct answer presents part of the solution. (Choose three.)

• A. Use (SNAPSHOT] hints in the report queries
• B. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT ON
• C. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT OFF
• D. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• E. Use (TABLOCK) hints in the report queries
• F. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• G. ALTER DATABASE [ProdDB] SET ALLOW.SNAPSHOT ISOLATION ON
• H. Use (SNAPSHOT]hints in the update statements

Answer: ABF

Explanation:
- Scenario: The SalesOrderDetail table holds the details about each sale. It is in the Sales schema owned by the SalesStaff Windows group. This table is constantly being updated, inserted into, and read.
- Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet committed should be ignored.
- READ_COMMITTED_SNAPSHOT { ON | OFF } ON Enables Read-Committed Snapshot option at the database level. When it is enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. Once this option is enabled, the transactions specifying the read committed isolation
level use row versioning instead of locking.
When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement. OFF Turns off Read-Committed Snapshot option at the database level. Transactions specifying the READ COMMITTED isolation level use locking. ALTER DATABASE SET Options (Transact-SQL) SET Statements (Transact-SQL)

NEW QUESTION 18

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.

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

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.

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.
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.
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.
The wait statistics monitoring requirements for the instances are described in the following table.

You need to analyze the wait type and statistics for specific instanced in the environment.
Which object should you use to gather information about each instance? To answer, drag the appropriate
objects to the correct instances. Each object 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.
NOTE: Each correct selection is worth one point.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Instance 1: sys.dm_exec_query_stats
From Scenario: Instance1 requirement: Aggregate statistics since last server restart. sys.dm_exec_query_stats returns aggregate performance statistics for cachedquery plans in SQL Server.
Instance 4: sys.dm_os_wait_stats
sys.dm_os_wait_statsreturns information about all the waits encountered by threads that executed. From Scenario: Instance4 requirement: Identify the most prominent wait types.

Instance 5:sys.dm_exec_session_wait_stats
From Scenario: Instance5 requirement: Identify all wait types for queries currently running on the server. sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed
for each session.

NEW QUESTION 19

You need to grant access to an OLTP database regardless of the user operating the application. Which strategy should you use?

• A. Application role
• B. Database user
• C. Server login
• D. Server role

Answer: A

NEW QUESTION 20

Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2. You need to ensure that User1 can run sp1.
The solution must minimize the number of permissions assigned to User1. What should you do?

• A. Grant User1 the INSERT permission on Table2.
• B. Add User1 to the db_datawriter role.
• C. Change sp1 to run as the sa user.
• D. Grant User1 the EXECUTE permission on sp1.

Answer: D

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms191291.aspx

NEW QUESTION 21

Overview
General Overview
ADatum Corporation has offices in Miami and Montreal.
The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a WAN link that has 5-ms latency. A. Datum standardizes its database platform by using SQL Server 2014 Enterprise edition.
Databases
Each office contains databases named Sales, Inventory, Customers, Products, Personnel, and Dev. Servers and databases are managed by a team of database administrators. Currently, all of the database
administrators have the same level of permissions on all of the servers and all of the databases.
The Customers database contains two tables named Customers and Classifications. The following graphic shows the relevant portions of the tables:

The following table shows the current data in the Classifications table:

The Inventory database is updated frequently. The database is often used for reporting.
A full backup of the database currently takes three hours to complete. Stored Procedures
A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from five different tables from the Sales and Customers databases in a table named Table1.
After Table1 is created, the reporting process reads data from Table1 sequentially several times. After the process is complete, Table1 is deleted.
A stored procedure named USP_2 is used to generate a product list. The product list contains the names of products grouped by category.
USP_2 takes several minutes to run due to locks on the tables the procedure accesses. The locks are caused by USP_1 and USP_3.
A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction.
Currently, if one of the UPDATE statements fails, the stored procedure fails. A stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases.
The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.
All nested stored procedures handle errors by using structured exception handling. A stored procedure named USP_5 calls several stored procedures in the same database. Security checks are performed each time USP_5 calls a stored procedure.
You suspect that the security checks are slowing down the performance of USP_5. All stored procedures accessed by user applications call nested stored procedures.
The nested stored procedures are never called directly. Design Requirements
Data Recovery
You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Time Objective (RTO) of 5 minutes.
You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point Objective (RPO) of one day.
Classification Changes
You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future. Management requests that historical data be maintained for the previous classifications. Security A group of junior database administrators must be able to manage security for the Sales database. The junior database administrators will not have any other administrative rights. A. Datum wants to track which users run each stored procedure.
Storage
ADatum has limited storage. Whenever possible, all storage space should be minimized for all databases and all backups.
Error Handling
There is currently no error handling code in any stored procedure.
You plan to log errors in called stored procedures and nested stored procedures. Nested stored procedures are never called directly.
You need to recommend a solution to ensure that USP_4 adheres to the security requirements. What should you include in the recommendation?

• A. Enable SQL Server Audit.
• B. Enable trace flags.
• C. Configure data manipulation language (DML) triggers.
• D. Enable C2 audit tracing.

Answer: A

Explanation:
Scenario:
A stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases. The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.
Beginning in SQL Server 2008 Enterprise, you can set up automatic auditing by using SQL Server Audit.

NEW QUESTION 22

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.
Your company is developing a new business intelligence application that will access data in a Microsoft Azure SQL Database instance. All objects in the instance have the same owner.
A new security principal named BI_User requires permission to run stored procedures in the database. The stored procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT operations or dynamic SQL commands.
The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions, you should use the principle of least privilege.
You need to create the required security principals and grant the appropriate permissions. Solution: You run the following Transact-SQL statement in the database:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions. You revoke or deny all permissions to underlying objects, such as tables, and grant EXECUTE permissions on stored procedures. This effectively creates a security perimeter around your data and database objects.
Best Practices
Simply writing stored procedures isn't enough to adequately secure your application. You should also consider the following potential security holes.
Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.
Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. All users inherit permissions from public. Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles.
Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access all database objects.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedur

NEW QUESTION 23

You administer a single server that contains a Microsoft SQL Server 2021 default instance.
You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?

• A. Use the SQL Server default instance and configure an affinity mask.
• B. Install a new named SQL Server instance on the server.
• C. Use the SQL Server default instance and enable Contained Databases.
• D. Install a new default SQL Server instance on the server.

Answer: B

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 24

You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization's SQL Server 2021 deployment.
Which of the following tools would you use to accomplish this goal?

• A. SQLIO
• B. SQLIOSim
• C. SQLIOStress
• D. chkdsk

Answer: B

Explanation:
The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server.
References:
https://support.microsoft.com/en-us/help/231619/how-to-use-the-sqliosim-utility-to-simulate-sql-server-activity-

NEW 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.
You have a Microsoft Azure SQL database that has Blob Auditing configured. You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.

Does this meet the goal?

• A. YES
• B. NO

Answer: A

Explanation:
fn_get_audit_file returns information from an audit file created by a server audit in SQL Server.
This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-14/10_45_22_173_1.xel:
SELECT * FROM sys.fn_get_audit_file ('https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-
Note: Blob auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

NEW QUESTION 26

You have an SQL Server 2014 server. You plan to create four stored procedures that will use transactions. The stored procedures will be configured as shown in the following table.

You need to recommend an isolation level for each stored procedure. The solution must support the concurrency strategy of each stored procedure and must minimize locks.
What should you recommend?
To answer, drag the appropriate isolation levels to the correct stored procedures. Each isolation level may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
SP1 – SNAPSHOT, SP2 – REPEATABLE READ, SP3 – READ COMMITTED, SP4 - SERIALIZABLE
References: SET TRANSACTION ISOLATION LEVEL (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms173763.aspx

NEW QUESTION 27

You have configured Resource Governor with three resource pools.
You have assigned the first resource pool a minimum CPU and memory value of 20%. You have assigned the second resource pool a minimum CPU and memory value of 30%. You want to assign maximum CPU and memory values to the third resource pool.
What is the maximum CPU and memory value you can assign to this resource pool?

• A. 30%
• B. 50%
• C. 70%
• D. 100%

Answer: B

Explanation:
The maximum resource value assigned to the third pool is 100%; the sum of the minimum resource values assigned to the other pools is 50%.

NEW QUESTION 28
......