locked
How to restore partitioning on SQL server Enterprise Edition RRS feed

  • Question

  • Hello,

    I suspect that my current Dynamics CRM 2011 organization database was migrated from a Standard SQL server to an Enterprise SQL server. Because, when moving from Standard to Enterprise edition, partitioning was not applied to the AuditBase table.

    The CRM server is running Update Rollup 14.

    Do you know how to restore partitioning on Audit table?

    Thank you very much.

    Thomas Canaple

    Tuesday, May 13, 2014 11:40 AM

Answers

  • This issue has been identified and fixed in rollup 7. You already have rollup 14, so not sure why is that happened.

    1. Go to Settings->System->Auditing, Check Audit Summary View and Audit Log Management, see if it is partitioned.
    2. If not, run the sql
    3. Go to Audit Log Management, Check the first partition's TO value, and set system date later than that, run iisreset in command window
    4. Create some contacts and do some CRUD operations
    5. Go to Audit Log Management, delete the first partition
    6. Go to Audit Summary View

    Before doing that, remember to backup your database.

    -------------SQL--------------

    BEGIN TRY

     BEGIN TRAN

     DECLARE @engineEdition as sql_variant
     SELECT @engineEdition = SERVERPROPERTY('EngineEdition')

     IF @engineEdition <> 3
     BEGIN
      RAISERROR 34567 'Sql Server Edition should be Enterprise to proceed.'
     END

     -- Drop existing partition function and scheme
     IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'AuditPScheme')
     BEGIN
      PRINT 'Dropping Partition Scheme AuditPScheme...'
      DROP PARTITION SCHEME [AuditPScheme]
     END

     IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'AuditPFN')
     BEGIN
      PRINT 'Dropping Partition Function AuditPFN...'
      DROP PARTITION FUNCTION [AuditPFN]  
     END

     PRINT 'Creating partition function and scheme...'
     
     -- Create partition function
     DECLARE @currentDate DATETIME;
     SET @currentDate  = GETDATE();  

     DECLARE @firstPartition DATETIME;
     DECLARE @secondPartition DATETIME;

     SET @firstPartition = DATEADD(ms, -3, (DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0))));
     SET @secondPartition = DATEADD(ms, -3, (DATEADD(qq, 2, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0)))); 
     
     CREATE PARTITION FUNCTION AuditPFN(datetime)
     AS RANGE LEFT FOR VALUES (@firstPartition,  @secondPartition)

     -- Create partition scheme
     CREATE PARTITION SCHEME AuditPScheme AS PARTITION AuditPFN ALL TO ([PRIMARY])

     PRINT 'Creating indexes on AuditBase...'
     
     -- Create all neccesary indexes on AuditBase
     -- Creating clustered index on AuditPScheme will affectively partition the table
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit_Primary' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit_Primary]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'cndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[cndx_PrimaryKey_Audit]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_ObjectId' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_ObjectId]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_UserId' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_UserId]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_ObjectTypeCode' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[fndx_ObjectTypeCode]
     END

     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_Action' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[fndx_Action]
     END

     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_SystemManaged_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_SystemManaged_Audit]
      
      UPDATE EntityIndex
      SET RecreateIndex = 1
      WHERE Name = 'ndx_SystemManaged_Audit'
     END

     CREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase]
     (
      [CreatedOn] DESC,
      [AuditId] DESC
     ) ON [PRIMARY]

     CREATE UNIQUE CLUSTERED INDEX [cndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
     (
      [CreatedOn] DESC,
      [AuditId]  DESC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
     (
      [AuditId] ASC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [ndx_ObjectId] ON [dbo].[AuditBase]
     (
      [ObjectId] ASC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [ndx_UserId] ON [dbo].[AuditBase]
     (
      [UserId] ASC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [fndx_ObjectTypeCode] ON [dbo].[AuditBase]
     (
      [ObjectTypeCode] ASC
     )
     WHERE ([ObjectTypeCode] IS NOT NULL)
     ON AuditPScheme (CreatedOn)

     COMMIT TRAN
     
     PRINT 'Partitioning of AuditBase is successfull.'
    END TRY
    BEGIN CATCH
          ROLLBACK TRAN
          PRINT 'EXECUTION FAILED: ' + ERROR_MESSAGE()
    END CATCH

    Wednesday, May 14, 2014 2:39 AM

All replies

  • Hi,

    you can remove the Audit Partition described in this article: KB2567984. Hope this helps!

    Kind regards

    Martin

    Tuesday, May 13, 2014 6:09 PM
  • This issue has been identified and fixed in rollup 7. You already have rollup 14, so not sure why is that happened.

    1. Go to Settings->System->Auditing, Check Audit Summary View and Audit Log Management, see if it is partitioned.
    2. If not, run the sql
    3. Go to Audit Log Management, Check the first partition's TO value, and set system date later than that, run iisreset in command window
    4. Create some contacts and do some CRUD operations
    5. Go to Audit Log Management, delete the first partition
    6. Go to Audit Summary View

    Before doing that, remember to backup your database.

    -------------SQL--------------

    BEGIN TRY

     BEGIN TRAN

     DECLARE @engineEdition as sql_variant
     SELECT @engineEdition = SERVERPROPERTY('EngineEdition')

     IF @engineEdition <> 3
     BEGIN
      RAISERROR 34567 'Sql Server Edition should be Enterprise to proceed.'
     END

     -- Drop existing partition function and scheme
     IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'AuditPScheme')
     BEGIN
      PRINT 'Dropping Partition Scheme AuditPScheme...'
      DROP PARTITION SCHEME [AuditPScheme]
     END

     IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'AuditPFN')
     BEGIN
      PRINT 'Dropping Partition Function AuditPFN...'
      DROP PARTITION FUNCTION [AuditPFN]  
     END

     PRINT 'Creating partition function and scheme...'
     
     -- Create partition function
     DECLARE @currentDate DATETIME;
     SET @currentDate  = GETDATE();  

     DECLARE @firstPartition DATETIME;
     DECLARE @secondPartition DATETIME;

     SET @firstPartition = DATEADD(ms, -3, (DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0))));
     SET @secondPartition = DATEADD(ms, -3, (DATEADD(qq, 2, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0)))); 
     
     CREATE PARTITION FUNCTION AuditPFN(datetime)
     AS RANGE LEFT FOR VALUES (@firstPartition,  @secondPartition)

     -- Create partition scheme
     CREATE PARTITION SCHEME AuditPScheme AS PARTITION AuditPFN ALL TO ([PRIMARY])

     PRINT 'Creating indexes on AuditBase...'
     
     -- Create all neccesary indexes on AuditBase
     -- Creating clustered index on AuditPScheme will affectively partition the table
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit_Primary' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit_Primary]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'cndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[cndx_PrimaryKey_Audit]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_ObjectId' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_ObjectId]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_UserId' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_UserId]
     END
     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_ObjectTypeCode' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[fndx_ObjectTypeCode]
     END

     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_Action' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[fndx_Action]
     END

     IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_SystemManaged_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
     BEGIN
      DROP INDEX [dbo].[AuditBase].[ndx_SystemManaged_Audit]
      
      UPDATE EntityIndex
      SET RecreateIndex = 1
      WHERE Name = 'ndx_SystemManaged_Audit'
     END

     CREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase]
     (
      [CreatedOn] DESC,
      [AuditId] DESC
     ) ON [PRIMARY]

     CREATE UNIQUE CLUSTERED INDEX [cndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
     (
      [CreatedOn] DESC,
      [AuditId]  DESC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
     (
      [AuditId] ASC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [ndx_ObjectId] ON [dbo].[AuditBase]
     (
      [ObjectId] ASC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [ndx_UserId] ON [dbo].[AuditBase]
     (
      [UserId] ASC
     ) ON AuditPScheme (CreatedOn)

     CREATE NONCLUSTERED INDEX [fndx_ObjectTypeCode] ON [dbo].[AuditBase]
     (
      [ObjectTypeCode] ASC
     )
     WHERE ([ObjectTypeCode] IS NOT NULL)
     ON AuditPScheme (CreatedOn)

     COMMIT TRAN
     
     PRINT 'Partitioning of AuditBase is successfull.'
    END TRY
    BEGIN CATCH
          ROLLBACK TRAN
          PRINT 'EXECUTION FAILED: ' + ERROR_MESSAGE()
    END CATCH

    Wednesday, May 14, 2014 2:39 AM
  • Thank you Wilson, I will try your solution.

    I think that this situation occurred before we set-up Update Rollup 7, but was detected recently.

    Wednesday, May 14, 2014 9:53 AM
  • Thank you again Wilson. It works!

    Because we authenticate using ADFS, I cannot change server time and must wait for the following partition to be created.

    Friday, May 16, 2014 8:51 AM