Auditing and Table Partitions in Dynamics CRM 2011

In this article we’ll be looking behind the scenes at how Dynamics CRM 2011’s new audit logs are engineered to maintain performance, and explain why what looked like anomalous behaviour to one of our customers was actually working just fine.

If you’ve been looking closely at how the auditing feature works you may have noticed that it makes use of Microsoft SQL Server’s partitioning feature as a means of improving the scalability and performance of the audit logs while retaining the audit data in the organisation database.  Specifically, the SDK article Auditing Feature Overview says:

“For Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online servers that use Microsoft SQL Server Enterprise editions, auditing data is recorded over time (quarterly) in partitions. A partition is called an audit log in the Microsoft Dynamics CRM Web application. Partitions are not supported, and therefore, not used, on a Microsoft Dynamics CRM 2011server that is running Microsoft SQL Server Standard edition.”

General information about partitioning is described in the MSDN article Partitioned Table and Index Concepts, and in more detail in the white paper “Partitioned Table and Index Strategies Using SQL Server 2008”. It’s well worth understanding these concepts if you have to design the database storage for applications using large data sets.  For now we’ll briefly explain that the audit data table is partitioned on a date column function that places each calendar quarter’s data into a different partition, and each partition behaves like a ‘mini table’ ensuring that audit operations aren’t overly slowed by having to navigate a single ever growing table.

If you decompose that quote above you might think it means the following two things:

• If you are using SQL Server Enterprise edition partitioning will be in use.
• If you are using SQL Server Standard edition partitioning will not be in use.

So a customer was a bit surprised when they looked at a Dynamics CRM 2011 instance that was using SQL Server 2008 Standard Edition and saw this under Settings –> System –> Auditing –> Audit Log Management:

Surely this suggests that quarterly partitioning is in use on Standard Edition?  Adding to their confusion even more was that if they then looked at the same screen on their SQL Server Enterprise Edition environment they saw only a single row (so no hint of partitions).  It looks as if it works exactly the opposite way to that statement above!  And that would be strange since Standard Edition just doesn’t support partitioning.

So let’s dig a bit deeper and see what is actually happening…

In order to get to the bottom of this we started by looking at what was happening on another SQL Server Enterprise Edition deployment.

First of all we ran a query on the tenant’s database to see what partitions existed for the table that stores the auditing data, AuditBase.  ContactBase is in there just as a control: it should have just the one storage partition.

 select t.name, p.partition_number
from sys.partitions p,sys.tables t
where t.object_id = p.object_id
and t.name in ('ContactBase','AuditBase')
and p.index_id in (0,1)

Here’s what we saw:

So it looks as if on Enterprise we can have partitioning working, which starts to disprove the idea that it never works.

Next we turned SQL Server Profiler on while refreshing the Audit Log grid, and see that this query is executed:

 IF (CONVERT(int, (SELECT SERVERPROPERTY('EngineEdition'))) = 3) BEGIN
SELECT partition_number AS 'PartitionNumber', prv.value AS 'End Date', used_page_count*8 AS 'Size in KB'
FROM sys.dm_db_partition_statspst LEFT OUTER JOIN sys.partition_range_values prv ON prv.boundary_id = pst.partition_number
WHERE object_id = object_id('AuditBase') AND index_id in (0,1)
END ELSE BEGIN
SELECT DATENAME(qq, CreatedOn) AS 'Quarter', YEAR(CreatedOn) AS 'Year', COUNT(*) AS 'Rows'
FROM AuditBase
GROUP BY DATENAME(qq, CreatedOn), YEAR(CreatedOn)
END

Now we start to see what is really happening.  The Dynamics CRM platform is executing a query that will return results that look similar, but are critically different, based on the edition of SQL Server Database Engine that is in use.

The SERVERPROPERTY documentation tells us that the value 3 indicates Enterprise Edition.  If this is detected then the result set consists of information about each physical partition, including the storage size in kilobytes.

For other editions (such as Standard) the actual AuditBase table is queried to group the data it contains by calendar quarter, and the number of rows in each quarter is calculated.

So we can now explain why the Standard Edition gave the illusion of having partitioning in use.  The grid isn’t showing partition data, but the results of a GROUP BY query that looks very similar.

This gives us a hint that although Dynamics CRM is gathering information to be able to show a set of quarterly results in the grid in both cases there will be a subtle difference in the last column: one will be a size and the other a number of rows.  Sure enough if we compare what we see in Dynamics CRM for the Standard Edition above with what we see for Enterprise Edition we have ‘Rows’ in one and ‘KB’ in the other:

One mystery remains.  If the Dynamics CRM gives a set of rows on both Standard and Enterprise why could the customer see only a single row on their Enterprise environment, suggesting that partitioning is not in place?

Well it turns out that partitioning is indeed missing from their environment.  This had happened because a backup of an organisation database had been taken from a Standard Edition deployment and redeployed to Enterprise Edition.  We already have KB articles describing that the partition information isn’t ‘magically’ fixed when redeploying from Enterprise to Standard and so has to be removed prior to the backup on the source deployment.  See KB2567984 for example.

The opposite is also true: when moving from Standard to Enterprise the overall redeployment operation works but you don’t get partitions applied to AuditBase.  However, the EngineEdition in the query above will now be Enterprise, so you get a single row describing the non-partitioned table and not the set of ‘fake’ partition rows that you would have seen (for the same database) on Standard Edition.

The bad news here is that in turn you lose the ability to be able to delete just a portion of the audit log, and you obviously can’t delete the active storage unit.  So its important that you clear down any aged log data that you don’t want in the source Standard Edition deployment, and then potentially add the partitioning behaviour to AuditBase in the Enterprise Edition.  I haven’t found the correct way to do that last task yet, but will try to do so and add a follow-up post.

Summary

In this post we established that:

1. Table partitioning is only used on SQL Server Enterprise Edition, and separates the data into quarterly ‘chunks’ for management (i.e. deletion)
2. Dynamics CRM ‘fakes’ a similar set of data on Standard Edition using a GROUP BY clause on the unpartitioned data
3. Restoring organisation databases across SQL Server editions can lead to complications

Thanks for reading and I hope you feel more informed.  Please be sure to give your feedback on whether you like this type of content, and to ask any questions to help you and others understand the material better.