Close

Fout bij BAM Partioning en Opschoning

De BAM job voor het partitioneren en opschonen van de BAM data leverde in Productie de volgende fout op:

Source: Spawn a new partition Execute SQL Task
Description: Executing the query “EXEC [dbo].[bam_Metadata_SpawnPartition] N’OrderAc…” failed with the following error: “RegenerateViews_ViewDefinitionTooLong”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
End Error

Na googelen op Internet vond ik uit dat het gaat om een bug in BizTalk Server. Informatie kun je vinden in: http://support.microsoft.com/kb/2520221/en-us. Voorstel in het knowledge base artikel is het installeren van een BizTalk CU package. Omdat dit een vrij radicale oplossing is, ben ik op zoek gegaan naar een work-around.

Eerst wat achtergrond informatie. Voor het partitioneren en opschonen van de BAM database moet een SQL Server Agent job worden aangemaakt. Deze job is gek genoeg default niet aanwezig. Het proces werkt als volgt:

  • Elke keer als er een BAM EndActivity call wordt gedaan in BizTalk worden gegevens overgeheveld van de bam_<Activity>_Active en bam_<Activity>_ActiveRelationships tabel naar de bam_<Activity>_Completed en bam_<Activity>_CompletedRelationships tabel. Hierdoor blijven de Active tabellen klein, wat goed is voor de performance van BAM.
  • De Completed tabellen moeten ook klein worden gehouden. Hiervoor wordt periodiek de SQL Server Agent job BAM Partitioning and Archiving aangeroepen. Deze job roept op zijn beurt SSIS package BAM_DM_<Activity> aan. Er wordt een Completed_<guid> en CompletedRelationships_<guid> tabel aangemaakt en gegevens worden overgeheveld vanuit de Completed tabellen.
  • Om een compleet beeld te hebben in de BAM Portal, wordt niet de tabel met completed instances geraadpleegd, maar een view bam_<Activity>_Completed Instances die een UNION SELECT maakt op de Completed tabel en alle aangemaakte Completed_<guid> tabellen.
  • Om dit mogelijk te maken roept SSIS package BAM_DM_<Activity> stored procedure bam_Metadata_SpawnPartition. Deze stored procedure roept op zijn beurt weer een stored procedure bam_Metadata_RegenerateViews aan. Deze stored procedure voert een ALTER VIEW statement aan. Bijvoorbeeld:
  • ALTER VIEW dbo.[bam_MessageAudit_CompletedInstances] AS
    SELECT * FROM dbo.[bam_MessageAudit_Completed] WITH (NOLOCK)
    UNION ALL
    SELECT * FROM [dbo].[bam_MessageAudit_D25D15A0_9D0A_42E0_97CF_8DC6778D9F6F] WITH (NOLOCK)

Bij het hergenereren van de view ging het fout. Omdat het package elk uur gescheduled stond, ontstonden er zoveel Completed_<guid> tabellen dat het gegenereerde ALTER VIEW statement niet meer paste in de voorhanden varchar() velden. Dat was de root cause van de fout. Omdat het hergenereren van de view fout ging, ging de hele BAM partitionering en opschoning job fout. Hierdoor bleven de Completed tabellen steeds meer groeien. Dit leidt op termijn tot performance problemen in BizTalk en een tekort aan storage space voor de BAM Primary Import database. Een serieus probleem dus.

De oplossing is als volgt:

  • Partitioning job een keer per dag runnen i.p.v. een keer per uur.
  • Completed tabel archiveren.
  • Oorspronkelijke completed tabel truncaten.
  • Oude partitions weggooien. Merk op dat de aangemaakte partitions worden opgenomen in tabel bam_Metadata_Partitions. Behalve het weggooien van de partitions moet tabel bam_Metadata_Partitions ook worden opgeschoond.

Hieronder een stappenplan:

Stap 1:

Disable SQL Server Agent job [BAM Partitioning and Archiving].

Stap 2:

Pas scheduling van job [BAM Partitioning and Archiving] aan. Schedule een keer per dag i.p.v. een keer per uur. Zie onderstaande screenprint:

Agent

Kies in overleg met DBA een geschikt tijdstip. SQL Server Agent job moet disabled blijven.

Stap 3:

Draai SQL script om partition tables te verwijderen en tabel  [bam_Metadata_Partitions] op te schonen.

USE ErpBTAcc_BAMPrimaryImport

DECLARE @@instancesTable NVARCHAR(256)
DECLARE @@RelationshipsTable NVARCHAR(256)

DECLARE partition_cursor CURSOR local
FOR SELECT InstancesTable, RelationshipsTable
FROM [dbo].[bam_Metadata_Partitions] WHERE ActivityName = ‘MessageAudit’
ORDER BY CreationTime ASC

OPEN partition_cursor
FETCH NEXT FROM partition_cursor INTO @@instancesTable, @@RelationshipsTable
WHILE @@fetch_status = 0
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[‘ + @@instancesTable + ‘]’) AND type in (N’U’))
BEGIN
EXEC (‘DROP TABLE [dbo].[‘ + @@instancesTable + ‘]’)
EXEC (‘DROP TABLE [dbo].[‘ + @@RelationshipsTable + ‘]’)
PRINT ‘Partition ‘ + @@instancesTable + ‘ was deleted’
END

DELETE FROM [dbo].[bam_Metadata_Partitions] WHERE InstancesTable = @@instancesTable

PRINT ‘Record for ‘ + @@instancesTable + ‘ deleted from table bam_Metadata_Partitions’

FETCH NEXT FROM partition_cursor INTO @@instancesTable, @@RelationshipsTable

END

CLOSE partition_cursor
DEALLOCATE partition_cursor

Stap 4:

Maak backup tabellen voor de volgende twee tabellen:

  • bam_MessageAudit_Completed_BU
  • bam_MessageAudit_CompletedRelationships_BU

Script: bam_MessageAudit_Completed_BU

USE ErpBTAcc_BAMPrimaryImport
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[bam_MessageAudit_Completed_BU](
[RecordID] [bigint] NOT NULL,
[ActivityID] [nvarchar](128) NOT NULL,
[ReceivedAt] [datetime] NULL,
[Process] [nvarchar](50) NULL,
[EntryOrExitPoint] [nvarchar](50) NULL,
[SourceMessageID] [nvarchar](50) NULL,
[SourceMessageType] [nvarchar](50) NULL,
[BusinessEntityID] [nvarchar](50) NULL,
[MessageID] [nvarchar](50) NULL,
[LastModified] [datetime] NULL
) ON [PRIMARY] GO

Script: bam_MessageAudit_CompletedRelationships_BU

USE [ErpBTAcc_BAMPrimaryImport] GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[bam_MessageAudit_CompletedRelationships_BU](
[RecordID] [bigint] NULL,
[ActivityID] [nvarchar](128) NULL,
[ReferenceName] [nvarchar](128) NULL,
[ReferenceData] [nvarchar](128) NULL,
[ReferenceType] [nvarchar](128) NOT NULL,
[LongReferenceData] [ntext] NULL,
[ReferenceDataExtend] [nvarchar](896) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

Stap 5:

Backup the completed tabellen (data veilig stellen):

  • bam_MessageAudit_Completed_BU
  • bam_MessageAudit_CompletedRelationships_BU
  • Let op: truncate script meteen na backup uitvoeren, anders raak je door truncate mogelijk gegevens kwijt.

Script 1:

USE ErpBTAcc_BAMPrimaryImport

INSERT INTO bam_MessageAudit_CompletedRelationships_BU ([RecordID], [ActivityID], [ReferenceName], [ReferenceData], [ReferenceType], [LongReferenceData],[ReferenceDataExtend])
SELECT * FROM bam_MessageAudit_CompletedRelationships

Script 2:

USE ErpBTAcc_BAMPrimaryImport

INSERT INTO bam_MessageAudit_Completed_BU ([RecordID], [ActivityID], [ReceivedAt],[Process],[EntryOrExitPoint],[SourceMessageID],[SourceMessageType],[BusinessEntityID],[MessageID],[LastModified])
SELECT * FROM bam_MessageAudit_Completed

Stap 6:

Truncate (na backup) the completed tabellen:

  • bam_MessageAudit_Completed
  • bam_MessageAudit_CompletedRelationships

Script:

USE ErpBTAcc_BAMPrimaryImport

TRUNCATE TABLE bam_MessageAudit_CompletedRelationships
TRUNCATE TABLE bam_MessageAudit_Completed

Stap 7:

Voer de volgende controles uit:

  • Tabel [bam_Metadata_Partitions] is leeg

Select * FROM [dbo].[bam_Metadata_Partitions] WHERE ActivityName = ‘MessageAudit’

  • Partition tables zijn verwijderd.
  • De backup tabellen zijn gevuld.

Stap 8:

Run handmatig job [BAM Partitioning and Archiving]. Als de job een positief resultaat heeft:

Enable job [BAM Partitioning and Archiving].

Backup plan:

Mocht bovenstaande procedure niet goed werken, dan kan het BAM profiel opnieuw worden gedeployed. Er zijn backups van de *_Completed tabellen, dus er gaat geen info verloren.

  1. bm.exe remove-all -DefinitionFile:”C:Program Files (x86)Rfs.Integration.Common.BAM for BizTalk1.0BAMMessageAudit.xml”
  2. bm.exe deploy-all -DefinitionFile:”C:Program Files (x86)Rfs.Integration.Common.BAM for BizTalk1.0BAMMessageAudit.xml”
  3. Check via: bm.exe get-views of view MessageAudit is aangemaakt.
  4. Geef BizTalk administrators rechten op de view:

bm.exe add-account -AccountName:” WEHKAMPERP BT BizTalk Server Administrators acc” -View: Audit