How to: Create a Lifetime Policy

Artículo
09/22/2010

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

This tutorial demonstrates how to use Lifetime Services in the SQL Server Modeling Services database. In this tutorial, you will learn the following tasks:

Add a Lifetime policy to the Modeling Services database.
Create a table that uses the Lifetime policy to remove old data.
Observe the effects of a Lifetime policy rule.
Disable an existing Lifetime policy.

The scenario for this tutorial focuses on an enterprise that uses a custom Monitoring schema in the Modeling Services database. An ErrorsTable table in this schema records various types of application errors for reporting and diagnostic purposes. Schema developers begin to work on a new version of the Monitoring schema. They observe that the ErrorsTable grows very large over the course of several months. This causes unnecessary disk usage as well as performance problems for the applications that interface with this table. Management determines that it is unnecessary to store errors longer than one month. To solve this problem, the schema developers define a lifetime policy to regularly delete these old rows in the [Monitoring].[ErrorsTable].

Note

The Modeling Services does not currently schedule jobs to execute the lifetime services policies. This feature is not fully implemented. This topic explains how to setup lifetime services policies and how to manually execute them.

To create a Lifetime policy

On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.
In the Connect to Server dialog, type the name of the Modeling Services server in the Server name combo box.
Specify the Authentication properties, and then click the Connect button.
On the toolbar, click the New Query button. A blank query window should open.

Insert the following T-SQL statements into the query window.

use Repository
go

-- Create a schedule to run the Lifetime policy.
exec [msdb].[dbo].sp_add_schedule 
    @schedule_name = N'[Monitoring].[Monthly]',
    @freq_type = 16, -- months
    @freq_interval = 1, -- first day of the month
    @freq_recurrence_factor = 1, -- every month
    @active_start_time = 010000 -- 01:00:00 am

-- Create a Lifetime policy.
if not exists(select * from [Repository.Item].[RemovalPoliciesTable] 
   where Name = 'DailyRemoval')
insert [Repository.Item].[RemovalPolicies] ([Name], [Schedule], [Rule])
   values ('MonthlyRemoval', '[Monitoring].[Monthly]', N'[TimeStamp] <= @lastTimeStamp')

Press the F5 key to run the T-SQL query. This query creates a SQL Server Agent schedule named [Monitoring].[Monthly]. This schedule occurs every month on the first day of the month at 1:00 A.M. The second part of the query adds a row to the [Repository.Item].[RemovalPolicies] table that specifies a Lifetime policy named MonthlyRemoval. The [Monitoring].[Monthly] schedule provides the Lifetime policy with a schedule. The Rule column specifies the rule to use for the removal of old rows. Rules that use the @lastTimeStamp variable require that their target tables have a timestamp column named TimeStamp. The rule [TimeStamp] <= @lastTimeStamp removes all rows with a TimeStamp value less than the time stamp at the end of the last run of this Lifetime policy.

Note

Note that on the first run of the Lifetime policy, the @lastTimeStamp value is 0, which means that no rows are removed.

To apply a Lifetime policy to a table

After creating a schedule and associated Lifetime policy, you must create a table that is compatible with Lifetime Services in the Modeling Services. First, create the [Monitoring].[ErrorsTable] table with a timestamp column named TimeStamp.

In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statements.
```
-- Create a schema.
create schema [Monitoring] authorization [RepositoryOwner]
go

-- Create a table with a TimeStamp column.
create table [Monitoring].[ErrorsTable](
   [Id] bigint not null identity,
   [Error] nvarchar(256) not null,
   [ErrorDate] datetime not null,
   [TimeStamp] timestamp not null
)
go
```
Press the F5 key to run the query that creates the [Monitoring].[ErrorsTable].

Next, insert initial error rows into the new table. These will be used to test the Lifetime policy.

In the query window, replace the previous text with the following T-SQL statement.

-- Insert test data into the [Monitoring].[ErrorsTable].
insert [Monitoring].[ErrorsTable] (Error, ErrorDate)
   select N'Access Error', GETDATE() union all
   select N'Read Error', GETDATE() union all
   select N'Access Error', GETDATE() union all
   select N'Update Error', GETDATE()

Press the F5 key to run the query. The TimeStamp column automatically populates with a database-wide time stamp value. This time stamp value increases by one for every change in the database (for example, an inserted or modified row in any table).

Add an extended property named RemoveOldItems to the [Monitoring].[ErrorsTable] table to apply the MonthlyRemoval Lifetime policy to this table.

In the query window, replace the previous text with the following T-SQL statement.

-- Apply the policy to the table.
execute sys.sp_addextendedproperty
  @name = N'RemoveOldItems', @value = N'MonthlyRemoval',
  @level0Type = N'Schema', @level0Name = [Monitoring],
  @level1Type = N'Table', @level1Name = [ErrorsTable]

To observe the effects of Lifetime policies

Before manually exploring the operation of the Lifetime policy, examine the rows in both the [Repository.Item].[RemovalPolicies] view and the [Monitoring].[ErrorsTable] table.

In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statements.
```
-- View the rows in the [Repository.Item].[RemovalPolicies] view
-- as well as the rows in the target table.
select * from [Repository.Item].[RemovalPolicies]
select * from [Monitoring].[ErrorsTable]
```
Press the F5 key to run the query. In the results for the [Repository.Item].[RemovalPolicies] query, you should see the MontlyRemoval policy. Note that the LastTimeStamp column is 0. The results for the [Monitoring].[ErrorsTable] show the four rows previously added. Note the values of the TimeStamp column for each row.
In the query window, replace the previous text with the following T-SQL statement. This calls the internal stored procedure [Repository.Item].[RemoveOldItems] to simulate what happens when Lifetime Services runs the scheduled Lifetime policy.
```
-- Simulate the calling of [Repository.Item].[RemoveOldItems].
-- Note this should never be called in practice, because the 
-- Repository will manage the schedules for calling this based 
-- on the schedule.
exec [Repository.Item].[RemoveOldItems] @policy='MonthlyRemoval'

-- View the rows in the [Repository.Item].[RemovalPolicies] view
-- as well as the rows in the target table.
select * from [Repository.Item].[RemovalPolicies]
select * from [Monitoring].[ErrorsTable]
```
Note

To call [Repository.Item].[RemoveOldItems], you must be a member of the db_owner role on the Modeling Services database or a member of the sysadmin role on the database server.

Warning

This tutorial directly calls [Repository.Item].[RemoveOldItems] to immediately observe the effects of a Lifetime policy. Except for a learning exercise, do not directly call the [Repository.Item].[RemoveOldItems] stored procedure or manually execute any jobs related to lifetime services. Modeling Services Lifetime Services manages this for you. Also the frequency of running policies controls the data lifespan, so running the same policy twice in succession results in the deletion of all of the rows from the table (this result might vary depending on the rule used).
Press the F5 key to run the query. In addition to running the [Repository.Item].[RemoveOldItems], these statements also show the rows of the [Repository.Item].[RemovalPolicies] view and the [Monitoring].[ErrorsTable] table. Note that no rows are deleted from the target table on the first run of the MonthlyRemoval policy. However, the LastTimeStamp column now records the time stamp at the time that the policy ran.
If Lifetime Services had run the [Repository.Item].[RemoveOldItems] stored procedure, it would then use the schedule to determine the next run of the policy. In this example, Lifetime Services would wait one month to run the policy again. At that time, any rows with a TimeStamp value less than the LastTimeStamp column would be deleted.

In this tutorial, you will simulate the effect of the next run of the policy by calling [Repository.Item].[RemoveOldItems] again manually.

In the query window, replace the previous text with the following T-SQL statements.
```
-- Add one row.
insert [Monitoring].[ErrorsTable] (Error, ErrorDate)
   select N'Application Error', GETDATE()
go

-- Run the MonthlyRemoval policy again.
exec [Repository.Item].[RemoveOldItems] @policy='MonthlyRemoval'
go

-- View the rows in the [Repository.Item].[RemovalPolicies] view
-- as well as the rows in the target table.
select * from [Repository.Item].[RemovalPolicies]
select * from [Monitoring].[ErrorsTable]
```
Press the F5 key to run the query. Before running the MontlyRemoval policy again, this script inserts a new row into the [Monitoring].[ErrorsTable]. This row automatically gets a TimeStamp value that is higher than the LastTimeStamp value for the policy. When the policy is run again, all other older rows are deleted based on the policy. Note that the LastTimeStamp value is updated again. For rules that use the TimeStamp column, the lifetime of data is determined by the frequency of running the policy.

To disable a Lifetime policy

To remove the Lifetime policy from a single table, remove the RemoveOldItems extended property from the table.

In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statement.

-- Remove the extended property from the table.
execute sys.sp_dropextendedproperty
  @name = N'RemoveOldItems', 
  @level0Type = N'Schema', @level0Name = [Monitoring],
  @level1Type = N'Table', @level1Name = [ErrorsTable]

Press the F5 key to run the query. After removing the RemoveOldItems extended property, future runs of the MonthlyRemoval policy will not affect the [Monitoring].[ErrorsTable].
To remove the policy from the Modeling Services database, delete the policy from the [Repository.Item].[RemovalPolicies] table.

In the query window, replace the previous text with the following T-SQL statements.
```
-- Delete the policy.
delete [Repository.Item].[RemovalPoliciesTable] where Name = 'MonthlyRemoval'
```
Press the F5 key to run the query and remove the MonthlyRemoval policy.

Compartir a través de

How to: Create a Lifetime Policy

To create a Lifetime policy

To apply a Lifetime policy to a table

To observe the effects of Lifetime policies

To disable a Lifetime policy

See Also

Concepts

Recursos adicionales