0

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!

10
  • Logshipping cannot be used in this scenario. The secondary database is either read-only or in restoring mode. So it cannot be used. I would suggest go for T-Rep. That will make sense for your case. Can you clarify on their accountants can then use once it's disconnected from the live database. ? In T-Rep, you have to take initial snapshot of the data and then all the Inserts/Updates/Deletes (can be configured what you want to replicate) gets replicated to the subscriber database.
    – Kin Shah
    Jul 25, 2013 at 21:09
  • 'Unlikely' does not equal 'no'. So they have to explain what to do if both they and the master DB want to modify the same value... Jul 25, 2013 at 21:11
  • 1
    Why can't they edit the primary copy of the database through an application? That would spare you (and themselves) a lot of trouble. Once you start writing to multiple copies of the same database, you have to worry about conflicting updates, and in many cases there's no good answer to which update should win. Jul 25, 2013 at 21:45
  • 1
    @kin one option I've used in the past, rather than using a database compare tool (which can take awhile on a large database) is to keep a "transaction" table in a separate database. Basically store every change that the accountants make on the secondary both in the database and record the statement itself. Then run the statements after each re-load. It can have some serious downsides (structure changes, large numbers of transactions etc) but it can be made to work. Jul 25, 2013 at 22:40
  • 1
    I had kind of similar requirements for the test environment for a largish e-commerce application. We needed to do tests with data as large as production, yet some data needed to be altered (for instance, email addresses of customers to a testing address so that our test emails didn't end up in the customers inbox). At the end I found it easiest to create a script doing the changes which we ran every time we replicated. This would mean to make the changes in the change script in parallel, so it might not be optimal.
    – 0xCAFEBABE
    Jul 26, 2013 at 8:15

2 Answers 2

4

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

1
  • Thanks for the advice! That would definitely solve the problem; I'll have to check on how many tables and columns I would need to apply this to. Jul 26, 2013 at 0:41
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.