Data Maintenance with SqlTrackingService

Windows Workflow Foundation provides a data maintenance feature for the SqlTrackingService. This feature enables you to partition tracking data upon completion of workflow instances. Partitioning involves moving tracking data inserted from workflow instances that have since finished executing into separate database tables known as partitions. Windows Workflow Foundation provides two separate schemes for partitioning, enabling you to choose the one that is better for your workflow solution:

• Partition On Completion

• On Demand partitioning

Partition On Completion

Partition On Completion is designed for applications that have no downtime and do not want to incur downtime. The partitioning is done on the completion of every workflow instance. The records that are generated from tracking a workflow instance are added to the regular tracking tables until the workflow instance finishes. At that point, the records are moved to partition tables. When partitioning is enabled, a new set of tables is created periodically for tracking data. The tracked data of completed workflow instances moves to these new partitions without disrupting currently running applications.

Configuring Partition On Completion

By default, partitioning for the SqlTrackingService is not turned on. To enable partitioning that is managed by the SqlTrackingService, set the PartitionOnCompletion property to true as shown in the following code example.

[C#]

string connectionString = "Initial Catalog=Tracking;Data Source=localhost;Integrated Security=SSPI;";
SqlTrackingService sqlTrackingService = new SqlTrackingService(connectionString);
sqlTrackingService.PartitionOnCompletion = true;

When you use the PartitionOnCompletion property, you can configure partition tables so that new partition tables are created on time-based intervals. You do this by changing the TrackingPartitionInterval.Interval column in the SqlTrackingService schema. A stored procedure named SetPartitionInterval enables you to perform this task programmatically to customize the interval during application execution. The default partition interval is monthly, “m”. The available values also include daily, “d”, and yearly, “y”. When this is enabled, Windows Workflow Foundation creates a new partition set (new set of tables) on the time interval that you specify. The table vw_TrackingPartitionSetName contains the information about the partition set name, including Name yyyy_m, CreatedDateTime, EndDateTime, and the PartitionInterval. When workflow instances are finished, the corresponding tracked data is moved to the newly created set of tables. The following code demonstrates how to set the partition interval when you use the PartitionOnCompletion scheme.

[C#]

internal static void SetPartitionInterval()
{
    // Valid values are 'd' (daily), 'm' (monthly), and 'y' (yearly).  
    // The default is 'm'.
    SqlCommand command = new SqlCommand("dbo.SetPartitionInterval");
    command.CommandType = CommandType.StoredProcedure;
    command.Connection = new SqlConnection(connectionString);
            
    SqlParameter intervalParameter = new SqlParameter("@Interval", SqlDbType.Char);
    intervalParameter.SqlValue = 'd';
    command.Parameters.Add(intervalParameter);
    try
    {
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
    finally
    {
        if ((command != null) && (command.Connection != null) && 
            (ConnectionState.Closed != command.Connection.State))
        {
            command.Connection.Close();
        }
        command.Dispose();
    }
}

On Demand Partitioning

On Demand Partitioning is designed for applications that periodically or consistently have downtime and want to do partitioning during that downtime. To enable this scheme, create a task that runs the PartitionCompletedWorkflowInstances stored procedure that is created when you run the SqlTracking_Logic.sql script. When this stored procedure is run, the tracking data that was inserted by completed workflow instances from the live tables is moved to the partition tables.

Displaying Tracking Partition Information and Tables

The following code shows how to extract partition information, including the created tables from the SqlTrackingService database.

[C#]

internal static void ShowTrackingPartitionInformation()
{
    // Show the contents of the TrackingPartitionName table.

    SqlCommand command = new SqlCommand("SELECT * FROM vw_TrackingPartitionSetName");
    SqlDataReader reader = null;
    command.CommandType = CommandType.Text;
    command.Connection = new SqlConnection(connectionString);

    try
    {
        command.Connection.Open();
        reader = command.ExecuteReader();
        if (reader.Read())
        {
            Console.WriteLine();
            Console.WriteLine("***************************");
            Console.WriteLine("Partition information: ");
            Console.WriteLine("PartitionId: {0}", reader[0]);
            Console.WriteLine("Name: {0}", reader[1]);
            Console.WriteLine("Created: {0}", reader[2]);
            Console.WriteLine("End: {0}", reader[3] is System.DBNull ? "NULL" : reader[3]);
            Console.WriteLine("Partition Interval: {0}", reader[4]);
            Console.WriteLine("***************************");
        }
        else
        {
            Console.WriteLine();
            Console.WriteLine("No partition information present.");
        }

    }
    finally
    {
        if ((reader != null) && (!reader.IsClosed))
            reader.Close();
        if ((command != null) && (command.Connection != null) && (ConnectionState.Closed != command.Connection.State))
        {
            command.Connection.Close();
        }
        command.Dispose();
    }
}

The following code demonstrates how to display the names of the partition tables that are in the SqlTrackingService database.

internal static void ShowPartitionTableInformation()
{
    SqlCommand command = new SqlCommand(
        "declare @trackingName varchar(255) select @trackingName = Name from vw_TrackingPartitionSetName " +
        "select name from sysobjects where name like '%' + @trackingName");
    
    Console.WriteLine();
    Console.WriteLine("***************************");
    Console.WriteLine("Partition tables: ");
    SqlDataReader reader = null;
    command.CommandType = CommandType.Text;
    command.Connection = new SqlConnection(connectionString);

    try
    {
        command.Connection.Open();
        reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(reader[0]);
        }
    }
    finally
    {
        if ((reader != null) && (!reader.IsClosed))
            reader.Close();
        if ((command != null) && (command.Connection != null) && (ConnectionState.Closed != command.Connection.State))
        {
            command.Connection.Close();
        }
        command.Dispose();
    }
    Console.WriteLine("***************************");
}

See Also

Reference

SqlTrackingService
PartitionOnCompletion

Concepts

Using SqlTrackingService
Querying SqlTrackingService Data With SqlTrackingQuery
Windows Workflow Tracking Services

Other Resources

SQL Data Maintenance Sample
Query Using SQLTrackingService Sample