Shulou(Shulou.com)06/01 Report--

According to the SQL Server audit requirements of the grade protection project, keep the SQL Server Default Trace file for more than half a year.

Solution

As far as the user is concerned, Default Trace can only turn the trace off or on, and cannot modify any parameters. Therefore, we have to synchronize the trace file and save it.

It is planned to synchronize the * .trc files of the centralized transaction database to the local through the robocopy provided by Windows on the standby domain control server for long-term preservation.

Robocopy "\ 10.198.1.111\ e$\ MSSQL12.XXX\ MSSQL\ Log"D:\ Robocopy\ 10.198.1.111" * .trc / MON:1 / mot:15 / mon:2

-- = introduction to Robocopy =

Robocopy can monitor the folder to be backed up in real time. As long as the folder is modified to a certain time and program, Robocopy will start the backup immediately. It will always monitor the folder unless the user terminates.

[simple command]

Robocopy d:\ work e:\ back / e / copyall / mot:1 / mon:2

[parameter explanation]

The above command indicates that all information from the folder is copied to the destination folder and monitored. After executing this command, the command prompt window is shown in the following figure. Once a minute has passed and there are at least two or more changes to the source folder, Robocopy will automatically start another process

To perform the above backup operation Robocopy can monitor the source folder structure, file and folder name, size, and last modification time. Here, even when information such as properties and permissions are modified, Robocopy is recorded as the number of changes.

-- = introduction to Default Trace =

SQL Server Default Trace is enabled by default after the SQL Server instance is installed. Is the lightest trace enabled by default in SQL Server. It consists of five trace files (.trc). The maximum value for each file is 20MB, which is stored in the SQL Server log directory. As shown in the following figure, when SQL Server restarts, or when the currently used file reaches its maximum value, the oldest file is deleted.

Useful audits it records are Object events and Security Audit events.

Objects events include:

Object Altered

Object Created

Object Deleted

Security Audit events include:

Audit Add DB user event

Audit Add login to server role event

Audit Add Member to DB role event

Audit Add Role event

Audit Add login event

Audit Backup/Restore event

Audit Change Database owner

Audit DBCC event

Audit Database Scope GDR event (Grant, Deny, Revoke)

Audit Login Change Property event

Audit Login Failed

Audit Login GDR event

Audit Schema Object GDR event

Audit Schema Object Take Ownership

Audit Server Starts and Stops

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.