Tag: management

SQL Server : Auditing

Create a New Audit

The “Audit” object is really just a reference to where the audit file(s) will be located and how they will be managed. It doesn’t define what specific auditing will be done. That is controlled by the server-level and database-level audits explained later.

  • Expand the Management Studio tree for the database of interest. “Security > Audits”.
  • Right-click on “Audits” and select the “New Audit” option.
  • Name the “Audit name” to indicate what it is doing. In this case “ServerAudit”.
  • Decide what should happen on audit failure.
  • Pick the destination (File, Security Log, Application Log). I picked File.
  • If you pick “File”, enter the path and file handling details.
  • Click “OK”.
  • Right-click on the new audit and select the “Enable Audit” option. If you don’t do this you will not be able to write any audit information to the audit location.

Server-Level Audits

  • Expand the Management Studio tree for the server. “Security > Server Audit Specifications”.
  • Right-click on “Server Audit Specifications” and pick the “New Server Audit Specification” option.
  • Set the name. It’s easier if you make this match the Audit you created above.
  • Select the matching “Audit” from the drop-down.
  • Pick the audit action type(s) required.
  • Click “OK”.
  • Right-click on the new server audit specification and select the “Enable Server Audit Specification” option.
  • Right-click on the audit you defined previously (not the server audit specification) and select the “View Audit Logs” option.

You can find an explanation of all the Audit Action Groups and Actions here.

Database-Level Audits

The process is similar to the server-level audits.

  • Create a new Audit as described previously.
  • Expand the specific database of interest in the tree. (Server-Name > Databases > Database-Name > Security > Database Audit Specifications).
  • Right-click on “Database Audit Specifications” and pick the “New Database Audit Specification” option.
  • Set the name. It’s easier if you make this match the Audit you created above.
  • Select the matching “Audit” from the drop-down.
  • Pick the audit action type(s) required. Make them as specific as possible by targeting (Database, Schema or Object) and clicking the “…” buttons to select specific qualifiers.
  • Click “OK”.
  • Right-click on the new database audit specification and select the “Enable Database Audit Specification” option.
  • Right-click on the audit you defined previously (not the server audit specification) and select the “View Audit Logs” option.

You can find an explanation of all the Audit Action Groups and Actions here.

Importing Audits

You can import audit files using the following statement, with the file pattern adjusted accordingly.

SELECT *
FROM sys.fn_get_audit_file (
         'c:\temp\ServerAudit*.sqlaudit',
         Default,
         Default
       )

The “Default” setting mean we will read all files matching the pattern. We can actually set an initial file and offset to limit the files we read. This is explained in the following document.

sys.fn_get_audit_file (Transact-SQL)

We can also create a table holding the files as follows.

SELECT * INTO MyAuditTable
FROM sys.fn_get_audit_file (
         'c:\temp\ServerAudit*.sqlaudit',
         Default,
         Default
        )

SELECT * FROM MyAuditTable

Policy-Based Management

  • Expand the tree. (Server-name > Management > Policy Management).
  • You will see Facets, Conditions and Policies. Facets represent groups of items you can test. Conditions a test itself, using a facet. A policy is a group of one or more conditions. Policies can be scheduled, event-based or on-demand etc.
  • Expand the “Facets” tree node.
  • Double-click on the “Database” facet.
  • Scroll down the list of properties and find AutoShrink. This is what we will test.
  • Richt-click on “Conditions” and select the “New Condition” option.
  • Give it a reasonable name (AutoShrinkCondition).
  • Select the “Database” facet.
  • Enter the Field “@AutoShrink:, the operator “=” and the value “FALSE’.
  • Click “OK”.
  • Right-click on “Policy” and select the “New Policy” option.
  • Give it a reasonable name (AutoShrinkPolicy).
  • Select the condition(s) you want in the policy.
  • Decide how it should be evaluated (on-demand, scheduled etc.).
  • Click “OK”.
  • For an on-demand policy, right-click on the policy and select the “Evaluate” option. The results will let you know if all databases have AutoShrink=FALSE or not.

For more information see:

SQL Server : Instance Configuration and Management

Recycling Logs

By default SQL Server retains six logs are only recycles a log when the instance is restarted. As a result, the logs can grow really big, wasting a lot of space and making them really hard to open and read. You should probably consider recycling the logs on a regular basis and altering the number of logs retained.

In this example, we will recycle the logs every day and retain 30 days worth of logs.

  • Expand “Management”.
  • Right-click on the “SQL Server Logs” node and select the “Configure” menu option.
  • Check the Limit option and enter the maximum number of error log files to “30”.
  • Click the “OK” button.

Recycle the error log every day.

  • Expand “SQL Server Agent”.
  • Right-click on “Jobs”.
  • Select the “New Job…” menu option.
  • Set Name to “RecycleErrorLog” and Owner to “sa”.
  • Click on the “Steps” page and click the “New” button.
  • Set the “Step name” to “Step_1” and set the Command to “sp_cycle_errorlog”.
  • Click the “OK” button.
  • Click on the “Schedules” page and click the “New” button.
  • Set the Name to “DailyMidnight”.
  • Change “Occurs” to “Daily” and leave the default “Occurs once at” as “00:00:00”.
  • Click the “OK” button.
  • Click the “OK” button on the “New Job” dialog.

For more information see:

Patch Management

  • RTM – Release to Market : The initial release.
  • Hotfix : Specific fixes.
  • CU – Cumulative Update : Rollup hotfixes.
  • SP – Service Pack : A large number of patches combined together.

Check out the Update Center for Microsoft SQL Server.

Trace Flags

Where “n” is a trace flag number. To alter a trace flag at session level do the following.

dbcc traceon(n)

To alter the trace flag globally do the following. It will not persist past a restart of SQL Server.

dbcc traceon(n, -1)

To make a persistent change to a trace flag do the following.

  • Open “SQL Server Configuration Manager”.
  • Double-click on the “SQL Server Service”.
  • Click the “Startup Parameters” tab.
  • Add a startup parameter “-T3226” and click the “Add” button.
  • Click the “Apply” button and click “OK” on the warning message.

The trace flag will be applied on subsequent restarts.

For more information see: