sqlserver基本介紹，sql server 面試_SQL Server審核面試問題-小白筆記-匯編語言學習筆記

sqlserver基本介紹，sql server 面試_SQL Server審核面試問題

2023-10-18 阅读 20 评论 0

摘要：sql server 面試 In this article, we will discuss a number of common and important SQL Server Audit questions that you may be asked during the SQL Server database administrator interview. I advise you to check them out and get ready for the interview. 在本

sql server 面試

In this article, we will discuss a number of common and important SQL Server Audit questions that you may be asked during the SQL Server database administrator interview. I advise you to check them out and get ready for the interview.

在本文中，我們將討論在SQL Server數據庫管理員面試過程中可能會問到的一些常見和重要SQL Server審核問題。我建議您檢查一下并準備好面試。

Q1. What is the SQL Server Audit process?

sqlserver基本介紹， Q1。 什么是SQL Server審核過程？

SQL Server Audit is the process of tracking and logging the actions that are performed on the SQL Server instance to achieve the main audit goals of securing the company data
SQL Server審核是跟蹤和記錄在SQL Server實例上執行的操作以實現保護公司數據安全的主要審核目標的過程。

SQL Server Audit OverviewSQL Server審核概述。

Q2. Mention some?common and critical events?that are considered as a minimum requirement for an audit?

Q2。 是否提到一些常見和重要事件，這些事件被視為審核的最低要求？

Failed Logins, 登錄失敗 ， Login Changes,?登錄更改 ， Users Changes, 用戶更改 ， Schema Changes, and 架構更改和Audit Changes 審核更改

SQL Server Audit OverviewSQL Server審核概述。

Q3. Why is it important to audit the failed logins in your SQL Server instance?

Q3。 為什么審核SQL Server實例中失敗的登錄很重要？

Because receiving an excessive number of failed logins could be an indication of an attack on your SQL Server
因為收到過多的失敗登錄信息可能表明您SQL Server受到攻擊

SQL Server Audit OverviewSQL Server審核概述。

dba sql審核、 Q4. Why is it important to audit the login changes in your SQL Server instance?

Q4。 為什么審核SQL Server實例中的登錄更改很重要？

Because this login change could be a fake key that someone will use to hack your SQL Server instance
因為此登錄更改可能是偽造的密鑰，所以有人會用它來入侵您SQL Server實例

SQL Server Audit OverviewSQL Server審核概述。

Q5. Why is it important to audit the user changes in your SQL Server instance?

Q5。 為什么審核SQL Server實例中的用戶更改很重要？

Because this user permission change could be an alarm for an attack on your SQL Server instance
因為此用戶權限更改可能是SQL Server實例受到攻擊的警報

SQL Server Audit OverviewSQL Server審核概述。

Q6. Why is it important to audit the schema changes in your SQL Server instance?

sql server服務器名稱。 Q6。 為什么審核SQL Server實例中的架構更改很重要？

It is recommended to track all database schema changes in order to catch any illegal schema changes
建議跟蹤所有數據庫架構更改，以捕獲任何非法的架構更改

SQL Server Audit OverviewSQL Server審核概述。

Q7. What are the limitations of the C2 audit and why it is not recommended?

Q7。 C2審核的局限性是什么？為什么不建議這樣做？

The C2 Auditing feature has two limitations. The first one is that there is no option to configure and specify what we need to audit, where it will audit all actions at the SQL Server instance and database levels. The second limitation for C2 Auditing is that there is no option to configure the path where the generated SQL Server Audit TRC files will be saved, where it will automatically save these files into the default DATA folder of the installed instance. These two limitations may lead to an issue of generating large trace files that will fill the disk where the database data files saved and stop writing data into the databases
C2審核功能有兩個限制。第一個是沒有選項來配置和指定我們需要審核的內容，它將在SQL Server實例和數據庫級別審核所有操作。 C2審核的第二個限制是沒有選項來配置將生成SQL Server Audit TRC文件保存到的路徑，它將自動將這些文件保存到已安裝實例的默認DATA文件夾中。這兩個限制可能導致生成大型跟蹤文件的問題，該跟蹤文件將填滿保存數據庫數據文件的磁盤并停止將數據寫入數據庫

Implementing a manual SQL Server Audit實施手動SQL Server審核。

Q8. Could we use the SQL Profiler to audit your SQL Server instance? Is it a recommended tool?

Q8。 我們可以使用SQL事件探查器來審計您SQL Server實例嗎？ 是推薦工具嗎？

SQL Server Audit using the SQL Profiler could be a great option when the events are selected properly, and properties are filtered correctly so that it will collect the requested information only. In this way, the size of the generated trace file will be minimized which makes it easier to be analyzed. Due to the fact that the SQL Profiler tool will be eventually deprecated by Microsoft, it cannot be considered as a long-term auditing solution
如果正確選擇了事件，并且正確過濾了屬性，以便僅收集請求的信息，則使用SQL事件探查器SQL Server審核可能是一個不錯的選擇。這樣，將使生成的跟蹤文件的大小最小化，從而使其更易于分析。由于SQL Profiler工具最終將被Microsoft棄用，因此不能將其視為長期審核解決方案。

Implementing a manual SQL Server Audit實施手動SQL Server審核。

sql server的主要功能， Q9. Explain why we cannot take advantage of the SQL Server Extended Events feature as a SQL Server audit solution, although it is a lightweight, highly scalable and configurable events framework?

Q9。 解釋為什么盡管它是一個輕量級，高度可伸縮和可配置的事件框架，但為什么不能利用SQL Server擴展事件功能作為SQL Server審核解決方案呢？

This is because the Extended Events feature is limited in terms of auditing the different types of database DML changes. In addition, no option to compare the values before and after the modification process using this feature
這是因為擴展事件功能在審核數據庫DML更改的不同類型方面受到限制。此外，在使用此功能的修改過程之前和之后，沒有選項可以比較這些值

Creating a SQL Server audit using SQL Server Extended Events and Triggers使用SQL Server擴展事件和觸發器創建SQL Server審核。

Q10. How could we use the SQL Server triggers as a SQL Server Audit solution to track the database changes?

Q10。 我們如何使用SQL Server觸發器作為SQL Server審核解決方案來跟蹤數據庫更改？

SQL Server triggers can be customized to build a SQL Server auditing solution that fits your company requirements. For example, triggers can be created on all tables that contain critical data to track and log the modified or inserted data, with the ability to compare the data before and after the modification. You can also create an INSTEAD OF trigger to prevent the changes on a specific table and log the failed action to a data repository instead of performing that change
可以自定義SQL Server觸發器，以構建適合您公司要求SQL Server審核解決方案。例如，可以在包含關鍵數據的所有表上創建觸發器，以跟蹤和記錄修改或插入的數據，并能夠比較修改前后的數據。您還可以創建INSTEAD OF觸發器以防止對特定表進行更改，并將失敗的操作記錄到數據存儲庫中，而不是執行該更改

Creating a SQL Server audit using SQL Server Extended Events and Triggers使用SQL Server擴展事件和觸發器創建SQL Server審核。

Q11. Is it recommended to use the Change Data Capture feature to audit the database changes? Why?

sqlserver怎么用？ Q11。 是否建議使用“更改數據捕獲”功能來審核數據庫更改？ 為什么？

First of all, the SQL Server database audit using CDC provides no option to track the SELECT statement. In addition, CDC feature?requires significant maintenance and administration effort including an automatic process to archive the CDC tables, due to the fact that, SQL Server will keep the tracking data in the changing table for a configurable number of days only, and will be stored in the same or different data file within the tracked database
首先，使用CDCSQL Server數據庫審核沒有提供跟蹤SELECT語句的選項。此外，由于以下事實，CDC功能需要大量維護和管理工作，包括自動過程來存檔CDC表，原因是SQL Server僅將跟蹤數據在更改表中保留可配置的天數，并將其存儲。在跟蹤數據庫中相同或不同的數據文件中
Having the tracked data stored on the same database, creating an auditing report that shows all DML changes on all databases requires good development skills and big programming effort to call each function for each table from all databases and consolidate the data together
將跟蹤的數據存儲在同一數據庫中，創建一個審計報告以顯示所有數據庫上所有DML的更改都需要良好的開發技能和大量的編程工作，才能從所有數據庫中為每個表調用每個函數并將數據整合在一起
Another reason for not considering the CDC as a recommended SQL Server audit solution is that it will not handle the DDL changes on the CDC enabled tables automatically, which requires an extra effort to reflect this DDL change to the CDC tracking tables
不將CDC視為推薦SQL Server審核解決方案的另一個原因是，它不會自動處理已啟用CDC的表上的DDL更改，這需要付出額外的努力才能將DDL更改反映到CDC跟蹤表中
Also, the CDC capture jobs will not work when the SQL Server Agent service is not running. In this case, the database log file will grow rapidly, as the log truncation will not advance, until all the pending changes are logged in the CDC tracking tables
另外，當SQL Server代理服務未運行時，CDC捕獲作業將不起作用。在這種情況下，數據庫日志文件將Swift增長，因為日志截斷不會繼續進行，直到所有未決的更改都記錄在CDC跟蹤表中為止

Change Data Capture for auditing SQL Server更改數據捕獲”以審核SQL Server。

Q12. Is it recommended to use the Change Tracking feature to audit the database changes? Why?

Q12。 是否建議使用“更改跟蹤”功能來審核數據庫更改？ 為什么？

CT feature is not recommended to be used as a SQL Server audit solution. First of all, CT records no information about the inserted or deleted data, and no option to compare the data before and after the data modification process, without retaining the version history
不建議將CT功能用作SQL Server審核解決方案。首先，CT不記錄有關已插入或刪除的數據的信息，并且在保留版本歷史記錄的情況下，沒有在數據修改過程之前和之后比較數據的選項。
If the tables to be tracked have no Primary Key constraints defined on it, CT is not a tracking option here. In addition, CT requires extra coding effort to retrieve useful information by joining the internal tables of SQL Change Tracking with the tracked source table, based on the Primary Key value of the changed row
如果要跟蹤的表上沒有定義主鍵約束，則CT在這里不是跟蹤選項。另外，CT需要額外的編碼工作，以根據更改后的行的主鍵值將SQL Change Tracking的內部表與被跟蹤的源表連接起來，以檢索有用的信息。

Creating a SQL Server audit using SQL Server Change Tracking使用SQL Server更改跟蹤創建SQL Server審核。

Q13. What makes the SQL Audit built-in feature light and easy to use?

Q13。 是什么使SQL Audit內置功能輕巧易用？

It is built using the Extended Events feature
它是使用擴展事件功能構建的

sql server2012使用？ Q14. List the three main components in the SQL Server Audit feature and the differences between them.

Q14。 列出SQL Server審核功能中的三個主要組件以及它們之間的區別。

SQL Server Audit in which you can define the path to store the audit information, the auditing synchronization mode, the audit file rollover mechanism, and the action to be performed in case of audit failure SQL Server審核 ，您可以在其中定義存儲審核信息的路徑，審核同步模式，審核文件翻轉機制以及在審核失敗的情況下要執行的操作
SQL Server Audit Specifications that are used to track and log the changes performed at the SQL Server instance level and raised by the Extended Events feature SQL Server審核規范 ，用于跟蹤和記錄在SQL Server實例級別執行并由擴展事件功能引起的更改
SQL Database Audit Specification that is used to track and log different types of actions, performed at the database level and raised by the Extended Events feature SQL數據庫審核規范 ，用于跟蹤和記錄不同類型的操作，這些操作在數據庫級別執行并由擴展事件功能引發

SQL Server Audit Feature ComponentsSQL Server審核功能組件。

Q15. Will the SQL Server Audit feature work when the database is detached from the current SQL instance and attached to a new instance? Why?

Q15。 當數據庫從當前SQL實例分離并附加到新實例時，SQL Server審核功能是否可以工作？ 為什么？

No, it will not work. In the current instance, the audit specification is connected to a specific SQL Server Audit that controls the audit storage mechanism. When the database, with a Database Audit Specification configured on it, is detached from the current instance and attached to a new instance with no SQL Server Audit, has SQL Server Audit with a different GUID, or this new SQL Server instance does not support the SQL Server Audit feature, the Audit Specification will not work and not record will be logged. To fix this issue, you need to connect the Audit Specification to an existing SQL Server Audit, or simply create a new one and connect that Database Audit Specification to it
不，它不起作用。在當前實例中，審核規范連接到控制審核存儲機制的特定SQL Server審核。當將其上配置了數據庫審核規范的數據庫與當前實例分離并附加到沒有SQL Server審核的新實例，具有其他GUIDSQL Server審核或此新SQL Server實例不支持SQL Server審核功能，審核規范將不起作用，并且不會記錄任何記錄。若要解決此問題，您需要將審核規范連接到現有SQL Server審核，或僅創建一個新的并將該數據庫審核規范連接到它

Using the SQL Server Audit Feature to Audit Different Actions使用SQL Server審核功能來審核不同的操作

Q16. What should we consider when configuring the SQL Server Audit feature on a database participating in SQL Mirroring or Always on Availability Group site?

sql server有什么用、 Q16。 在參與SQL鏡像的數據庫或“始終在可用性組”站點上的數據庫上配置SQL Server審核功能時，應該考慮什么？

In this case, you need to create the same SQL Server Audit, with the same GUID as the principal or primary server, in the mirrored or secondary replicas. In addition, you should grant permission to the SQL Server service account on the folder where the audit logs will be stored. Otherwise, the audit specification will not work in case of failover
在這種情況下，您需要在鏡像副本或輔助副本中創建與主體服務器或主服務器相同的GUID，并具有相同的GUID。此外，您應授予將存儲審核日志的文件夾上SQL Server服務帳戶的權限。否則，在故障轉移的情況下審核規范將不起作用

Using the SQL Server Audit Feature to Audit Different Actions使用SQL Server審核功能來審核不同的操作

Q17. Is it recommended to use the System-versioned Temporal Table feature as a SQL Server audit solution? Why?

Q17。 是否建議使用系統版本的臨時表功能作為SQL Server審核解決方案？ 為什么？

Firstly, the System-versioned Temporal Table feature records no information about the type of the performed changes. In addition, both the source data and the historical data will be kept in the same database, making this option less secure SQL audit solution. Also, the System-versioned Temporal Table feature provides no option to audit the DDL or Server level changes
首先，系統版本的時態表功能不記錄有關已執行更改的類型的信息。此外，源數據和歷史數據都將保存在同一數據庫中，從而使該選項的安全性降低了SQL審計解決方案。此外，系統版本的臨時表功能不提供審計DDL或服務器級別更改的選項

check Performing a SQL Server Audit using System-Versioned Temporal Tables請選中使用系統版本的臨時表執行SQL Server審核。

Q18. Why is it very important to define the SQL auditing scope at the beginning of the audit process design and why we need to narrow down that scope?

Q18。 為什么在審計流程設計的開始就定義SQL審計范圍非常重要，為什么我們需要縮小范圍呢？

The process of defining the scope of the SQL audit project is very important step as it helps in specifying what is required to be audited, who we should audit and for how long, which is very important to take decision in the next auditing steps within the project and prevent the excessive consumption of the SQL Server resources that leads to performance degradation issue. In addition, narrowing down the amount of collected data will help in making it easier to review it
定義SQL審核項目范圍的過程非常重要，因為它有助于指定需要審核的內容，我們應審核的對象以及審核的時間，這對于在Windows的后續審核步驟中做出決定非常重要。項目，并防止過度使用SQL Server資源而導致性能下降的問題。此外，縮小收集到的數據量將有助于使其更易于查看

SQL Server Auditing Best PracticesSQL Server審核最佳實踐。

sqlserver數據庫？ Q19. Why is it important to audit your auditing system?

Q19。 為什么審核您的審核系統很重要？

Auditing the changes that are performed on the SQL Server audit solution helps in catching any unauthorized user who is trying to disable the auditing process to perform illegal activities. In this way, you will make sure that no action is performed with being audited. In addition, this helps also in meeting the auditor’s requirements regarding the audit solution logs’ integrity by providing evidence for covering all eventualities
審核在SQL Server審核解決方案上執行的更改有助于捕獲試圖阻止審核過程執行非法活動的任何未授權用戶。這樣，您將確保不執行任何審計操作。此外，這還通過提供涵蓋所有突發事件的證據來幫助滿足審核員對審核解決方案日志完整性的要求。
Another simple use for this audit is to remind the database administrator to reenable the audit in case he disabled it for maintenance purposes and missed enabling it again
此審核的另一種簡單用法是提醒數據庫管理員重新啟用審核，以防他出于維護目的而禁用它，而錯過了再次啟用它的情況。