As discussed in Part I,
the execution mode of each policy is determined by the characteristics of the
Management facet that is used by the condition in the policy.
Facets support “On
Change – Prevent” if there is transactional support for the DDL statements that
change the facet state. Only the Login, User and Database Security facets support
the “On Change – Prevent” mode. However, it is usually more important to
prevent or correct a violation than just to log it. Fortunately, we can use SQL
Server Agent alerts and jobs to remedy the limitations. When policies are
executed in one of the three automated modes, if a policy violation occurs, a
message is written to the SQL Server error log and the Application log. The
error message numbers are shown below.
|
Execution |
Message |
|
On Change |
34050 |
|
On Change |
34051 |
|
On Schedule |
34052 |
|
On Change |
34053 |
A SQL Server
Agent alert can be set up to detect the error message and invoke a job to
correct the violation.
Let’s look at an example. As we know, it is important to
back up the transaction log of a database that is on Full or Bulk-logged
recovery model regularly so that the transaction log won’t fill up. We can
create a policy to check the last time the transaction log was backed up and
make sure it was done within the last day.
1. In
SSMS, expand Management in Object Explorer, expand Policy Management,
right click Conditions, and select New Condition. In the New
Condition dialog box, in the Name field, type Transaction Log
Last Backup Date. Pick the Database facet. In the Expression
area, in the Field box, select @RecoveryModel, in the Operator
box select =, and in the Value field select Full. Create
another clause with @RecoveryModel and a different value of BulkLogged,
and select Or in the AndOr box. Select both clauses and right
click in the highlighted area, then click Group Clause to group the two clauses.
This creates an expression to check if the targeted database is in Full or
Bulk-logged recovery model.
We still need another clause to
check if the transaction log of the database was backed up within the last day.
Select AND in the AndOr field, @LastLogBackupDate in the Field
box, >= in the Operator box, and in the Value field, click on
the … button. This brings up an Advanced Edit dialog box. Type DateAdd(‘day’,
-1, GetDate()) in the Cell value box. Close the dialog box.
2.
Right click Policies in the Object Explorer, and select New
Policy. In the New Policy dialog box, in the Name box, type Safe
Transaction Log Backup Date. Check the Enabled
box to enable the automated execution modes. In the Check condition box,
select the Transaction Log Last Backup Date condition under the Database facet. Select the Online
User Database condition under the Database facet in the Against
targets box. In the Execution Mode
box, select On Schedule as the execution mode, and in the Schedule
box, pick the CollectorSchedule_Every_15min schedule that will run the
policy every 15 minutes. Note that the On Change – Prevent execution
mode is not available for the database facet.
The Online User Database condition is one of the policies that are shipped with SQL
Server 2008. By default, the policies are not installed on the SQL Server. To
import them, right click Policies under Policy Management, and
select Import Policy. This brings up an Import dialog box. Click
the … button in the Files to import box. Another Select Policy
box pops up. Navigate to the directory C:\Program Files\Microsoft SQL
Server\100\Tools\Policies\DatabaseEngine\1033, and select all of the files
under this folder. Click Open to close this box. Click Ok to
close the Import dialog box. SQL Server will import all the policies
under that directory.
SQL Server creates a job called “check_Safe Transaction Log
Backup Date_job” to evaluate the “Safe Transaction Log Backup Date” policy
every 15 minutes. If you look at the only job step of this job, you see that SQL
Server Agent uses a PowerShell cmdlet called Evaluate-Policy to evaluate the
policy.
If the transaction log of a database is found not backed up
within the last day, an error message with an error number 34052 will be logged
into the SQL Server error log and the Application log. In our example, we have
a user database called Matrix that has not been backed up. Once the policy is
evaluated by the “check_Safe Transaction Log Backup Date_job” job, a red cross appears
on the Matrix database, which means the Matrix database violates the policy.
An error message is also logged into the SQL Server error
log.
Error: 34052, Severity: 16, State: 1.
Policy ‘Safe Transaction Log Backup Date’ has been violated by target ‘/Server/POWERPC/Database/Matrix’.
We can implement an alert to detect the error 34052 and
invoke a job called “Fix Transaction Log Backup” to parse the error message and
back up the transaction log of the violating database. The error message is
passed by the alert to the job in an (A-MSG) token. The job contains one
Transact-SQL script job step with the following script.
DECLARE @errormsg nvarchar(800), @start int
DECLARE @policyname sysname
DECLARE @dbname sysname
DECLARE @sqlstring nvarchar(800)
SET @errormsg = N’$(ESCAPE_SQUOTE(A-MSG))’
SET @start=9
SET @errormsg = SUBSTRING(@errormsg, @start, LEN(@errormsg) – @start)
SET @policyname = SUBSTRING(@errormsg, 1, CHARINDEX(””, @errormsg)-1)
SET @start=CHARINDEX(‘Database’, @errormsg) + 9
SET @dbname = SUBSTRING(@errormsg, @start, LEN(@errormsg) – @start)
SET @sqlstring = ‘BACKUP LOG ‘ + @dbname + ‘ TO DISK=”C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\’
+ @dbname + ‘_Log.bak”’
print ‘sqlstring: ‘ + @sqlstring
exec (@sqlstring)
+ @dbname + ‘_Log.bak”’
In our example, we create an alert called Unsafe
Transaction Log Backup Alert to invoke the job. This alert detects any errors
with a number 34052 and a message text containing the policy name, Safe
Transaction Log Backup Date. The Fix Transaction Log Backup job is
also specified in the Response pane of the alert, and it will run when
the alert is raised.
After the “Unsafe Transaction Log Backup Alert” alert is
defined, the next time the policy is evaluated, an alert is raised
automatically and the “Fix Transaction Log Backup” job is invoked to back up
the transaction log of the Matrix database. Here is the output from the job in the
job history.
Date 4/20/2008 8:30:03 PM
Log Job History (Fix Transaction Log Backup)
Step ID 1
Server POWERPC
Job Name Fix Transaction Log Backup
Step Name Back Up Log of Violating Database
Duration 00:00:00
Sql Severity 0
Sql Message ID 3014
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: POWERPC\Yan. sqlstring: BACKUP LOG Matrix TO DISK=’C:\Program
Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Matrix_Log.bak’ [SQLSTATE
01000] (Message 0) Processed 3 pages for database ‘Matrix’, file ‘Matrix_log’ on file 1. [SQLSTATE
01000] (Message 4035) BACKUP LOG successfully processed 3 pages in 0.037 seconds (0.607
MB/sec). [SQLSTATE 01000] (Message 3014). The step succeeded.
As we can see above, the job backed up the transaction log
of the Matrix database successfully.
If you got an error message
“Variable A-MSG not found”, you need to enable alert tokens. Right-click SQL
Server Agent in Object Explorer, select Properties, and on the Alert
System pane, select Replace tokens for all job responses to alerts
to enable tokens.
Conclusion
In this article, we have shown you how to use SQL Server
Agent alerts and jobs to fix policy incompliance automatically.
