Monday, December 30, 2013

Dynamics AX - Archiving Logging Data Per Table

If you are an administrator of Dynamics AX and/or a SQL Server DBA, you probably realize that logging data can build up over time.  With over 400 million such records in our SYSDATABASELOG, I wanted to figure out a way to archive logging data on a per table basis and not just based on a timestamp.  While Microsoft's Intelligent Data Management Framework worked okay for a while, it was quite buggy and didn't provide me with the granular control required by our business.  Therefore I authored my own logging process and recently updated the logic to allow me to archive logging data on a per-table basis.

The first thing you will want to do is create a database in the same instance as your Dynamics AX database.  This database will serve as a temporary repository for the data to be archived.  The database will contain three tables:
  • DATAARCHIVELOG - this will log each iteration of your archive process
  • DONOTARCHIVE - this table will allow you to create custom archival intervals per table
  • SYSDATABASELOG. - this table's schema should be identical to the SYSDATABASELOG in Dynamics AX
Here are the table schemas I've created:



Remember, this is just a temporary repository - having it in the same instance will help with performance, as your permanent archival database should really be on another server to conserve space.

As for the permanent database repository, you only need to script out the schema for the SYSDATABASELOG and create a database with just that table.

While Microsoft recommends only keeping three months of logging data in your production database, the business requirements may override that recommendation.  I personally keep six months of logging data.  There was a recent request to keep additional logging for a particular table and these scripts will give you that ability.

If you have tables that you want to change your retention for, you would do so as follows: find the table ID in AX (correlates to TABLE_ in SYSDATABASELOG) and enter the months (MONTHSTOKEEP) you want to adjust the retention for:


Here is the script to move the data to the temporary repository, as well as make a log entry for the activity:

DECLARE @MAXRECID BIGINT
   ,@RECORDSARCHIVED INT
   ,@RECORDSDELETED INT

INSERT  INTO AX_DB_Archive.dbo.SYSDATABASELOG
        SELECT TOP 10
                SDL.USERNAME
               ,SDL.DESCRIPTION
               ,SDL.LOGTYPE
               ,SDL.DATA
               ,SDL.TABLE_
               ,SDL.LOGRECID
               ,SDL.CREATEDDATETIME
               ,SDL.DEL_CREATEDTIME
               ,SDL.CREATEDBY
               ,SDL.CREATEDTRANSACTIONID
               ,SDL.DATAAREAID
               ,SDL.RECVERSION
               ,SDL.RECID
        FROM    AX_DB.dbo.SYSDATABASELOG SDL
                LEFT OUTER JOIN AX_DB_Archive.dbo.DONOTARCHIVE DNA ON SDL.TABLE_ = DNA.TABLEID
        WHERE   DATAAREAID = '<COMPANY>'
                AND CREATEDDATETIME < ISNULL(DATEADD(month,-MONTHSTOKEEP,GETDATE()),DATEADD(month,-6,GETDATE()))
        ORDER BY RECID

SELECT  @RECORDSARCHIVED = @@ROWCOUNT
       
SELECT  @MAXRECID = MAX(RECID)
FROM    AX_DB_Archive.dbo.SYSDATABASELOG
WHERE   DATAAREAID = '<COMPANY>'

DELETE TOP ( 10 )
        SDL
FROM    AX_DB.dbo.SYSDATABASELOG SDL
        LEFT OUTER JOIN AX_DB_Archive.dbo.DONOTARCHIVE DNA ON SDL.TABLE_ = DNA.TABLEID
WHERE   DATAAREAID = '<COMPANY>'
        AND RECID <= @MAXRECID
        AND CREATEDDATETIME < ISNULL(DATEADD(month,-MONTHSTOKEEP,GETDATE()),DATEADD(month,-6,GETDATE()))

SELECT  @RECORDSDELETED = @@ROWCOUNT 

INSERT  INTO AX_DB_Archive.dbo.DATAARCHIVELOG
        ( TABLENAME
        ,RECORDSARCHIVED
        ,RECORDSDELETED
        ,MAXRECID
        ,DATAAREAID
        )
VALUES  ( 'SYSDATABASELOG'
        ,@RECORDSARCHIVED
        ,@RECORDSDELETED
        ,@MAXRECID
        ,'<COMPANY>'
        )

Here is the script to migrate the data to the permanent repository:

DECLARE @MAXRECID_ARCHIVED BIGINT

INSERT  INTO [LINKED SERVER].AX_DB_Archive.dbo.SYSDATABASELOG
        SELECT TOP 1000
                *
        FROM    AX_DB_Archive.dbo.SYSDATABASELOG
        ORDER BY RECID

SELECT  @MAXRECID_ARCHIVED = MAX(RECID)
FROM    [LINKED SERVER].AX_DB_Archive.dbo.SYSDATABASELOG
                              
DELETE  FROM
FROM    AX_DB_Archive.dbo.SYSDATABASELOG
WHERE   RECID <= @MAXRECID_ARCHIVED




Thursday, December 12, 2013

SQL Server 2012 and the [NT SERVICE\winmgmt] account

I've been diving in to some of the finer details of SQL Server 2012 recently and one item I noticed was an account I've never seen before called [NT SERVICE\winmgmt].  By definition:

Windows Management Instrumentation (WMI) must be able to connect to the Database Engine. To support this, the per-service SID of the Windows WMI provider (NT SERVICE\winmgmt) is provisioned in the Database Engine.

Now, that's all fine and dandy - er, not really.  As Argenis Fernandez [blog | twitter] pointed out in this blog post, it's quite possible to circumvent one of these accounts and get yourself connected as a sysadmin.  Not cool.  Especially since per Microsoft, there are a very distinct set of lesser privileges that this account requires - so why are they not the default?  I'm still searching for the answer to that.


The SQL WMI provider requires the following permissions:

  • Membership in the db_ddladmin or db_owner fixed database roles in the msdb database.
  • CREATE DDL EVENT NOTIFICATION permission in the server.
  • CREATE TRACE EVENT NOTIFICATION permission in the Database Engine.
  • VIEW ANY DATABASE server-level permission.
    SQL Server setup creates a SQL WMI namespace and grants read permission to the SQL Server Agent service-SID.
If you wish to copy and paste a script into SSMS, this will reduce the permission of the [NT SERVICE\winmgmt] account to the least required.

ALTER SERVER ROLE [sysadmin] DROP MEMBER [NT SERVICE\Winmgmt]
GO

USE [msdb]
GO
CREATE USER [NT SERVICE\Winmgmt] FOR LOGIN [NT SERVICE\Winmgmt]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [NT SERVICE\Winmgmt]
GO

use [master]
GO
GRANT CREATE DDL EVENT NOTIFICATION TO [NT SERVICE\Winmgmt]
GO
GRANT CREATE TRACE EVENT NOTIFICATION TO [NT SERVICE\Winmgmt]
GO
GRANT VIEW ANY DATABASE TO [NT SERVICE\Winmgmt]
GO