Mirroring Table Modifications

Question

I have a set of tables that are used to track bills. These tables are loaded from an SSIS process that runs weekly.

I am in the process of creating a second set of tables to track adjustments to the bills that are made via the web. Some of our clients hand key their bills and all of those entries need to be backed up on a more regular schedule (the SSIS fed data can always be imported again so it isn't backed up).

Is there a best practice for this type of behavior? I'm looking at implementing a DDL trigger that will parse the ALTER TABLE call and change the table being called. This is somewhat painful, and I'm curious if there is a better way.

Answer 1

I personally would have the SSIS-fed tables in one database (set to simple recovery mode) and the other tables in a separate database on the same server which is set to full recovery mode,. Then I would set up backups on the second datbase on a regular schedule. A typical backup schedule would be full backup once a week, differntials nightly and transaction backups every 15-30 minutes depending on how much data is being input.) Be sure to periodically test recovering the backups, learning how to do that when the customer is screaming becasue the datbase is down isn;t a good thing.

Answer 2

I ended up using a DDL trigger to make a copy of changes from one table to the other. The only problem is that if a table or column name contains part of a reserved word - ARCH for VARCHAR - it will cause problems with the modification script.

Thanks, once again, to Brent Ozar for error checking my thoughts before I blogged them.

-- Create pvt and pvtWeb as test tables
CREATE TABLE [dbo].[pvt](
   [VendorID] [int] NULL,
   [Emp1] [int] NULL,
   [Emp2] [int] NULL,
   [Emp3] [int] NULL,
   [Emp4] [int] NULL,
   [Emp5] [int] NULL
) ON [PRIMARY];
GO


CREATE TABLE [dbo].[pvtWeb](
   [VendorID] [int] NULL,
   [Emp1] [int] NULL,
   [Emp2] [int] NULL,
   [Emp3] [int] NULL,
   [Emp4] [int] NULL,
   [Emp5] [int] NULL
) ON [PRIMARY];
GO


IF EXISTS(SELECT * FROM sys.triggers WHERE name = ‘ddl_trigger_pvt_alter’)
   DROP TRIGGER ddl_trigger_pvt_alter ON DATABASE;
GO

-- Create a trigger that will trap ALTER TABLE events
CREATE TRIGGER ddl_trigger_pvt_alter
ON DATABASE
FOR ALTER_TABLE
AS
   DECLARE @data XML;
   DECLARE @tableName NVARCHAR(255);
   DECLARE @newTableName NVARCHAR(255);
   DECLARE @sql NVARCHAR(MAX);

   SET @sql = ”;
   -- Store the event in an XML variable
   SET @data = EVENTDATA();

   -- Get the name of the table that is being modified
   SELECT @tableName = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘, ‘NVARCHAR(255)’);
   -- Get the actual SQL that was executed
   SELECT @sql = @data.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘, ‘NVARCHAR(MAX)’);

   -- Figure out the name of the new table
   SET @newTableName = @tableName + ‘Web’;

   -- Replace the original table name with the new table name
   -- str_replace is from Robyn Page and Phil Factor’s delighful post on 
   -- string arrays in SQL. The other posts on string functions are indispensible
   -- to handling string input
   --
   -- http://www.simple-talk.com/sql/t-sql-programming/tsql-string-array-workbench/
   -- http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-1/
   --http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-2/
   SET @sql = dbo.str_replace(@tableName, @newTableName, @sql);

   -- Debug the SQL if needed.
   --PRINT @sql;

   IF OBJECT_ID(@newTableName, N’U’) IS NOT NULL
   BEGIN
       BEGIN TRY
           -- Now that the table name has been changed, execute the new SQL
           EXEC sp_executesql @sql;
       END TRY
       BEGIN CATCH
           -- Rollback any existing transactions and report the full nasty 
           -- error back to the user.
           IF @@TRANCOUNT > 0
               ROLLBACK TRANSACTION;

           DECLARE
               @ERROR_SEVERITY INT,
               @ERROR_STATE    INT,
               @ERROR_NUMBER   INT,
               @ERROR_LINE     INT,
               @ERROR_MESSAGE  NVARCHAR(4000);

           SELECT
               @ERROR_SEVERITY = ERROR_SEVERITY(),
               @ERROR_STATE    = ERROR_STATE(),
               @ERROR_NUMBER   = ERROR_NUMBER(),
               @ERROR_LINE     = ERROR_LINE(),
               @ERROR_MESSAGE  = ERROR_MESSAGE();

           RAISERROR(‘Msg %d, Line %d, :%s’,
               @ERROR_SEVERITY,
               @ERROR_STATE,
               @ERROR_NUMBER,
               @ERROR_LINE,
               @ERROR_MESSAGE);
       END CATCH
   END
GO




ALTER TABLE pvt
ADD test INT NULL;
GO

EXEC sp_help pvt;
GO

ALTER TABLE pvt
DROP COLUMN test;
GO

EXEC sp_help pvt;
GO