Replicate to a secondary database that also allows edits

Posted on

Question :

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!

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).

I’d suggest setting up some ETL or replication processes to run, having the primary source database as the distributor database and the accountants’ database as the subscriber. You can schedule it to sync up data at an interval of their choosing, and control what tables/fields/schemas get updated using a set of triggers and stored procedures, while leaving their edited objects untouched by creating a ‘flag’ column on all the tables of the subscriber database.

Let’s hypothetically call the flag column [is_modified]. You would write your replication scripts to only conditionally update records were the value of the column is 0 or NULL. You could write a stored procedure for users for updating; with field location specified by basic parameters, and setting the flag value to 1.

CREATE PROCEDURE AccountingDB.dbo.EditNumerical
@Table varchar(max),
@Column varchar(max),
@Quarter int,
@Year int,
@NewValue int

DECLARE @SQLQuery varchar(max)
SET @SQLQuery = 
'UPDATE ' + @Table + '
  SET ' + @Column + '= ' + @NewValue +',
  dbo.is_modified = 1,
  dbo.modified_by = ' + USER_NAME() + ',
  dbo.modified_timestmp = ' + GETDATE() + '
  WHERE dbo.Quarter = ' + @Quarter + '
  AND dbo.Year = @Year' + @Year + ''

EXEC @SQLQuery

Having the user call a stored procedure similar to above would execute the update for the specified value, but also update 3 columns to be used for both conditional replication and tracking changes. If needed, you could also build identical views off the base tables, but excluding these columns if your users prefer cleaner result-sets.

Leave a Reply

Your email address will not be published.