How to alter the same table on 2 SQL Servers with DDL trigger

Question

I have createed an archive SQL database for some tables.
I have one production server and one archive server (the archived database only has a few tables that I archived).

Now I'm searching for an option when we deploy a new version for the production database (alter tables statements) that I can run automatically run on my archived server.

I'm trying this with DDL triggers and I have a linked server to my archived database server. But it doesn't work.
Have someone any idea how to fix this? (it can be done by de deployment tool self, so I need to fix this)

I have tried this with DDL triggers on the database.

EDIT: it has be done by 2 triggers. One for logging the statement and one for execute on the Linkedserver

trigger one:

CREATE TRIGGER [LogTrigger]
ON DATABASE
FOR 
    ALTER_TABLE
   AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO TableLog
    (
       EventVal,
       EventDate,
       ChangedBy
     )
        VALUES (
           EVENTDATA(),
           GETDATE(),
           USER
        );
END;
GO

trigger two:

ALTER TRIGGER [trgTablechanges]

ON DATABASE
FOR ALTER_TABLE
AS
      SET NOCOUNT ON
      DECLARE @xEvent XML
      DECLARE @tests nvarchar(MAX)
      SET @xEvent = eventdata()
      SET @tests = CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

     EXECUTE server.dbname.dbo.sp_executesql @tests;

GO

Answer 1

You can do it by the way of two phase commit.

For this purpose, you have to create a distributed transaction for each CREATE/ALTER/DROP statement and activate MSDTC. But there is no absolute guaranties that no trouble occurs.

Another way will be to use Service Broker to propagate DDL command from one DB to the other. This way is much more stable and performant.