CRM audit log and SQL partitioning

Last half year I spent on migrating our clients from Dynamics CRM 2011 to 2015 version. That was a long and interesting journey. In some periods it was really exhausting... Trying to manage with all the issues that come up from CRM bugs and behavior nuances to our product specific problems and clients solutions on top of that. But it's finally finished.
As part of upgrade to new environment MS SQL Server version and edition were upgraded from 2008 R2 Standard to 2014 Enterprise. We were particularly interested in next features of upper edition: AlwaysOn, online index rebuilds, table and index partitioning. More details at Features Supported by the Editions of SQL Server 2014.
Auditing in CRM works a bit differently with SQL Server depending on its edition. Thus problems arise when organization database moves between SQL editions. This article explains details of audit work insides - Auditing and Table Partitions in Dynamics CRM 2011. There is KB article for problem when you can't restore organization database on standard edition if it was created on enterprise edition - "Database cannot be started in this edition of SQL Server" error when restoring a Microsoft Dynamics CRM database.
When you do restoration other way from standard to enterprise it looks like audit just works fine. But there are minor issues. 'Audit Log Management' in CRM won't show any meaningful information. Also CRM doesn't utilize partitioned for that restored organization.
I did some research on how CRM handles partitioning for AuditBase table and extracted next script: CRM-ReCreate-AuditBase-Partitioning.sql
. I put it on Gist cause cause it's too long to put it directly here. What it does is that it recreates AuditBase table with utilizing partitioning. That's it. It will drop original table and all audit history will be lost. It may be acceptable, may be not. To save history we can backup table.

select * into AuditBase_back
from AuditBase

Then run CRM-ReCreate-AuditBase-Partitioning.sql script above and restore data back.

insert into AuditBase
select [AttributeMask]
      ,[TransactionId]
      ,[Action]
      ,[ObjectId]
      ,[ObjectIdName]
      ,[UserId]
      ,[ChangeData]
      ,[CreatedOn]
      ,[Operation]
      ,[AuditId]
      ,[CallingUserId]
      ,[ObjectTypeCode] from AuditBase_back

Strangely columns had different order after recreating AuditBase table. So that's why they implicitly stated in query. They must match order in your new AuditBase table. And then get rid of backup data.

drop table AuditBase_back

Finally you can see meaningful data in audit log management:
Audit Log Management
The only small issue is that all previous audit entries will be in one first partition.

NB All information above provided as is. It isn't supported nor recommended by Microsoft. Always do backups before running any scripts.

Show Comments