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

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

Answer : F

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

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

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

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

Answer : C

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

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

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

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

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

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/

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

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

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

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