Level protection Project SQL? Server audit Scenarios

Save the SQL Server Default trace file for more than half a year based on the level of protection Project SQL Server audit requirements.

Solution Solutions

for users, Default Trace you can only turn off or turn on the trace, and you cannot modify any parameters. Therefore, we only have to synchronize the trace files to save.

Plan to synchronize the *.trc files of the centralized transaction database to the local, long-term storage on the standby server, through the robocopy that comes with Windows.

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

--= = = Robocopy introduction ===============================================

Robocopy can monitor the folders to be backed up in real time, and as long as the folders are modified to a certain time and program, Robocopy will start the backup immediately. It will always monitor the folder unless the user terminates it himself.
[Simple command]
Robocopy d:\work e:\back/e/copyall/mot:1/mon:2
[Parameter explanation]
The above command indicates that all the information in the folder is copied to the destination folder and monitored. After you execute this command, the command Prompt window appears as shown. Once the time is over a minute and the source folder has at least 2 or more modifications, Robocopy will automatically start another process
, perform the previous backup operation. Robocopy can monitor the source folder structure, file and folder name, size, and last modified time, where even when information such as properties and permissions are modified, Robocopy is also remembered as the number of changes.

-- ====  Default Trace Introduction ===============================================

SQL Server default Trace is turned on after the SQL Server instance is installed. Is the most lightweight trace that is turned on by default in SQL Server, consisting of 5 trace files (. trc) with a maximum value of 20MB per file, stored in the SQL Server log directory. For example, The oldest file is deleted when SQL Server restarts, or when the file currently in use reaches the maximum value.

It records useful audits with the object event and the Security audit event.

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

Level protection Project SQL? Server audit Scenarios