Rocket Matter Migrations: Temporary Database Modifications

Rocket Matter Migrations: Temporary Database Modifications

Prior to the Migration

Prior to performing a migration, the following temporary indexes should be created:

  1. CREATE INDEX [__TO_DELETE_IX_Activities_MatterId_DeletedMatterId_InvoiceId_DateAndTime_C09AF] ON [Activities] ([MatterId], [DeletedMatterId], [InvoiceId],[DateAndTime])
  2. CREATE INDEX [__TO_DELETE_IX_Activities_MatterId_DeletedMatterId_InvoiceId_DateAndTime_FDEF4] ON [Activities] ([MatterId], [DeletedMatterId], [InvoiceId],[DateAndTime]) INCLUDE ([UserId], [BillableUnits], [ActivityTypeId], [Cost], [IsCredit], [IsLineItemOverride], [NonBillable], [IsTaxable])
  3. CREATE INDEX [__TO_DELETE_IX_Activities_DeletedMatterId_InvoiceId_MatterId_DateAndTime_42704] ON [Activities] ([DeletedMatterId], [InvoiceId],[MatterId], [DateAndTime])
  4. CREATE INDEX [__TO_DELETE_IX_Activities_CreatedDate_165B5] ON [Activities] ([CreatedDate])
  5. CREATE INDEX [__TO_DELETE_IX_Activities_DateAndTime_5E6C1] ON [Activities] ([DateAndTime]) INCLUDE ([UserId], [BillableUnits], [NonBillable])
  6. CREATE INDEX [__TO_DELETE_IX_CalendarEntriesUsers_ID_3EEB4] ON [CalendarEntriesUsers] ([ID])
  7. CREATE INDEX [__TO_DELETE_IX_CalendarEntriesUsers_BillForParticipant_C2457] ON [CalendarEntriesUsers] ([BillForParticipant]) INCLUDE ([NonBillableForParticipant])
  8. CREATE INDEX [__TO_DELETE_IX_IntegrationSyncItems_EntityNameId_EntityIntegrationId_5DF30] ON [IntegrationSyncItems] ([EntityNameId], [EntityIntegrationId]) INCLUDE ([EntityId], [UserIntegrationId], [DateCreated], [DateModified], [IsDeleted], [TenantId], [ChildSyncItemId])
  9. CREATE INDEX [__TO_DELETE_IX_IntegrationSyncItemSkippedAttendees_IntegrationSyncItemId_068CB] ON [IntegrationSyncItemSkippedAttendees] ([IntegrationSyncItemId])
  10. CREATE INDEX [__TO_DELETE_IX_PaymentsInvoices_AmountPaid_88346] ON [PaymentsInvoices] ([AmountPaid])
  11. CREATE INDEX [__TO_DELETE_IX_LedgerEntries_Amount_IsPaid_InvoiceId_0A67C] ON [LedgerEntries] ([Amount], [IsPaid],[InvoiceId])
  12. CREATE INDEX [__TO_DELETE_IX_LedgerEntries_IsCredit_8C111] ON [LedgerEntries] ([IsCredit]) INCLUDE ([Amount], [MatterId])
  13. CREATE INDEX [__TO_DELETE_IX_Activities_DeletedMatterId_InvoiceId_MatterId_DateAndTime_F5FD7] ON [Activities] ([DeletedMatterId], [InvoiceId],[MatterId], [DateAndTime]) INCLUDE ([UserId], [BillableUnits], [ActivityTypeId], [Cost], [IsCredit], [IsLineItemOverride], [NonBillable], [IsTaxable])
The following triggers should be disabled:
  1. DISABLE TRIGGER CalendarEntries_Tag_Update ON CalendarEntries
  2. DISABLE TRIGGER Activities_Tag_Update ON Activities
  3. DISABLE TRIGGER Documents_Tag_Update ON Documents

After the Migration

Delete the temporary indexes:

  1. SELECT
  2.     schema_name(t.schema_id) + '.' + t.[name] AS table_view,
  3.     i.[name] AS index_name,
  4.     FORMATMESSAGE('DROP INDEX %s.%s', t.name, i.name) AS drop_statement
  5. FROM 
  6.     sys.objects t
  7. INNER JOIN 
  8.     sys.indexes i ON t.object_id = i.object_id
  9. CROSS APPLY (
  10.     SELECT col.[name] + ', '
  11.     FROM sys.index_columns ic
  12.     INNER JOIN sys.columns col ON ic.object_id = col.object_id
  13.     AND ic.column_id = col.column_id
  14.     WHERE ic.object_id = t.object_id
  15.     AND ic.index_id = i.index_id
  16.     ORDER BY col.column_id
  17.     FOR XML PATH ('')) D (column_names)
  18. WHERE
  19.     t.is_ms_shipped <> 1
  20.     AND i.name LIKE '__TO_DELETE%'
  21.     AND index_id > 0
  22. ORDER BY 
  23.     schema_name(t.schema_id) + '.' + t.[name],
  24.     i.index_id

Re-Enable the relevant triggers

  1. ENABLE Trigger Activities_Tag_Update ON Activities
  2. ENABLE Trigger CalendarEntries_Tag_Update ON CalendarEntries
  3. ENABLE TRIGGER Documents_Tag_Update ON Documents

Batch-Update the activities.

  1. -- Update the Matters table with recent activity, pending invoicing, current balance, and pending taxes
  2. UPDATE m
  3. SET
  4.     m.LastActivityDate = mra.RecentActivityDate,
  5.     m.PendingInvoicing = ISNULL(p.Amount + p.TaxAmount, 0),
  6.     m.CurrentBalance = ISNULL(mgb.Balance, 0),
  7.     m.PendingTaxes = ISNULL(p.TaxAmount, 0)
  8. FROM 
  9.     Matters m
  10. LEFT JOIN 
  11.     Matters_RecentActivity mra ON m.Id = mra.MatterId
  12. LEFT JOIN 
  13.     Activities_PendingInvoicingByMatter p ON m.Id = p.MatterId
  14. LEFT JOIN 
  15.     Matters_GetWithBalances mgb ON m.Id = mgb.MatterId;

  17. -- Update the Clients table with trust balance
  18. UPDATE cl
  19. SET
  20.     cl.TrustBalance = ISNULL(t.Balance, 0)
  21. FROM 
  22.     Clients cl
  23. LEFT JOIN 
  24.     ClientTrustAccountBalances t ON cl.Id = t.ClientId;