I have a production database and an archive database in a second SQL Server instance.
When I insert or update (NOT DELETE) data in the production database, I need to insert or update the same data in the archive database.
What is the good way for do that?
Thanks
If they are in the same db instance, a trigger would be trivial assuming it's not a lot of tables.
If the size of this grows, you'll probably want to look into SQL Server replication. Microsoft has spent a lot of time and money to do it right.
If you are considering using triggers for this, then you may want to take into account the load sizes for your production database. If it is very intensive database, consider using some high availability solution such as Replication or Mirroring or Log shipping. Depending on your needs, either of the solution could serve you right. Also at the same time, you should consider your "cold" recovery solutions which would need to be changed in accordance to what you implement.
Replication will replicate your deletions as well. However, not deleting the deletions from your archive database may cause problems down the line on unique indexes, where a value is valid in the production database but not valid in the archive database because the values already exist there. If your design means that this is not an issue, then a simple trigger in the production table will do this for you:
CREATE TRIGGER TR_MyTable_ToArchive ON MyTable FOR INSERT, UPDATE AS
BEGIN
SET ROW_COUNT OFF
-- First inserts
SET IDENTITY_INSERT ArchiveDB..MyTable ON -- Only if identity column is used
INSERT INTO ArchiveDB..MyTable(MyTableKey, Col1, Col2, Col3, ...)
SELECT MyTableKey, Col1, Col2, Col3, ...
FROM inserted i LEFT JOIN deleted d ON i.MyTableKey = d.MyTableKey
WHERE d.MyTableKey IS NULL
SET IDENTITY_INSERT ArchiveDB..MyTable OFF -- Only if identity column is used
-- then updates
UPDATE t SET Col1 = i.col1, col2 = i.col2, col3 = i.col3, ...
FROM ArchiveDB..MyTable t INNER JOIN inserted i ON t.MyTableKey = i.MyTableKey
INNER JOIN deleted d ON i.MyTableKey = d.MyTableKey
END
This assumes that your archive database resides on the same server as your production database. If this is not the case, you'll need to create a linked server entry, and then replace ArchiveDB..MyTable with ArchiveServer.ArchiveDB..MyTable, where ArchiveServer is the name of the linked server.
If there is a lot of load on your production database already, however, bear in mind that this will double it. To circumvent this, you can add an update flag field in each of your tables, and run a scheduled task at a time when the database load is at a minimum, like 1am. Your trigger would then set the field to I for an insert or U for an update in the production database, and the scheduled task would perform then update or insert in the archive database, depending on the value of this field, and then reset the field to NULL once it has finished.