Sql-server – Replicate to a secondary database that also allows edits

sql serversql-server-2012

I have a client with a somewhat odd requirement. They want to be able to take a snapshot of their sales database that their accountants can then use once it's disconnected from the live database. That makes sense. But the accountants also want to be able to make edits to historical data in the secondary database, and have those edits retained the next time they take a snapshot.

I'm at a loss for how to do this. I could enable change tracking, then go through the change-tracking tables and reapply their changes after recreating the secondary database, but that sounds like it would quickly get messy.

Could I possibly use log shipping for this? They tell me that the data they would be editing in the secondary, historical database is unlikely to be touched in the primary database. But if there have been changes to the secondary database, will I still be able to restore transaction logs?

I'm really pretty clueless on how to proceed… Any advice would be appreciated!

Best Answer

If you need bidirectional synchronization, have a look at merge replication. (Good introduction: http://www.sqlservercentral.com/stairway/72401/)

However, you question sounds like you don't want those changes to be applied to the master sales database. In that case I would setup shadow tables that take the changes and use replication to load the master tables. The logic to write to the shadow tables and read from the shadow tables if there is a row, form the master table otherwise could be placed in a view with an instead-of trigger. The following is a conceptual example of that model:

create table dbo.product-master(Id int, ...);
create table dbo.product-shadow(Id int,....);
GO
CREATE VIEW dbo.product
AS
SELECT m.Id,
       CASE WHEN s.ID is NULL THEN m.col1 ELSE s.col1 END AS col1, --show shadow row if it exists, master row otherwise
       CASE WHEN s.ID is NULL THEN m.col2 ELSE s.col2 END AS col2,
       CASE WHEN s.ID is NULL THEN m.col2 ELSE s.col3 END AS col3
FROM dbo.product-master m
LEFT JOIN dbo.product-shadow s
ON m.Id = s.Id;
GO
CREATE TRIGGER dbo.product-IUD INSTEADOF INSERT,UPDATE,DELETE
AS
BEGIN
  --use INSERTED and DELETED virtual tables and the MERGE statement to apply changes to the shadow table
END;
GO

To load the master tables you could use any kind of replication (see link above for an introduction).

Related Question