0

I apologize for the verbosity of this post.

To catch DDL changes after a snapshot or full load (using aws/dms here) has been performed, there is sourceDB.tblAWSObjects which replicates to targetDB.tblAWSObjects.

In the SourceDB , a database DDL Trigger creates records in the tblAWSObjects for ALTER/CREATE/DELETE on triggers, views and functions.

  1. There are 10 records in sourceDB.tblAWSObjects.

  2. There are 0 records in targetDB.tblAWSObjects

  3. Replication starts as DROP/CREATE and target table targetDB.tblAWSObjects is dropped/created.

  4. A Database Trigger Fires FOR CREATE_TABLE on targetDB.

    a. This trigger checks if the table is tblAWSObjectsand creates a table trigger.

    b. Trigger contents

      SET @cmd = 
         'CREATE TRIGGER LoadAWSObjectsControl
         ON dbo.tblAWSObjects
         AFTER INSERT   
         AS  
         BEGIN
    
         DECLARE @ID INT = (SELECT ID FROM inserted)
    
         BEGIN TRY
             INSERT INTO _Test2([Count],[Message])VALUES(0, ''BEFORE tblAWSObject INSERT'')
    
             INSERT INTO [tblAWSObjectsControl]([ID],[Command],[PostTime],[HostName],[LoginName],[DBName],aws_record_datetime,IsApplied)
             SELECT
                 i.[ID], i.[Command],i.[PostTime],i.[HostName],i.[LoginName],i.[DBName],GETDATE(), 0 AS IsApplied
             FROM
                 Inserted i
                 LEFT OUTER JOIN tblAWSObjectsControl c ON i.ID = c.ID
             WHERE
                 c.ID IS NULL
    
         END TRY
         BEGIN CATCH
             --@@TRANCOUNT = 2 HERE
             INSERT INTO _Test2([Count],[Message])VALUES(@ID, ''IN tblAWSObject CATCH'')
         END CATCH
    
         INSERT INTO _Test2([Count],[Message])VALUES(@ID, ''AFTER tblAWSObject INSERT'')
    
         END
         '
    
         EXEC (@cmd) WITH RESULT SETS NONE;
    
  5. There is a non-replicated table named tblAWSObjectsControl with a trigger.

    ALTER TRIGGER [dbo].[LoadAWSObjects]
    ON [dbo].[tblAWSObjectsControl]
    AFTER INSERT   
    AS  
    BEGIN
     DECLARE @Command NVARCHAR(MAX)
    
     SELECT @Command = Command from inserted
    
     INSERT INTO _Test2([Count],[Message])VALUES(0, 'BEFORE tblAWSObjectsControl COMMAND')
    
     BEGIN TRY               
         EXECUTE (@Command) WITH RESULT SETS NONE -- Everything is fine when this is commented. 
           --When an error occurs nothing is replicated 
           --and only trace messages for the one exception, which resides in record 7 out of 10 records, gets saved to _TestTable and tblAWSObjectsErrorLog.
     END TRY
     BEGIN CATCH     
         DECLARE @Trig_Error TABLE([ErrorNumber] [int] NULL, [ErrorState] [int] NULL,[ErrorSeverity] [int] NULL, [ErrorProcedure] [nvarchar](128) NULL,  [ErrorLine] [int] NULL, [ErrorMessage] [nvarchar](4000) NULL,[ErrorDateTime] [datetime] NOT NULL);
    
         INSERT INTO @Trig_Error([ErrorNumber],[ErrorState],[ErrorSeverity],[ErrorProcedure],[ErrorLine],[ErrorMessage],[ErrorDateTime]) 
         SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_STATE() AS ErrorState,ERROR_SEVERITY() AS ErrorSeverity,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage,getdate() as ErrorDateTime
    
         ROLLBACK TRANSACTION -- If this is commented out no messages are saved to any table, including _Table2 debug.
    
         INSERT INTO tblAWSObjectsErrorLog(ErrorNumber,ErrorState,ErrorSeverity,ErrorProcedure,ErrorLine,ErrorMessage,ErrorDateTime)
         SELECT [ErrorNumber],[ErrorState],[ErrorSeverity],[ErrorProcedure],[ErrorLine],[ErrorMessage], [ErrorDateTime]
         FROM
             @Trig_Error
    
     END CATCH
     INSERT INTO _Test2([Count],[Message])VALUES(0, 'AFTER tblAWSObjectsControl COMMAND')
     /*
     The WITH RESULT SETS NONE is required in order for the system stored procedure 
     sp_describe_first_result_set to not return an error
    */
    END;  
    

In a nutshell.

  • Database Trigger Creates a Trigger when Replicated table tblAWSObjects gets created.
  • The new trigger inserts records into tblAWSObjectControl, only if it does not already exist by ID, see LEFT JOIN.
  • tblAWSObjectControl, which only exists at the Target has a trigger to EXEC the command when a new record is created.

Now, 10 records get replicated, not sure the internals used of AWS/Data Migration Task; however, I do know that they are never sent in batch.

An exception occurs in EXEC(@Command). Instead of 10 records coming over with one error in the error log, no records are saved in tblAWSObject, no records are saved in tblAWSControl and only the debug trace that occurred after the first and only exception is saved.

_Test2(Debug)

ID Message
0 tblAWSObjects trigger CREATED
0 AFTER tblAWSObjectsControl COMMAND
7 IN tblAWSObject CATCH
7 AFTER tblAWSObject INSERT

tblAWSObjectsErrorLog

ErrorMessage
Invalid object name 'dbo.xx'.

tblAWSObjects (empty)

tblAWSObjectsControl (empty)

EDIT :

It appears that AWS/DMS is using prepared statements for the inserts. The first 7/10 records I can see in the trace as:

exec sp_execute 1,1040,N'CREATE FUNCTION...'...
exec sp_execute 1,1041,N'CREATE FUNCTION...'...
exec sp_execute 1,1044,N'DROP VIEW...'...
exec sp_execute 1,1059,N'CREATE FUNCTION...'...
exec sp_execute 1,1060,N'CREATE FUNCTION...'...
exec sp_execute 1,1061,N'CREATE PROCEDURE...'...
exec sp_execute 1,1062,N'DROP PROCEDURE...'...
<--STOPS HERE-->

The prepared statements should be 10, however, they stop being sent after the 7th, the one resulting in EXEC failure. In all my wisdom, I would think that the TRY CATCH would not propagate an exception back to the AWS/DMS client, however, it still stops after 7 and nothing gets saved to the target table, not even the first 7. I am pretty sure there is some reconciliation process that I am missing.

EDIT:

If I change the block in LoadAWSObjects from:

BEGIN TRY               
    EXECUTE (@Command) WITH RESULT SETS NONE
END TRY

To:

BEGIN TRY               
    --EXECUTE (@Command) WITH RESULT SETS NONE
    DECLARE @X INT
    SET @X = 1 / 0
END TRY

Everything works as expected. The errors are logged and both tables are in synced.

4
  • To clarify on try/catch/exceptions inside of triggers. If the code inside of a trigger throws an exception then it's game over; regardless of how you wrap the inner logic inside of a try/catch block. Jul 6, 2023 at 12:24
  • 1
    @JonathanFite I am seeing this now. I came across this post --> dba.stackexchange.com/questions/8693/…. It appears that a risky workaround is to COMMIT TRANSACTION as the first statement in ddl trigger, do risky work in TRY and at the very end of trigger BEGIN TRANSACTION. Doing that gets both records into the desired table, however, the errors don't get logged to tblAWSObjectsError. NOTE: There are no other triggers involved.
    – Ross Bush
    Jul 6, 2023 at 14:25
  • @JonathanFite - I am leaning towards creating a SQL Job that will periodically attempt to run unapplied commands. At least then I know I can log errors.
    – Ross Bush
    Jul 6, 2023 at 15:04
  • Thanks for finding that answer, it's the first one I've seen that lets it happen, but I do agree with the poster on that it is incredibly risky. Jul 6, 2023 at 18:37

0