3

I am investigating a reporting solution for a production database.

We currently have a production db which backs up fully at 01:00 each day. This backup is then copied using a Robocopy job to a separate SQL db which restores it.

We then report on the second database in order to keep some extensive queries away from production.

I am hoping someone can help me with my thoughts - is it possible instead of having our current setup - to copy only updated or newly added rows across incrementally (note not deleted - I need these to stay in the second database)?

Thanks in advance.

2
  • Thanks Marc, do I need to copy it over?
    – LaLa
    Jan 7 '14 at 13:18
  • As soon as four more users vote for it, it'll be moved automatically - or if a moderator sees this and uses his powers :-)
    – marc_s
    Jan 7 '14 at 13:18
4

From the way I see it, you have two pretty good options here:

  1. Replication
  2. Log Shipping

If you setup transactional replication you could have your data modifications replicated to a subscriber database (or databases).

With log shipping it essentially would be transferring transaction log backups (note, you'd need to have your database in the full recovery model to take transaction log backups). Then you could have your secondary database in standby mode, allowing for read-only traffic.

The way I would choose between these two options is the level in which you want reporting and data movement to your secondary server. Are you reporting on the entire database? In that case, I'd opt for log shipping. But if you're primary reporting on a single table or just a handful of tables and you don't necessarily want the entire database's footprint living on the secondary server, then replication would be a better option.

EDIT

As per your comments below, it looks like you don't want to transmit DELETE operations to your reporting server. In this case, Log Shipping wouldn't meet the requirements because all logged operations would be on the reporting server, including your DELETEs.

What you can do with replication is set it up so that it only replicates INSERT and UPDATE operations.

See the below screenshot. Basically when you create your publication, you can set many properties for each article. One of the options is what action to take for each operation: INSERT, UPDATE, and DELETE. All you need to do is specify to not replicate DELETE operations:

enter image description here

8
  • Hi Thomas, We currently have our production database setup in full recovery mode so we have transaction logs. How would I go about this route? Any pointers or tips would be great!
    – LaLa
    Jan 7 '14 at 13:54
  • I have just read your reply once again. The database isn't massive (circa 50Gb) but we would only want to report on around 10 tables, totaling approximately 4Gb.
    – LaLa
    Jan 7 '14 at 13:57
  • None of these options are compatible with the requirement to keep deleted rows on the secondary server. @LaLa, can you please elaborate on this requirement? It cannot possibly work with your current setup?
    – Daniel B
    Jan 7 '14 at 13:58
  • Hi Daniel, No it does not currently work, the problem is if we turn on an archiving job on the production system. We want to report on the older data before it is removed from the system, hence me struggling to find a solution.
    – LaLa
    Jan 7 '14 at 13:59
  • 1
    Ok, if you don't want DELETE operations to go to your reporting server then you can configure that in the replication publication. Basically, you can specify to just replicate INSERT and UPDATE operations. Is that what you're going after? Jan 7 '14 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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