SQL database infrastructure administration.

You have a database named DB1. Users report that a database application that updates the data in DB1 is unresponsive. You need to identify which process prevents the application from responding. What should you do?

Correct Answer: Run sp_autostats.

You maintain a database named DB1 that has a nonpartitioned table. You create an index in the table. Automatic statistic updates are disabled. Uses report that some Microsoft SQL Server Reporting Service (SSRS) queries take long time to run. You determine that the issue is caused by stale statistics for Index1. You need to update the statistics for the index. What should you do?

Correct Answer: Run the system stored procedure sp_updatestats.

You have a Microsoft SQL Server instance that has a database named DB1. The database is used for reporting purposes. You plan to capture all queries for a specific table and save the data as a text file. You need to ensure that queries are captured and that a failure to capture a query will shut down the SQL Server instance. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

Correct Answer: Create a SQL Server Profiler trace and disable c2 audit tracing.

You maintain a database named DB1 that has a nonpartitioned table. You create an index in the table. Automatic statistic updates are disabled. Uses report that some Microsoft SQL Server Reporting Service (SSRS) queries take a long time to run. You determine that the issue is caused by stale statistics for Index1. You need to update the statistics for the index. What should you do?

Correct Answer: Set the value of the Database Read-Only property to True.

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?

Correct Answer: SQL Server Management Studio (SSMS) Object Explorer

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?

Correct Answer: SQL Server Data Collector

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?

Correct Answer: Activity Monitor

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?

Correct Answer: Object Explorer in the Microsoft SQL Server Management Studio (SSMS)

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?

Correct Answer: SQL Server Data Collector

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series. You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table. You need to monitor WingDB and gather information for troubleshooting issues. What should you use?

Correct Answer: sys.dm_tran_active_snapshot_database_transactions

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 reduce the amount of time it takes to backup OperationsMain. What should you do?

Correct Answer: Modify the backup script to use the keyword SKIP in the WITH statement

A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups. DB1 experiences a failure. You must restore FG1 and then FG2. You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online. What should you specify when you run the recovery command?

Correct Answer: the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2

You have a database that stores information for a shipping company. You create a table named Customers by running the following Transact-SQL statement. (Line numbers are included for reference only.) You need to ensure that salespeople can view data only for the customers that are assigned to them. Which Transact-SQL segment should you insert at line 07?

Correct Answer: RETURNS dbo.CustomersORDER BY @salesPerson

You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per hour. Queries on DB1 are taking longer than normal to complete. You run the following Transact-SQL statement: SELECT * FROM sys.database_query_store_options You determine that the Query Store is in Read-Only mode. You need to maximize the time that the Query Store is in Read-Write mode. Which Transact-SQL statement should you run?

Correct Answer: ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)

You administer a single server that contains a Microsoft SQL Server 2016 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?

Correct Answer: Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.

You administer a Microsoft SQL Server 2016 default instance. The instance is hosted by a server that has a local firewall configured. The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server. You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections. What should you do?

Correct Answer: Execute the following Transact-SQL command: sp_configure 'remote admin connections',

You administer all the deployments of Microsoft SQL Server 2016 in your company. You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off- loaded from the primary database to another server. You also need to be able to add indexes to the secondary database. Which configuration should you use?

Correct Answer: 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

You administer all the deployments of Microsoft SQL Server 2016 in your company. You need to ensure that data changes are sent to a non-SQL Server database server in near real time. You also need to ensure that data on the primary server is unaffected. Which configuration should you use?

Correct Answer: Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode

You administer all the deployments of Microsoft SQL Server 2016 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?

Correct Answer: Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

You administer all the deployments of Microsoft SQL Server 2016 in your company. You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail. You also need to minimize the amount of storage used by the database. Which configuration should you use?

Correct Answer: SQL Server that includes an application database configured to perform transactional replication

You administer a Microsoft SQL Server 2016 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table: The differential backup of the reporting database fails. Then, the reporting database fails at 14:00 hours. You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal. What should you do?

Correct Answer: Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.

You administer a Microsoft SQL Server 2016 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table: At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database. You need to ensure that the databases are restored. You also need to ensure that data loss is minimal. What should you do?

Correct Answer: Restore the latest full backup.

You administer a Microsoft SQL Server 2016 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table: At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is restored. You also need to ensure that data loss is minimal. What should you do?

Correct Answer: Restore the latest full backup.

You administer several Microsoft SQL Server 2016 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

Correct Answer: Change the Merge agent on the problem subscribers to run continuously.

You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA. Currently, Server01l/HA is the primary replica. You need to ensure that the following requirements are met: ✑ Backup operations occur on Server02/HA. ✑ If Server02/HA is unavailable, backup operations occur on Server03/HA. ✑ Backup operations do not occur on Server01/HA. How should you configure HaContoso?

Correct Answer: set the exclude replica of Server01/HA to true. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.

You administer a Microsoft SQL Server 2016 instance that has several SQL Server Agent jobs configured. SQL Server Agent jobs fail, the error messages returned by the job steps are truncated. The following error message is an example of the truncated error message: "Executed as user CONTOSO\ServiceAccount. ...0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution fa... The step failed." You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs. What should you do?

Correct Answer: Expand agent logging to include information from all events.

You administer a Microsoft SQL Server 2016 server that has SQL Server Integration Services (SSIS) installed. You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. You need to configure the SQL Server environment to support these packages. What should you do?

Correct Answer: Create an Integration Services catalog.

You administer two instances of Microsoft SQL Server 2016. You deploy an application that uses a database on the named instance. The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

Correct Answer: Start the SQL Server Browser Service.

You administer a Microsoft SQL Server 2016 database. Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error. You need to identify the root cause of the issue by retrieving the error message. What should you do?

Correct Answer: Execute sp_who.

Welcome to awslagi.com – Sharing AWS, Google Cloud, Microsoft Azure IT Exams. It's free for all!

Administering a SQL Database Infrastructure

Administer SQL database infrastructure with confidence.

4.8★★★★★(177 ratings)

19,489 studentsIntermediate

Gain operational skills for managing SQL Database infrastructure, including security, maintenance, backup, and performance. The exam emphasizes the administrative responsibilities needed to keep database platforms stable and available. It is a strong choice for DBAs supporting production workloads. These skills help protect data, maintain uptime, and ensure that business systems continue to run smoothly.

What you'll learn

Manage SQL infrastructure- Perform maintenance tasks- Configure backup and restore- Improve database security- Monitor performance

70-764

Lectures2

Quizzes0

Duration30 week

Students19,489

$20.00

📢 Unlock Full Access to Success!

"The content displayed here is a promotional preview and contains only 50% of the actual exam database. To access the complete set of updated questions, please upgrade to the Full Version."

👉 Click the button and follow the instructions to secure your full access immediately!