Dynamics AX 2012 and SQL Server 2012 AlwaysOn Availability Groups
JasonLar
21
Like
Share
ReportWhen setting up Microsoft SQL Server 2012 AlwaysOn Availability Groups with Microsoft Dynamics AX 2012 you may run into an issue when attempt to enable the “Partial Containment” option on the Dynamics AX production database, which is a pre-requisite for AlwaysOn.
Written by Michael DeVoe
When you attempt to enable “Partial Containment” on the Dynamics AX 2012 production database you may receive the error message;
Whether this is a SQL Server 2012 Bug or working as designed is a bit of a debate, but there is a simple workaround to bypass this error message and allow Partial Containment on the AX 2012 production database. We just need to make a small modification to the AX 2012 SQL Stored Procedure SP_ConfigureTablesForChangeTracking.
| Old Code - SP_ConfigureTablesForChangeTracking |
| SELECT DISTINCT DatabaseTables.NAME, case when ChangeTrackingTables.object_Id is null then 0 else 1 end, CASE WHEN EnabledTables.PHYSICALTABLENAME IS NULL THEN 0 ELSE 1 END
FROM SYS.TABLES DatabaseTables LEFT OUTER JOIN AIFSQLCDCENABLEDTABLES EnabledTables ON DatabaseTables.NAME = EnabledTables.PHYSICALTABLENAME LEFT OUTER JOIN sys.change_tracking_tables ChangeTrackingTables on DatabaseTables.object_id = ChangeTrackingTables.object_id |
| New Code - SP_ConfigureTablesForChangeTracking |
| SELECT DISTINCT DatabaseTables.NAME, case when ChangeTrackingTables.object_Id is null then 0 else 1 end, CASE WHEN EnabledTables.PHYSICALTABLENAME IS NULL THEN 0 ELSE 1 END
FROM SYS.TABLES DatabaseTables LEFT OUTER JOIN AIFSQLCDCENABLEDTABLES EnabledTables ON CAST(DatabaseTables.NAME as nvarchar(128)) COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(EnabledTables.PHYSICALTABLENAME as nvarchar(128)) COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN sys.change_tracking_tables ChangeTrackingTables on DatabaseTables.object_id = ChangeTrackingTables.object_id |
Once this change has been made and the new stored procedure is saved, you will be able to enable “Partial Containment” on the AX 2012 database, which will allow you to set up AlwaysOn Availability Groups.
*This post is locked for comments