callicon +1 210 504 8191
whatsAppIcon +1 210 415 9856
emailIcon info@koenig-solutions.com
koenig-logo
Login
whatsAppIcon +1 210 415 9856
phoneIcon +1 210 504 8191
koenig-logo
All Courses

Administering a SQL Database Infrastructure Quiz Questions and Answers

Answer :
  • Encrypt both columns by using deterministic encryption.
  • Provision column master keys and column encryption keys by using Microsoft SQL Server Management Studio (SSMS).

Explanation :

A: Use deterministic encryption for columns that will be used as search or groupi ng parameters, for example a government ID number. Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted co lumns. D: Always Encrypted uses two types of keys: column encryption keys and column master keys. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys. Incorrect Answers: B: A column encryption key (CEK), is a content encryption key (i.e. a key used to protect data) that is protected by a CMK. All Microsoft CMK store providers encrypt CEKs by using RSA with Optimal Asymmetric Encryption Padding (RSA-OAEP) with the default parameters specified by RFC 8017 in Section A.2.1. C: Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexin g, and joining on encrypted columns. References: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017
Answer :
  • Create an Extended Events session by using the sqlserver.error_reported event.

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
Answer :
  • Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
  • Execute the following Transact-SQL command: sp_configure 'remote admin connections',
  • Execute the Reconfigure command.

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 :
  • Activity Monitor

Explanation :

Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server. Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. The Activity User Tasks Pane shows information for active user connections to the instance, including the following column: * Login: The SQL Server login name under which the session is currently executing. The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column: * CPU (ms/sec): The rate of CPU use by the query References: https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx
Answer :
  • ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

Explanation :

Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries. By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario. Avoid keeping historical data that you do not plan to use. This will reduce changes to read-only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy: ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14)); References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
Answer :
  • 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.

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 :
  • Create an extended events session to capture deadlock information.

Explanation :

Troubleshooting deadlocks You have been receiving reports from users indicating that certain applications are returning deadlock errors. To maximize the effectivene ss of troubleshooting these problems, you decide to focus on the deadlocks that are hit most frequently. You create an Extended Events session that: Configures deadlock event tracking for the session. Specifies a target that aggregates based on an identif ier for the deadlock. You run the Extended Events session, and after additional deadlocks are reported you are able to obtain aggregated deadlock information, along with the complete XML deadlock graph for each source. Using this information, you are able to pin point the most common deadlocks and start working on a solution.
Answer :
  • SET AUTO_CREATE_STATISTICS ON

Explanation :

AUTO_UPDATE_STATISTICS { ON | OFF } ON specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017#auto_update_statistics
Answer :
  • Set the Remote Connection Timeout on the Publisher to 0.

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 :
  • SQL Server that includes an application database configured to perform snapshot replication

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