Administering a SQL Database Infrastructure v1.0 (70-764)

Page:    1 / 30   
Total 445 questions

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?

  • A. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
  • B. Execute the following Transact-SQL command: sp_configure 'remote admin connections',
  • C. Execute the Reconfigure command.
  • D. Execute the following Transact-SQL command: sp_configure 'remote access', 1
  • E. Restart the SQL Server Agent Service.
  • F. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.


Answer : ABC

Explanation:
SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or
Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL
Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.
By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434
The following example enables the DAC from a remote computer. sp_configure 'remote admin connections', 1;

GO -
RECONFIGURE;

GO -
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/remote-admin-connections-server-configuration-option

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?

  • A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  • B. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
  • C. 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
  • D. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
  • E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  • F. SQL Server that includes an application database configured to perform transactional replication
  • G. SQL Server that includes an application database configured to perform snapshot replication
  • H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance


Answer : F

References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication

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?

  • A. SQL Server that includes an application database configured to perform transactional replication
  • B. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
  • C. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  • D. SQL Server that includes an application database configured to perform snapshot replication
  • E. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary
  • F. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  • G. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
  • H. 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


Answer : A

Explanation:
SQL Server supports the following heterogeneous scenarios for transactional and snapshot replication:
-> Publishing data from SQL Server to non- SQL Server Subscribers.
-> Publishing data to and from Oracle has some restrictions.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/non-sql/heterogeneous-database-replication

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?

  • 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

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?

  • A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  • 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 different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
  • E. 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
  • F. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  • G. SQL Server that includes an application database configured to perform snapshot replication
  • H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance


Answer : H

Explanation:
A Windows Server Failover Cluster (WSFC) is a group of independent servers that work together to increase the availability of applications and services. SQL
Server takes advantage of WSFC services and capabilities to support Always On availability groups and SQL Server Failover Cluster Instances.
References:
https://docs.micosoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server

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?

  • A. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  • B. Perform a point-in-time restore.
  • C. Restore the latest full backup.
  • D. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  • E. Restore the latest full backup. Then, restore the latest differential backup.
  • F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  • G. Perform a page restore.
  • H. Perform a partial restore.


Answer : C

Explanation:
The differential backup of the reporting database has failed, so it can't be used.

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?

  • A. Perform a partial restore.
  • B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  • C. Restore the latest full backup.
  • D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  • E. Perform a page restore.
  • F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  • G. Perform a point-in-time restore.
  • H. Restore the latest full backup. Then, restore the latest differential backup.


Answer : H

Explanation:
At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.
References:
https://technet.microsoft.com/en-us/library/ms345448(v=sql.105).aspx

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?

  • A. Perform a partial restore.
  • B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  • C. Perform a point-in-time restore.
  • D. Restore the latest full backup.
  • E. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  • F. Perform a page restore.
  • G. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  • H. Restore the latest full backup. Then, restore the latest differential backup.


Answer : F

Explanation:
The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Typically, pages that are candidates for restore have been marked as "suspect" because of an error that is encountered when accessing the page.
Note: Requirements for Restoring Pages
A page restore is subject to the following requirements:
-> The databases must be using the full or bulk-logged recovery model.
Etc.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server

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?

  • A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
  • B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
  • C. Change the Merge agent on the problem subscribers to run continuously.
  • D. Set the Remote Connection Timeout on the Publisher to 0.


Answer : A

Explanation:
You might have different profiles for different instances of an agent. For example, a Merge Agent that connects to the Publisher and Distributor over a dialup connection could use a set of parameters that are better suited to the slower communications link by using the slow link profile.
Note: When replication is configured, a set of agent profiles is installed on the Distributor. An agent profile contains a set of parameters that are used each time an agent runs: each agent logs in to the Distributor during its startup process and queries for the parameters in its profile.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-agent-profiles

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?

  • A. Set the backup preference of HaContoso to Prefer Secondary. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.
  • B. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 20. Set the backup priority of Server03/HA to 10.
  • C. Set the backup preference of HaContoso to Secondary only. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.
  • D. 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.


Answer : B

Explanation:
Secondary only: Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.
Backup Priority (Lowest=1, Highest=100)
Specifies your priority for performing backups on this replica relative to the other replicas in the same availability group. The value is an integer in the range of
0..100. 1 indicates the lowest priority, and 100 indicates the highest priority. If Backup Priority = 1, the availability replica would be chosen for performing backups only if no higher priority availability replicas are currently available.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server

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?

  • A. Expand agent logging to include information from all events.
  • B. Disable the Limit size of job history log feature.
  • C. Configure event forwarding.
  • D. Configure output files.


Answer : D

Explanation:
When you have a multiple-step job, then log all steps against a single file. Check the 'Append output to existing file' checkbox for all steps in the job that execute after the initial step. This results in a log file with all of the job steps from the last job execution. Each time the first step executes (each time the job is kicked-off) the file will be overwritten, so we have a record of the last set of output.
References:
https://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/

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?

  • A. Create SSIS configuration files for the packages.
  • B. Create an Integration Services catalog.
  • C. Install Data Quality Services.
  • D. Install Master Data services.


Answer : B

Explanation:
Use can use Project Deployment Model for a project, containing packages and parameters, which is deployed to the SSISDB catalog on an instance of SQL
Server.
References:
https://docs.microsoft.com/en-us/sql/integration-services/packages/deploy-integration-services-ssis-projects-and-packages

You administer a Microsoft SQL Server 2016 server.
When transaction logs grow, SQL Server must send an email message to the database administrators.
You need to configure SQL Server to send the email messages.
What should you configure?

  • A. SQL Mail
  • B. An Extended Events session
  • C. Alerts and operators in SQL Server Agent
  • D. Policies under Policy-Based Management


Answer : C

Explanation:
Operators are aliases for people or groups that can receive electronic notification when jobs have completed or alerts have been raised. The SQL Server Agent service supports the notification of administrators through operators. Operators enable notification and monitoring capabilities of SQL Server Agent.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail

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?

  • A. Use the Data Quality Client to configure the application.
  • B. Start the SQL Server Browser Service.
  • C. Use the Master Data Services Configuration Manager to configure the application.
  • D. Start the SQL Server Integration Services Service.


Answer : B

Explanation:
The SQL ServerBrowser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
Browsing a list of available servers
Connecting to the correct server instance
Etc.
References:
https://docs.microsoft.com/en-us/sql/tools/configuration-manager/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?

  • A. Create an Extended Events session by using the sqlserver.error_reported event.
  • B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
  • C. Flag all stored procedures for recompilation by using sp_recompile.
  • D. Execute sp_who.


Answer : A

Explanation:
Trapping SQL Server Errors with Extended Events
One very useful usage of Extended Events is the ability to trap SQL Server error without the need to have a server trace running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in. This means that we a solution to monitor and trap errors as soon as they happen can be easily created, in order to help developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big applications, where the code base is quite old and there is no-one really knowing everything of the solution.
To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the following script:
CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
Etc.
References:
http://sqlblog.com/blogs/davide_mauri/archive/2013/03/17/trapping-sql-server-errors-with-extended-events.aspx

Page:    1 / 30   
Total 445 questions